How to Read Excel File Using Poi in Java

In this tutorial, we will larn to how to read excel file in Java using Apache POI. POI stands for Poor Obfuscation Implementation, is a powerful Java library which works with dissimilar Microsoft Office formats such equally Excel, Word etc. Let'southward dig into the lawmaking at present.

Apache POI can read both Excel formats XLS (Excel 2003 and earlier) and XLSX (Excel 2007 and later). In club to apply POI nosotros need to accept the following dependencies added in your project.

• poi-3.15.jar
• poi-ooxml-3.xv.jar
• poi-ooxml-schemas-three.15.jar
• xmlbeans-two.6.jar

If yous are running on maven add the beneath dependency to your pom.xml

 <dependency>    <groupId>org.apache.poi</groupId>    <artifactId>poi</artifactId>    <version>3.15</version>  </dependency>  <dependency>    <groupId>org.apache.poi</groupId>    <artifactId>poi-ooxml</artifactId>    <version>3.15</version>  </dependency>              

Read Excel File in Java using POI – XLS format (2003 or before)

package com.javainterviewpoint;  import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.util.Iterator;  import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row;  public class XLSReader {     public static void main(String[] args)     {         try         {             String excelPath = "C:\\Jackson\\Employee.xls";             FileInputStream fileInputStream = new FileInputStream(new File(excelPath));              // Create Workbook instance holding .xls file             HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);              // Become the beginning worksheet             HSSFSheet sheet = workbook.getSheetAt(0);              // Iterate through each rows             Iterator<Row> rowIterator = canvass.iterator();              while (rowIterator.hasNext())             {                 // Get Each Row                 Row row = rowIterator.adjacent();                  // Iterating through Each cavalcade of Each Row                 Iterator<Cell> cellIterator = row.cellIterator();                  while (cellIterator.hasNext())                 {                     Cell cell = cellIterator.side by side();                      // Checking the cell format                     switch (cell.getCellType())                     {                     instance Cell.CELL_TYPE_NUMERIC:                         Organisation.out.impress(cell.getNumericCellValue() + "\t");                         intermission;                     case Prison cell.CELL_TYPE_STRING:                         Organization.out.impress(cell.getStringCellValue() + "\t");                         break;                     case Prison cell.CELL_TYPE_BOOLEAN:                         System.out.impress(jail cell.getBooleanCellValue() + "\t");                         pause;                     }                 }                 Organisation.out.println("");             }          } catch (IOException ie)         {             ie.printStackTrace();         }      } }

• Create HSSFWorkbook instance from the Excel canvass

HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);

• Become into the desired sheet using getSheetAt() method

HSSFSheet sail = workbook.getSheetAt(0);

• Iterate the sheet to get the individual rows.

Iterator rowIterator = canvas.iterator();

• Iterate over the rows which is acquired above to get the private cells.

Iterator cellIterator = row.cellIterator();

• Finally, display the output based on the format of the cell.

switch (jail cell.getCellType()) {      case Cell.CELL_TYPE_NUMERIC:           System.out.print(jail cell.getNumericCellValue() + "\t");           break;      case Prison cell.CELL_TYPE_STRING:           System.out.impress(cell.getStringCellValue() + "\t");           suspension;      case Jail cell.CELL_TYPE_BOOLEAN:           System.out.print(prison cell.getBooleanCellValue() + "\t");           pause; }

Output :

Read Excel File in Java using POI – XLSX format (2007 or later)

In lodge to read XLSX file format we just need to replace HSSF to XSSF in the above code.

parcel com.javainterviewpoint;  import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.util.Iterator;  import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook;  public grade XLSXReader {     public static void main(String[] args)     {         try         {             String excelPath = "C:\\Jackson\\Employee.xlsx";             FileInputStream fileInputStream = new FileInputStream(new File(excelPath));              // Create Workbook instance belongings .xls file             XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);              // Go the get-go worksheet             XSSFSheet sail = workbook.getSheetAt(0);              // Iterate through each rows             Iterator<Row> rowIterator = sheet.iterator();              while (rowIterator.hasNext())             {                 // Get Each Row                 Row row = rowIterator.adjacent();                  // Iterating through Each cavalcade of Each Row                 Iterator<Cell> cellIterator = row.cellIterator();                  while (cellIterator.hasNext())                 {                     Cell cell = cellIterator.next();                      // Checking the cell format                     switch (cell.getCellType())                     {                     case Cell.CELL_TYPE_NUMERIC:                         System.out.impress(jail cell.getNumericCellValue() + "\t");                         pause;                     case Jail cell.CELL_TYPE_STRING:                         System.out.print(cell.getStringCellValue() + "\t");                         break;                     example Cell.CELL_TYPE_BOOLEAN:                         Organization.out.print(cell.getBooleanCellValue() + "\t");                         break;                     }                 }                 System.out.println("");             }          } catch (IOException ie)         {             ie.printStackTrace();         }      } }

Output :

Employee ID	Employee Name	Country	 i.0	Robert	Australia	 ii.0	Peter	England	 3.0	Sam	Australia	 four.0	Domic	Brazil	 five.0	Godwin	Republic of india

Convert Excel To Java

In society to catechumen Excel To Coffee, nosotros volition follow the same above procedure instead of displaying it out we will have a POJO grade for each belongings and set the value to it.

package com.javainterviewpoint;  import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.util.ArrayList; import java.util.Iterator; import coffee.util.Listing;  import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook;  public class ConvertExcelToJava {     public static void main(String[] args)     {         List employees = ConvertExcelToJava();                  for(Employee employee : employees)         {             System.out.println("Employee Id   : "+employee.getEmpId());             System.out.println("Employee Name : "+employee.getEmpName());             Organization.out.println("Country       : "+employee.getCountry());             Organization.out.println(" ");         }              }     public static List ConvertExcelToJava()     {         List employeeList = new ArrayList();         endeavor         {             String excelPath = "C:\\Jackson\\Employee.xlsx";             FileInputStream fileInputStream = new FileInputStream(new File(excelPath));              // Create Workbook instance holding .xls file             XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);              // Get the first worksheet             XSSFSheet canvas = workbook.getSheetAt(0);              // Iterate through each rows             Iterator rowIterator = sheet.iterator();                                       while (rowIterator.hasNext())             {                 // Get Each Row                 Row row = rowIterator.side by side();                                  //Leaving the offset row alone equally it is header                 if(row.getRowNum() == 0)                     go along;                                  // Iterating through Each column of Each Row                 Iterator cellIterator = row.cellIterator();                                  Employee employee = new Employee();                 while (cellIterator.hasNext())                 {                     Jail cell cell = cellIterator.next();                                          int columnIndex = cell.getColumnIndex();                                          switch (columnIndex+1)                     {                     example 1:                         employee.setEmpId(cell.getNumericCellValue());                         break;                     case 2:                         employee.setEmpName(jail cell.getStringCellValue());                         intermission;                     case 3:                         employee.setCountry(cell.getStringCellValue());                         pause;                     }                 }                 employeeList.add(employee);             }         } catch (IOException ie)         {             ie.printStackTrace();         }         return employeeList;     } }

Output:

Employee Id   : one.0 Employee Name : Robert Land       : Australia   Employee Id   : 2.0 Employee Proper noun : Peter Country       : England   Employee Id   : 3.0 Employee Name : Sam Land       : Commonwealth of australia   Employee Id   : iv.0 Employee Proper noun : Domic Land       : Brazil   Employee Id   : 5.0 Employee Proper noun : Godwin State       : India

eastintheong.blogspot.com

Source: https://www.javainterviewpoint.com/read-excel-file-java-using-poi/