CODEWITHSUNDEEP
javaexcelapache-poi
Nabeel Mohammed
Nabeel Mohammed

Reputation: 11

How to add a sheet to an existing large excel file(20MB) using java?

I have an excel file of size 20MB. I want to add a new sheet to this file. I am using Apache POI for this. But when I load the workbook, I am getting heap memory exception at the below line:

XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(new File(fileName)));

I have tried searching for alternative but everyone is suggesting to increase my heap memory, but I cannot do this in the server side. Please help me!

One more thing! Is it possible to create a new sheet in the existing excel file through opcpackage. If yes, how?

Below is the code for your reference:

public static void main(String[] args) {
    try {
        OPCPackage pkg = OPCPackage.open(new File("fileName.xls"));
        XSSFWorkbook wb = new XSSFWorkbook(pkg);
        System.out.println("Number of sheets: " + wb.getNumberOfSheets());
        pkg.close();
    } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    } catch (InvalidFormatException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}

I'am getting the following exception:

java.lang.OutOfMemoryError: Java heap space

at line:

XSSFWorkbook wb = new XSSFWorkbook(pkg);

Thankyou!

Upvotes: 1

Views: 522

Answers (1)

centic
centic

Reputation: 15872

If the contents is not too complicated you can use the streaming-input functionality and construct a new SXSSFWorkbook with that data in streaming-output fashion, thus not requiring to hold the complete workbook in memory.

There is also a more elaborate example that shows how reading information can be done in streaming fashion.

Naturally this will be complicated if there is lots of styling/formatting applied in the source document.

Upvotes: 1

Related Questions