How to create large excel file with JasperReport and JExcelApi without OutMem?

[ggiannini]

HI to all!

I developed a Servlet application to generate report from a database using JasperReport to create the template report print (i used the File Virtualizer to resolve the initial out of memory issues). But i'm in trouble with the export of the *.jrprint object to Excel using JExcelApi implemented in the JasperReport Exporter.

With small report all works, but when generating a large report (about 200000 rows of 15 coloums splitted in two or more sheets of 65536 rows, using the MAX_ROW_PER_ SHEET property in JasperReport) the Out of Memory bring up!

I'm trying to modifying the source code of JExcelApi setting the useTemporaryFile to True and recompile it: with the small reports the temporary file is generated and all works, but with large report file, the temporary file is generated with 0 bytes and then, after a few minutes of memory ram growing to the max (the Xmx setting is 1024 in JVM), the Out of memory message bring up!

I suppose that the useTemporaryFile works only at one sheet a time, but when the sheet is large, this setting dont works.

Any suggestion will appreciate.

thank in advance

GIANFRANCO GIANNINI

[ggiannini]

Any suggestion will appreciate.

 

thank in advance

[dnvsrikanth]

Use Virtualizer friend!!!

 

DNV Srikanth

[ggiannini]

DNV Srikanth, in my question text i wrote i've used the File Virtualizer!! it work for generate the jrprint object, but it' not enough for exporting in Excel...

 

Thanks

 

-------------------------

>>...I developed a Servlet application to generate report from a database using JasperReport to create the template report print (i used the ***File Virtualizer*** to resolve the initial out of memory issues). But i'm in trouble with the export of the *.jrprint object to Excel using JExcelApi implemented in the JasperReport Exporter.

 

With small report all works, but when generating a large report (about 200000 rows of 15 coloums splitted in two or more sheets of 65536 rows, using the MAX_ROW_PER_ SHEET property in JasperReport) the Out of Memory bring up!

 

I'm trying to modifying the source code of JExcelApi setting the useTemporaryFile to True and recompile it: with the small reports the temporary file is generated and all works, but with large report file, the temporary file is generated with 0 bytes and then, after a few minutes of memory ram growing to the max (the Xmx setting is 1024 in JVM), the Out of memory message bring up!

 

I suppose that the useTemporaryFile works only at one sheet a time, but when the sheet is large, this setting dont works.

[dnvsrikanth]

Oh am sorry,

 

We also initially used File Virtualizer. As  we got some problems with File Virtualizer we are now using JRSwapFileVirtualizerto solve the out of memory problem.

 

I didnt use MAX_ROW_PER_ SHEET in Excel, but I hope using Swap File Virualizer may solve the problem.

 

DNV Srikanth

[ggiannini]

Thanks for your help, DNV Srikanth,

I've used JRSwapFileFirtualizer, the problem is that this type of virtualizer helps only in creating the filled report (the .jrprint object),

                JRAbstractLRUVirtualizer virtualizer = null;
                JRSwapFile swapFile = new JRSwapFile(WARDirectory + "/Upload/", 1024, 1024);
                virtualizer = new JRSwapFileVirtualizer(1, swapFile, true);
                JRProperties.setProperty("net.sf.jasperreports.export.xls.exclude.origin.keep.first.band.1", "columnHeader");
              
                JRResultSetDataSource resultSetDataSource = new JRResultSetDataSource(rsDati);
              
                HashMap parameterMap = new HashMap();
                parameterMap.put(JRParameter.REPORT_VIRTUALIZER, virtualizer);
           

                JasperFillManager.fillReportToFile(WARDirectory + "/Upload/" + request.getSession().getId() +
                 TemplateName.substring(0,TemplateName.length()-6) + ".jasper", WARDirectory + "/Upload/" + request.getSession
                 ().getId() + TemplateName.substring(0,TemplateName.length()-6) + ".jrprint"  , parameterMap, resultSetDataSource);

                rsDati.close();
                virtualizer.cleanup();

That code works!!

The problem come In exporting the jrprint object in xls, JasperReport uses third-part library (JExcelApi or Apache POI), with no help in limiting memory growing.

  JRXlsExporter xlsExporter = new JRXlsExporter();
              
                xlsExporter.setParameter(JExcelApiExporterParameter.JASPER_PRINT, jasperPrint);
                xlsExporter.setParameter(JExcelApiExporterParameter.MAXIMUM_ROWS_PER_SHEET, 65536);
            
                xlsExporter.setParameter(JExcelApiExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS,  Boolean.TRUE);
                xlsExporter.setParameter(JExcelApiExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_COLUMNS,  Boolean.TRUE);
                xlsExporter.setParameter(JExcelApiExporterParameter.IS_DETECT_CELL_TYPE,  Boolean.TRUE);
                xlsExporter.setParameter(JExcelApiExporterParameter.IS_ONE_PAGE_PER_SHEET,  Boolean.FALSE);
                xlsExporter.setParameter(JExcelApiExporterParameter.OUTPUT_FILE_NAME, WARDirectory + "/Upload/" + request.getSession().getId() + TemplateName.substring(0,TemplateName.length()-6) + ".xls");

                xlsExporter.exportReport();

In that code, the Ram memory grows until the out of memory brings up (the JVM memory allocation in Java Application Server  is XmX1024m)

In JExcelApi there is a new function "UseTEmporaryFileDuringWrite" but is usefull only in excel file with more worksheet: if, like my case, the large amount of data is in one sheet, this functionality fails!

I tried to modifying the source code of that JExcelApi but with no good results.

I tried to write to the programmer of JexcelApi but no reply from him! and also i tried to write to Teodore Danciu (programmer of Jasper Reports) with no reply from him...

It's a big limitation of export libraries if they have no swap file mechanisms!

 

[ggiannini]

I've used JRSwapFileFirtualizer, the problem is that this type of virtualizer helps only in creating the filled report (the .jrprint object),

   
The problem come In exporting the jrprint object in xls, JasperReport uses third-part library (JExcelApi or Apache POI), with no help in limiting memory growing.

 

In that code, the Ram memory grows until the out of memory brings up (the JVM memory allocation in Java Application Server  is XmX1024m)

In JExcelApi there is a new function "UseTEmporaryFileDuringWrite" but is usefull only in excel file with more worksheet: if, like my case, the large amount of data is in one sheet, this functionality fails!

I tried to modifying the source code of that JExcelApi but with no good results.

I tried to write to the programmer of JexcelApi but no reply from him! and also i tried to write to Teodore Danciu (programmer of Jasper Reports) with no reply from him...

It's a big limitation of export libraries if they have no swap file mechanisms!

Code:

         JRAbstractLRUVirtualizer virtualizer = null;                JRSwapFile swapFile = new JRSwapFile(WARDirectory + "/Upload/", 1024, 1024);                virtualizer = new JRSwapFileVirtualizer(1, swapFile, true);                JRProperties.setProperty("net.sf.jasperreports.export.xls.exclude.origin.keep.first.band.1", "columnHeader");                              JRResultSetDataSource resultSetDataSource = new JRResultSetDataSource(rsDati);                              HashMap parameterMap = new HashMap();                parameterMap.put(JRParameter.REPORT_VIRTUALIZER, virtualizer);                           JasperFillManager.fillReportToFile(WARDirectory + "/Upload/" + request.getSession().getId() +                 TemplateName.substring(0,TemplateName.length()-6) + ".jasper", WARDirectory + "/Upload/" + request.getSession                 ().getId() + TemplateName.substring(0,TemplateName.length()-6) + ".jrprint"  , parameterMap, resultSetDataSource);                rsDati.close();                virtualizer.cleanup();JRXlsExporter xlsExporter = new JRXlsExporter();                              xlsExporter.setParameter(JExcelApiExporterParameter.JASPER_PRINT, jasperPrint);                xlsExporter.setParameter(JExcelApiExporterParameter.MAXIMUM_ROWS_PER_SHEET, 65536);                            xlsExporter.setParameter(JExcelApiExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS,  Boolean.TRUE);                xlsExporter.setParameter(JExcelApiExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_COLUMNS,  Boolean.TRUE);                xlsExporter.setParameter(JExcelApiExporterParameter.IS_DETECT_CELL_TYPE,  Boolean.TRUE);                xlsExporter.setParameter(JExcelApiExporterParameter.IS_ONE_PAGE_PER_SHEET,  Boolean.FALSE);                xlsExporter.setParameter(JExcelApiExporterParameter.OUTPUT_FILE_NAME, WARDirectory + "/Upload/" + request.getSession().getId() + TemplateName.substring(0,TemplateName.length()-6) + ".xls");                xlsExporter.exportReport(); 

[ggiannini]

I just trying to modifying the JExcelApi source code and i've found in it that the "UseTemporaryFileDuringWrite" is used only on a sheet at once.

[lucianc]

We're aware of the memory issues related to exporting large sheets with JExcelApi.  We have not found a workaround for this to this day.

Let us know how your attempt to address this goes.  Your contribution is greatly appreciated.

Regards,

Lucian

[ggiannini]

Thank for your reply, Lucian!

Looking at the JExcelApi source code, it seems that the simple swap mechanism of "UseTemporaryFileDuringWrite" works only at a sheet at once: it writes the swap file only at the end of sheet creation (??): perhaps, modifying the code in a way that the swap file is created every row creation,  the swap mechanism really works!

I'm trying to adapt the Jasper Report swap mechanism, looking at his source code, for use in JExcelApi simple swap mechanism

I'm glad if i could contribute to this project, but i contact Andrew Khan (the JExcelApi programmer) to this problem but no reply from him.

Gianfranco Giannini

 

 

 

 

Code:

---- part of JExcelApi source code implementing very simple swap mechanism ---  /***********************************************************************      Copyright (C) 2007 Andrew Khan** This library is free software; you can redistribute it and/or* modify it under the terms of the GNU Lesser General Public* License as published by the Free Software Foundation; either* version 2.1 of the License, or (at your option) any later version.** This library is distributed in the hope that it will be useful,* but WITHOUT ANY WARRANTY; without even the implied warranty of* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU* Lesser General Public License for more details.** You should have received a copy of the GNU Lesser General Public* License along with this library; if not, write to the Free Software* Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA***************************************************************************/package jxl.write.biff;import java.io.OutputStream;import java.io.IOException;import java.io.File;import java.io.RandomAccessFile;import jxl.common.Logger;/** * Used to generate the excel biff data using a temporary file.  This * class wraps a RandomAccessFile */class FileDataOutput implements ExcelDataOutput{  // The logger  private static Logger logger = Logger.getLogger(FileDataOutput.class);  /**    * The temporary file   */  private File temporaryFile;  /**   * The excel data   */  private RandomAccessFile data;  /**   * Constructor   *   * @param tmpdir the temporary directory used to write files.  If this is   *               NULL then the sytem temporary directory will be used   */  public FileDataOutput(File tmpdir) throws IOException  {    temporaryFile = File.createTempFile("jxl",".tmp", tmpdir);   // temporaryFile.deleteOnExit();    data = new RandomAccessFile(temporaryFile, "rw");  }  /**   * Writes the bytes to the end of the array, growing the array   * as needs dictate   *   * @param d the data to write to the end of the array   */  public void write(byte[] bytes) throws IOException  {    data.write(bytes);  }  /**   * Gets the current position within the file   *   * @return the position within the file   */  public int getPosition() throws IOException  {    // As all excel data structures are four bytes anyway, it's ok to     // truncate the long to an int    return (int) data.getFilePointer();  }  /**   * Sets the data at the specified position to the contents of the array   *    * @param pos the position to alter   * @param newdata the data to modify   */  public void setData(byte[] newdata, int pos) throws IOException  {    long curpos = data.getFilePointer();    data.seek(pos);    data.write(newdata);    data.seek(curpos);  }  /**    * Writes the data to the output stream   */  public void writeData(OutputStream out) throws IOException  {		byte[] buffer = new byte[1024];		int length = 0;		data.seek(0);		while ((length = data.read(buffer)) != -1)		{			out.write(buffer, 0, length);		}  }  /**   * Called when the final compound file has been written   */  public void close() throws IOException  {    data.close();    // Explicitly delete the temporary file, since sometimes it is the case    // that a single process may be generating multiple different excel files   // temporaryFile.delete();  }}

[lucianc]

Hopefully the JExcelApi project lead will provide some guidance on this.

Here at JasperReports we have not yet gone deep into the inner workings of JExcelApi so we can't say whether improving its temp file swapping mechanism is feasible.  In any case, if there's anything we could do to help you in your investigation, let us know.

Regards,

Lucian

[rahulbhatt04]

Hi Gianfranco

Can you share your custmized JExcel API with me I am also sharing same problem

 

[ggiannini]

hi rahulbhatt04,

If you are a Java Programmer, you're welcome to contribute in this project, so download the JExcelApi source code and looking at FIle.java,SheetWriter.java and FileDataOutput.java for the swap mechanism: it uses the swap file only  a sheet at once, so it is useless in the case of large sheet.

I see the problem in JExcelApi code, that when we modify a Sheet, for example, insert a new Row at Position 2, then all related formulas must be updated, and for this we must have them "accessible" in RAM Memory in an array!

So, i'm trying to implement an "isFormulaEnable" method to disable the need to have formulas in a sheet (if we're sure that no formulas is in that sheet, of course!)

Gianfranco

PS: in the part of source code the section in File.jave when i forced to use swap file.

The other code is FileDataOutput.java in the section of creating the swap file

 

 

 

Code:

 private void createDataOutput() throws IOException  {   // if (workbookSettings.getUseTemporaryFileDuringWrite())  //  {      data = new FileDataOutput        (workbookSettings.getTemporaryFileDuringWriteDirectory());  //  }  //  else  //  {  //    initialFileSize = workbookSettings.getInitialFileSize();  //    arrayGrowSize = workbookSettings.getArrayGrowSize();  //    data = new MemoryDataOutput(initialFileSize, arrayGrowSize);  //  }  }------------------------------------------  public FileDataOutput(File tmpdir) throws IOException  {    temporaryFile = File.createTempFile("jxl",".tmp", tmpdir);   // temporaryFile.deleteOnExit();    data = new RandomAccessFile(temporaryFile, "rw");  }-------------------------------------------

[snem]

 Is there any update about this issue?

Regards

[jawwadfarooq1]

After about 2 years, still there is no solution to the problem.

Any suggestions how to create large Excel files?

[vsawant]

It has now been 5 years - I am using 5.5 and I ran into this issue today - still no solution from Jasper?

[tikendra.verma]

Tried some trial and err with JRVirtualizer but still OOME is thrown...........i hv tried max page size 2,5,100 all..using latest jasperreports version