java poi 更新excel,在Java Apache POI中更新现有的Excel文件

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 results, List 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();

}

}

解决方案

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

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