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.