Jasper Reports Generation in XLS Format

Problem Statement:

Inbuilt reporting engine inside an ERP application is good value addition for user and If ERP can allow integration of third party reporting engine as well then it is further value add. ADempiere supports reporting in two ways

• In built reporting engine( Print formats)
• Jasper report integration

Print formats allow reporting to be done in different formats like PDF,XLS and HTML. In case of Jasper Reports integration, reporting can be done only in PDF format. But there is growing need to support other formats like XLS and HTML for Jasper based reports as well in ADempiere. Another important need is user should have a choice to decide report output format( The way it works in Print Format )

Scope of Article:

As a part of this article we will cover the changes we need to do to generate different formats based on user choice (i.e. PDF or XLS).

Technology :

• ADempiere 360 or above
• Need poi-3.7.jar

Current Behavior :

1. Run Expense Management report which is developed in Jasper and integrated into ADempiere
   

2. Clicked on Start button, then report is generated in PDF format by default.
3. There is no option to select specific format of report in tool bar

Changes Detail :

We need to make code changes in ZkJRViewer.java file to generate report in XLS format.

1. 1. 1. Add poi-3.7.jar in classpath
      2. Import following files into ZkJRViewer.java file.

          import net.sf.jasperreports.engine.export.JRXlsExporter;
          import net.sf.jasperreports.engine.export.JRXlsExporterParameter;
          import org.adempiere.webui.component.Listbox;
          import org.zkoss.zk.ui.event.Event;
          import org.zkoss.zk.ui.event.EventListener;
          import org.zkoss.zk.ui.event.Events;
          import org.zkoss.zul.Listitem;
          import org.zkoss.zul.Separator;import java.io.FileOutputStream;

      3. Implement EventListener interface into ZkJRViewer.java file.

  public class ZkJRViewer extends Window implements EventListener {

1. 1. 1. Add following Instance variables

private Listbox previewType = new Listbox();
private Iframe iframe = null ;
private AMedia media = null ;

1. 1. 1. Update ZkJRViewer() constructor to invoke super class constructor.

public ZkJRViewer(JasperPrint jasperPrint, String title) {
super() ; 
---
}

1. 1. 1. add  following changes in init() method to have drop down in toolbar to select PDF or XLS

private void init() {
-------

toolbar.appendChild(new Separator("vertical"));

previewType.setMold("select");

previewType.appendItem("PDF", "PDF");
previewType.appendItem("Excel", "XLS");
toolbar.appendChild(previewType);
previewType.addEventListener(Events.ON_SELECT, this);
-------
iframe = new Iframe(); // iframe will be instance variable now.
------

try { 
renderReport() ;  // move whole code of try block into renderReport() new method.

}

--- 

} // init

1. 1. 1. update onEvent() method to invoke renderReport() method to generate report in selected format

public void onEvent(Event event) throws Exception {
if( event.getName().equals(Events.ON_CLICK) || event.getName().equals(Events.ON_SELECT) )
      renderReport();
}

1. 1. 1. Following renderReport() method can generate report in PDF or XLS based on previewType selection.

private void renderReport() throws Exception {
Listitem selected = previewType.getSelectedItem();
if ( selected == null || "PDF".equals(selected.getValue() ) )  {
String path = System.getProperty("java.io.tmpdir");
String prefix = makePrefix(jasperPrint.getName());
if ( log.isLoggable(Level.FINE) )  {
log.log(Level.FINE, "Path="+path + " Prefix="+prefix);
}
File file = File.createTempFile(prefix, ".pdf", new File(path));
JasperExportManager.exportReportToPdfFile(jasperPrint, file.getAbsolutePath());
media = new AMedia(getTitle(), "pdf", "application/pdf", file, true);
}
else if ("XLS".equals(previewType.getSelectedItem().getValue())) {
String path = System.getProperty("java.io.tmpdir");
String prefix = makePrefix(jasperPrint.getName());
if (log.isLoggable(Level.FINE)) {
log.log(Level.FINE, "Path="+path + " Prefix="+prefix);
}
File file = File.createTempFile(prefix, ".xls", new File(path));
FileOutputStream fos = new FileOutputStream(file);
 JRXlsExporter exporterXLS = new JRXlsExporter();
exporterXLS.setParameter(JRXlsExporterParameter.JASPER_PRINT, jasperPrint);
exporterXLS.setParameter(JRXlsExporterParameter.OUTPUT_STREAM, fos);
exporterXLS.setParameter(JRXlsExporterParameter.OUTPUT_FILE, file.getAbsolutePath());
exporterXLS.setParameter(JRXlsExporterParameter.IS_DETECT_CELL_TYPE, Boolean.TRUE);
exporterXLS.setParameter(JRXlsExporterParameter.IS_WHITE_PAGE_BACKGROUND, Boolean.FALSE);
exporterXLS.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS, Boolean.TRUE );
exporterXLS.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_COLUMNS, Boolean.TRUE );
exporterXLS.setParameter(JRXlsExporterParameter.IS_COLLAPSE_ROW_SPAN, Boolean.TRUE);
exporterXLS.setParameter(JRXlsExporterParameter.IS_IGNORE_GRAPHICS, Boolean.FALSE);
 exporterXLS.exportReport();
 media = new AMedia(getTitle(), "xls", "application/vnd.ms-excel", file, true);
}
iframe.setContent(media);
}

1. 1. 1. After applying above changes, start application and run the same report and check the drop down to select report format PDF or XLS

1. 1. 1. Now select Excel to generate report in XLS format.

1. 1. 1. Following is the generated XLS format report.

Summary:

As a part of this article we come to know what are the changes need to do to have XLS report format in ADempiere. After applying changes User can select either PDF or XLS format of report. Hope you have enjoyed reading this article.

Walking Tree promotes ADempiere and we support the users as well as the developers to ensure that the business is able to take complete advantage of ADempiere’s wonderful capability. In case you are looking for a professional assistance then do visit our website to get in touch with us.

References :

• http://mvnrepository.com/artifact/org.apache.poi/poi/3.7
• http://community.jaspersoft.com/questions/518462/problems-exporting-excel
• http://p2p.wrox.com/j2ee/38785-exporting-data-into-excel-using-jasper-reports.html