Efficient way to read XLSX file and store data in Grails

Question

I need to read an XLSX file which contains about 50 thousand lines in 5 sheets and has about 7 MB in Grails.

I need to read a file sheet by sheet and store each row in a database table.

but i get

Java heap space. Stacktrace follows:
Message: Executing action [abx] of controller [abc.xyz.controller]  caused exception: Runtime error executing action
        Line | Method
    ->>  198 | doFilter                 in grails.plugin.cache.web.filter.PageFragmentCachingFilter

I tried to increase the heap space by setting "GRAILS_OPTS" as

GRAILS_OPTS=-XX:MaxPermSize=128m -XX:PermSize=128m -Xms1024m -Xmx1024m -XX:-UseGCOverheadLimit

but didn't work at all.

I came across this question

How to read an XLSX file> 40MB in size , but there is no proper implementation here.

I tried using SAX to read XML from an XLSX file, following an example using doc

http://poi.apache.org/spreadsheet/how-to.html

Grails Controller:

//      
//
        OPCPackage pkg = OPCPackage.open(filename);
        XSSFReader r = new XSSFReader( pkg );
        SharedStringsTable sst = r.getSharedStringsTable();

        XMLReader parser1 =
                XMLReaderFactory.createXMLReader(
                        "org.apache.xerces.parsers.SAXParser"
                );
        ContentHandler handler = new SheetHandler(sst);

 //
//

class SheetHandler.java

class SheetHandler extends DefaultHandler {
    private SharedStringsTable sst;
    private String lastContents;
    private boolean nextIsString;
    private List<String> rowData


    private SheetHandler(SharedStringsTable sst) {
        rowData = []
        this.sst = sst;
    }

    public void startElement(String uri, String localName, String name,
                             Attributes attributes) throws SAXException {

        // c => cell
        if(name.equals("c")) {
            // Print the cell reference
            //System.out.print(attributes.getValue("r") + " - ");
            // Figure out if the value is an index in the SST
            String cellType = attributes.getValue("t");
            if(cellType != null && cellType.equals("s")) {
                nextIsString = true;
            } else {
                nextIsString = false;
            }
        }
        // Clear contents cache
        lastContents = "";
    }

    public void endElement(String uri, String localName, String name)
            throws SAXException {
        if(name  == "row"){
            println rowData
            rowData = []
        }
        // Process the last contents as required.
        // Do now, as characters() may be called more than once
        if(nextIsString) {
            int idx = Integer.parseInt(lastContents);
            lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString();
            nextIsString = false;
        }

        // v => contents of a cell
        // Output after we've seen the string contents
        if(name.equals("v")) {
            rowData << lastContents
            System.out.println(lastContents);
        }
    }

    public void characters(char[] ch, int start, int length)
            throws SAXException {
        lastContents += new String(ch, start, length);
    }
}

, "" " xlsx". .

XSSFReader, , .

Answer 1

Shashank . , , , . xlsx xml

 <r> for row 
 <c> for cell
 <v> for value

 <r><c t="s" r="A32" s="50"><v>value in the cell</v></c></r> if there is a value in the cell
 <r><c t="s" r="A32" s="50"></c></r> if there is no value in the cell.

, 8 , 3- 5- , , xml, : ( startElement endElement)

r cvvc cvvc cc cvvc cc cvvc cvvc cvvc r

cvvc

<c><v></v></c>

, , startElement endElement c , , , rowData​​p >

  rowData << ""

, .

, .