Apache POI provides libraries for reading and writing excel file in Java. There are other libraries available in market but Apache POI is most popular and reliable among them.
In this tutorial I will show you how you can read or write data to excel file in Java using Apache POI Library.
There are few things that you should know about this library.
- Class prefixed by HSSF are used to perform operations on MS Excel 2003 file. For example, HSSFWorkbook and HSSFSheet. The file format for excel 2003 file is XLS.
- Class prefixed by XSSF are used to perform operations on MS Excel 2007 or later versions file. For example, XSSFWorkbook and XSSFSheet. The file format for excel 2007 and later versions file is XLSX.
- HSSFWorkbook and XSSFWorkbook class represents excel workbook.
- HSSFSheet and XSSFSheet class represents excel worksheet.
- Row and Cell class represents excel row and column.
Download Apache POI Library
Go to below link and download the binary distribution of latest version.
https://poi.apache.org/download.html
You will get a compressed file. Just extract it and import all the jars present in it to your project.
Writing to Excel File in Java
Below example shows how you can perform write operation on an excel file. Here I am saving data of some users in an excel file.
|
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 |
package com; import java.io.FileOutputStream; import java.io.IOException; 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 WriteExcel { public static void main(String args[]) { int i, j; Object data[][] = { {"Name", "Age", "Gender"}, {"Neeraj", 22, "Male"}, {"Pankaj", 25, "Male"}, {"Sneha", 20, "Female"}, }; XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("User Data"); Row row; Cell cell; for(i = 0; i < 4; ++i) { row = sheet.createRow(i); for(j = 0; j < 3; ++j) { cell = row.createCell(j); if(data[i][j] instanceof String) cell.setCellValue((String) data[i][j]); if(data[i][j] instanceof Integer) cell.setCellValue((Integer) data[i][j]); } } try { FileOutputStream fos = new FileOutputStream("E:/user.xlsx"); workbook.write(fos); workbook.close(); } catch (IOException e) { e.printStackTrace(); } } } |
After writing the excel file will look like as show in below image.
Reading from Excel File in Java
In this example I am simply reading the excel file that I have generated in previous example.
|
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 |
package com; 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.CellType; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class ReadExcel { public static void main(String args[]) { try { FileInputStream fis = new FileInputStream("E:/user.xlsx"); XSSFWorkbook workbook = new XSSFWorkbook(fis); XSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); Iterator<Cell> cellIterator; Row row; Cell cell; while(rowIterator.hasNext()) { row = rowIterator.next(); cellIterator = row.iterator(); while(cellIterator.hasNext()) { cell = cellIterator.next(); if(cell.getCellTypeEnum() == CellType.STRING) { System.out.print(cell.getStringCellValue() + " === "); } else if(cell.getCellTypeEnum() == CellType.NUMERIC) { System.out.print((int)cell.getNumericCellValue() + " === "); } } System.out.print("\n"); } workbook.close(); } catch(IOException e) { e.printStackTrace(); } } } |
Output
Name === Age === Gender ===
Neeraj === 22 === Male ===
Pankaj === 25 === Male ===
Sneha === 20 === Female ===
Comment below if you have any queries related to above read and write excel file example in java.
