Wednesday, November 09, 2011

Excel Jasper Reports With Macros

I am a big fan of separation of concerns. Did I say this before? :-)

When it comes to View technologies there is a miss conception to think about View part as "passive" when in reality we have seen "active" Views for years with Javascript in the center of our HTML pages. Well, the same applies for PDF and Excel.

So I don't understand why providing Macro support in a Jasper Report should be considered wrong and since especially in the Financial Industry Excel is a core technology where Macros are simply needed I decided to present here a solution to provide Excel Jasper Reports containing Macros.

Some times the macro is needed by business while other times the macro can save time with formatting features that are unavailable in the iReport/Jasper last version. For the second case I do recommend to fill a feature request because it is a hack to force a formatting capability using a Macro and your users might not be OK with code running in their Excel which imposes a big security concern. But for sure this hack allows to move while keeping separation of concerns, in other words liberating Java developers from the need to touch code, recompile and redeploy (The Controller) just to fulfill a need in formatting that is not available where it should be available (The View).

Create an Excel Workbook containing the Macro to be applied to the Excel Jasper Report

  1. Create a brand new Excel Workbook
  2. Rename Sheet1 as 'Macro' and delete the rest of them
  3. Select from the Menu: Tools | Macro | Visual Basic Editor
  4. Right click on "This Workbook" and select "View Code". Paste the below in the code pane:
    Private Sub Workbook_Open()
        If Application.Sheets.Count > 1 Then
            Worksheets("Macro").Visible = False
        End If
        MsgBox "Hello Excel Jasper Report!"
    End Sub
  5. Save the Workbook as "myTemplate.xls", close and open. A pop up should come up automatically. This code as you already noticed has a hack which basically allows to hide the unique sheet we are forced to leave in the workbook because of an Excel imposed limitation.
  6. Now we are ready to use myTemplate.xls as the template out of which our Jasper Report will be built.

Use the Excel Template from jasper API

First, you will need to extend the exporter. I am showing here how to do it with JRXlsExporter which uses POI API but the same idea should be applicable to other exporters.
package com.nestorurquiza.utils;


import net.sf.jasperreports.engine.export.JRXlsExporter;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class CustomJRXlsExporter extends JRXlsExporter {
    private static final Logger log = LoggerFactory.getLogger(CustomJRXlsExporter.class);
    private InputStream is;
    public InputStream getIs() {
        return is;

    public void setIs(InputStream is) { = is;

    protected void openWorkbook(OutputStream os) {
        if(is != null) {
            try {
                workbook = new HSSFWorkbook(is);
                emptyCellStyle = workbook.createCellStyle();
                emptyCellStyle.setFillForegroundColor((new HSSFColor.WHITE()).getIndex());
                dataFormat = workbook.createDataFormat();
                createHelper = workbook.getCreationHelper();
            } catch (IOException e) {
                log.error("Creating a new Workbook when I was supposed to use an existing one.", e);

Second just use the Custom Exporter passing the input stream to your Excel Template file.
CustomJRXlsExporter exporter = new CustomJRXlsExporter();
String xlsTemplate = ctx.getParameter("xlsTemplate");
if( xlsTemplate != null ) {
    String xlsTemplateFilePath = getReportsPath() + "/xlsTemplate/" + ctx.getParameter("xlsTemplate");
    exporter.setIs(new FileInputStream(xlsTemplateFilePath));

You end up with your report and a message box popping up from the macro code "Hello Excel Jasper Report!".

A more realistic example? I will probably start documenting some of them in the near future so search this blog for jasper excel macro.

This technique is simple but powerful. I hope it is integrated somehow in the different exporters as it was proposed 5 years ago.

As I have said there I think iReport should be able to use a hint so the front end report designer can get the formatting they want using an Excel Template without the need to wait for an external API implementation.