我正在尝试编写一个每天运行的
Java程序(使用任务调度程序),并且每次运行时都会在Excel电子表格中附加一列.我遇到的问题是它只是重写文件,而不是附加到它.我正在使用Apache POI,这里是相关代码：
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.
Essentially you need to get the rows and cells instead of creating
them in your program.
HSSFRow row1 = worksheet.createRow(0);
您可能需要获取行而不是创建它.
HSSFRow row1 = worksheet.getRow(0);
https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/Sheet.html#getRow(int)
这个小例子更新了第二行的第二个单元格：
//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();
