Apache POI is a very useful library when it comes to reading/writing in Excel. POI stands for Poor Obfuscation Implementation, referring humorously to the fact that the file formats seemed to be deliberately obfuscated, but poorly, since they were successfully reverse-engineered. Ref
Lets see some basic steps on how to Read Excel File in Java using Apache POI
1. Add dependency in your pom.xml for Apache POI
|
1 2 3 4 5 |
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.15-beta2</version> </dependency> |
NOTE : For non maven users, you need to download the latest Apache POI jar from here and set it in your classpath along with some other jars.
|
1 2 3 4 |
poi-3.15-beta2.jar poi-ooxml-3.15-beta2.jar poi-ooxml-schemas-3.15-beta2.jar xmlbeans-2.6.0.jar |
2. Apache POI basics
Below are some of the useful classes that can be used while reading a excel file
XSSF – Used to reading and writting XLSX format files.
HSSF – Use to read and write Microsoft Excel (XLS) format files.
Workbook: is a high level representation of an Excel workbook. We have 2 implementations of the Workbook
HSSFWorkbook for xls files
XSSFWorkbook for xlsx files
Sheet: high level representation of an Excel worksheet. We also have 2 implementations of the Sheet – HSSFSheet and XSSFSheet.
Row: high level representation of a row in a spreadsheet. HSSFRow and XSSFRow are implementations
Cell: high level representation of a cell in a row. HSSFCell and XSSFCell are implementations classes.
Example
Since we are reading a .xlsx file we are using XSSFWorkbook to read data.
If you need to read a .xls file you can use HSSFWorkbook instead.
We will be reading the sample excel file that has below data
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 |
package com.kscodes.sampleproject; 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.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class ReadXlsUsingPOI { public static void main(String[] args) { FileInputStream inputStream = null; Workbook workbook = null; try { String excelFilePath = "C:\\kscodes\\testExcelFile.xlsx"; inputStream = new FileInputStream(new File(excelFilePath)); // Create a Workbook from the file input stream workbook = new XSSFWorkbook(inputStream); // Get the First sheet from the wrokbook Sheet firstSheet = workbook.getSheetAt(0); Iterator<Row> iterator = firstSheet.iterator(); while (iterator.hasNext()) { // Get the Row that we need to read Row nextRow = iterator.next(); Iterator<Cell> cellIterator = nextRow.cellIterator(); while (cellIterator.hasNext()) { // Get the Cell to get data from it Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: System.out.print(cell.getStringCellValue()); break; case Cell.CELL_TYPE_BOOLEAN: System.out.print(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: System.out.print(cell.getNumericCellValue()); break; } System.out.print(" - "); } System.out.println(); } } catch (Exception e) { System.out.println("Exception while reading Excel " + e); } finally { try { if (workbook != null) { workbook.close(); } if (inputStream != null) { inputStream.close(); } } catch (IOException e) { } } } } |
Output
|
1 2 3 4 |
Name - Departement - Salary - John Doe - Sales - 5000.0 - Scott Tiger - HR - 4000.0 - David Smith - Security - 3000.0 - |