Tuesday, October 05, 2010

Re-write Excel files with POI 3.6


Apache POI is a well known (java) project to handle reading and writing MS-Office documents. Another project in that area is jexcel (Lars Vogel has written a tutorial on its usage).

I was using POI in the past to just write new documents. Now I needed to read a worksheet and update it. POI has a nice "busy developers guide" on this, which did not directly work for me, so I've updated it to work for me:

import org.apache.poi.ss.usermodel.Row;

import org.apache.poi.ss.usermodel.Sheet;

import org.apache.poi.ss.usermodel.Workbook;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.ss.usermodel.Cell;



public void run() {


Workbook wb;

// Check if Workbook is present - otherwise create it

try {

InputStream inp = new FileInputStream(WB_NAME);

wb = new HSSFWorkbook(inp);

} catch (Exception e) {

wb = new HSSFWorkbook();


// Now write to it

try {

// Check if we have our sheet

if (wb.getNumberOfSheets()==0) {



Sheet sheet = wb.getSheetAt(0);


// Write row=2, cell=3 ==> D3

Row row = sheet.getRow(2);

if (row==null) // empty sheet

row = sheet.createRow(2);

Cell cell = row.getCell(3);

if (cell == null)

cell = row.createCell(3);


cell.setCellValue("a test");


// Write the output to a file

FileOutputStream fileOut = new FileOutputStream(WB_NAME);




catch (Exception e) {






Of course your mileage may wary :-)


No comments: