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

    10

  2. Clicked on Start button, then report is generated in PDF format by default.

    3

  3. There is no option to select specific format of report in tool bar

11

Changes Detail :

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

    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.
    4.   public class ZkJRViewer extends Window implements EventListener {
    5. Add following Instance variables
    6. private Listbox previewType = new Listbox();
      private Iframe iframe = null ;
      private AMedia media = null ;
    7. Update ZkJRViewer() constructor to invoke super class constructor.
    8. public ZkJRViewer(JasperPrint jasperPrint, String title) {
      super() ; 
      ---
      }
    9. add  following changes in init() method to have drop down in toolbar to select PDF or XLS
    10. 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
    11. update onEvent() method to invoke renderReport() method to generate report in selected format
    12. public void onEvent(Event event) throws Exception {
      if( event.getName().equals(Events.ON_CLICK) || event.getName().equals(Events.ON_SELECT) )
            renderReport();
      }
    13. Following renderReport() method can generate report in PDF or XLS based on previewType selection.
    14. 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);
      }
    15. After applying above changes, start application and run the same report and check the drop down to select report format PDF or XLS
    16. 12

    17. Now select Excel to generate report in XLS format.
    18. 4

    19. Following is the generated XLS format report.
    20. 5

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 :

Tagged with: ,
Posted in ADempiere, EagleRP
2 comments on “Jasper Reports Generation in XLS Format
  1. Syed Munazzar Ali says:

    It works when i place poi-3.7.jar file in C:\Program Files\Java\jdk1.6.0_33\jre\lib\ext\poi-3.7.jar, but adempiere reports ain’t exporting into excel its throwing some error poi.hssfcell.somemethod not found.
    I think this is because of class path, can anyone help me to resolve this issue.
    Please specify the classpath for poi-3.7.jar.
    Thanks in advance..!!

  2. Indra dutta says:

    Please specify the classpath for poi-3.7.jar..

    Not able to understand

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

We Have Moved Our Blog!

We have moved our blog to our company site. Check out https://walkingtree.tech/index.php/blog for all latest blogs.

Sencha Select Partner Sencha Training Partner
Xamarin Authorized Partner
Recent Publication
%d bloggers like this: