Tips for Exporting to Excel

[mwarner_2]

These are the tips I've learned on how to create a report that is expected to be exported to Excel.

Reports need to be designed differently depending on how the user is going to view/use them. I find PDF is easier than Excel. Excel has taken some trial and error to get satisfactory results.

With Excel you have to make certain all the columns line up, so you don't get a whole bunch of different sized columns in Excel. Export a poorly laid out report to Excel, and this is clearly seen. This post doesn't cover that effort.

For exporting to Excel, my objective is to keep things clean and tidy. This is because the user wants the data in Excel so that they can slice, dice, and sort, group, filter, and play with it until they see/get what they need. They don't want to do clean up before they can do that. In Excel, I have one tab for data, and another tab for footnotes. In the data tab I have one row for the report name / company logo. The next row is for column headers. Then the rows for data.

Now in JS..

The Page Header section has the report name and the company logo graphic. They are both the same height, and their widths match exactly to the columns in the Column Header.

The Column Header section is where I place the table for the data. Having the data in a table, allows the user when viewing the data in HTML to sort the data, etc. The data table, has one row for column headers, one row for data.

In the Summary section, I show the user all their input selections, explain any abbreviations or calculations, show who ran the report and when, etc. Place a Break Palette Element at the very top of the Summary. Mine is at x: 0 px, y: 0 px, Position Type: Fix Relative To Top.

I also have a No Data section, which is a copy of the Summary page with the addition of a big "There was no data found for the selections made" notice, and the addition of the report name and company logo. With this No Data section, if the user made selections that have no resulting data, they see a notice telling them that, and they can see their selections. Often times an empty report, to a user, is seen as the system or the report not working. So I want to tell them that it was their choices that led them here. The Main Report has to have the same dataset query as the table, for this to work.

In Main Report, Report properties, Page Format (for this one example report I am looking at), my Page Format has the following properties:

• Format: Custom
• Width: 1350
• Height: 540
• Units: pixel.
• Page Orientation: Landscape
• Margins: 0

So I have a very wide report (like 18.75 inches), which is fine in Excel. I've had other reports that are like 3200 pixels wide. I do this because you don't want the user to get a column-squished report and expect them to like right-sizing the columns. So I make the columns wide enough so that most data is seen without wrapping. The height of the report is not really important. If there are thousands of rows, on screen, they will just have to scroll down to see them all. In Excel, thousands of rows is the expected norm.

Also, in Report properties, I have made these selections:

• When No Data Type: No Data Section
• Summary On A New Page: checked
• Summary with Page Header and Footer: checked --I could have this unchecked, and in the Summary section, I would have to add the report name and the company logo.
• Ignore Pagination: checked

Also, in the Main Report Properties, in Advanced, Properties, I have added these (which end up near the top of the Source XML):

<property name = "net.sf.jasperreports.export.xls.one.page.per.sheet" value="true"/>

•  Without the above, JS only puts as many data rows on a single tab as fit within the Page Height.

 

<property name="net.sf.jasperreports.export.xls.remove.empty.space.between.rows" value="true"/>

• The above gets rid of one row with a very large row height at the end of the data tab. Don't understand why that one very high row is created, but this is needed to get rid of it. Of course having this means that you can't (easily) have row spacing in the footnotes.

 

<property name="net.sf.jasperreports.export.xls.white.page.background" value="false"/>

•  The above shows the Excel grid lines, otherwise you get solid white, which kind of ruins the look of Excel (for a data dump).

 

<property name="net.sf.jasperreports.export.xls.detect.cell.type" value="true"/>

•  The above tells Excel to treat dates as dates, numbers as numbers, instead of all being treated as text when it is set to "false" (which is the default).
•  Note: Java's custom date formats are different from Excel's custom date formats, so a date format might look good in Excel but bad in Java/HTML, or vis-a-versa, so take care with custom date formats.

 

<property name="net.sf.jasperreports.export.xls.ignore.graphics" value="false"/>

•  The above tells Excel to not delete the company logo graphic. "true" (which is the default) deletes graphics.

 

<property name="net.sf.jasperreports.page.break.no.pagination" value="apply"/>

•  The above, as far as I know, has to be typed or cut-n-pasted directly into the Source XML. I have not found a place to select it under any Properties. I am not certain why it is needed, but it is.

 

<property name="net.sf.jasperreports.export.xls.sheet.names.all" value="Data/Footnotes"/>

•  The above tells Excel, to name the first tab "Data", and the second tab "Footnotes". Instead of "Data", I would recommend something more descriptive, maybe the report name.

 

<property name="net.sf.jasperreports.export.xls.freeze.row" value="3"/>

•   The above tells Excel to freeze the top 2 rows. Once in Excel therefore, the report name/company logo row, and the column headers row is frozen, and the user can scroll through thousands of records and still see them. Without this, the user would have had to do it themselves.

 

<property name="net.sf.jasperreports.print.keep.full.text" value="true"/>

•  The above keeps JS from truncating values wider than the explicity set colunmn widths. In Excel this is seen as wrapping.

 

When looking at your report, the look in Preview is different than the look in JasperReports Server. Also I've had the best luck using Save As XLSX Paginated (compared to the other options).

 

These are my observations. I welcome yours.

[hozawa]

You should create a wiki page for this. http://community.jaspersoft.com/sites/all/libraries/ckeditor/plugins/smiley/images/regular_smile.gif

http://community.jaspersoft.com/wiki/community-wiki

[mwarner_2]

I created a Wiki page for this, but wouldn't you know it, in the topic title, I spelled "Excel" as "Excl" and now I can't edit it.

[djohnson53]

I can fix that.

[mwarner_2]

Thank you fixing the title name!  And I see that you did some other great formatting changes.  Thanks so much!

[mwarner_2]

Here is the wiki page http://community.jaspersoft.com/wiki/tips-exporting-excel