title: Efficient way to read a XLSX file and storing the data in Grails
body: <p>I need to read a XLSX file which contains around 50K rows in 5 sheets and is of about 7MB in Grails.</p>
  
  <p>I need to read the file sheet by sheet and need to store each row to a database table.</p>
  
  <p>but i am getting </p>
  
  <pre><code>Java heap space. Stacktrace follows:
  Message: Executing action [abx] of controller [abc.xyz.controller]  caused exception: Runtime error executing action
          Line | Method
      -&gt;&gt;  198 | doFilter                 in grails.plugin.cache.web.filter.PageFragmentCachingFilter
  </code></pre>
  
  <p>I had tried increasing Heap space by setting up "GRAILS_OPTS" as</p>
  
  <pre><code>GRAILS_OPTS=-XX:MaxPermSize=128m -XX:PermSize=128m -Xms1024m -Xmx1024m -XX:-UseGCOverheadLimit
  </code></pre>
  
  <p>but not worked at all.</p>
  
  <p>I came across this question </p>
  
  <p><a href="https://stackoverflow.com/questions/11345146/how-to-read-xlsx-file-of-size-40mb">How to read XLSX file of size &gt;40MB</a> but no proper implementation is available here.</p>
  
  <p>I tried using SAX for reading the XML from the XLSX file by following way from its how to use doc</p>
  
  <p><a href="http://poi.apache.org/spreadsheet/how-to.html" rel="nofollow noreferrer">http://poi.apache.org/spreadsheet/how-to.html</a></p>
  
  <p>Grails controller: </p>
  
  <pre><code>//      
  //
          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);
  
   //
  //
  </code></pre>
  
  <p>class SheetHandler.java</p>
  
  <pre><code>class SheetHandler extends DefaultHandler {
      private SharedStringsTable sst;
      private String lastContents;
      private boolean nextIsString;
      private List&lt;String&gt; rowData
  
  
      private SheetHandler(SharedStringsTable sst) {
          rowData = []
          this.sst = sst;
      }
  
      public void startElement(String uri, String localName, String name,
                               Attributes attributes) throws SAXException {
  
          // c =&gt; 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 &amp;&amp; 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 =&gt; contents of a cell
          // Output after we've seen the string contents
          if(name.equals("v")) {
              rowData &lt;&lt; lastContents
              System.out.println(lastContents);
          }
      }
  
      public void characters(char[] ch, int start, int length)
              throws SAXException {
          lastContents += new String(ch, start, length);
      }
  }
  </code></pre>
  
  <p>By implementing the above i am unable to distinguish "Labels" from the "rows of xlsx file". and i am also unable to get the null values of columns.</p>
  
  <p>I am unable to use the XSSFReader properly please help me in solving the problem.</p>