Avoid file contents in an excel from getting overwritten

Question

I want to have just a single FileOutputStream that writes contents of my workbook to a file in my application, and create multiple worksheets within this excel. I am using Apache POI to read/write to my excel. I have the below method where I am doing this -

private static void writeToSpreadSheet(String test,Map<String,String> errorMap,Object object) throws IOException {
    HSSFWorkbook workbook = new HSSFWorkbook()
    HSSFSheet sheet = workbook.createSheet(test);
    FileOutputStream fis = new FileOutputStream("output/test.xls");
    //do stuff and finally write workbook contents to a file
    workbook.write(fis);
    if (fis != null)
        fis.close();
}

The problem I am facing here is, every time I called the writeToSpreadSheet, a new file is getting created, and the existing data is getting overwritten. I want one file only, and need new worksheeets to be added to my existing file. How do I achieve this?

Have you looked at following: [1. How to add new sheets to existing excel workbook using apache POI?](http://stackoverflow.com/questions/12459181/how-to-add-new-sheets-to-existing-excel-workbook-using-apache-poi) `AND` [2. Add a sheet into existing excel file](http://stackoverflow.com/questions/17383955/add-a-sheet-into-existing-excel-file) — Smit, Aug 08 '14 at 23:41
@Smit, my problem is not adding new sheets, but adding new sheets to an existing workbook, and writing that workbook's contents to a single file. The problem I am having right now is everytime this method is called, the workbook content gets written to a new file. — rickygrimes, Aug 08 '14 at 23:44
So you are saying you want to add new sheet to existing workbook every time you call this method and all the new content should go to newly created sheet? — Smit, Aug 09 '14 at 00:00

Smit · Answer 1 · 2014-08-09T00:42:27.737

I am not near my machine so I can't provide you the exact code base, but if you follow the exact steps then you could achieve the desired results.

I have assembled the code from here and there and its not going to work as is. You have to modify the code and made it work the way you wanted. I leave that part for you.

final File file = "/output/test.xls";
HSSFWorkbook workbook = null;
FileOutputStream fileOut = new FileOutputStream(file);

private static void writeToSpreadSheet(String test,
        Map<String, String> errorMap, Object object) throws IOException {
    // Check whether your file exist
    // if not then crate a workbook
    // something like below

    if (!file.exists()) {
        System.out.println("Creating a new workbook '" + file + "'");
        workbook = new HSSFWorkbook();
    } else {
        // create a method to get very last sheet number something like
        // following .
        int sheetIndex = getLastSheetIndex();
        // if you dont to go with find last sheet index idea then you can
        // create your unique name may be like timestamp or so
        // add the new sheet with new index
        HSSFSheet sheet = workbook.createSheet("Test Sheet " + sheetIndex);
        // Write your content
        workbook.write(fileOut);
        fileOut.close();
    }

}

private static int getLastSheetIndex() {
    int sheetIndex = 1;
    while (workbook.getSheet("Test Sheet " + sheetIndex) != null) {
        sheetIndex++;
    }
    return sheetIndex;
}

Currently what are you doing:

private static void writeToSpreadSheet(String test,
        Map<String, String> errorMap, Object object) throws IOException {
    // Creating the new workbook every time you call this method
    HSSFWorkbook workbook = new HSSFWorkbook();

    // Adding the same sheet to newly created workbook
    HSSFSheet sheet = workbook.createSheet(test);
    FileOutputStream fis = new FileOutputStream("output/test.xls");
    // causing to overwrite your old workbook
    workbook.write(fis);
    if (fis != null)
        fis.close();
}

Avoid file contents in an excel from getting overwritten

1 Answers1