These are the tips I've learned on how to create a report that is expected to be exported to Excel. Most of my reports now days, have a user selection of 'PDF or Excel'. So the reports are built for both PDF format and for Excel format.
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 Jaspersoft Studio...
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. I also have the report name and company logo at the top of this section. I don't put them in the Column Header or the Page Header (unless the report also has PDF output) because it kind of junks up the look of the Excel data page. Place a Break Palette Element at the very top of the Summary. Mine is at x: 0 px, y: 0 px, Height 1px, Position Type: Fix Relative To Top. At the top of the Summary section, I have a page break (which is 1 pixel high). All Other elements on the summary page start on or lower than Y position 1, so as to not overlap with the page break.
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: Letter is what I always use
- Width: When I have a report that does both PDF and Excel, the the width is 792.
- Height: When I have report that does both PDF and Excel, then the height is 612
- Note: Excel doesn't care what your margins here are. PDF likes these just fine.
- Units: pixel. Much easier to work in than inches
- Page Orientation: Landscape
- Margins: 36 for all margins.
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: After much experiementation, I have decided that unchecked is best, otherwise when doing a PDF or Excel format report, the PDF does not page properly, and can't be recovered from. It is the lessor of two evils (until Jaspersoft fixes this in a new version).
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.remove.empty.space.between.columns" value="true"/>
- The above gets rid of empty columns, such as caused by margins, or by poor layout.
<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.
<property name="net.sf.jasperreports.exports.xls.font.size.fix.enabled" value="false"/>
- The above keeps JS from reducing the font size by 1 when exporting to Excel. For example, without this, an Arial 10 in Studio, in Server when exported to Excel ends up as an Arial 9 font.
When looking at your report, the look in Preview is different than the look in JasperReports Server.
To keep the pagination, when running the report from the server, then Save/Export as Excel or XLSX. Excel Paginated or XLSX Paginated reintroduce Excel paging (which you don't want).
Recommended Comments
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 accountSign in
Already have an account? Sign in here.
Sign In Now