Edit existing excel files using jxl api / Apache POI

Question

I am interested and would like to learn more about java , how to 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 save 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 save it Thanks in advance !!

score 33 · Answer 1 · edited Jan 09 '13 at 15:33

33

The tutorials here are very helpful and well-written. They use an external JAR developed by the Apache POI project. 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

edited Jan 09 '13 at 15:33

Roy Dictus

32,551
8
60
76

answered Feb 06 '09 at 17:53

Zabbala

2,183
1
17
17

At what point do you close the FileInputStream? If you try to read from it after the output stream is closed, will it reflect all of the changes? And, shouldn't the FileOutputStream be opened in append mode? – Edward Q. Bridges Jun 12 '12 at 15:48
@eqbridges As I found out the InputStream is closed by default in the constructor. (I know you comment is over a year old but may be it will help others) – das Keks Nov 11 '13 at 14:56

score 5 · Answer 2 · answered Jun 12 '11 at 06:07

One very important tip that I learned the hard way. Open the OutputStream only after you have completed writing to your excel workbook. Zabbala's example is spot on and shows this correctly. If you open the OutputStream any earlier, your changes would not be written to the file after your program exits and you would be scratching your head as I did.

Edwin Pomayay Yaranga · Answer 3 · 2011-04-12T14:02:17.177

3

I refresh the formulas with another tab for this I use the next sentence

HSSFSheet worksheetse = workbook.getSheetAt(0);
worksheetse.setForceFormulaRecalculation(true);

but it's necesary that you apply the method setForceFormulaRecalculation for all the tabs that have the formulas.

Sorry for my English

edited Apr 12 '11 at 14:02

answered Apr 08 '11 at 23:12

Edwin Pomayay Yaranga

31
2

Note that `HSSFFormulaEvaluator.evaluateAllFormulaCells(workbook);` may not work if the spreadsheet uses specific types of functions (with n arguments, which is not implemented in poi). However `worksheetse.setForceFormulaRecalculation(true);` seems to work in this case. – Vasilen Donchev Nov 08 '12 at 15:12
@VassilenDontchev Can you please give some examples to show in which cases `HSSFFormulaEvaluator.evaluateAllFormulaCells(workbook);` will fail – SpringLearner Jun 23 '16 at 13:12

score 2 · Answer 4 · edited Mar 13 '11 at 09:43

2

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

edited Mar 13 '11 at 09:43

moinudin

134,091
45
190
216

answered Mar 08 '10 at 16:08

Alex

21
2

Edit existing excel files using jxl api / Apache POI

4 Answers4

Linked