Apache Poi Help to Read Excel File ?

Excel files (spreadsheets) are widely used by people all over the world for various tasks related to arrangement, assay, and storage of tabular information.

Since excel files are then mutual, we developers often encounter employ-cases when we need to read data from an excel file or generate a report in excel format.

In this commodity, I'll show yous how to read excel files in Java using a very simple still powerful open source library called Apache POI.

And in the next article, Yous'll learn how to create and write to an excel file using Apache POI.

Let's get started!

Dependencies

First of all, We need to add together the required dependencies for including Apache POI in our projection. If y'all utilise maven, you need to add together the following dependencies to your pom.xml file -

Maven

                          <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->                                                <dependency                >                                                              <groupId                >              org.apache.poi                                  </groupId                >                                                              <artifactId                >              poi                                  </artifactId                >                                                              <version                >              3.17                                  </version                >                                                              </dependency                >                            <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->                                                <dependency                >                                                              <groupId                >              org.apache.poi                                  </groupId                >                                                              <artifactId                >              poi-ooxml                                  </artifactId                >                                                              <version                >              3.17                                  </version                >                                                              </dependency                >                                    

Gradle

If you lot use gradle and so you can add together the following to your build.gradle file

            compile              "org.apache.poi:poi:3.17"              compile              "org.apache.poi:poi-ooxml:3.17"                      

The kickoff dependency poi is used to piece of work with the old Microsoft's binary file format for excel. These file formats take .xls extension.

The second dependency poi-ooxml is used to piece of work with the newer XML based file format. These file formats have .xlsx extension.

Sample Excel file that Nosotros'll read

Following is a sample excel file that nosotros'll read in our code. It is created using Google Sheets and has .xlsx extension.

Note that, Although the sample file is of the newer XML based file format (.xlsx). The code that we'll write will work with both types of file formats - .xls and .xlsx

Apache POI terminologies

Apache POI excel library revolves around following four key interfaces -

1. Workbook: A workbook is the loftier-level representation of a Spreadsheet.

2. Sheet: A workbook may contain many sheets. The sample excel file that we looked at in the previous department has ii sheets - Employee and Department

3. Row: Equally the name suggests, Information technology represents a row in the spreadsheet.

4. Cell: A cell represents a column in the spreadsheet.

HSSF and XSSF implementations -

Apache POI library consists of 2 different implementations for all the above interfaces.

1. HSSF (Horrible SpreadSheet Format): HSSF implementations of POI's high-level interfaces like HSSFWorkbook, HSSFSheet, HSSFRow and HSSFCell are used to work with excel files of the older binary file format - .xls

2. XSSF (XML SpreadSheet Format): XSSF implementations are used to work with the newer XML based file format - .xlsx.

Program to Read an excel file using Apache POI

The post-obit program shows you how to read an excel file using Apache POI. Since we're not using whatsoever file format specific POI classes, the program volition work for both types of file formats - .xls and .xlsx.

The program shows three unlike ways of iterating over sheets, rows, and columns in the excel file -

                          import              org.apache.poi.openxml4j.exceptions.                            InvalidFormatException              ;              import              org.apache.poi.ss.usermodel.                            *              ;              import              java.io.                            File              ;              import              java.io.                            IOException              ;              import              java.util.                            Iterator              ;              public              class              ExcelReader              {              public              static              final              String              SAMPLE_XLSX_FILE_PATH              =              "./sample-xlsx-file.xlsx"              ;              public              static              void              main              (              String              [              ]              args)              throws              IOException              ,              InvalidFormatException              {              // Creating a Workbook from an Excel file (.xls or .xlsx)              Workbook              workbook              =              WorkbookFactory              .              create              (              new              File              (SAMPLE_XLSX_FILE_PATH)              )              ;              // Retrieving the number of sheets in the Workbook              System              .out.              println              (              "Workbook has "              +              workbook.              getNumberOfSheets              (              )              +              " Sheets : "              )              ;              /*            =============================================================            Iterating over all the sheets in the workbook (Multiple ways)            =============================================================         */              // one. Y'all can obtain a sheetIterator and iterate over it              Iterator                              <                Sheet                >                            sheetIterator              =              workbook.              sheetIterator              (              )              ;              System              .out.              println              (              "Retrieving Sheets using Iterator"              )              ;              while              (sheetIterator.              hasNext              (              )              )              {              Sheet              sheet              =              sheetIterator.              next              (              )              ;              System              .out.              println              (              "=> "              +              sheet.              getSheetName              (              )              )              ;              }              // 2. Or yous can apply a for-each loop              System              .out.              println              (              "Retrieving Sheets using for-each loop"              )              ;              for              (              Sheet              sheet:              workbook)              {              System              .out.              println              (              "=> "              +              sheet.              getSheetName              (              )              )              ;              }              // three. Or you tin utilise a Java 8 forEach with lambda              System              .out.              println              (              "Retrieving Sheets using Java eight forEach with lambda"              )              ;              workbook.              forEach              (sheet              ->              {              System              .out.              println              (              "=> "              +              sheet.              getSheetName              (              )              )              ;              }              )              ;              /*            ==================================================================            Iterating over all the rows and columns in a Sheet (Multiple ways)            ==================================================================         */              // Getting the Sheet at alphabetize zero              Sheet              canvass              =              workbook.              getSheetAt              (              0              )              ;              // Create a DataFormatter to format and get each cell's value as String              DataFormatter              dataFormatter              =              new              DataFormatter              (              )              ;              // 1. You can obtain a rowIterator and columnIterator and iterate over them              Arrangement              .out.              println              (              "\n\nIterating over Rows and Columns using Iterator\n"              )              ;              Iterator                              <                Row                >                            rowIterator              =              sheet.              rowIterator              (              )              ;              while              (rowIterator.              hasNext              (              )              )              {              Row              row              =              rowIterator.              next              (              )              ;              // Now let'due south iterate over the columns of the electric current row              Iterator                              <                Cell                >                            cellIterator              =              row.              cellIterator              (              )              ;              while              (cellIterator.              hasNext              (              )              )              {              Prison cell              cell              =              cellIterator.              next              (              )              ;              String              cellValue              =              dataFormatter.              formatCellValue              (prison cell)              ;              System              .out.              print              (cellValue              +              "\t"              )              ;              }              Arrangement              .out.              println              (              )              ;              }              // 2. Or you can use a for-each loop to iterate over the rows and columns              System              .out.              println              (              "\n\nIterating over Rows and Columns using for-each loop\northward"              )              ;              for              (              Row              row:              canvass)              {              for              (              Cell              jail cell:              row)              {              Cord              cellValue              =              dataFormatter.              formatCellValue              (jail cell)              ;              Organization              .out.              print              (cellValue              +              "\t"              )              ;              }              System              .out.              println              (              )              ;              }              // 3. Or you tin use Coffee 8 forEach loop with lambda              System              .out.              println              (              "\n\nIterating over Rows and Columns using Java viii forEach with lambda\northward"              )              ;              sheet.              forEach              (row              ->              {              row.              forEach              (cell              ->              {              String              cellValue              =              dataFormatter.              formatCellValue              (cell)              ;              System              .out.              print              (cellValue              +              "\t"              )              ;              }              )              ;              Organization              .out.              println              (              )              ;              }              )              ;              // Closing the workbook              workbook.              close              (              )              ;              }              }                      

Note that we're not fifty-fifty using the concrete classes like HSSFWorkbook and XSSFWorkbook to create an instance of the Workbook. We're creating the workbook using a WorkbookFactory instead. This makes our program format independent and it works for both types of files - .xls and .xlsx.

The program shows three different ways to iterate over sheets, rows, and columns. I prefer the Java 8 forEach loop with a lambda expression. You may use whichever method you like.

Note that, I've used a DataFormatter to format and get each prison cell's value as String.

Retrieving Cell values by CellType

Instead of using a DataFormatter to format and get each prison cell's value as Cord regardless of the Prison cell blazon, You may check each cell's type and then remember its value using various type-specific methods similar this -

                          private              static              void              printCellValue              (              Prison cell              cell)              {              switch              (cell.              getCellTypeEnum              (              )              )              {              case              BOOLEAN:              Arrangement              .out.              print              (cell.              getBooleanCellValue              (              )              )              ;              break              ;              example              STRING:              Organisation              .out.              print              (cell.              getRichStringCellValue              (              )              .              getString              (              )              )              ;              break              ;              instance              NUMERIC:              if              (              DateUtil              .              isCellDateFormatted              (prison cell)              )              {              Organization              .out.              impress              (prison cell.              getDateCellValue              (              )              )              ;              }              else              {              Arrangement              .out.              print              (prison cell.              getNumericCellValue              (              )              )              ;              }              pause              ;              instance              FORMULA:              Organization              .out.              print              (cell.              getCellFormula              (              )              )              ;              break              ;              case              Bare:              Organisation              .out.              print              (              ""              )              ;              break              ;              default              :              Arrangement              .out.              print              (              ""              )              ;              }              System              .out.              print              (              "\t"              )              ;              }                      

You may now call the above method in the chief plan to impress each jail cell's value -

            sheet.              forEach              (row              ->              {              row.              forEach              (cell              ->              {              printCellValue              (cell)              ;              }              )              ;              System              .out.              println              (              )              ;              }              )              ;                      

Conclusion

That's all folks! In this article, You learned how to read excel files in Java using Apache POI library. You tin can observe the entire source code on the github repository.

Also, Don't forget to check out the next article to learn how to create and write to an excel file using Apache POI

Thank you for reading. Until next time!

hodgesherach.blogspot.com

Source: https://www.callicoder.com/java-read-excel-file-apache-poi/

Share this post