Excel files operations using Apache POI - Yash-777/LearnJava GitHub Wiki
Excel files (spreadsheets) are widely used by people all over the world for various tasks related to organization, analysis, and storage of tabular data. Since excel files are so common, we developers often encounter use-cases when we need to read data from an excel file or generate a report in excel format.
In this article, I’ll show you how to read excel files in Java using a very simple yet powerful open source library called Apache POI.
Apache POI excel library revolves around following four key interfaces -
- Workbook: A workbook is the high-level representation of a Spreadsheet.
- Sheet: A workbook may contain many sheets. The sample excel file that we looked at in the previous section has two sheets - Employee and Department
- Row: As the name suggests, It represents a row in the spreadsheet.
- Cell: A cell represents a column in the spreadsheet.
Apache POI library consists of two different implementations for all the above interfaces.
- XSSFWorkbook and HSSFWorkbook are classes which act as an Excel Workbook
- HSSFSheet and XSSFSheet are classes which act as an Excel Worksheet
-
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(Microsoft Excel 2003 file) -
XSSF (XML SpreadSheet Format): XSSF implementations are used to work with the newer XML based file format -
.xlsx. (Microsoft Excel 2007 file or later)
Image Reference
Maven dependencies to your pom.xml file
<!-- 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>Example to read an Excel file
public class ApachePOIExcelRead {
private static final String FILE_NAME = "/tmp/MyFirstExcel.xlsx";
public static void main(String[] args) {
try {
FileInputStream excelFile = new FileInputStream(new File(FILE_NAME));
Workbook workbook = new XSSFWorkbook(excelFile);
Sheet datatypeSheet = workbook.getSheetAt(0);
Iterator<Row> iterator = datatypeSheet.iterator();
while (iterator.hasNext()) {
Row currentRow = iterator.next();
Iterator<Cell> cellIterator = currentRow.iterator();
while (cellIterator.hasNext()) {
Cell currentCell = cellIterator.next();
//getCellTypeEnum shown as deprecated for version 3.15
//getCellTypeEnum ill be renamed to getCellType starting from version 4.0
if (currentCell.getCellTypeEnum() == CellType.STRING) {
System.out.print(currentCell.getStringCellValue() + "--");
} else if (currentCell.getCellTypeEnum() == CellType.NUMERIC) {
System.out.print(currentCell.getNumericCellValue() + "--");
}
}
System.out.println();
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}