Wednesday, June 02, 2010

BIRT Excel Output

BIRT 2.5.2 provides a variety of tools to construct reports to analyze data. These include charts, aggregation elements, drill to detail capabilities, nested tables, data cubes and crosstabs. These features are presented very well in the AJAX based viewer when deployed to the web, supporting pagination, table of contents, and exporting of data and contents to other formats. Out of the box, BIRT supports exporting to HTML, paginated HTML, WORD, PDF, PostScript, PPT, and Excel. BIRT also provides an extension point to implement your own emitters. For an example of implementing an XML emitter see Developing an Eclipse BIRT XML Report Rendering Extension or tBIRT: Writing an Emitter.

As stated earlier BIRT supports exporting to Excel. The BIRT Excel emitter creates a Microsoft Office XML XLS document that can be opened in Microsoft Office 2003 or greater. To use this feature either add the __format=xls parameter to the BIRT viewer URL or use the AJAX export button.



if you are using the Report Engine API, simply set up a render option for XLS.



//RunAndRender Task
IReportRunnable design = null;
design = engine.openReportDesign("Reports/myreport.rptdesign");
IRunAndRenderTask task = engine.createRunAndRenderTask(design);
EXCELRenderOption options = new EXCELRenderOption();
options.setOutputFormat("xls");
options.setOutputFileName("output/resample/myxls.xls");
task.setRenderOption(options);
task.run();
task.close();

//or Render Task
IReportDocument document = null;
document = engine.openReportDocument("output/resample/myreport.rptdocument");
EXCELRenderOption options = new EXCELRenderOption();
options.setOutputFormat("xls");
options.setOutputFileName("output/resample/xlsoutput.xls");
IRenderTask task = engine.createRenderTask(document);
task.setRenderOption(options);
task.render();



While the XLS output is quite good, some features are not supported. For example new worksheets on page breaks are not created and images and charts are not exported to the XLS. While the team continues to improve the XLS emitter there are some other options for emitting XLS. One of these options is to use the Tribix emitter located on source forge. The Tribix project offers emitters for RTF, PPT and XLS.



If you wish to use just the XLS emitter, download the org.uguess.birt.report.engine.emitter.xls_version and org.uguess.birt.report.engine.common_version plugins and copy them to the plugins directory in your Eclipse install location. You will also need to copy them to the runtime location as well. For example, if you are using the WebViewer this will be the WebViewer/WEB-INF/Platform/plugins directory. You will also need to remove org.eclipse.birt.report.engine.emitter.excel.config_version and org.eclipse.birt.report.engine.emitter.prototype.excel_version plugins from both locations to replace the out of the box XLS emitters. Restart Eclipse with the –clean option and the Tribix emitter should work. No API changes should be required if you are using the RE API. New worksheets per page and image support should now work.



If you desire more XLS output options take a look at the Actuate XLS emitter that will be available in Actuate BIRT 11 which will be released this fall. It allows exporting charts as either images or as native XLS charts.



It also provides the capability to export formulas using a new scripting language called EasyScript, within the BIRT Expression Builder.





The Actuate BIRT XLS emitter also offers the capability to export BIRT Crosstabs to native XLS Pivot Tables.



If you are interested in trying out the Actuate BIRT XLS emitter take a look at the Acutate 11 Milestone download page located on BIRT-Exchange.

29 comments:

Mark Bannister said...

Both of these look really interesting.

I work for a company who are currently using SSRS for most of our reporting requirements, although we're intending to move to BIRT in the not-too-distant future. In particular, most of our users access the report output in Excel format - BIRT's support for Excel output (particularly multi-sheet output) has been raised as an issue.

Incidentally, when I tried clicking on the Actuate 11 Milestone link, I got a 404 error. Searching for Actuate 11 on the BIRT Exchange wiki:
http://www.birt-exchange.org/org/wiki/index.php?title=Special%3ASearch&search=actuate+11&go=Go
returned the message 'There is no page titled "actuate 11".'

Jason Weathersby said...

Mark,

Thanks for the heads up. Link should get fixed today.

Jason

Mark Bannister said...

Link's working fine now - thanks!

Rama Krishna said...

Hi,

Can you tell us this is open source or commercial offering only?

The current open source version of xls emitter is not par. It doesn't export charts also. Do you have any idea these features will be ported to open source version of xls emitter in BIRT?

Current we are using TRIBIX open source xls emitter, but we prefer to use one directly given by BIRT itself.

Thanks,
Rama

Unknown said...

Hi,

I am creating a XLS report from BIRT. My report is a table and i have precisely defined the column width. I have also switched off the options of shrinking. When I export the report width of the column shrinks. Any help on how to keep the width exact?

Jason Weathersby said...

What version of BIRT are you using?

Anonymous said...

Hi,

I am facing the same problem, while exporting the report in XLS the whole BIRT report format distubs. My report are working fine from BIRT.
Please help.

Jason Weathersby said...

Can you give some more detail on what it is doing?

Anonymous said...

Hello Sir ,

I saw the same in bug:
https://bugs.eclipse.org/bugs/show_bug.cgi?id=267988

and Some solutions in

http://www.itexperience.net/2008/03/17/excel-2007-error-different-format-than-specified-by-the-file-extension/

This(Changing Registry/MIME Type) doesn't looks good option for me


Is there any Version of BIRT (after 2.5.3) which exports data in 2007 Excel format.

agl.vijay@gmail.com

Jason Weathersby said...

Actuate's Excel emitter can export to 2007 format.

Ansh said...

Hello Jason,

I am preparing reports in BIRT 2.3 and upon exporting one of my report which is a simple LIST report with 18 columns to the excel format the format is not coming fine.
The problem is the first 3 column labels are coming at the exact positions then the next 3 are coming in the next row then the next 3 are coming in the next row, so the total number of rows in this case are 6 and the data follows the similar fashion.

Please help.

Jason Weathersby said...

Have you tried this in one of the later versions of BIRT?

Jason

Ansh said...

Hi Jason,
I cannot try with the upper version as i work for some client there they have V2.3.
Can you suggest anything for 2.3.

Thanks in advance.

Jason Weathersby said...

Any way you could email me a report that I can run that shows the error?

Jason

Ansh said...

Hi Jason,

Please share your mail id , i will share you my report.

Thanks

Jason Weathersby said...

my email is jasonweathersby at windstream dot net.

Jason

Shoaeb said...

The generated XLS report from BIRT gives me 10mb of file and it has 3500 records.

I need to reduce the size of the generated report without disturbing the data.

Any suggestions are appreciated.

Shoaeb.

Jason Weathersby said...

This may have to do with styles. Can you email me the report?

Jason

Shoaeb said...

Jason,

i have zipped and sent the Report to you with 10MB size.

please check you mail,

Thanks,
Shoaeb

Anonymous said...

I need the Excel output from BIRT which is having the "Format Cell" option in Excel set to be "Text"(what ever the content is..).

Akki said...

Jason,
We are facing file size issue with BIRT output. For 4000 records output XLS is of 15 mb. We have tried the emitters. But of no use.

Cara mengobati said...

: thank you for updating

gathu said...

hi.
how can we load images from birt report to excel sheet??

Anonymous said...

I am using Birt Eclipse version 4.5.0. I am facing issue in extracting birt report outputs in displaying images. It is displaying images in pdf output but not in excel. I have tried exporting in spudsoft excel also. But it failed. Please suggest some idea to solve this

Satish said...

Hi,
I am using birt to generate xlsx reports in my Java application. My report returns more than 1,048,576 rows (max limit in xlsx).
I want 1,048,576 rows in sheet 0. And remaining rows in next sheet (sheet 1 or tab 1).
Could you please help me to achieve this.
I am new to birt. Please help me.

Turbo ERP said...

Thanks for sharing, it is a very informative blog.
Export Software

Turbo ERP said...

Great Info, Your blog is very informative and interesting, your all post are amazing, keep sharing more interesting topics.
thanks for the blog. it really helps me alot.
Export Documentation Software

Turbo ERP said...

Turbo-PMS® : Photo Management System is India's No.1 Innovative & Most Trusted Software Packages for Export Management & Photo Catalog Generation, Software for Photo Management, Exporters GST Invoice Format, Export Documentation Software, Photo Catalog Software, Generates Photo Offers & Quotation with Photos by Scanning Item Barcode Labels.
Photo Catalog Software

Charles said...

You can create Crosstab chart in Excel and Google Sheets without any coding skills.

ChartExpo™ is both an Excel and Google Sheets data visualization tool to create 50+ custom charts from few clicks.

Try it for Free:
ChartExpo™ for Excel
ChartExpo™ for Google Sheets


How to create Crosstab Chart?

Thanks.