XLS option for jasper reports
Contents
- 1 Status
- 2 Contributors
- 3 Overview
- 4 Purpose
- 5 References
- 6 Design Considerations
- 7 Glossary
- 8 Functional Requirements
- 9 Functional team
- 10 Acceptance criteria
- 11 QA and test cases
- 12 Development infrastructure
- 13 Technical Requirements
- 14 Technical team
- 15 Data Requirements
- 16 Non-Functional Requirements
- 17 Open Discussion Items
- 18 Closed Discussion Items
Status
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
Design Considerations
Assumptions
Dependencies
Constraints
Glossary
Functional Requirements
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
User roles & profiles
Business process definition
User stories
Functional requirements based on business processes
User Interface Mockups
Acceptance criteria
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.
Development infrastructure
Technical Requirements
- ADempiere 360 or above
- Need poi-3.7.jar
Technical team
- Volunteers for analyzing:
- Result of analysis: