How to append to existing excel file using Java HSSF

Question

I am still relatively new to Java and have cobbled together enough code that allows me to write my data out to a new excel file. However, I want it to write (append to the end) to the existing file. prepareDataToWriteToExcel() gets some data to write 3 columns of data.

public List writeDataToExcelFile(String fileName) throws IOException {
    Map excelData = prepareDataToWriteToExcel();
    List receiversList=new  ArrayList();
    HSSFWorkbook myWorkBook = new HSSFWorkbook();
    HSSFSheet mySheet = myWorkBook.createSheet();
    HSSFRow myRow = null;
    HSSFCell myCell = null;
    Iterator it=excelData.entrySet().iterator(); 
    int rowNum=0;
    while (it.hasNext()) {
        myRow = mySheet.createRow(rowNum);
        Map.Entry pairs = (Map.Entry)it.next();
        String[]arr= (String[]) pairs.getValue();
        for (int cellNum = 0; cellNum < arr.length ; cellNum++){
            myCell = myRow.createCell((short) cellNum);
            myCell.setCellValue(arr[cellNum]);     
        }
        receiversList.add(arr[2]);
        rowNum++;
    }
    try{
        FileOutputStream out = new FileOutputStream(fileName);
        myWorkBook.write(out);
        System.out.println("WRITING TO EXCEL COMPLETED");
        out.close();
    }catch(Exception e){}  
    return receiversList;
}

Check out http://poi.apache.org/spreadsheet/quick-guide.html#ReadWriteWorkbook — Jon Rubins, Mar 16 '14 at 19:11

score 0 · Answer 1 · answered Mar 16 '14 at 19:28

I found this to be a very good example of updating an existing excel worksheet. As you'll notice, you create your workbook with the file you want to modify. This will allow you to find where you can create your new column and write your data too.

try {
    FileInputStream file = new FileInputStream(new File("C:\\update.xls"));

    HSSFWorkbook workbook = new HSSFWorkbook(file);
    HSSFSheet sheet = workbook.getSheetAt(0);
    Cell cell = null;

    // Find empty cell     

    // Update the value of cell

    file.close();

    FileOutputStream outFile = new FileOutputStream(new File("C:\\update.xls"));
    workbook.write(outFile);
    outFile.close();

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

do you have to read in the existing data and write it with the new data to make an append-type of functionality? — Jared, Mar 16 '14 at 22:44
Yes. If you don't read it in, how will you know where to append it to? When you are adding in your data, you are specifying the row and cell number. Those values need to be after the data that already exists. This isn't just a plain text file that you can append to the end of it :) — JBuenoJr, Mar 16 '14 at 22:50

How to append to existing excel file using Java HSSF

1 Answers1