Groovy转换XLSX到CSV遇GC overhead limit exceeded错误求助

Hey there, let's fix that out-of-memory issue you're running into with large XLSX files in your Groovy script. We've got two key things to address here: adjusting how much memory Groovy can use, and rewriting your code to avoid loading the entire Excel file into memory at once. Let's dive in.

1. 给Groovy脚本分配更多堆内存 #

First off, if you're running your script from the command line, you can pass a VM argument to increase the maximum heap size directly:

groovy -Xmx2g your-script-name.groovy

The -Xmx2g flag sets the maximum heap memory to 2 gigabytes. Feel free to bump this up to -Xmx4g or -Xmx8g if your file is extra large—just match it to what your system can handle.

If you're using an IDE like IntelliJ IDEA to run the script:

• Go to Run > Edit Configurations
• Find your Groovy script's configuration
• In the VM options field, enter -Xmx2g (or a larger value)
• Save the config and re-run your script

2. 优化代码：用POI的SAX流式解析处理大文件 #

While increasing memory can work for slightly larger files, it's not a long-term fix for huge spreadsheets. The real solution is to use POI's SAX-based streaming parser, which reads the Excel file line by line instead of loading everything into memory at once. This keeps your memory usage super low, even for massive files.

Here's a revised version of your script using this approach:

@Grapes([
    @Grab(group='org.apache.poi', module='poi-ooxml', version='3.9'),
    @Grab(group='org.apache.poi', module='poi', version='3.9')
])
import org.apache.poi.openxml4j.opc.OPCPackage
import org.apache.poi.xssf.eventusermodel.XSSFReader
import org.apache.poi.xssf.model.SharedStringsTable
import org.apache.poi.xssf.usermodel.XSSFRichTextString
import org.xml.sax.Attributes
import org.xml.sax.InputSource
import org.xml.sax.SAXException
import org.xml.sax.XMLReader
import org.xml.sax.helpers.DefaultHandler
import org.xml.sax.helpers.XMLReaderFactory

class ExcelToCsvHandler extends DefaultHandler {
    private SharedStringsTable sst
    private String currentContent
    private boolean isCellValue
    private PrintWriter printWriter
    private int lastCol = -1
    private boolean firstCellInRow = true

    ExcelToCsvHandler(SharedStringsTable sst, PrintWriter printWriter) {
        this.sst = sst
        this.printWriter = printWriter
    }

    @Override
    void startElement(String uri, String localName, String qName, Attributes attributes) throws SAXException {
        if ("c".equals(qName)) {
            // Check if this cell holds a shared string
            String cellType = attributes.getValue("t")
            isCellValue = "s".equals(cellType) || "str".equals(cellType)
            
            // Calculate current column index
            String cellRef = attributes.getValue("r")
            int currentCol = cellRef.replaceAll("[^0-9]", "").toInteger() - 1
            
            // Fill empty columns between last cell and current cell
            if (!firstCellInRow) {
                for (int i = lastCol + 1; i < currentCol; i++) {
                    printWriter.print("\"\";")
                }
            }
            lastCol = currentCol
        }
        currentContent = ""
    }

    @Override
    void characters(char[] ch, int start, int length) throws SAXException {
        if (isCellValue) {
            currentContent += new String(ch, start, length)
        }
    }

    @Override
    void endElement(String uri, String localName, String qName) throws SAXException {
        if ("v".equals(qName)) {
            // Resolve cell value (handle shared strings)
            String value = isCellValue 
                ? new XSSFRichTextString(sst.getEntryAt(currentContent.toInteger())).toString() 
                : currentContent
            
            // Escape double quotes in value for valid CSV
            value = value.replace('"', '""')
            printWriter.print("\"${value}\";")
            
            isCellValue = false
            firstCellInRow = false
        } else if ("row".equals(qName)) {
            // End of row, add newline and reset column tracking
            printWriter.println()
            lastCol = -1
            firstCellInRow = true
        }
    }
}

def convertXlsxToCsv(String inputPath) {
    OPCPackage pkg = OPCPackage.open(inputPath)
    try {
        XSSFReader reader = new XSSFReader(pkg)
        SharedStringsTable sharedStrings = reader.getSharedStringsTable()

        XMLReader parser = XMLReaderFactory.createXMLReader()
        parser.setContentHandler(new ExcelToCsvHandler(sharedStrings, new PrintWriter(System.out)))

        // Iterate through all sheets in the workbook
        XSSFReader.SheetIterator sheetIterator = reader.getSheetsData()
        while (sheetIterator.hasNext()) {
            InputStream sheetStream = sheetIterator.next()
            String sheetName = sheetIterator.getSheetName()
            
            println sheetName
            parser.parse(new InputSource(sheetStream))
            
            sheetStream.close()
        }
    } finally {
        pkg.close()
    }
}

convertXlsxToCsv("venrx.xlsx")

Quick breakdown of the optimized code: #

• The ExcelToCsvHandler class handles SAX parsing events, which lets us process each cell and row as we encounter them, without loading the whole file
• It handles shared strings (a common optimization in XLSX files) and properly escapes double quotes for valid CSV output
• We iterate through each sheet's data stream instead of loading the entire workbook into memory

Why your original code hit an OOM error #

Your original code used WorkbookFactory.create(), which loads every sheet, row, and cell from the Excel file into memory all at once. A 20MB XLSX file can expand to hundreds of megabytes (or even gigabytes) in memory because of how POI stores the data internally. This quickly overwhelms the default heap size and triggers the GC overhead limit error.

内容的提问来源于stack exchange，提问作者user8862613