Apache POI – Reading and Writing Excel file in Java
In this article, we will discuss about how to read and write an excel file using Apache POI
1. Basic definitions for Apache POI library
This section briefly describe about basic classes used during Excel Read and Write.
HSSF
is prefixed before the class name to indicate operations related to a Microsoft Excel 2003 file.XSSF
is prefixed before the class name to indicate operations related to a Microsoft Excel 2007 file or later.XSSFWorkbook
andHSSFWorkbook
are classes which act as an Excel WorkbookHSSFSheet
andXSSFSheet
are classes which act as an Excel WorksheetRow
defines an Excel rowCell
defines an Excel cell addressed in reference to a row.
2. Download Apache POI
Apache POI library is easily available using Maven Dependencies.
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.15</version>
</dependency>
3. Apache POI library – Writing a Simple Excel
The below code shows how to write a simple Excel file using Apache POI libraries. The code uses a 2 dimensional data array to hold the data. The data is written to a XSSFWorkbook
object. XSSFSheet
is the work sheet being worked on. The code is as shown below:
package com.mkyong;
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;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
public class ApachePOIExcelWrite {
private static final String FILE_NAME = "/tmp/MyFirstExcel.xlsx";
public static void main(String[] args) {
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet("Datatypes in Java");
Object[][] datatypes = {
{"Datatype", "Type", "Size(in bytes)"},
{"int", "Primitive", 2},
{"float", "Primitive", 4},
{"double", "Primitive", 8},
{"char", "Primitive", 1},
{"String", "Non-Primitive", "No fixed size"}
};
int rowNum = 0;
System.out.println("Creating excel");
for (Object[] datatype : datatypes) {
Row row = sheet.createRow(rowNum++);
int colNum = 0;
for (Object field : datatype) {
Cell cell = row.createCell(colNum++);
if (field instanceof String) {
cell.setCellValue((String) field);
} else if (field instanceof Integer) {
cell.setCellValue((Integer) field);
}
}
}
try {
FileOutputStream outputStream = new FileOutputStream(FILE_NAME);
workbook.write(outputStream);
workbook.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
System.out.println("Done");
}
}
On executing the above code, you get below excel as an output.

4. Apache POI library – Reading an Excel file
The below code explains how to read an Excel file using Apache POI libraries. The function getCellTypeEnum
is deprecated in version 3.15 and will be renamed to getCellType
from version 4.0 onwards.
package com.mkyong;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Iterator;
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();
}
}
}
On executing the above code, you get the below output.
Datatype--Type--Size(in bytes)--
int--Primitive--2.0--
float--Primitive--4.0--
double--Primitive--8.0--
char--Primitive--1.0--
String--Non-Primitive--No fixed size--
In the version of 3.9 of poi dependency you have to use “Cell.CELL_TYPE_STRING” or “Cell.CELL_TYPE_x” instead of “CellType.x” in which x may equal to BLANK, BOOLEAN, ERROR, FORMULA, NUMERIC OR STRING
For the ones who didn’t understand here is the example;
if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
// your code
}
Thanks Buddy
Just FYI:
The type org.apache.poi.POIXMLDocument cannot be resolved. It is indirectly referenced from required .class files
When writing large Excel files there’s also SXSSFWorkbook. It’s a streaming version of XSSFWorkbook with a lower memory footprint.
Thanks for your SXSSFWorkbook suggestion.
SXSSFWorkbook stores in temporary file. Is there a solution to avoid this ? on server security is issue if we have temp files.
Hi i am looking for an example code about writing to excel from jtable. Can you help me.
package LeProjetExcel; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; This code contains 2 class one that reads and puts my data into an arrayList secon that transforms my arraylist in jTable main in secon class The place where you indicate the root is in the main only works with .xls extension because HSSF package LeProjetExcel; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.sql.Connection; import java.util.ArrayList; import java.util.Iterator; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.poifs.filesystem.POIFSFileSystem; import org.apache.poi.ss.usermodel.CellType; public class ExcelReading2 { ExcelReading2(){ } public ArrayList ConvertionFichierExelEnArrayList(String racineDeFichierExcel) { ArrayList TableauDeRetour = new ArrayList(); ArrayList values = new ArrayList(); Connection… Read more »
Writing to excel works fine. thank you so much.
What are the jars required?
This dependency is required for Apache POI.
org.apache.poi
poi
3.7
Could yo tell me how to import apache poi? I do not know exactly
Thanks you so much; the logical flow has worked.
if .dot file convert html is it possible?
sorry, i found the answer
for (int i = 0; i < wb.getNumberOfSheets(); i++) {
Sheet datatypeSheet = workbook.getSheetAt(i);
}
how about the excel has multiple sheet?
I m getting all the data line by line in output. I need it to be printed in row and column wise as it is in excel
how to read date into excel file
How can we handle non english strings like hindi text or thai text while reading excel sheets?
Hi Sir/Madam xlsx(Excel) format file Read content issues that attaching file from Email and store into local drive, Please any help on this and that really appreciated. ———————————————————————————————————————————————————– Below java code now we are using ———————————- String contentType = message.getContentType(); String attachFiles = “”; // String saveDirectory = (String) resources.get(SystemRqstAppConstants.WebConstants.CUSTOMERITEMVENDORPO_PATH); String saveDirectory =”D:/ResumeFiles/”; List errorsList= null; String messageContent = “”; logger.info(“:::::Timecards Auto Update before Attchments:::::”); if (contentType.contains(“multipart”)) { // content may contain attachments String client=””; if(subject.contains(“PFIZER”) || subject.contains(“Pfizer”) || subject.contains(“pfizer”)) client=”Pfizer”; else if(subject.contains(“CSC”) || subject.contains(“Csc”) || subject.contains(“csc”)) client=”CSC”; logger.info(“Timecards Auto Update client name: “+client); Multipart multiPart = (Multipart) message.getContent(); int… Read more »
You need to update your code, I have taken and it is not working, throwing errors. It wasted my time.