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

Question

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!

Did you try with SXSSF? Also does using a File rather than an InputStream as per the docs help? — Gagravarr
– Gagravarr, Commented Aug 22, 2016 at 10:56
Yes, I have tried using SXSSF and File instead of InputStream but still faced the same exception: java.lang.OutOfMemoryError: Java heap space . — Nabeel Mohammed
– Nabeel Mohammed, Commented Aug 23, 2016 at 5:45
Can anyone tell me is it possible to create a new sheet in an existing excel file using OPCPackage? If yes, how? — Nabeel Mohammed
– Nabeel Mohammed, Commented Aug 23, 2016 at 5:47
OPC is the low level packaging layer, which stores the XML files and links between them. You really need to move up a level to XSSF or SXSSF, manipulating the XML files and their relations + dependencies isn't much fun — Gagravarr
– Gagravarr, Commented Aug 23, 2016 at 7:27

centic · Accepted Answer · 2016-08-23 14:56:04Z

1

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.

answered Aug 23, 2016 at 14:56

centic

16k10 gold badges74 silver badges131 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related