Download to Excel in Usable Format

Comments

3 comments

  • Avatar
    Jlyons (Edited )

    Hi Peter,

    I can help you with this. We’ve had to deal with this for several reports. The main reason the cells split is due to the way Jasper renders things in Excel. Instead of putting headers in a “header,” it tries to fit everything into cells, which then splits all of the cells underneath the header elements. The trick is to hide the header elements when exporting to Excel so you only get the list of data in a usable format.

    I use a Group (or Groups), report properties, and meticulous element alignment in the data section to make this happen. For this, I’ll assume you have a Page Header (with report title, etc.) and a Column Header (to show the column labels).

    1. First thing I do is clean up the part I want to be rendered in Excel (the data). We want to see the Column Header and data. Make sure each element in the Column Header is the same width and is aligned with the corresponding element in the Detail band. Also make sure the height of the elements in the Detail band is the same as the height of the band itself. Any deviation will result in split columns and/or rows. Jasper provides several functions for matching the size and ensuring alignment.

    2. The next thing I do is replace the Page Header band with a Group. You can name it whatever you want. For this, I’ll name it “Page.” The expression can be empty (don’t need a field or variable for this) and you only need the header band (we won’t use the footer).

    3. Make sure your “Page” group is the outermost group in the report. (If you have multiple groups, Jasper makes new groups the innermost group by default.)

    4. Copy and paste the elements from the Page Header band into the “Page” group. Delete the Page Header band.

    6. Add two properties to the report by clicking anywhere outside the page boundaries of the report (in the grey background) and going to Properties Panel > Advanced > Misc > Edit Properties.

    7. Click Edit Properties and click the button with ellipses (...).

    8. Click the Add button and enter the following and click OK (copy and paste recommended)
    Property name: net.sf.jasperreports.export.xls.exclude.origin.band.1
    Use Expression: unchecked
    Value: groupHeader
    (Note: This is telling Jasper what TYPE of band to exclude. I don’t think the “1” matters. It could be any number as long as it matches the number in #9. If you had multiple groups, I believe you would use different numbers for each.)

    9. Click the Add button again and enter the following and click OK (copy and paste recommended)
    Property name: net.sf.jasperreports.export.xls.exclude.origin.group.1
    Use Expression: unchecked
    Value: Page
    (Note: This is telling Jasper the NAME of the band to exclude.)

    10. Click finish and try exporting to Excel. It should show you only the Column Header and data list.

    Let me know if that doesn't work for you. If it doesn't work, please provide the name of the report file you're working on and I can give it a try.

  • Avatar
    Peter Tardif

    Thank you very much!  I appreciate the detailed response.  I am working on this report the second week of July and will reach out with updates/questions I encounter.  

Please sign in to leave a comment.