title: Efficient way to implement excel import in grails
body: <p>This code should probably go in code review but I won't get quick response there (Only 2 groovy questions there).
  I have the following code for importing data from excel into my grails application. The problem is that I didn't test for >1000 rows in the excel file so my app froze when my client tried to upload 13k rows. I have checked the stacktrace.log (app is in production) but no exception. The system admin thinks the jvm ran out of memory. We have increased the size of the heap memory. <strong>However, I want to ask if there's a better way to implement this.</strong> I am using apache poi and creating domain objects as I read each row from excel. After that, I pass the list of objects to the controller that validates and saves them in the database. <strong>Should I tell my client to limit number of items imported at a time?</strong>  <strong>Is there a better way to write this code?</strong></p>
  
  <pre><code>def importData(file, user){
      def rows = []
      def keywords = Keyword.list()
      int inventoryCount = Inventory.findAllByUser(user).size()
  
      def inventory = new Inventory(name:"Inventory ${inventoryCount +1}", user:user)
  
      Workbook workbook = WorkbookFactory.create(file)
      Sheet sheet = workbook.getSheetAt(0)
  
      int rowStart = 1;
      int rowEnd = sheet.getLastRowNum() + 1 ;
      for (int rowNum = rowStart; rowNum &lt; rowEnd; rowNum++) {
          Row r = sheet.getRow(rowNum);
  
          if(r != null &amp;&amp; r?.getCell(0, Row.RETURN_BLANK_AS_NULL)!=null ){
              def rowData =[:]
              int lastColumn = 8;
              for (int cn = 0; cn &lt; lastColumn; cn++) {
                  Cell c = r.getCell(cn, Row.RETURN_BLANK_AS_NULL);
                  if (c == null) {
                      return new ExcelFormatException("Empty cell not allowed",rowNum+1, cn+1)
                  } else {
  
                      def field = properties[cn+1]
                      if(field.type==c.getCellType()){
                          if(c.getCellType()==text){
  
                              rowData&lt;&lt;[(field.name):c.getStringCellValue().toString()]
                          }else if(c.getCellType()==numeric){
                              if(field.name.equalsIgnoreCase("price") ){
                                  rowData&lt;&lt;[(field.name):c.getNumericCellValue().toDouble()]
                              }else{
                                  rowData&lt;&lt;[(field.name):c.getNumericCellValue().toInteger()]
                              }
                          }
                      }else{
                          return new ExcelFormatException("Invalid value found", rowNum+1, cn+1)
                      }
                  }
  
              }
  
              def item = new InventoryItem(rowData)
              String keyword = retrieveKeyword(item.description, keywords)
              String criticality = keyword?"Critical":"Not known"
              int proposedMin = getProposedMin(item.usagePerYear)
              int proposedMax = getProposedMax(proposedMin, item.price, item.usagePerYear, item?.currentMin)
              String inventoryLevel = getInventoryLevel(item.usagePerYear, item.quantity, proposedMin, item.currentMin) 
  
              item.proposedMin = proposedMin
              item.proposedMax = proposedMax
              item.inventoryLevel = inventoryLevel
              item.keyword = keyword 
              item.criticality = criticality
  
              inventory.addToItems(item)
          }
  
      }
  
      return inventory
  }
  </code></pre>
  
  <p>Functions used in above code:</p>
  
  <pre><code>def retrieveKeyword(desc, keywords){
      def keyword
      for (key in keywords){
          if(desc.toLowerCase().contains(key.name.toLowerCase())){
              keyword = key.name
              break
          }
      }
      return keyword
  }
  int getProposedMin(int usage){
      (int) ((((usage/12)/30) *7) + 1)
  }
  
  
  int getProposedMax(int pmin, double price, int usage, int cmin){
      int c = price == 0? 1: ((Math.sqrt((24 * (usage/12)*5)/(0.15*price))) + (pmin - 1))
      if(cmin &gt;= c){
          return pmin
      }
      return c
  }
  
  String getInventoryLevel(int usage, int qty, int proposedMin, int currentMin){
      if(qty != 0){
          double c = usage/qty
          if(usage==0)
              return "Excess"
          if(c&lt;0.75){
              return "Inactive"
          }else if(proposedMin&lt;currentMin){
              return "Excess"
          }else if(c&gt;=0.75){
              return "Active"
          }
      }else if(usage==0 &amp;&amp; qty == 0){
          return "Not used"
      }else if(usage&gt;3 &amp;&amp; qty ==0){
          return "Insufficient"
      }else if(proposedMin &gt; currentMin){
          return "Insufficient"
      }
  
  }
  </code></pre>
  
  <p>Controller action:</p>
  
  <pre><code>def importData(){
      if(request.post){
          def file = request.getFile("excelFile")
  
          //validate file
          def file_types = ["application/vnd.ms-excel","application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"]
  
          if(!file_types.contains(file.getContentType())){
              render view:"importData", model:[error:"Invalid File type"]
              return
          }
  
          def inv = excelService.importData(file.getInputStream(),User.get(principal.id))
          if(inv){
              if(inv instanceof ExcelFormatException){
                  def err = (ExcelFormatException) inv
                  render view:"importData", model:[error:err.message +". Error occurred at: Row: "+err.row+" Col: "+err.col]
                  return
              }else{
                  render view:"viewData", model:[inventory:inv]
                  return
              }
          }
      }
  
  
  }
  </code></pre>