使用jxl api / Apache POI编辑现有的excel文件

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

我很感兴趣，并且想了解更多关于java的知识，如何在现有的excel表中编写和操作现有的数据。我想知道你能给我一个主意如何编辑现有excel文件并将其保存使用jxl api / Apache POI或者给我一个示例程序如何编辑一些数据在现有excel文件,然后保存它提前谢谢! !

4 个解决方案

#1

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:

这里的教程非常有用，而且写得很好。它们使用Apache POI项目开发的外部JAR。这里有一个编辑一个单元格的简单示例:

    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

希望它能帮助

#2

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.

这是我学到的一个非常重要的技巧。只有在您完成对excel工作簿的编写之后才打开OutputStream。Zabbala的例子是对的，并且正确地展示了这一点。如果您在更早的时候打开OutputStream，您的更改将不会在程序退出后写入文件，您将会像我一样挠头。

#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.

但是有必要对所有有公式的选项卡应用setforceformula ecalculation方法。

Sorry for my English

对不起,我的英语

#4

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

你好，我和neXGen有同样的问题。但奇怪的是，如果我打开文件和openoffice，它是有效的!

Edit: perhaps i found a solution, put this after changing the values:

编辑:也许我找到了一个解决方案，把这个放在修改后的值:

HSSFFormulaEvaluator.evaluateAllFormulaCells(workbook);

#1