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) {

wb.createSheet("Overview");

}

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.setCellType(Cell.CELL_TYPE_STRING);

cell.setCellValue("a test");

 

// Write the output to a file

FileOutputStream fileOut = new FileOutputStream(WB_NAME);

wb.write(fileOut);

fileOut.close();

}

catch (Exception e) {

e.printStackTrace();

}

}

 

 

Of course your mileage may wary :-)