CODEWITHSUNDEEP
javaexcel
Anshuman
Anshuman

Reputation:

Edit existing excel files using jxl api / Apache POI

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 !!

Upvotes: 25

Views: 85560

Answers (4)

Zabbala
Zabbala

Reputation: 2203

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

Upvotes: 33

asarkar
asarkar

Reputation: 51

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.

Upvotes: 5

Edwin Pomayay Yaranga
Edwin Pomayay Yaranga

Reputation: 31

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

Upvotes: 3

Alex
Alex

Reputation: 21

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

Upvotes: 2

Related Questions