Export Excel Performance

[matth33]

Hi,

I have to produce an excel report with several sheets. Each each use a different datasource.

One of them is a big one.

About 100000 rows and 30 columns. I have to limit the size of that sheet to 65 000 line.

S i have made de the following think :

+ In my .jasper file, ignore pagination is false.

+ I have turn the page height to (65000 * detail band heigt)+ column header ban height

I should two sheets generated.

So, in my java programme that run the report, i use virtualizer to limit memory consumption.

 I ran my programme with -Xms1024m -Xmx1500m argument.

But i ran into out of memory error.

What's the problem with my code ? Is there anoter way to increase performance of filling report or exporting report ?

Code:

// Init des option d'exportJRXlsExporter exporter = new JRXlsExporter();//exporter.setParameter(JRXlsExporterParameter.MAXIMUM_ROWS_PER_SHEET, 65000);exporter.setParameter(JRXlsExporterParameter.IS_DETECT_CELL_TYPE, Boolean.TRUE);exporter.setParameter(JRXlsExporterParameter.IS_WHITE_PAGE_BACKGROUND, Boolean.FALSE);exporter.setParameter(JRXlsExporterParameter.IS_FONT_SIZE_FIX_ENABLED, Boolean.TRUE); exporter.setParameter(JRXlsExporterParameter.IS_IGNORE_CELL_BORDER, Boolean.FALSE);exporter.setParameter(JRXlsExporterParameter.IS_ONE_PAGE_PER_SHEET, Boolean.TRUE); // gestion des onglets dynamiqueexporter.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS, Boolean.FALSE) ;Connection conn = dataSource.getConnection(); // creating the virtualizerJRSwapFile swapFile = new JRSwapFile("D:\\temp", 2048, 2048);virtualizer = new JRSwapFileVirtualizer(1, swapFile, true); List jasperPrint = new ArrayList();// Onglet Tableau généralloggerUtils.logProcess(DISPLAYLOG,"INFO"," ---> $ETAPE - Debut Tableau General"); HashMap paramTableauGeneral= new HashMap();paramTableauGeneral.put("ANNEE",annee);paramTableauGeneral.put("ORIGINE",origine);paramTableauGeneral.put(JRParameter.REPORT_VIRTUALIZER, virtualizer); StringBuffer fichierTableauGeneral = new StringBuffer(REP_FICHIER_JASPER).append(FICHIER_JASPER_TABLEAU_GENERAL);JasperPrint jpTableauGeneral = JasperFillManager.fillReport(fichierTableauGeneral.toString(),paramTableauGeneral, conn);jasperPrint.add(jpTableauGeneral);loggerUtils.logProcess(DISPLAYLOG,"INFO"," ---> $ETAPE - Fin Tableau General"); if (virtualizer != null){virtualizer.setReadOnly(true); }// Generation finaleloggerUtils.logProcess(DISPLAYLOG,"INFO"," ---> $ETAPE - Debut assemblage final");Set key = exporter.getParameters().keySet();exporter.setParameter(JRExporterParameter.JASPER_PRINT_LIST, jasperPrint);println("FICHIER SORTIE" + REP_PRODUCTION_ETAT+nomFichier);exporter.setParameter(JRExporterParameter.OUTPUT_FILE_NAME,REP_PRODUCTION_ETAT+nomFichier);exporter.exportReport();listeFichierProduit.add(nomFichier);loggerUtils.logProcess(DISPLAYLOG,"INFO"," ---> $ETAPE - Fin assemblage final");if (virtualizer != null){virtualizer.cleanup();}conn.close();

[matth33]

No idea with that problem ?

There is no solution to increase excel export performance ?

[neozerabbit]

I do not use swap file virtualize. I virtualize by report. Perhaps you should not set virtualizer to read only.

I am not sure.

 

I DO :

 virtualizer = new JRFileVirtualizer(1, tmpDirectory);

...

reportParameters.put(JRParameter.REPORT_VIRTUALIZER, virtualizer);

...

 JasperPrint reportPrint = reportGenerationBusinessService.fillReport(reportObj, reportParameters, new JREmptyDataSource());

....

export to XLS

 

I made test with more than 20 000 lines of data

 

I do not use the JExcel exporter because it is low. I use POI exporter (like you, it seems)

Here my params :

logger.debug("Start Export XLS POI");
        long nanoTime = System.nanoTime();
        File reportOutputFile = createExportFile(reportFileName, ".xls");
               
        JRXlsExporter exporterJRX = new JRXlsExporter();
        exporterJRX.setParameter(JRExporterParameter.OUTPUT_FILE, reportOutputFile);
        exporterJRX.setParameter(JRExporterParameter.JASPER_PRINT, jasperPrint);
        exporterJRX.setParameter(JRExporterParameter.CHARACTER_ENCODING, "UTF8");
        exporterJRX.setParameter(JRXlsExporterParameter.IS_ONE_PAGE_PER_SHEET, true);       
        exporterJRX.setParameter(JRXlsExporterParameter.SHEET_NAMES, sheetsName);
        exporterJRX.setParameter(JRXlsExporterParameter.MAXIMUM_ROWS_PER_SHEET, 65000);
        exporterJRX.setParameter(JRXlsExporterParameter.IS_DETECT_CELL_TYPE, true);
        exporterJRX.setParameter(JRXlsExporterParameter.IS_IGNORE_CELL_BACKGROUND, false);
        exporterJRX.setParameter(JRXlsExporterParameter.IS_IGNORE_CELL_BORDER, false);
        exporterJRX.setParameter(JRXlsExporterParameter.IS_WHITE_PAGE_BACKGROUND, true);
        exporterJRX.setParameter(JRXlsExporterParameter.IS_COLLAPSE_ROW_SPAN, false);
        exporterJRX.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS, false);
        exporterJRX.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_COLUMNS, false);
       
        Map<String, String> numberFormats = new HashMap<String, String>();
        numberFormats.put("#,##0.0000000000", "#,##0.00;-#,##0.00");
        numberFormats.put("#,##0.0000000000 %", "#,##0.00;-#,##0.00" );
        exporterJRX.setParameter(JRXlsExporterParameter.FORMAT_PATTERNS_MAP, numberFormats);
        exporterJRX.exportReport();
       
        logger.info("End Export XLS POI " + (System.nanoTime()-nanoTime)/MILLIS + "ms" );
        return reportOutputFile;

HOPE it'll help you

 

Post Edited by neozerabbit at 10/07/2011 14:56