title: JRFileVirtualizer and JRXlsxExporter
body: <p>How to export a large xlsx file using JasperReport with JRFileVirtualizer? I need some sample codes</p>
  
  <p>Here's mine, but failed</p>
  
  <pre><code>package afs.view.servlets;
  
  import java.io.IOException;
  import java.io.OutputStream;
  
  import java.sql.Connection;
  
  import java.util.HashMap;
  import java.util.Map;
  
  import javax.naming.InitialContext;
  
  import javax.servlet.*;
  import javax.servlet.http.*;
  
  import javax.sql.DataSource;
  
  import net.sf.jasperreports.engine.JRException;
  import net.sf.jasperreports.engine.JRExporterParameter;
  import net.sf.jasperreports.engine.JRParameter;
  import net.sf.jasperreports.engine.JasperFillManager;
  import net.sf.jasperreports.engine.JasperPrint;
  import net.sf.jasperreports.engine.export.JRXlsExporterParameter;
  import net.sf.jasperreports.engine.export.ooxml.JRXlsxExporter;
  import net.sf.jasperreports.engine.fill.JRFileVirtualizer;
  
  public class ExcelPaymentPremiControl extends HttpServlet {
      private static final String CONTENT_TYPE = "text/html; charset=UTF-8";
  
      public void init(ServletConfig config) throws ServletException {
          super.init(config);
      }
  
      public void doGet(HttpServletRequest request,
                        HttpServletResponse response) throws ServletException,
                                                             IOException {
          response.setContentType(CONTENT_TYPE);
          /*PrintWriter out = response.getWriter();
          out.println("&lt;html&gt;");
          out.println("&lt;head&gt;&lt;title&gt;ExcelPaymentPremiControl&lt;/title&gt;&lt;/head&gt;");
          out.println("&lt;body&gt;");
          out.println("&lt;p&gt;The servlet has received a GET. This is the reply.&lt;/p&gt;");
          out.println("&lt;/body&gt;&lt;/html&gt;");
          out.close();*/
  
          System.out.println("================================");
          System.out.println("ExcelPaymentPremiControl_servlet");
          System.out.println("================================");
  
          try{
              String invoice_no = request.getParameter("invoice_no");
              String binding_no = request.getParameter("binding_no");
              String ref = request.getParameter("ref");
              String ref_reclass = request.getParameter("ref_reclass");
              String reinsured_name = request.getParameter("reinsured_name");
              String reinsurance_name = request.getParameter("reinsurance_name");
              String report = "C://jsreport/Binding/PaymentPremiControlExcel.jasper";
  
              Map map = new HashMap();
              map.put("P_INVOICE_NO", invoice_no);
              map.put("P_BINDING_NO", binding_no);
              map.put("P_REF", ref);
              map.put("P_REF_RECLASS", ref_reclass);
              map.put("P_REINSURED_NAME", reinsured_name);
              map.put("P_REINSURANCE_NAME", reinsurance_name);
  
              JRFileVirtualizer jRFileVirtualizer = new JRFileVirtualizer(1000, report);
              jRFileVirtualizer.setReadOnly(false);
              map.put(JRParameter.REPORT_VIRTUALIZER, jRFileVirtualizer);
  
              long start = System.currentTimeMillis();
  
              InitialContext initialContext = new InitialContext();
              DataSource ds =(DataSource)initialContext.lookup("java:comp/env/jdbc/Simasre-dev-ORDBMSDS"); // get from your application module configuration
              Connection conn = ds.getConnection(); 
  
              JasperPrint jasperPrint = JasperFillManager.fillReport(report, map, conn); //Here is line 77
              OutputStream ouputStream = response.getOutputStream();
  
              response.setHeader("Content-Disposition", "attachement; filename=\"ReportPaymentPremiControl.xlsx\"");
  
              JRXlsxExporter exporter = new JRXlsxExporter();
              exporter.setParameter(JRExporterParameter.JASPER_PRINT, jasperPrint);
              exporter.setParameter(JRXlsExporterParameter.IGNORE_PAGE_MARGINS, Boolean.FALSE);  
              exporter.setParameter(JRXlsExporterParameter.IS_COLLAPSE_ROW_SPAN, Boolean.TRUE); 
              exporter.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS, Boolean.TRUE);
              exporter.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_COLUMNS, Boolean.TRUE);
              exporter.setParameter(JRXlsExporterParameter.IS_DETECT_CELL_TYPE, Boolean.TRUE);
              exporter.setParameter(JRXlsExporterParameter.IS_WHITE_PAGE_BACKGROUND, Boolean.FALSE);
              exporter.setParameter(JRXlsExporterParameter.IS_IGNORE_GRAPHICS, Boolean.FALSE);
              exporter.setParameter(JRXlsExporterParameter.IS_IGNORE_CELL_BORDER, Boolean.FALSE);
              exporter.setParameter(JRExporterParameter.OUTPUT_STREAM, ouputStream);
  
              long end = System.currentTimeMillis();
              System.out.println("FILLING TIME ExcelPaymentPremiControl: "+((end-start)/1000)+" second(s)");
  
              try{
                  exporter.exportReport();
                  jRFileVirtualizer.cleanup();
              }catch(JRException jre){
                  jre.printStackTrace();
              }finally{
                  if(ouputStream != null){
                      try{
                          ouputStream.close();
                      }catch(Exception e){
                          e.printStackTrace();
                      }
                  }
                  conn.close();
              }
          }catch(Exception e){
              e.printStackTrace();
          }
  
          response.getOutputStream().close();
          response.setContentType(CONTENT_TYPE);
      }
  }
  </code></pre>
  
  <p>Here is the error stacktrace. The code was run just fine when exporting a xlsx file with small amount of row. But occurred when I was trying to export  40000++ rows xlsx files</p>
  
  <pre><code>java.io.IOException: The system cannot find the path specified
    at java.io.WinNTFileSystem.createFileExclusively(Native Method)
    at java.io.File.createNewFile(File.java:883)
    at net.sf.jasperreports.engine.fill.JRFileVirtualizer.pageOut(JRFileVirtualizer.java:113)
    at net.sf.jasperreports.engine.fill.JRAbstractLRUVirtualizer.virtualizeData(JRAbstractLRUVirtualizer.java:664)
    at net.sf.jasperreports.engine.fill.JRAbstractLRUVirtualizer.evict(JRAbstractLRUVirtualizer.java:485)
    at net.sf.jasperreports.engine.fill.JRAbstractLRUVirtualizer.registerObject(JRAbstractLRUVirtualizer.java:440)
    at net.sf.jasperreports.engine.base.ElementsBlock.register(VirtualizableElementList.java:249)
    at net.sf.jasperreports.engine.base.ElementsBlock.preAdd(VirtualizableElementList.java:298)
    at net.sf.jasperreports.engine.base.ElementsBlock.add(VirtualizableElementList.java:333)
    at net.sf.jasperreports.engine.base.ElementsBlock.add(VirtualizableElementList.java:349)
    at net.sf.jasperreports.engine.base.VirtualizableElementList.add(VirtualizableElementList.java:125)
    at net.sf.jasperreports.engine.base.JRVirtualPrintPage.addElement(JRVirtualPrintPage.java:136)
    at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillBand(JRVerticalFiller.java:2116)
    at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillColumnBand(JRVerticalFiller.java:2067)
    at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillDetail(JRVerticalFiller.java:778)
    at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReportContent(JRVerticalFiller.java:308)
    at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReport(JRVerticalFiller.java:155)
    at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:909)
    at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:822)
    at net.sf.jasperreports.engine.fill.JRFiller.fill(JRFiller.java:61)
    at net.sf.jasperreports.engine.JasperFillManager.fill(JasperFillManager.java:446)
    at net.sf.jasperreports.engine.JasperFillManager.fill(JasperFillManager.java:276)
    at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:745)
    at afs.view.servlets.ExcelPaymentPremiControl.doGet(ExcelPaymentPremiControl.java:77)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
    at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:227)
    at weblogic.servlet.internal.StubSecurityHelper.invokeServlet(StubSecurityHelper.java:125)
    at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:300)
    at weblogic.servlet.internal.TailFilter.doFilter(TailFilter.java:26)
    at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)
    at oracle.adf.library.webapp.LibraryFilter.doFilter(LibraryFilter.java:175)
  net.sf.jasperreports.engine.JRRuntimeException: java.io.IOException: The system cannot find the path specified
    at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)
    at oracle.security.jps.ee.http.JpsAbsFilter$1.run(JpsAbsFilter.java:111)
    at java.security.AccessController.doPrivileged(Native Method)
    at oracle.security.jps.util.JpsSubject.doAsPrivileged(JpsSubject.java:313)
    at oracle.security.jps.ee.util.JpsPlatformUtil.runJaasMode(JpsPlatformUtil.java:413)
    at oracle.security.jps.ee.http.JpsAbsFilter.runJaasMode(JpsAbsFilter.java:94)
    at oracle.security.jps.ee.http.JpsAbsFilter.doFilter(JpsAbsFilter.java:161)
    at oracle.security.jps.ee.http.JpsFilter.doFilter(JpsFilter.java:71)
    at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)
    at oracle.dms.servlet.DMSServletFilter.doFilter(DMSServletFilter.java:136)
    at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)
    at weblogic.servlet.internal.RequestEventsFilter.doFilter(RequestEventsFilter.java:27)
    at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)
    at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.wrapRun(WebAppServletContext.java:3715)
    at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:3681)
    at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:321)
    at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:120)
    at weblogic.servlet.internal.WebAppServletContext.securedExecute(WebAppServletContext.java:2277)
    at weblogic.servlet.internal.WebAppServletContext.execute(WebAppServletContext.java:2183)
    at weblogic.servlet.internal.ServletRequestImpl.run(ServletRequestImpl.java:1454)
    at weblogic.work.ExecuteThread.execute(ExecuteThread.java:207)
    at weblogic.work.ExecuteThread.run(ExecuteThread.java:176)
    at net.sf.jasperreports.engine.fill.JRAbstractLRUVirtualizer.virtualizeData(JRAbstractLRUVirtualizer.java:669)
    at net.sf.jasperreports.engine.fill.JRAbstractLRUVirtualizer.evict(JRAbstractLRUVirtualizer.java:485)
    at net.sf.jasperreports.engine.fill.JRAbstractLRUVirtualizer.registerObject(JRAbstractLRUVirtualizer.java:440)
    at net.sf.jasperreports.engine.base.ElementsBlock.register(VirtualizableElementList.java:249)
    at net.sf.jasperreports.engine.base.ElementsBlock.preAdd(VirtualizableElementList.java:298)
    at net.sf.jasperreports.engine.base.ElementsBlock.add(VirtualizableElementList.java:333)
    at net.sf.jasperreports.engine.base.ElementsBlock.add(VirtualizableElementList.java:349)
    at net.sf.jasperreports.engine.base.VirtualizableElementList.add(VirtualizableElementList.java:125)
    at net.sf.jasperreports.engine.base.JRVirtualPrintPage.addElement(JRVirtualPrintPage.java:136)
    at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillBand(JRVerticalFiller.java:2116)
    at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillColumnBand(JRVerticalFiller.java:2067)
    at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillDetail(JRVerticalFiller.java:778)
    at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReportContent(JRVerticalFiller.java:308)
    at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReport(JRVerticalFiller.java:155)
    at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:909)
    at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:822)
    at net.sf.jasperreports.engine.fill.JRFiller.fill(JRFiller.java:61)
    at net.sf.jasperreports.engine.JasperFillManager.fill(JasperFillManager.java:446)
    at net.sf.jasperreports.engine.JasperFillManager.fill(JasperFillManager.java:276)
    at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:745)
    at afs.view.servlets.ExcelPaymentPremiControl.doGet(ExcelPaymentPremiControl.java:77)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
    at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:227)
    at weblogic.servlet.internal.StubSecurityHelper.invokeServlet(StubSecurityHelper.java:125)
    at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:300)
    at weblogic.servlet.internal.TailFilter.doFilter(TailFilter.java:26)
    at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)
    at oracle.adf.library.webapp.LibraryFilter.doFilter(LibraryFilter.java:175)
    at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)
    at oracle.security.jps.ee.http.JpsAbsFilter$1.run(JpsAbsFilter.java:111)
    at java.security.AccessController.doPrivileged(Native Method)
    at oracle.security.jps.util.JpsSubject.doAsPrivileged(JpsSubject.java:313)
    at oracle.security.jps.ee.util.JpsPlatformUtil.runJaasMode(JpsPlatformUtil.java:413)
    at oracle.security.jps.ee.http.JpsAbsFilter.runJaasMode(JpsAbsFilter.java:94)
    at oracle.security.jps.ee.http.JpsAbsFilter.doFilter(JpsAbsFilter.java:161)
    at oracle.security.jps.ee.http.JpsFilter.doFilter(JpsFilter.java:71)
    at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)
    at oracle.dms.servlet.DMSServletFilter.doFilter(DMSServletFilter.java:136)
    at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)
    at weblogic.servlet.internal.RequestEventsFilter.doFilter(RequestEventsFilter.java:27)
    at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)
    at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.wrapRun(WebAppServletContext.java:3715)
    at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:3681)
    at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:321)
    at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:120)
    at weblogic.servlet.internal.WebAppServletContext.securedExecute(WebAppServletContext.java:2277)
    at weblogic.servlet.internal.WebAppServletContext.execute(WebAppServletContext.java:2183)
    at weblogic.servlet.internal.ServletRequestImpl.run(ServletRequestImpl.java:1454)
    at weblogic.work.ExecuteThread.execute(ExecuteThread.java:207)
    at weblogic.work.ExecuteThread.run(ExecuteThread.java:176)
  Caused by: java.io.IOException: The system cannot find the path specified
    at java.io.WinNTFileSystem.createFileExclusively(Native Method)
    at java.io.File.createNewFile(File.java:883)
    at net.sf.jasperreports.engine.fill.JRFileVirtualizer.pageOut(JRFileVirtualizer.java:113)
    at net.sf.jasperreports.engine.fill.JRAbstractLRUVirtualizer.virtualizeData(JRAbstractLRUVirtualizer.java:664)
    ... 50 more
  </code></pre>
  
  <p>The tags is the environments those I'm using right now</p>
  
  <p>Thanks before</p>