Updating an existing Excel file in Java Apache POI

Question

I am trying to write a Java program that will work daily (using the task scheduler) and will add a column to the Excel spreadsheet every time it starts. The problem I am facing is simply overwriting the file, not adding to it. I am using Apache POI, here is the corresponding 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(); } } 

Answer 1

I think you create new rows and cells again and again and cause excel rewriting.

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

 HSSFRow row1 = worksheet.createRow(0); 

You may need to get a string 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 the 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();