Jump to content
Changes to the Jaspersoft community edition download ×

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


longbeach751

Recommended Posts

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

Link to comment
Share on other sites

  • Replies 3
  • Created
  • Last Reply

Top Posters In This Topic

Popular Days

Top Posters In This Topic

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...