java - 如何使用 Apache-POI 删除大 xls/xlsx 文件中的行

Question

我正在尝试删除我的 xlsx 文件的第二张和第三张表的第一行，但是对于巨大的文件，我会得到 Java 热空间异常或 GC 开销。

我正在使用 Apache-POI，我不明白如何使用 XSSF 和 SAX。

我的实际代码如下：

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;
    }

这就是发生的事情

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)

有人可以帮忙并提供一些例子吗？

标签： javaexcelapache-poixlsxxls