Apache POI is a powerful Java library to work with different Microsoft Office file formats such as Excel, Power point, Visio, MS Word etc. The name POI was originally an acronym 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. In this tutorial we will use Apache POI library to perform different functions on Microsoft Excel spreadsheet. Let’s get started.
Tools & Technologies:
Make sure to include apache poi jar file to your project. If your project uses Maven as dependency management, add following in your Pom.xml file.
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.8</version>
</dependency>
Code language: HTML, XML (xml)
If you are not using Maven then you can directly add required JAR files in your classpath.
poi-2.5.1.jar
(or in this case 3.8) jar file.To read an excel file, Apache POI provides certain easy-to-use APIs. In below sample code we use different classes from POI library to read content of cell from excel file. This is for quick reference.
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
//..
FileInputStream file = new FileInputStream(new File("C:\\test.xls"));
//Get the workbook instance for XLS file
HSSFWorkbook workbook = new HSSFWorkbook(file);
//Get first sheet from the workbook
HSSFSheet sheet = workbook.getSheetAt(0);
//Get iterator to all the rows in current sheet
Iterator<Row> rowIterator = sheet.iterator();
//Get iterator to all cells of current row
Iterator<Cell> cellIterator = row.cellIterator();
Code language: Java (java)
Notice how each class in POI library starts with HSSF prefix! e.g. HSSFWorkbook, HSSFSheet etc. HSSF stands for Horrible SpreadSheet Format! I’m not kidding.. It really is. Similar to HSSF, POI has different prefix for other file formats too:
The classes we used in above code snippet, HSSFWorkbook
and HSSFSheet
works for .xls format. In order to work with newer xls format viz .xlsx, you need to see newer POI classes like:
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFSheet;
//..
FileInputStream file = new FileInputStream(new File("C:\\test.xlsx"));
//Get the workbook instance for XLS file
XSSFWorkbook workbook = new XSSFWorkbook (file);
//Get first sheet from the workbook
XSSFSheet sheet = workbook.getSheetAt(0);
//Get iterator to all the rows in current sheet
Iterator<Row> rowIterator = sheet.iterator();
//Get iterator to all cells of current row
Iterator<Cell> cellIterator = row.cellIterator();
Code language: Java (java)
Use XSSFWorkbook
and XSSFSheet
class in all of the below examples in order to make them work with .xlsx files. Consider a sample excel file: test.xls
We will read above xls file using Apache POI and prints the data.
try {
FileInputStream file = new FileInputStream(new File("C:\\test.xls"));
//Get the workbook instance for XLS file
HSSFWorkbook workbook = new HSSFWorkbook(file);
//Get first sheet from the workbook
HSSFSheet sheet = workbook.getSheetAt(0);
//Iterate through each rows from first sheet
Iterator < Row > rowIterator = sheet.iterator();
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
//For each row, iterate through each columns
Iterator < Cell > cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
System.out.print(cell.getBooleanCellValue() + "\t\t");
break;
case Cell.CELL_TYPE_NUMERIC:
System.out.print(cell.getNumericCellValue() + "\t\t");
break;
case Cell.CELL_TYPE_STRING:
System.out.print(cell.getStringCellValue() + "\t\t");
break;
}
}
System.out.println("");
}
file.close();
FileOutputStream out =
new FileOutputStream(new File("C:\\test.xls"));
workbook.write(out);
out.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
Code language: Java (java)
The above code is self explanatory. It read the sheet from workbook and iterate through each row and cell to print its values. Just note how we use different methods like getBooleanCellValue
, getNumericCellValue
etc to read cell value. Before reading a cell content, we need to first determine its type using method cell.getCellType()
and then call appropriate method to read content. Output:
Emp Id Name Salary
1.0 John 2000000.0
2.0 Dean 420000.0
3.0 Sam 280000.0
4.0 Cass 6000000.0
Code language: CSS (css)
Let us create a new excel file and write data in it. Following is the API which we will use for this purpose.
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
//..
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("Sample sheet");
//Create a new row in current sheet
Row row = sheet.createRow(0);
//Create a new cell in current row
Cell cell = row.createCell(0);
//Set value to new value
cell.setCellValue("Blahblah");
Code language: Java (java)
Below is the complete code that writes a new excel with dummy data:
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("Sample sheet");
Map < String, Object[] > data = new HashMap < String, Object[] > ();
data.put("1", new Object[] {
"Emp No.",
"Name",
"Salary"
});
data.put("2", new Object[] {
1 d, "John", 1500000 d
});
data.put("3", new Object[] {
2 d, "Sam", 800000 d
});
data.put("4", new Object[] {
3 d, "Dean", 700000 d
});
Set < String > keyset = data.keySet();
int rownum = 0;
for (String key: keyset) {
Row row = sheet.createRow(rownum++);
Object[] objArr = data.get(key);
int cellnum = 0;
for (Object obj: objArr) {
Cell cell = row.createCell(cellnum++);
if (obj instanceof Date)
cell.setCellValue((Date) obj);
else if (obj instanceof Boolean)
cell.setCellValue((Boolean) obj);
else if (obj instanceof String)
cell.setCellValue((String) obj);
else if (obj instanceof Double)
cell.setCellValue((Double) obj);
}
}
try {
FileOutputStream out =
new FileOutputStream(new File("C:\\new.xls"));
workbook.write(out);
out.close();
System.out.println("Excel written successfully..");
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
Code language: Java (java)
Output:
new.xls
Updating an existing excel file is straight forward. Open the excel using different API that we discussed above and set the cell’s value. One thing we need to note here is that we can update the excel file only when we close it first.
update.xls
Following Java code read the above excel file and doubles the salary of each employee:
try {
FileInputStream file = new FileInputStream(new File("C:\\update.xls"));
HSSFWorkbook workbook = new HSSFWorkbook(file);
HSSFSheet sheet = workbook.getSheetAt(0);
Cell cell = null;
//Update the value of cell
cell = sheet.getRow(1).getCell(2);
cell.setCellValue(cell.getNumericCellValue() * 2);
cell = sheet.getRow(2).getCell(2);
cell.setCellValue(cell.getNumericCellValue() * 2);
cell = sheet.getRow(3).getCell(2);
cell.setCellValue(cell.getNumericCellValue() * 2);
file.close();
FileOutputStream outFile = new FileOutputStream(new File("C:\\update.xls"));
workbook.write(outFile);
outFile.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
Code language: Java (java)
Steps to update excel file will be:
setCellValue
methods.Output:
update.xls
Apache POI provides API to add excel formulas to cell programmatically. Following method that comes handy for this:
cell.setCellFormula("someformula")
Code language: Java (java)
For example:
cell.setCellFormula("A2*B2*C5")
//or
cell.setCellFormula("SUM(A1:A7)")
Code language: Java (java)
Note: Formula string should not start with equal sign (=) Thus, following is incorrect way of adding formula:
cell.setCellFormula("=A2*B2*C5") //Ops! Won't work
Code language: Java (java)
The above code will throw:
org.apache.poi.ss.formula.FormulaParseException:
The specified formula '=A2*B2*C5' starts with an equals sign which is not allowed.
Code language: JavaScript (javascript)
Following Java code creates a new excel sheet which calculates Simple Interest. It defines Principal amount, Rate of Interest and Tenure. We add an excel formula to calculate interest.
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("Calculate Simple Interest");
Row header = sheet.createRow(0);
header.createCell(0).setCellValue("Pricipal Amount (P)");
header.createCell(1).setCellValue("Rate of Interest (r)");
header.createCell(2).setCellValue("Tenure (t)");
header.createCell(3).setCellValue("Interest (P r t)");
Row dataRow = sheet.createRow(1);
dataRow.createCell(0).setCellValue(14500 d);
dataRow.createCell(1).setCellValue(9.25);
dataRow.createCell(2).setCellValue(3 d);
dataRow.createCell(3).setCellFormula("A2*B2*C2");
try {
FileOutputStream out =
new FileOutputStream(new File("C:\\formula.xls"));
workbook.write(out);
out.close();
System.out.println("Excel written successfully..");
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
Code language: Java (java)
Output:
formula.xls
In certain cases your excel file might have formula defined and you may want to trigger those formulas since you updated it using POI. Following code snippet will do the trick.
FileInputStream fis = new FileInputStream("/somepath/test.xls");
Workbook wb = new HSSFWorkbook(fis); //or new XSSFWorkbook("C:\\test.xls")
FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
for (int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) {
Sheet sheet = wb.getSheetAt(sheetNum);
for (Row r: sheet) {
for (Cell c: r) {
if (c.getCellType() == Cell.CELL_TYPE_FORMULA) {
evaluator.evaluateFormulaCell(c);
}
}
}
}
Code language: Java (java)
We use FormulaEvaluator
class to evaluate formula defined in each of the cell.
Adding style to a cell is also piece of cake. Check following example which creates two new cell one with bold font and another with italic and add text to it.
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("Style example");
HSSFFont font = workbook.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
HSSFCellStyle style = workbook.createCellStyle();
style.setFont(font);
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
cell.setCellValue("This is bold");
cell.setCellStyle(style);
font = workbook.createFont();
font.setItalic(true);
style = workbook.createCellStyle();
style.setFont(font);
row = sheet.createRow(1);
cell = row.createCell(0);
cell.setCellValue("This is italic");
cell.setCellStyle(style);
try {
FileOutputStream out = new FileOutputStream(new File("C:\\style.xls"));
workbook.write(out);
out.close();
System.out.println("Excel written successfully..");
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
Code language: Java (java)
Output:
style.xls
Changing background color of the cell is a bit tricky. Ideally we assume setting background color will have some API like setFillBackgroundColor
, but surprisingly to set background color of a cell, we have to set the foreground color :D. See below API.
Code language: Java (java)cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
So use setFillForegroundColor
method to set the background color of given cell. I hope this article is useful.
Java URL Encoder/Decoder Example - In this tutorial we will see how to URL encode/decode…
Show Multiple Examples in OpenAPI - OpenAPI (aka Swagger) Specifications has become a defecto standard…
Local WordPress using Docker - Running a local WordPress development environment is crucial for testing…
1. JWT Token Overview JSON Web Token (JWT) is an open standard defines a compact…
GraphQL Subscription provides a great way of building real-time API. In this tutorial we will…
1. Overview Spring Boot Webflux DynamoDB Integration tests - In this tutorial we will see…
View Comments
Hi Viral,
I want to read only BOLD text from excel file and save it to another new excel file
Any suggestion appreciated.
Thanks
You can check the style for given cell and see if it is bold or not. Something like following:
[code language="java"]
HSSFCell cell = ... //get cell
short boldness = cell.getCellStyle().getFont(workbook).getBoldweight();
if(Font.BOLDWEIGHT_BOLD == boldness) {
//the cell is having bold font
///do something...
}
[/code]
Hope that helps.
Hi Viral,
could you please share an example for .xlsx file.
Thanks in Advance,
Satheesh
Hey Satheesh,
Change HSSF to XSSF, it should work.
I created code for reading xlsx file using the example given by Viral Patel and it works fine for me....Sateesh, for your reference I am pasting the code...
[code language="java"]
try{
FileInputStream file = new FileInputStream(new File("< path of excel file.....xlsx"));
XSSFWorkbook wb = new XSSFWorkbook(file);
XSSFSheet sheet = wb.getSheetAt(0);
//iterate through each row from first sheet
Iterator<Row> rowIterator = sheet.iterator();
while(rowIterator.hasNext()){
Row row = rowIterator.next();
//Fore each row iterate through each column
Iterator<Cell> cellIterator = row.cellIterator();
while(cellIterator.hasNext()){
Cell cell = cellIterator.next();
switch (cell.getCellType()){
case Cell.CELL_TYPE_BOOLEAN:
System.out.print(cell.getBooleanCellValue() + "\t\t");
break;
case Cell.CELL_TYPE_NUMERIC:
System.out.print(cell.getNumericCellValue() + "\t\t");
break;
case Cell.CELL_TYPE_STRING:
System.out.print(cell.getStringCellValue() + "\t\t");
break;
}
}
System.out.println("");
}
file.close();
FileOutputStream out = new FileOutputStream (new File("< path of excel file.....xlsx"));
wb.write(out);
out.close();
} catch(FileNotFoundException e){
e.printStackTrace();
} catch (IOException e){
e.printStackTrace();
}
[/code]
this is giving me an exception "org/apache/poi/UnsupportedFileFormatException"
boolean isbold = font.getBoldweight() > HSSFFont.BOLDWEIGHT_NORMAL;
Check SVTableUtils from poi-examples.jar
I want to count the number of columns in the sheet and print the number of rows in a particular coulm. How i can do that? Like manually i have to iterate ? can you provide the code or describe how to do it
i want enter null or empty string in DB is it possible if possible then how to do it
if any cell is empty it throws the null pointer exception
When i am trying to read the excel file from the above code it showing error at the following :
Iterator rowIterator = sheet.iterator();
//Get iterator to all cells of current row
Iterator cellIterator = row.cellIterator();
can u please help me to resolve it
i want to convert odp document to xls documnet please help
can i send the contents in the ppt to xls automatically by clicking a button
Hi Sir,
Getting red line on the following words when tried create a new excel file and write data in it as given above...
workbook, Row, Cell, sheet, Map, HashMap, Set, Date
how to apply conditions to the data while retrieving from excel. For suppose, I dont want total data of SALARY column. I need only the data who are earning salary morethan 10000/-
i want to copy array contents to excel file how can i do that.
Please Check that below mentioned example does not work as it is expected to be .
[code language="language"]
3. Create new Excel File : Below is the complete code that writes a new excel with dummy data: [/code]