Read Excel File Using Apache Poi and Add to

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

Since excel files are so common, we developers often run into use-cases when we need to read data from an excel file or generate a study in excel format.

In this article, I'll evidence y'all how to read excel files in Coffee using a very unproblematic nevertheless powerful open source library called Apache POI.

And in the adjacent commodity, You'll learn how to create and write to an excel file using Apache POI.

Allow's go started!

Dependencies

First of all, We need to add the required dependencies for including Apache POI in our project. If you use maven, you demand to add 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                >              iii.17                                  </version                >                                                              </dependency                >                            <!-- https://mvnrepository.com/antiquity/org.apache.poi/poi-ooxml -->                                                <dependency                >                                                              <groupId                >              org.apache.poi                                  </groupId                >                                                              <artifactId                >              poi-ooxml                                  </artifactId                >                                                              <version                >              iii.17                                  </version                >                                                              </dependency                >                                    

Gradle

If you lot apply gradle then you lot can add the following to your build.gradle file

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

The first dependency poi is used to work with the old Microsoft'southward binary file format for excel. These file formats have .xls extension.

The 2d dependency poi-ooxml is used to work with the newer XML based file format. These file formats accept .xlsx extension.

Sample Excel file that Nosotros'll read

Following is a sample excel file that we'll read in our lawmaking. Information technology 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 post-obit four central interfaces -

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

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

3. Row: Every bit the name suggests, It 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 two different implementations for all the above interfaces.

1. HSSF (Horrible SpreadSheet Format): HSSF implementations of POI's high-level interfaces similar 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 following program shows you lot how to read an excel file using Apache POI. Since we're not using any file format specific POI classes, the program will work for both types of file formats - .xls and .xlsx.

The program shows three different 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              concluding              Cord              SAMPLE_XLSX_FILE_PATH              =              "./sample-xlsx-file.xlsx"              ;              public              static              void              primary              (              Cord              [              ]              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              Organization              .out.              println              (              "Workbook has "              +              workbook.              getNumberOfSheets              (              )              +              " Sheets : "              )              ;              /*            =============================================================            Iterating over all the sheets in the workbook (Multiple ways)            =============================================================         */              // 1. Y'all can obtain a sheetIterator and iterate over information technology              Iterator                              <                Sheet                >                            sheetIterator              =              workbook.              sheetIterator              (              )              ;              Organisation              .out.              println              (              "Retrieving Sheets using Iterator"              )              ;              while              (sheetIterator.              hasNext              (              )              )              {              Sheet              sheet              =              sheetIterator.              adjacent              (              )              ;              System              .out.              println              (              "=> "              +              sheet.              getSheetName              (              )              )              ;              }              // 2. Or you can use a for-each loop              Organisation              .out.              println              (              "Retrieving Sheets using for-each loop"              )              ;              for              (              Sheet              canvass:              workbook)              {              System              .out.              println              (              "=> "              +              canvass.              getSheetName              (              )              )              ;              }              // iii. Or you can use a Java 8 forEach with lambda              System              .out.              println              (              "Retrieving Sheets using Java 8 forEach with lambda"              )              ;              workbook.              forEach              (canvass              ->              {              Organization              .out.              println              (              "=> "              +              sheet.              getSheetName              (              )              )              ;              }              )              ;              /*            ==================================================================            Iterating over all the rows and columns in a Canvas (Multiple ways)            ==================================================================         */              // Getting the Sheet at alphabetize zip              Sail              canvas              =              workbook.              getSheetAt              (              0              )              ;              // Create a DataFormatter to format and get each cell's value as Cord              DataFormatter              dataFormatter              =              new              DataFormatter              (              )              ;              // 1. You tin can obtain a rowIterator and columnIterator and iterate over them              System              .out.              println              (              "\northward\nIterating over Rows and Columns using Iterator\n"              )              ;              Iterator                              <                Row                >                            rowIterator              =              sheet.              rowIterator              (              )              ;              while              (rowIterator.              hasNext              (              )              )              {              Row              row              =              rowIterator.              adjacent              (              )              ;              // At present let's iterate over the columns of the electric current row              Iterator                              <                Prison cell                >                            cellIterator              =              row.              cellIterator              (              )              ;              while              (cellIterator.              hasNext              (              )              )              {              Prison cell              cell              =              cellIterator.              next              (              )              ;              String              cellValue              =              dataFormatter.              formatCellValue              (prison cell)              ;              Arrangement              .out.              impress              (cellValue              +              "\t"              )              ;              }              Organisation              .out.              println              (              )              ;              }              // 2. Or you can utilize a for-each loop to iterate over the rows and columns              System              .out.              println              (              "\n\nIterating over Rows and Columns using for-each loop\n"              )              ;              for              (              Row              row:              sheet)              {              for              (              Jail cell              cell:              row)              {              String              cellValue              =              dataFormatter.              formatCellValue              (cell)              ;              System              .out.              impress              (cellValue              +              "\t"              )              ;              }              System              .out.              println              (              )              ;              }              // 3. Or you can use Java 8 forEach loop with lambda              System              .out.              println              (              "\northward\nIterating over Rows and Columns using Java 8 forEach with lambda\n"              )              ;              sheet.              forEach              (row              ->              {              row.              forEach              (cell              ->              {              String              cellValue              =              dataFormatter.              formatCellValue              (prison cell)              ;              Organisation              .out.              print              (cellValue              +              "\t"              )              ;              }              )              ;              Organization              .out.              println              (              )              ;              }              )              ;              // Closing the workbook              workbook.              close              (              )              ;              }              }                      

Notation 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 means to iterate over sheets, rows, and columns. I adopt the Java viii forEach loop with a lambda expression. Yous may utilize whichever method y'all like.

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

Retrieving Cell values past CellType

Instead of using a DataFormatter to format and get each cell's value every bit String regardless of the Cell type, Y'all may check each cell's blazon and then retrieve its value using various type-specific methods similar this -

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

You may now telephone call the above method in the chief program to print each cell'southward value -

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

Decision

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

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

Thank you for reading. Until next time!

hendersongichist.blogspot.com

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

Share this post