Adding sheets to existing excel file without re-writing complete file, using Apache-poi

Question

I'm working on Apache poi and trying to add new sheets into existing excel file.

I'm adding the sheets to workbook object and writing the workbook object to file, using FileOutputStream, altogether.

FileOutputStream outputStream = null;
    FileInputStream inputStream = null;
    HSSFWorkbook workbook = null;
    int nRows = 5000;
    int nCols = 100;
    try {
        ArrayList<String> names = new ArrayList();
        names.add("NewSheet1");
        names.add("NewSheet2");
        names.add("NewSheet3");
        names.add("NewSheet4");
        workbook = new HSSFWorkbook();
        for (String name : names) {
            HSSFSheet sheet = workbook.createSheet(name);
            for (int rowCounter = 0; rowCounter < nRows; rowCounter++) {
                HSSFRow row = sheet.createRow(rowCounter);
                for (int colCounter = 0; colCounter < nCols; colCounter++) {
                    HSSFCell cell = row.createCell(colCounter);
                    cell.setCellValue("" + rowCounter + colCounter);
                }
            }
        }
        outputStream = new FileOutputStream(excelFilePath);
        workbook.write(outputStream);

    } catch (Exception ex) {
        ex.printStackTrace();
    } finally {
        try {
            if (outputStream != null) {
                outputStream.flush();
                outputStream.close();
                outputStream = null;
            }
            if (workbook != null) {
                workbook = null;
            }
            if (inputStream != null) {
                inputStream.close();
                inputStream = null;
            }
        } catch (Exception e) {
            e.getMessage();
        }
    }

But it is throwing Out of Memory Exception.

Exception in thread "main" java.lang.OutOfMemoryError: Java heap space
at java.util.Arrays.copyOf(Arrays.java:2245)
at java.util.Arrays.copyOf(Arrays.java:2219)
at java.util.ArrayList.grow(ArrayList.java:242)
at java.util.ArrayList.ensureExplicitCapacity(ArrayList.java:216)
at java.util.ArrayList.ensureCapacityInternal(ArrayList.java:208)
at java.util.ArrayList.add(ArrayList.java:440)
at org.apache.poi.hssf.usermodel.HSSFWorkbook$SheetRecordCollector.visitRecord(HSSFWorkbook.java:1200)
at org.apache.poi.hssf.record.aggregates.RecordAggregate$PositionTrackingVisitor.visitRecord(RecordAggregate.java:106)
at org.apache.poi.hssf.record.aggregates.RecordAggregate$PositionTrackingVisitor.visitRecord(RecordAggregate.java:106)
at org.apache.poi.hssf.record.aggregates.ValueRecordsAggregate.visitCellsForRow(ValueRecordsAggregate.java:254)
at org.apache.poi.hssf.record.aggregates.RowRecordsAggregate.visitContainedRecords(RowRecordsAggregate.java:269)
at org.apache.poi.hssf.model.Sheet.visitContainedRecords(Sheet.java:550)
at org.apache.poi.hssf.usermodel.HSSFWorkbook.getBytes(HSSFWorkbook.java:1247)
at org.apache.poi.hssf.usermodel.HSSFWorkbook.write(HSSFWorkbook.java:1157)
at com.ExcelTrial.main(ExcelTrial.java:43)

So, I tried other approach.

I'm trying to write the excel file sheet by sheet i.e. updating the file by opening in input mode for each sheet.

Excel file is already created in my disk.

I'm simply following the following steps:

• Open file in input, using FileInputStream
• Creating workbook, using this FileInputStream object
• Creating sheet, writing data

• Writing into file using FileOutputStream

  String excelFilePath = "C:\\TrialNew\\TrialNew.xls";
  FileOutputStream outputStream = null;
  FileInputStream inputStream = null;
  HSSFWorkbook workbook = null;
  int nRows = 5000;
  int nCols = 100;
  try {
      ArrayList<String> names = new ArrayList();
      names.add("NewSheet1");
      names.add("NewSheet2");
      names.add("NewSheet3");
      names.add("NewSheet4");
      for (String name : names) {
          inputStream = new FileInputStream(new File(excelFilePath));
          workbook = new HSSFWorkbook(inputStream);
          HSSFSheet sheet = workbook.createSheet(name);
          for (int rowCounter = 0; rowCounter < nRows; rowCounter++) {
              HSSFRow row = sheet.createRow(rowCounter);
              for (int colCounter = 0; colCounter < nCols; colCounter++) {
                  HSSFCell cell = row.createCell(colCounter);
                  cell.setCellValue("" + rowCounter + colCounter);
              }
          }
  
          outputStream = new FileOutputStream(excelFilePath);
  
          workbook.write(outputStream);
          outputStream.flush();
          outputStream.close();
          outputStream = null;
          workbook = null;
          inputStream.close();
          inputStream = null;
      }
  
  } catch (Exception ex) {
      ex.printStackTrace();
  } finally {
      try {
          if (outputStream != null) {
              outputStream.flush();
              outputStream.close();
              outputStream = null;
          }
          if (workbook != null) {
              workbook = null;
          }
          if (inputStream != null) {
              inputStream.close();
              inputStream = null;
          }
      } catch (Exception e) {
          e.getMessage();
      }
  }
  

It still gives the same exception. But one thing I noticed is, while I'm trying to add new sheet (For example "Newsheet2") to a file already containing "NewSheet1", it is re-writing the complete workbook instead of writing a new sheet at the end.

Is there any way to write a new sheet at the end instead of re-writing all the sheets again and again ? That might solve my problem.

Or if there is any other solution to avoid this Java Heap Space Exception, suggestions are most welcome.

Answer 1

Is there any way to write a new sheet at the end instead of re-writing all the sheets again and again?

Although I'm not familiar with the specifics of the file format, I seriously doubt that. There's probably info about the sheets in the header portion of the file, so you can't just slide a new sheet at the end of the file and have it work without rewriting the header (and therefore the whole file).

If you're using the newer excel format (Office 2007 onwards), you could try using XSSFWorkBook. I know it offers better performance for reading, so it may be more performant (i.e. less memory hungry) for writing as well.