更新 Java Apache POI 中的现有 Excel 文件 [英] Updating existing Excel file in Java Apache POI

问题描述

我正在尝试编写一个每天运行的 Java 程序(使用任务调度程序)，并在每次运行时将一列附加到 Excel 电子表格中.我遇到的问题是它只是重新写入文件，而不是附加到它.我正在使用 Apache POI，这是相关代码:

I'm trying to write a Java program that will run daily (using a task scheduler) and will append a column to an Excel spreadsheet every time it runs. The problem I am having is it merely re-writes the file, not appending to it. I am using Apache POI, here is the relevant code:

 public static void toExcel(List<String> results, List<Integer> notActive)throws IOException{
    try {
        FileInputStream fIPS= new FileInputStream("test.xls"); //Read the spreadsheet that needs to be updated
        HSSFWorkbook wb;
        HSSFSheet worksheet;
        if(fIPS.available()>=512) {
            wb = new HSSFWorkbook(fIPS); //If there is already data in a workbook
            worksheet = wb.getSheetAt(0);
        }else{
            wb = new HSSFWorkbook();    //if the workbook was just created
            worksheet = wb.createSheet("Data");
        }
         //Access the worksheet, so that we can update / modify it
        HSSFRow row1 = worksheet.createRow(0);  //0 = row number
        int i=0;
        Cell c = row1.getCell(i);
        while (!(c == null || c.getCellType() == Cell.CELL_TYPE_BLANK)) {   //cell is empty
            i++;
            c=row1.getCell(i);
        }
        HSSFRow rowx;
        int x=0;
        for(String s : results) {
            rowx = worksheet.createRow(x);
            HSSFCell cellx = rowx.createCell(i);   //0 = column number
            cellx.setCellValue(s);
            x++;
        }
        fIPS.close(); //Close the InputStream
        FileOutputStream output_file =new FileOutputStream("test.xls");//Open FileOutputStream to write updates
        wb.write(output_file); //write changes
        output_file.close();  //close the stream

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
}

推荐答案

我认为您正在一次又一次地创建新的行和单元格并导致 excel 的重写.

I think you are creating the new rows and cells again and again and causing the re-write of excel.

本质上，您需要获取行和单元格而不是创建它们在您的程序中.

Essentially you need to get the rows and cells instead of creating them in your program.

HSSFRow row1 = worksheet.createRow(0);

您可能需要获取该行而不是创建它.

You may need to get the row instead of creating it.

HSSFRow row1 = worksheet.getRow(0);

https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/Sheet.html#getRow(int)

这个小例子更新第二行的第二个单元格:

This small example updates the second cell of second row:

//Read the spreadsheet that needs to be updated
FileInputStream fsIP= new FileInputStream(new File("C:\\Excel.xls"));  
//Access the workbook                  
HSSFWorkbook wb = new HSSFWorkbook(fsIP);
//Access the worksheet, so that we can update / modify it. 
HSSFSheet worksheet = wb.getSheetAt(0); 
// declare a Cell object
Cell cell = null; 
// Access the second cell in second row to update the value
cell = worksheet.getRow(1).getCell(1);   
// Get current cell value value and overwrite the value
cell.setCellValue("OverRide existing value");
//Close the InputStream  
fsIP.close(); 
//Open FileOutputStream to write updates
FileOutputStream output_file =new FileOutputStream(new File("C:\\Excel.xls"));  
 //write changes
wb.write(output_file);
//close the stream
output_file.close();

这篇关于更新 Java Apache POI 中的现有 Excel 文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！