Reports in XLS (Excel) are not as cool as PDF ones

[overday]

Hi guys! First of all, sorry my bad english, I'll try to make it as clear as I can :)

 

 

I have an application wich needs to generate reports in PDF, HTML and XLS (Excel) formats.

 

 

When I try to generate in PDF or HTML, it works well, like this:

 

 

]http://img186.imageshack.us/img186/5210/pdfwb3.jpg

 

 

The same report, however, is shown like this when I try to generate it in XLS (Excel):

 

 

]http://img441.imageshack.us/img441/5282/xlslc1.jpg

 

 

As you can see, there are two problems I really need to resolve as soon as possible:

 

 

1) It creates a lot of small empty rows and columns. Note that before row 15 there are about 3 useless rows. The same happens after column A, and in a lot of other places.

 

 

2) When the text in "Label 1" rows are big enough to be in more than one line, the row does not fit the lines height.

 

 

Does anybody know how to fix this? Here's the code:

 

 

Code:

 

 

The report template is XML (iReport).

 

 

Thanks!

[wbizzare]

After bumping my head against this problem this is the simple way to solve:

 

Use iReports and make sure to vertically align all the report objects as much as possible and make sure that they are the same height. JasperReports will add extra rows and columns to cause alignment to the report specification.

 

Walt

[overday]

Thanks wbizzare, that should solve the first problem.

 

 

Any hint how to deal with the second problem?

 

 

]http://img107.imageshack.us/img107/8993/linhapj0.gif

 

 

It happens with every single cell that brings a bigger text than one line :(

[wbizzare]

In iReports go to the bottom of the element properties frame and you will see "Stretch with overflow". If you click on that property it will flow with the text - but it may not look as good because JR will add alignment rows but it's not bad.

 

Walt

[overday]

This property is already checked. The row is stretching. However, it's not stretching enough, and I don't know why nor if it is possible to make it stretch more.

 

 

]http://img384.imageshack.us/img384/2695/lineszv6.jpg

 

 

:(

[nicKInJasperForge]

I have this problem too!!!

[nicKInJasperForge]

Is nobody can help????????? This is the old probleb. It take place in 1.2.5 version.

Is nobode export reports with multy rows to excel???

Please help!!!

[arunvishvin]

This is purely related to the alignment problems in your design make sure you have read the tips and tricks of jasper assistant which is available on their website...go and check it out.

[Teodor Danciu]

Hi,

 

This problem comes from the fact that when we generate the XLS document, we do not have control over line spacing and word wrap.

Excel uses by default a line spacing that is slightly bigger then the one we have in AWT and PDF, so the same text will not fit the calculated cell height.

 

For better results, you can set the JRXlsAbstractExporter.IS_FONT_SIZE_FIX_ENABLED parameter to Boolean.TRUE.

This will force the use of a smaller font size so that the text content fits better in the cell.

 

I hope this helps.

Teodor

Post edited by: teodord, at: 2007/09/04 15:40

[imams]

Hi Teodor,

 

It seems this Excel (word wrap) is surfacing in many posts. I have seen your suggestions of setting IS_FONT_SIZE_FIX_ENABLED parameter to Boolean.TRUE. However, this might have worked for some people, but never worked for me & some others.

 

Can you pls advise of some alternate solution? We are using Jasper Report (2.0) in big organization production & now seems like we do not have any breakthrough. I personally think, this is major limitation in use of JasperReport.

 

- Rohit

[Teodor Danciu]

Hi,

 

I think if we had alternate solutions, they would have been there already.

 

To summarize it all: How do you control line spacing in a document format that does not allow you control line spacing?

 

We are open to suggestions as always.

 

Thank you,

Teodor

 

P.S. It also make me sad a little when limitations of other products are perceived as limitations of our products. But I guess this is just something we have to live with.

Post edited by: teodord, at: 2008/05/12 12:55

[imams]

Hi,

Instead of trying to export the report data in Excel, I now try to export it in CSV. What i can find is there is mismatch between the coulmn and the data under it. i.e. Data set is not appearing in the column in which the coumn name is present. Please find the attachment.

 

In the attched file, the coulmn name SEName (column E) and column data appears under different column(column F). Same is the case with coulmn J and Column K.

[lucianc]

imams wrote:

Please find the attachment.

 

There is no attachment. Also, please post the report JRXML along with the CSV output.

 

Regards,

Lucian

[marianela]

Hi everyone¡¡

 

I'm facin a similar problen in my web app. I used to use Ireports 1.2.7 (JR same version) to export my reports to PDF or XLS files. 2 weeks ago, we decide to upgrade the tool to Ireports 2.0.2; I change all the necesary jars in my classpath; but now I'm facing a big problem:

 

Since that change, my xls reports are not as cool as they used to be (with my old version of IR), now it seems that my app just ignore all the sentences where I try to set the parameters to export (like: IS_WHITE_PAGE_BACKGROUND) so my reports looks like the image attached.

 

Here is a part of my code:

 

JasperPrint jasperPrint = JasperFillManager.fillReport(new FileInputStream(reportFile), parameters, conn);

JRXlsExporter xlsExporter = new JRXlsExporter();

xlsExporter.setParameter(JRExporterParameter.JASPER_PRINT, jasperPrint);

xlsExporter.setParameter(JRExporterParameter.OUTPUT_FILE_NAME, outFileName);

xlsExporter.setParameter(JRXlsAbstractExporterParameter.IS_DETECT_CELL_TYPE, Boolean.TRUE);

xlsExporter.setParameter(JRXlsAbstractExporterParameter.IS_ONE_PAGE_PER_SHEET, Boolean.FALSE);

xlsExporter.setParameter(JRXlsAbstractExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS, Boolean.TRUE);

xlsExporter.setParameter(JRXlsAbstractExporterParameter.IS_WHITE_PAGE_BACKGROUND, Boolean.FALSE);

xlsExporter.exportReport();

 

What am I doing wrong??

Why the export parameters are ignore with the upgrade??

Pleaseeeee... I relly need help... I'm desperate...

 

Thanks a lot...

 

Marianela

 

Post edited by: marianela, at: 2008/05/21 21:29

Post edited by: marianela, at: 2008/05/21 21:31

[marianela]

Sorry... I can't attached the image..

Post edited by: marianela, at: 2008/05/21 21:35

[marianela]

Please!!!!

I really need to solve this issue... somebody? is my explanation clear? do I need to explain my self better???

 

Please... :S

 

Marianela

[lucianc]

marianela wrote:

now it seems that my app just ignore all the sentences where I try to set the parameters to export (like: IS_WHITE_PAGE_BACKGROUND)

 

I tested IS_WHITE_PAGE_BACKGROUND in JasperReports 2.0.2 and it worked fine.

 

If you want help, post enough information (i.e. sample JRXML, relevant Java code, XLS output) such that other people can reproduce your problem and identify its cause.

 

Regards,

Lucian

[thil2009]

Hi,

Any solution found for this issue, I too have similar requirement.

[contact.sankar.e]

Please check this link - what worked for me.

 

https://community.jaspersoft.com/questions/518408/one-cell-column-excel