How to delete rows in a big xls/xlsx file with Apache-POI

Question

I'm trying to delete the first row of the second and third sheet of my xlsx file, but with huge files I get Java heat space Exception or GC overhead.

I'm using Apache-POI, I don't understand how to use XSSF and SAX.

My actual code is the following:

public boolean deleteRow(String sheetName, String excelPath, int rowNo) throws Exception {

        XSSFWorkbook workbook = null;
        XSSFSheet sheet = null;

        try {
            File file = new File(excelPath);
            workbook = new XSSFWorkbook(file);
            sheet = workbook.getSheet(sheetName);
            if (sheet == null) {
                return false;
            }
            int lastRowNum = sheet.getLastRowNum();
            if (rowNo >= 0 && rowNo < lastRowNum) {
                sheet.shiftRows(rowNo + 1, lastRowNum, -1);
            }
            if (rowNo == lastRowNum) {
                XSSFRow removingRow = sheet.getRow(rowNo);
                if (removingRow != null) {
                    sheet.removeRow(removingRow);
                }
            }
            FileOutputStream outFile = new FileOutputStream(new File(excelPath));
            workbook.write(outFile);
            outFile.close();

        } catch (Exception e) {
            throw e;
        } finally {
            if (workbook != null)
                workbook.close();
        }
        return false;
    }

This is what happens

Cleaning up unclosed ZipFile for archive C:\temp\Report_XXXX.xlsx
Exception in thread "main" java.lang.reflect.InvocationTargetException
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.springframework.boot.loader.MainMethodRunner.run(MainMethodRunner.java:48)
        at org.springframework.boot.loader.Launcher.launch(Launcher.java:87)
        at org.springframework.boot.loader.Launcher.launch(Launcher.java:51)
        at org.springframework.boot.loader.JarLauncher.main(JarLauncher.java:52)
Caused by: org.apache.camel.CamelExecutionException: Exception occurred during execution on the exchange: Exchange[ID-DESKTOP-HPUQAQ7-1568621807670-2-1]
        at org.apache.camel.util.ObjectHelper.wrapCamelExecutionException(ObjectHelper.java:1842)
        at org.apache.camel.impl.DefaultExchange.setException(DefaultExchange.java:385)
        at org.apache.camel.component.bean.MethodInfo$1.proceed(MethodInfo.java:275)
        at org.apache.camel.component.bean.AbstractBeanProcessor.process(AbstractBeanProcessor.java:198)
        at org.apache.camel.component.bean.BeanProcessor.process(BeanProcessor.java:53)
        at org.apache.camel.component.bean.BeanProducer.process(BeanProducer.java:41)
        at org.apache.camel.processor.SendProcessor.process(SendProcessor.java:148)
        at org.apache.camel.processor.RedeliveryErrorHandler.process(RedeliveryErrorHandler.java:548)
        at org.apache.camel.processor.CamelInternalProcessor.process(CamelInternalProcessor.java:201)
        at org.apache.camel.processor.CamelInternalProcessor.process(CamelInternalProcessor.java:201)
        at org.apache.camel.component.direct.DirectProducer.process(DirectProducer.java:76)
        at org.apache.camel.processor.SharedCamelInternalProcessor.process(SharedCamelInternalProcessor.java:186)
        at org.apache.camel.processor.SharedCamelInternalProcessor.process(SharedCamelInternalProcessor.java:86)
        at org.apache.camel.impl.ProducerCache$1.doInProducer(ProducerCache.java:541)
        at org.apache.camel.impl.ProducerCache$1.doInProducer(ProducerCache.java:506)
        at org.apache.camel.impl.ProducerCache.doInProducer(ProducerCache.java:369)
        at org.apache.camel.impl.ProducerCache.sendExchange(ProducerCache.java:506)
        at org.apache.camel.impl.ProducerCache.send(ProducerCache.java:229)
        at org.apache.camel.impl.DefaultProducerTemplate.send(DefaultProducerTemplate.java:144)
        at org.apache.camel.impl.DefaultProducerTemplate.sendBody(DefaultProducerTemplate.java:161)
        at org.apache.camel.impl.DefaultProducerTemplate.sendBody(DefaultProducerTemplate.java:168)
        at it.carrefour.faat.RunFaatBatch.main(RunFaatBatch.java:26)
        ... 8 more
Caused by: java.lang.OutOfMemoryError: GC overhead limit exceeded
        at java.util.Arrays.copyOfRange(Arrays.java:3664)
        at java.lang.String.<init>(String.java:207)
        at com.sun.org.apache.xerces.internal.xni.XMLString.toString(XMLString.java:188)
        at com.sun.org.apache.xerces.internal.util.XMLAttributesImpl.getValue(XMLAttributesImpl.java:524)
        at com.sun.org.apache.xerces.internal.parsers.AbstractSAXParser$AttributesProxy.getValue(AbstractSAXParser.java:2321)
        at org.apache.xmlbeans.impl.store.Locale$SaxHandler.startElement(Locale.java:3198)
        at com.sun.org.apache.xerces.internal.parsers.AbstractSAXParser.startElement(AbstractSAXParser.java:509)
        at com.sun.org.apache.xerces.internal.impl.XMLNSDocumentScannerImpl.scanStartElement(XMLNSDocumentScannerImpl.java:374)
        at com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl$FragmentContentDriver.next(XMLDocumentFragmentScannerImpl.java:2784)
        at com.sun.org.apache.xerces.internal.impl.XMLDocumentScannerImpl.next(XMLDocumentScannerImpl.java:602)
        at com.sun.org.apache.xerces.internal.impl.XMLNSDocumentScannerImpl.next(XMLNSDocumentScannerImpl.java:112)
        at com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl.scanDocument(XMLDocumentFragmentScannerImpl.java:505)
        at com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(XML11Configuration.java:842)
        at com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(XML11Configuration.java:771)
        at com.sun.org.apache.xerces.internal.parsers.XMLParser.parse(XMLParser.java:141)
        at com.sun.org.apache.xerces.internal.parsers.AbstractSAXParser.parse(AbstractSAXParser.java:1213)
        at com.sun.org.apache.xerces.internal.jaxp.SAXParserImpl$JAXPSAXParser.parse(SAXParserImpl.java:643)
        at org.apache.xmlbeans.impl.store.Locale$SaxLoader.load(Locale.java:3414)
        at org.apache.xmlbeans.impl.store.Locale.parseToXmlObject(Locale.java:1272)
        at org.apache.xmlbeans.impl.store.Locale.parseToXmlObject(Locale.java:1259)
        at org.apache.xmlbeans.impl.schema.SchemaTypeLoaderBase.parse(SchemaTypeLoaderBase.java:345)
        at org.openxmlformats.schemas.spreadsheetml.x2006.main.WorksheetDocument$Factory.parse(Unknown Source)
        at org.apache.poi.xssf.usermodel.XSSFSheet.read(XSSFSheet.java:228)
        at org.apache.poi.xssf.usermodel.XSSFSheet.onDocumentRead(XSSFSheet.java:220)
        at org.apache.poi.xssf.usermodel.XSSFWorkbook.parseSheet(XSSFWorkbook.java:452)
        at org.apache.poi.xssf.usermodel.XSSFWorkbook.onDocumentRead(XSSFWorkbook.java:417)
        at org.apache.poi.ooxml.POIXMLDocument.load(POIXMLDocument.java:184)
        at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:286)
        at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:323)
        at it.carrefour.faat.jasperreport.JasperReportFill.deleteRow(JasperReportFill.java:106)
        at it.carrefour.faat.jasperreport.JasperReportFill.generateReport(JasperReportFill.java:84)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

Can someone please help and provide some example?

This is not possible using `apache poi`. The [XSSF and SAX (Event API)](https://poi.apache.org/components/spreadsheet/how-to.html#xssf_sax_api) is for reading only. The [SXSSF (Streaming Usermodel API)](https://poi.apache.org/components/spreadsheet/how-to.html#sxssf) is for writig only. But there is nothing for changing big `Excel` sheets directly. The only approach would be manipulating the sheet's `XML` using `Java` code. — Axel Richter, Sep 16 '19 at 10:35
I have shown this here https://stackoverflow.com/questions/46197278/how-to-set-cell-background-color-in-excel-using-java-poi/46217693#46217693 and here https://stackoverflow.com/questions/56739290/how-to-modify-a-large-excel-file-when-memory-is-an-issue/56757630#56757630. But such code is not easy to understand and deleting rows is much more complex because of the need shifting the following rows. — Axel Richter, Sep 16 '19 at 10:35
@AxelRichter thanks. Can another lib do the job easily? I'm not chained with apache-poi so I can explore more solutions. Otherwise I'll study your code. — Marco F., Sep 16 '19 at 11:06
I don't know any free `Java` library which is able to do so and don't need an installed `Excel` or `Calc`. There is `Aspose.Cells`. But this is not free. And there are free libraries which are able manipulating big sheets but are depending on installed `Excel` or `Calc` applications. — Axel Richter, Sep 16 '19 at 11:17
using https://github.com/monitorjbl/excel-streaming-reader to read the input file and SXSSFWorbook to write the output might be an option - both code bases are based on the same Workbook, Sheet, Row, Cell interfaces (but implementation classes are different) — PJ Fanning, Sep 16 '19 at 16:15
@PJ Fanning: The need for change data directly in a workbook mainly is to retain the dependencies and relations in that workbook as well as **all** the contents including drawings, charts, (pivot)tables, external references, styles, ..., intact. I am pretty sure that fully cloning the workbook by reading using `Excel Streaming Reader`and writing using `SXSSF` will be nearly impossible. Would be nice to see an example to convince me otherwise. — Axel Richter, Sep 16 '19 at 18:15

How to delete rows in a big xls/xlsx file with Apache-POI

0 Answers0