Row number must be between 0 and 65535, was <65

[longbeach751]

Hi,

In order to display images inside a generated Excel report, using JRXlsExporter instead of JExcelApiExporter due to performances issues, I created a class JRXlsExporterPatch2.java that extends the class JRXlsExporter.java

I edited and added several methods. Here is the code for

JRXlsExporterPatch2.java :

Code:

mport net.sf.jasperreports.engine.JRPrintImage;
import net.sf.jasperreports.engine.export.JRExporterGridCell;
import net.sf.jasperreports.engine.export.JRGridLayout;
import net.sf.jasperreports.engine.export.JRXlsExporter;
import net.sf.jasperreports.engine.export.JRGridLayout.ExporterElements;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class JRXlsExporterPatch2 extends JRXlsExporter
{
	
	// edited
	protected ExporterElements getExporterElements()
	{
//	  return JRGridLayout.NO_IMAGES_EXPORTER;
	         return JRGridLayout.UNIVERSAL_EXPORTER;
	}

//	 added 
	protected HSSFPatriarch patriarch =null;       

//	 edited 
	protected void createSheet(String name)
	{
	     sheet = workbook.createSheet(name);
	     patriarch = sheet.createDrawingPatriarch();
	}

//	 added 
	protected void exportImage(JRPrintImage image, JRExporterGridCell gridCell, int colIndex, int rowIndex) {
	         short forecolor = getNearestColor(image.getForecolor()).getIndex();
	         HSSFFont cellFont = getLoadedFont(getDefaultFont(), forecolor);
	         HSSFCellStyle cellStyle =
	                 getLoadedCellStyle(
	                         HSSFCellStyle.SOLID_FOREGROUND,
	                         forecolor,
	                         HSSFCellStyle.ALIGN_LEFT,
	                         HSSFCellStyle.VERTICAL_TOP,
	                         (short) 0,
	                         cellFont,
	                         gridCell
	                 );

	        HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, image.getWidth(), image.getHeight(), (short) colIndex, rowIndex,
	                (short) (colIndex + gridCell.colSpan), rowIndex + gridCell.rowSpan);
	        try {
	            patriarch.createPicture(anchor, workbook.addPicture(image.getRenderer().getImageData(), HSSFWorkbook.PICTURE_TYPE_JPEG));	        	
	        } catch (Exception ex) {
	            ex.printStackTrace();
	        }
	}
// edited	
	protected void setRowHeight(int rowIndex, int lastRowHeight)
	{
		row = sheet.getRow(rowIndex);
		
		if (row == null)
		{
			row = sheet.createRow(rowIndex);
		}

		row.setHeightInPoints(lastRowHeight);

	}

	
}

 

I use the latest version of the Apache POI library

poi-3.0.1-FINAL-20070705.jar (else it does not know about the addPicture(...) method)

 

It works well when I want to generate Excel files that have less than 65535 rows, which of course is the limit for a sheet in an Excel document.

 

But if I try more than 65535 rows, I get the following message :

java.lang.IndexOutOfBoundsException: Row number must be between 0 and 65535, was <65536> at org.apache.poi.hssf.usermodel.HSSFRow.setRowNum(HSSFRow.java:208) at org.apache.poi.hssf.usermodel.HSSFSheet.getRow(HSSFSheet.java:319) at com.lds.noee.util.noyau.reporting.jasper.reports.jasperReports.JRXlsExporterPatch2.setRowHeight(JRXlsExporterPatch2.java:80)

 

So does anyone know how to handle this error, or define an exportImage(...) method that avoids this error ? :(

 

My goal is to display images inside an Excel document.

I know the JExcelApi Api (JExcelApiExporter) does it well, but for a reason that I have not yet identified,

it takes an enormous time (like 1h30).

Also, I end up with an Excel file that is huge (27 MB ) :blink:

and quite impossible to open.

Thanks in advance for your advices.:)

Post edited by: longbeach751, at: 2007/08/06 14:44

[szaharia]

Hi,

 

The next JasperReports release will contain a fix for the nasty 'too many rows' exception, applicable for both POI and JExcelApi exporters.

However, in order to deal from java with huge Excel files, try to increase the jvm max memory size too, using the -Xmx jvm argument.

Regarding the POI advantages against JExcelApi, I tried to export the same jrxml (see the FirstJasper.jrxml sample) file to Excel, using both POI and JExcelApi exporters.

Results for POI were: creation time - 2.953 s; size on disk - 553kB.

Results for JExcelApi were: creation time - 2.078 s; size on disk - 410kB.

I let you decide which seems to be the most appropriate one.

 

Regards,

sanda

[longbeach751]

ok, thanks for your answer.

But in the mean time, until you release the next version of JasperReports, do you have a solution to my problem ?

 

Using POI and defining the exportImage(...) method.

 

Thanks all in advance for your help.

[szaharia]

Until then, the only solution I can imagine is to limit the number of rows per page at design time, by setting smaller values for the pageHeight attribute in your jrxml file.

You see, number of rows in a given page/sheet, is at the moment limited only by this pageHeight attribute. If the query returns more rows than a page with a given height can hold, a new page/sheet is generated, with the same height, containing the same maximum number of rows as previous page/sheet, and so on.

So, the solution should be to create shorter pages, ie smaller pageHeights.

But also try to increase the jvm allowed memory too, as I suggested earlier, because we still are talking about huge objects here.

 

Sincerely hth,

sanda