JasperReport Excel Options

Over the past year I have been learning about so many different OpenSource packages it isn’t even funny.  One thing that is available for free, but also has a paid version (which we at using at UniTek Global Services), is JasperServer.  Right now we are on version 4.2.1.  When we first rolled it out, everything seemed to work excellently with the exception of three things:

  1. In the report template, I placed the company logo to the left of two rows:  the report title and the description/generation date.  Whenever I generated the report as an XLS via Jaspersoft iReport Designer, the image would load correctly.  On the other hand, whenever I generated the report as an XLS via the JasperServer, the space for the image was left there without an image.
  2. Once the image issue was resolved (I will describe how soon), the XLS generated via the JasperServer would load the image on the same row as the title but it wouldn’t extend into the cell directly below so that it would appear to the left of the description as well.
  3. XLS reports generated via the JasperServer were not showing borders on any of the cells no matter how large I made the borders in the designer.

If you encounter these issues in your JasperReport template, do the following:

  1. In the Report Inspector pane, click on the top-most element (which is the template).
  2. In the Properties pan, select the Properties row and then select the elipsis at the right of the row.
  3. Show Images In XLS:
    To ensure that images will appear when exported as XLS, do the following:
    1. Click the Add button in the Properties window.
    2. Enter the following as the Property name:
      net.sf.jasperreports.export.xls.ignore.graphics
    3. Enter the following as the Property value:
      false
    4. Click OK to close the Add/modify property window.
  4. Allow Cells To Span Multiple Rows In XLS:
    To ensure that cells can span multiple rows when exported as XLS, do the following:
    1. Click the Add button in the Properties window.
    2. Enter the following as the Property name:
      net.sf.jasperreports.export.xls.collapse.row.span
    3. Enter the following as the Property value:
      false
    4. Click OK to close the Add/modify property window.
  5. Show Borders In XLS:
    To ensure that images will appear when exported as XLS, do the following:
    1. Click the Add button in the Properties window.
    2. Enter the following as the Property name:
      net.sf.jasperreports.export.xls.ignore.cell.border
    3. Enter the following as the Property value:
      false
    4. Click OK to close the Add/modify property window.

8 thoughts on “JasperReport Excel Options”

  1. Thanks. Works great.
    Is it possible to add this to the report server config somehow, to avoid having to repeat it for each report?
    I tried to add it to jasperreports.properties but that did not have any effect.

  2. Hi Chris,

    I have been trying to include Image in my XLS output (tried to include images added in JasperServer and even BLOB from database) but the image is not appearing in the XLS output but PDF shows them fine. Could you please let us know what are the changes that has to done to show the same in XLS output. (Im not a Java person and only calling the jasperreport in Oracle APEX). Waiting for your reply.

    Thank you.

    Regards,
    Sunil

  3. Hi, Nice suggestions,

    Can you please provide some guidance in putting array formula in jasper.
    e.g SUM(INDIRECT(“A1″&ROW:INDIRECT(“A1″&ROW())) not working when excel exporting from jasper. It is giving #VALUE error where it is not able to evaluate INDIRECT function.

    1. It is not really clear from your formula what you are trying to do (other than sum a range of cells). First I would say you need to fix what is being concatenated to "A1". I would actually try your formula out in Excel before trying it out via a JasperReport.

  4. I have been testing Jasper Reports Server 6.0 for my company. We currently use iReport 5.1 and custom built reporting websites and servers to generate the reports.

    One major drawback of I have come across so far is that the excel advanced features applied to many reports we already have do not work. Auto filtering is a big one but others like freezing rows don’t work either.

    Do you use these? If so, does it work for you?

  5. Hello dude. Very usefull article. But i have one question. I have multiple pages in my report. So there are multiple images at report and excel too. how can i disable the second image?

Leave a Reply

Your email address will not be published. Required fields are marked *


8 − six =

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="">