Maximum number of rows within an excel spreadsheet

[longbeach751]

Hi,

I have a problem when i try to generate an excel report, using JasperReports

 

I get the error jxl.write.biff.RowsExceededException: The maximum number of rows permitted on a worksheet been exceeded

 

Could you please guide me on what solution I could try with JasperReports / IReport to avoid this limitation ?

Is there a way to specify a new sheet when the maximum number of rows ( 65,536 ) has been reached ?

 

Thanks a lot for your help.

[szaharia]

Actually there is a possibility: you have to set the JRXlsAbstractExporterParameter.IS_ONE_PAGE_PER_SHEET export parameter to Boolean.TRUE. Doing so, for each report page will be generated a new sheet.

If you intend to have a very large number of rows per sheet (65,536 - 1 for example), I think you could set the pageHeight attribute with a VERY large value in your jrxml file.

 

hth,

sanda

[longbeach75]

sanda,

thanks for your reply.

But this is NOT the solution since I already have the IS_ONE_PAGE_PER_SHEET parameter set to Boolean.TRUE

and I have already tried to set the pageHeight attribute to a very large value.

 

So the solution is elsewhere. Can anyone point me to the right direction or give me some hints/settings that could help ?

 

Thanks a lot for your help.

[szaharia]

Hi,

It would be useful if you'd post a sample which could reproduce the problem.

Usually, that parameter solves this kind of issues.

 

Regards,

sanda

[longbeach751]

Code:

 case ReportInstance.EXPORT_2_XLS: {
            	
                //export = new JRXlsExporter();
                export = new JExcelApiExporter();
                
                
            	export.setParameter(JExcelApiExporterParameter.IS_ONE_PAGE_PER_SHEET, Boolean.TRUE);
                export.setParameter(JExcelApiExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS, Boolean.TRUE);
                /**********************/
                //Passage à TRUE au lieu de FALSE
                export.setParameter(JExcelApiExporterParameter.IS_WHITE_PAGE_BACKGROUND, Boolean.TRUE);
                /**********************/
                if(arraySheetsName!=null){
                	export.setParameter(JExcelApiExporterParameter.SHEET_NAMES, arraySheetsName);
                }
                export.setParameter(JExcelApiExporterParameter.IS_AUTO_DETECT_CELL_TYPE, fautoDetectCellType); 
                
                normalisedNamesMap = normaliseReportInstancesNameForXLSExport();
                
                break;
            }

 

and in my master .jrxml :

 

Code:

[code]jasperReport name="BalanceAgee" columnCount="1" printOrder="Vertical" orientation="Portrait" pageWidth="595" pageHeight="28000000" columnWidth="535" columnSpacing="0" leftMargin="30" rightMargin="30" topMargin="20" bottomMargin="20" whenNoDataType="AllSectionsNoDetail" isTitleNewPage="false" isSummaryNewPage="false">

 

So, as you can see, both parameters you talked about are set up to the appropriate value.

Any clue ? thanks

 

Also, I have a memory problem. I made an attempt with the JRFileVirtualizer class, it adds big chunks of swap files to my filesystem, i am still running it, for almost an hour, i already have like 1GB of swap files and it is increasing of course. I am not sure it is going to make any difference.

 

Basically, i have something like 75 000 rows to extract from the database and integrate in my Excel file, and I am facing 2 big problems:

1) java.lang.OutOfMemoryError exception

2) too many rows for the sheet exception

 

Does anyone know how to deal with these 2 problems ?

Thanks

Post edited by: longbeach751, at: 2007/07/26 15:42

[szaharia]

Hi,

 

you're right, there are 2 main problems.

The "Too many rows" exception gets solved only by setting, as you did, IS_ONE_PAGE_PER_SHEET to true. In addition, to gain more usable rows, you can set also the

IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS to true. You did it too.

The second problem: the "Out of memory" issue.

It can be caused when you create the JasperPrint object, which in this case is a really huge one. To save memory space, you have to create this object using a virtualizer (JRFileVirtualizer or JRSwapFileVirtualizer) which uses a swapping mechanism.

You could consult the virtualizer sample to find out more about the subject.

If you already used this, the next problem will be when you'll try to render the report pages in the Excel sheets. If the page information is too big (you have a lot of columns to show and/or a lot of rows) you have to reduce page dimensions in order to lower its size in kB. Because reducing number of columns is not a reliable practice, you should decrease the number of rows - ie the page height.

I tried a simple report, having a single column and 80000 rows, and the pageHeight=28000000, as you suggested. I got the OutOfMemory error, of course:P .

I decreased step-by-step dividing by 10 this number, and only when I put pageHeight=200000, I could visualize without problems my sheets. There were 9 sheets having 9996 rows per sheet (with my only one column).

So, here is the problem: to find an appropriate pageHeight value, in order to show as much information as possible. You could try some values, until you'll find the best one for your needs.

 

Regards,

sanda

Post edited by: shertage, at: 2007/07/26 17:56

[szaharia]

PS: If you set a "normal" pageHeight - the usual 842 for example, another OutOfMemory will be thrown, probably, because of too many sheets involved.