title: Creating large .xls with SXSSF
body: <p>I've got problem with exporting large .xls with SXSSF, saying large I mean 27 cols x 100 000 rows. Excel file is return on endpoint request. I've limited amount of rows - it can be 3x larger.</p>
  
  <p>I'm using template engine for inserting data.</p>
  
  <p><strong>Original code</strong></p>
  
  <pre><code>public StreamingOutput createStreamedExcelReport(Map&lt;String, Object&gt; params, String templateName, String[] columnsToHide) throws Exception {
          try(InputStream is = ReportGenerator.class.getResourceAsStream(templateName)) {
              assert is != null;
              final Transformer transformer = PoiTransformer.createTransformer(is);
              AreaBuilder areaBuilder = new XlsCommentAreaBuilder(transformer);
              List&lt;Area&gt; xlsAreaList = areaBuilder.build();
              Area xlsArea = xlsAreaList.get(0);
              Context context = new PoiContext();
              for(Map.Entry&lt;String, Object&gt; entry : params.entrySet()) {
                  context.putVar(entry.getKey(), entry.getValue());
              }
              xlsArea.applyAt(new CellRef("Sheet1!A1"), context);
              xlsArea.processFormulas();
              return new StreamingOutput() {
                  @Override
                  public void write(OutputStream out) throws IOException {
                      ((PoiTransformer) transformer).getWorkbook().write(out);
                  }
              };
          }
      }
  </code></pre>
  
  <p><strong>SXSSF</strong></p>
  
  <pre><code>public StreamingOutput createStreamedExcelReport(Map&lt;String, Object&gt; params, String templateName, String[] columnsToHide) throws Exception {
          try(InputStream is = ReportGenerator.class.getResourceAsStream(templateName)) {
              assert is != null;
              Workbook workbook = WorkbookFactory.create(is);
              final PoiTransformer transformer = PoiTransformer.createSxssfTransformer(workbook);
              AreaBuilder areaBuilder = new XlsCommentAreaBuilder(transformer);
              List&lt;Area&gt; xlsAreaList = areaBuilder.build();
              Area xlsArea = xlsAreaList.get(0);
              Context context = new PoiContext();
              for(Map.Entry&lt;String, Object&gt; entry : params.entrySet()) {
                  context.putVar(entry.getKey(), entry.getValue());
              }
              xlsArea.applyAt(new CellRef("Sheet1!A1"), context);
              xlsArea.processFormulas();
              return new StreamingOutput() {
                  @Override
                  public void write(OutputStream out) throws IOException {
                      transformer.getWorkbook().write(out);
                  }
              };
          }
      }
  </code></pre>
  
  <p>Export was running for 7 mins and I stopped server - it was too long. Acceptable time would be something like 1 min (max. 2 min). Most of that time CPU usage was about 60-80% and memory usage was constant. I tried also exporting 40 rows - it took something like 10 sec. </p>
  
  <p>Maybe my function needs to be optimized.</p>
  
  <p>Additional problem is that I'm inserting functions. In original code functions are replaced with values. In SXSSF version they are not.</p>