How to Read Excel File 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 different Microsoft Function formats such as Excel, Word etc. Let'southward dig into the code at present.

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

• poi-3.15.jar
• poi-ooxml-3.15.jar
• poi-ooxml-schemas-three.fifteen.jar
• xmlbeans-2.half dozen.jar

If you lot are running on maven add together the below 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 earlier)

package com.javainterviewpoint;  import coffee.io.File; import coffee.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.Jail cell; import org.apache.poi.ss.usermodel.Row;  public grade XLSReader {     public static void main(String[] args)     {         try         {             String excelPath = "C:\\Jackson\\Employee.xls";             FileInputStream fileInputStream = new FileInputStream(new File(excelPath));              // Create Workbook instance belongings .xls file             HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);              // Get the starting time worksheet             HSSFSheet sheet = workbook.getSheetAt(0);              // Iterate through each rows             Iterator<Row> rowIterator = sheet.iterator();              while (rowIterator.hasNext())             {                 // Become Each Row                 Row row = rowIterator.next();                  // Iterating through Each column of Each Row                 Iterator<Prison cell> cellIterator = row.cellIterator();                  while (cellIterator.hasNext())                 {                     Cell cell = cellIterator.next();                      // Checking the jail cell format                     switch (cell.getCellType())                     {                     example Cell.CELL_TYPE_NUMERIC:                         System.out.print(cell.getNumericCellValue() + "\t");                         break;                     example Cell.CELL_TYPE_STRING:                         System.out.print(prison cell.getStringCellValue() + "\t");                         break;                     example Cell.CELL_TYPE_BOOLEAN:                         System.out.impress(cell.getBooleanCellValue() + "\t");                         break;                     }                 }                 System.out.println("");             }          } take hold of (IOException ie)         {             ie.printStackTrace();         }      } }

• Create HSSFWorkbook instance from the Excel sheet

HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);

• Get into the desired sail using getSheetAt() method

HSSFSheet canvass = workbook.getSheetAt(0);

• Iterate the sheet to go the individual rows.

Iterator rowIterator = sheet.iterator();

• Iterate over the rows which is caused above to get the individual cells.

Iterator cellIterator = row.cellIterator();

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

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

Output :

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

In order to read XLSX file format nosotros just demand to supervene upon HSSF to XSSF in the above code.

package com.javainterviewpoint;  import java.io.File; import java.io.FileInputStream; import coffee.io.IOException; import coffee.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 class XLSXReader {     public static void master(String[] args)     {         try         {             String excelPath = "C:\\Jackson\\Employee.xlsx";             FileInputStream fileInputStream = new FileInputStream(new File(excelPath));              // Create Workbook instance holding .xls file             XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);              // Go the first worksheet             XSSFSheet canvass = workbook.getSheetAt(0);              // Iterate through each rows             Iterator<Row> rowIterator = canvass.iterator();              while (rowIterator.hasNext())             {                 // Get Each Row                 Row row = rowIterator.next();                  // Iterating through Each cavalcade of Each Row                 Iterator<Cell> cellIterator = row.cellIterator();                  while (cellIterator.hasNext())                 {                     Cell jail cell = cellIterator.next();                      // Checking the cell format                     switch (prison cell.getCellType())                     {                     case Cell.CELL_TYPE_NUMERIC:                         Organisation.out.print(jail cell.getNumericCellValue() + "\t");                         interruption;                     case Cell.CELL_TYPE_STRING:                         System.out.print(cell.getStringCellValue() + "\t");                         pause;                     case Cell.CELL_TYPE_BOOLEAN:                         System.out.print(cell.getBooleanCellValue() + "\t");                         suspension;                     }                 }                 Arrangement.out.println("");             }          } take hold of (IOException ie)         {             ie.printStackTrace();         }      } }

Output :

Employee ID	Employee Name	Land	 one.0	Robert	Australia	 two.0	Peter	England	 3.0	Sam	Australia	 iv.0	Domic	Brazil	 five.0	Godwin	India

Convert Excel To Java

In gild to convert Excel To Java, we will follow the aforementioned above procedure instead of displaying it out we volition have a POJO course for each property and ready the value to it.

package com.javainterviewpoint;  import java.io.File; import java.io.FileInputStream; import java.io.IOException; import coffee.util.ArrayList; import java.util.Iterator; import java.util.List;  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(Cord[] args)     {         List employees = ConvertExcelToJava();                  for(Employee employee : employees)         {             Organisation.out.println("Employee Id   : "+employee.getEmpId());             System.out.println("Employee Name : "+employee.getEmpName());             System.out.println("Land       : "+employee.getCountry());             Organization.out.println(" ");         }              }     public static List ConvertExcelToJava()     {         List employeeList = new ArrayList();         endeavour         {             String excelPath = "C:\\Jackson\\Employee.xlsx";             FileInputStream fileInputStream = new FileInputStream(new File(excelPath));              // Create Workbook instance property .xls file             XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);              // Get the first worksheet             XSSFSheet sheet = workbook.getSheetAt(0);              // Iterate through each rows             Iterator rowIterator = sheet.iterator();                                       while (rowIterator.hasNext())             {                 // Get Each Row                 Row row = rowIterator.next();                                  //Leaving the first row alone as information technology is header                 if(row.getRowNum() == 0)                     continue;                                  // Iterating through Each column of Each Row                 Iterator cellIterator = row.cellIterator();                                  Employee employee = new Employee();                 while (cellIterator.hasNext())                 {                     Cell cell = cellIterator.next();                                          int columnIndex = cell.getColumnIndex();                                          switch (columnIndex+1)                     {                     case 1:                         employee.setEmpId(prison cell.getNumericCellValue());                         break;                     case ii:                         employee.setEmpName(prison cell.getStringCellValue());                         pause;                     case 3:                         employee.setCountry(jail cell.getStringCellValue());                         break;                     }                 }                 employeeList.add together(employee);             }         } take hold of (IOException ie)         {             ie.printStackTrace();         }         return employeeList;     } }

Output:

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

savageonsch1973.blogspot.com

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

Share this post