XLS option for jasper reports

This Wiki is read-only for reference purposes to avoid broken links.

Contributors

Sponsoring: Walking Tree Consultancy Services Pvt. Ltd.

Concept: Walking Tree Consultancy Services Pvt. Ltd.

Implementation: Walking Tree Consultancy Services Pvt. Ltd.

Testing: Walking Tree Consultancy Services Pvt. Ltd.

Documentation: Walking Tree Consultancy Services Pvt. Ltd.

Overview

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 )

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).

Purpose

Main purpose of Exporting the Jasper Report out put in XLS or different formats is help to create page-oriented, ready-to-print documents in a simple and flexible manner. And users can view those reports without having to install specific viewers on their systems.

We need customization to get the required behaviour. Steps mentioned below, will help you to understand the changes we need to make in system.

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.

            public class ZkJRViewer extends Window implements EventListener {

4. Add following Instance variables

           private Listbox previewType = new Listbox();
           private Iframe iframe       = null ;
           private AMedia media        = null ;
           private File file           = null;
           private String title        = null;

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

           public ZkJRViewer(JasperPrint jasperPrint, String title) {
           super() ; 
           ---
           this.title = title.substring(0,title.indexOf(".pdf")) ;

           }

6. 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.

           iframe.setId(jasperPrint.getName());	//Sets report Title name

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

7. 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();
           }

8. 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(this.title, "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.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(this.title, "xls", "application/vnd.ms-excel", file, true);
           }
           iframe.setContent(media);
           }

After applying changes User can select either PDF or XLS format of report.

References

• http://blogs.walkingtree.in/2013/05/20/jasper-reports-generation-in-xls-format/

Functional team

• Volunteers for analyzing:
  • --John Agudelo 03:13, 25 June 2013 (UTC)- Functional Review
• Result of analysis:

Result of review by --John Agudelo 03:13, 25 June 2013 (UTC)

• Login with User(System/System). [1]
• Activate Report and process named: "Employee Aviled Leave Detail".
• Launch Adempiere web application.
• Login with User(GardenAdmin/GardenAdmin)
• Select 'Garden Admin' role
• Open "Open Holiday" report.
• Clicked on Start button

I can see ListBox with options XLS and PDF

• Selected the option XLS the Report is downloaded in XLS format file

• Conclusion: Accepted

QA and test cases

Steps:

 1.Launch Adempiere web application. 
 2.Login with User(GardenAdmin/GardenAdmin)
 3.Select 'Garden Admin' role
 4.Open Holiday report and run  which is developed in Jasper and integrated into ADempiere
 5.Clicked on Start button
 6.Now select Excel in drop down

Expected Results:

 After Step5: System shows drop down to select report format PDF or XLS 
 After Step6: Generate report in XLS format.

Technical Requirements

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

Technical team

• Volunteers for analyzing:
• Result of analysis: