Update existing Excel files in Java Apache poi
•
Java
I'm trying to write a java program that runs every day (using the task scheduler) and attaches a column to the Excel spreadsheet every time it runs The problem I encountered was that it just rewrites the file, not attaches 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(); } }
Solution
I think you create new rows and cells again and again and lead to rewriting excel
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 in 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();
The content of this article comes from the network collection of netizens. It is used as a learning reference. The copyright belongs to the original author.
THE END
二维码