Edit existing excel files using jxl api / Apache POI

Question

Hi All,

I am interesting and would like to learn more on java , how write into existing excel sheets / manipulating the existing data. I was wondering if you could give me an idea on how to edit an existing excel file and saves it using the jxl api / Apache POI or perhaps give me a sample program on how to edit some data in an existing excel file and then saves it Thanks in advance !!

Answer 1

The tutorials here are very helpful and well-written. Here's an simple example of editing one cell:

    InputStream inp = new FileInputStream("wb.xls");
    Workbook wb = WorkbookFactory.create(inp);
    Sheet sheet = wb.getSheetAt([sheet index]);
    Row row = sheet.getRow([row index]);
    Cell cell = row.getCell([cell index]);
    String cellContents = cell.getStringCellValue(); 
    //Modify the cellContents here
    // Write the output to a file
    cell.setCellValue(cellContents); 
    FileOutputStream fileOut = new FileOutputStream("wb.xls");
    wb.write(fileOut);
    fileOut.close();

Hope it helps

Answer 2

This is very good, for version 3.5 of POI, but, how you can do that with version 2.5 or 3.02 ?? I have a trouble with these versions, when i create an excel file with any, i can't read the same file!!! Is for that, i make the question, if i create an excel file with poi, how can i read it, with poi, but with the 2.5 or 3.0.2 version???

Thanks in advance

Answer 3

in my case the some of the cell have formula which gets updated based on the value which i am updating using POI. those cells are not getting updated. only if i open the file and change values they get updated.

Eg: Cell 1(A1)Formula -> =IF(D11=1,360,365)

if i update D11 from POI A1 is not getting updated.

Answer 4

So, to update/modify a single cell in a huge sheet, you have to load the entire workbook and save it as a brand new file :-?

There must be better ways to do this!

Answer 5

Hello i have the same problem than neXGen. But strangely if i open the file with openoffice, it works!

Edit: perhaps i found a solution, put this after changing the values: HSSFFormulaEvaluator.evaluateAllFormulaCells(workbook); ;-)