Friday, June 14, 2013

SpudSoft Excel Emitter - Single Sheet with No Repeating Header and Page Break Interval Zero/ 0 (Java)

I came across using BIRT (Business Intelligence and Reporting Tools), which is an open source Eclipse-based reporting system that integrates with your Java/Java EE application to produce compelling reports.

It has emitters that are used to export the reports to differnet formats such as HTML, PDF, and Office and OpenOffice formats including Excel (xls : 2003 and xlsx: 2007).

When using BIRT's Excel Emitter, it is good to export to a single sheet but it cannot export charts and diagrams. So, I had to use a different emitter.

There are various extended emitters of BIRT. Among which SpudSoft is the one which is recommend by senior programmers.


More about the emitters and comparison can be found here.

Article about Replacing the default BIRT XLS Emitter can be read here.

The point of writing this article is to show how anyone can export a report to an Excel format in a Single Sheet using Java code.

If you are using RenderOption, 

    IRenderOption options = null

    options = new EXCELRenderOption(outputFormat: "xls")
  
    // SpudSoft Emitter
    options.setEmitterID("uk.co.spudsoft.birt.emitters.excel.XlsEmitter")
    options.setOption("ExcelEmitter.SingleSheetWithPageBreaks", true);

// reportFileFullPath is a String that contains absolute path of the file. IReportRunnable reportDesign = reportEngine.openReportDesign(reportFileFullPath);

ReportDesignHandle designHandle = (ReportDesignHandle) reportDesign.getDesignHandle(); DesignElementHandle designElement = designHandle.getBody().get(0); if( designElement instanceof TableHandle ) { ((TableHandle)designElement).setPageBreakInterval(0); ((TableHandle)designElement).setRepeatHeader(false); }


If you want to use the default emitter,
  // Default Emitter
  //options.setOption(IRenderOption.EMITTER_ID,"org.eclipse.birt.report.engine.emitter.prototype.excel");


If you want to use the Tribix emitter,
    // Tribix Emitter
    //options.setOption(IRenderOption.EMITTER_ID, "org.uguess.birt.report.engine.emitter.xls");
  
There is also an extended emitter called Native Emitter.
    // Native Emitter
    //options.setOption(IRenderOption.EMITTER_ID,"org.eclipse.birt.report.engine.emitter.nativexls");

If you use ExcelEmitter.SingleSheet, it would still show you the report in a Single Excel Sheet but, the header will be repeated. 
Hence, 

  1. You will have to use the previous code or 
  2. Use  options.setOption("ExcelEmitter.SingleSheet", true), and set ever reports "Repeat Header" property ( from the Property Editor) to "false" (as can be seen in the following diagram).
    • Create the report
    • Click on the table (1)
    • On the Property Editor, go to Advanced (2)
    • Change Repeat header property to false.
    • Change PageBreakInterval to 0.


More on this can be seen at the blog of SpudSoft.

Whoever is reading, if you have any question, I'm here to help you with everything I've got.