Last weekend, I was working on one of my personal projects. I needed to read a MS Excel spreadsheet. I know that the folks at Apache have a nice API for reading and writing MS Excels files, so I thought I’d download the latest version and give it a try.
The Apache project is named “Apache POI”. You can download it from http://poi.apache.org. Once downloaded, then extract the zip distribution and add the JAR files to your classpath. You will also need the dependency JAR files. If you’re using Maven then this is automatic. You can also download the dependencies manually. See this page and at the bottom they provide a list of dependencies.
Here is a snippet of code to read a MS Excel 2010 file.
[java]
import java.io.FileInputStream;
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;
…
…
XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream("foo.xslx"));
int sheetIndex = 0;
XSSFSheet sheet = workbook.getSheetAt(sheetIndex);
for (Row tempRow : sheet) {
// print out the first three columns
for (int column = 0; column < 3; column++) {
Cell tempCell = tempRow.getCell(column);
if (tempCell.getCellType() == Cell.CELL_TYPE_STRING) {
System.out.print(tempCell.getStringCellValue() + " ");
}
}
System.out.println();
}
[/java]
The API also provides support for writing to a MS Excel in the native Microsoft format. If you want to get really fancy then you can even create formula fields etc. You can really go town with this one. By using this API it is much more powerful than shipping around CSV files.
Anyways, I hope you find this useful during your coding adventures.
Hello,
MY name is Kinjan. I am working on one of my project ..
My requirement is some thing like that, i want to read excel cell values and update this values to database.
When excel cell is updated my java spring program should automatically notified that cell is updated and further process will go on..
If there is any solution to this then please let me know .
Thanks and Regards
Kinjan Ajudiya
Hi,
I’m wondering if it is possible to create some graphical representation (pie chart) from the excel sheets by using java.
All i need is program should intake the excel sheet and represent its graphical form.
@mrana: Yes, you can create Charts but you’ll need to use an additional API. Here are the basic steps:
1. Read Excel spreadsheet using Apache POI
2. Use the data for generate a chart using JFreeChart: http://www.jfree.org/jfreechart/
Hope this helps 🙂
Will this code work for MS Excel 2003 and 2007 as well?
Hi,
This code will read .xslx files. If you need to read older files such as .xls then you will need to use the HSSF classes from Apache POI. See this guide here for plenty of examples: http://poi.apache.org/spreadsheet/quick-guide.html
I am getting the error Multiple Markers on this line..
on the following line of code.
XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(“foo.xslx”));
Hi Bilal,
You will need to download the JAR files for the Apache POI project. See this link for downloads: http://poi.apache.org/download.html
Shall this work for Office 2011 as well.??
Yes, this should work with Office 2011. Let me know if you need anything else 🙂
why it is giving error on getcelltype()and getStringCellValue()?
if (tempCell.getCellType() == Cell.CELL_TYPE_STRING) {
System.out.print(tempCell.getStringCellValue() + ” “);
}
You will need to the Apache POI JAR files to your project.
The JAR files are available here: https://poi.apache.org/download.html#POI-3.13
– be sure to choose the “Binary Distribution” zip file
– once downloaded, extract the zip file
If you are using Eclipse, then I have a video tutorial that shows how to add JAR files to your project. It is not specific to the Apache POI files … but it is a generic video that shows how to add any JAR file to your project.
– https://luv2code.com/2014/08/17/java-eclipse-tutorial-part-8-adding-jar-files/
Let me know if you still need help.
Thanks.
I downloaded the same file already. Only error is on cell class. when I import (import org.apache.poi.xssf.usermodel.XSSFCell;) it shows unused.