Use BIRT API to export data to CSV & excel

blured · 2008-09-04T07:16:26+00:00

Hi, Using the Report API I can export my reports to html & pdf using : IRenderOptions options = new RenderOption() options.setOutputFormat("html"); or options.setOutputFormat("pdf"); however how can I export the report to CSV or EXCEL ? Regards, Blured.

blured

Hi,

Using the Report API I can export my reports to html & pdf using :
IRenderOptions options = new RenderOption()
options.setOutputFormat("html");

or

options.setOutputFormat("pdf");

however how can I export the report to CSV or EXCEL ?

Regards,
Blured.

Find more posts tagged with

Comments

cypherdj

Hi Blured,

for excel, set output format to "xls".

For CSV, you will need to use the CSV emitter, see my post from a few days ago, I could not get it working, if you do, please do let me know,

Cedric

ninkibah

Like everybody else, I have had no problem generating pdf and HTML. Now I want to generate an Excel file. I tried using EXCELRenderOptions, setting the output format to "xls":
public RenderOption makeExcelOptions(int reportNumber) {
EXCELRenderOption options = new EXCELRenderOption();
options.setOutputFileName("output/resample/" + name + reportNumber + ".xlsx");
options.setOutputFormat("xls");

return options;
}

When I look at the generated file, it looks like an xml file, and it contains spreadsheet looking tags and attributes, with the correct data. If I try opening it with Open Office (2.4.1), it thinks its a CSV file. Sadly, I have no copy of MS Office 2007 to test it with, but I always thought that the new MS Office file formats zipped one or more XML files together. Is there some trick I'm missing? Or am I generating the expected output?

Jonathan

Virgil Dodson

The default XLS output from BIRT is in the Excel 2003 XML format. If you have an older version of Office, there are compatibility packs you can download from MS to open the newer formats. Someone has created an XLS emitter, part of the <a class='bbc_url' href='http://www.birt-exchange.com/devshare/designing-birt-reports/33-tribix-extended-emitters-for-birt/'>Tribix</a> project, that creates the Excel 97 binary format that you may want to try.

ninkibah

Virgil, 
that link to tribix worked great. I guess my problem is that Open Office doesn't support all the formats that work for MS Office. 
 
Anyway, for those who come after me, I followed most of the instructions given here: <a class='bbc_url' href='http://tribix.sourceforge.net/docs/How to use XLS emitter with BIRT runtime.htm'>http://tribix.sourceforge.net/docs/How to use XLS emitter with BIRT runtime.htm</a> 
 
Some minor clarifications: 
1. You don't need the common tribix library. It's bundled in the xls emitter download. Just extract these plugins to the $BIRT_HOME/plugins directory 
 
2. The POI library needs to go in the lib directory inside the emitter plugin: 
$BIRT_HOME/plugins/org.uguess.birt.report.engine.emitter.xls_2.3.0.200807241457/lib 
 
I (being an OSGI neophyte) tried putting it in the BIRT_HOME/lib directory and also directly on the classpath of my app, but neither of these ideas work. 
 
Then I set my output format to "xls", and everything went very well. 
 
The code sample given, suggests you should set the fixed column width to 50. I never tried that. It might produce a neater spreadsheet. The one I got had lots of empty columns of varying widths. No doubt, the emitter is trying to preserve the layout info specified in the report. However, for Excel generation, it might just be easier for the users to have a fixed width column, even if it doesn't look like what we had in PDF or HTML. 
 
Once again thanks for the advice. It worked a treat, 
Jonathan

RRP

Hi,
I am trying to BIRT Engine api to export data to EXCEL file.
Could you please let me know if BIRT Engine API support export data to Excel file ?
As suggested on this thread I tried setting output type to "xls" and extension of the file as "xlsx".
But it seems like BIRT generates file in XML format.
Here is the part of code ...
HTMLRenderOption htmlRenderOption = new HTMLRenderOption();
htmlRenderOption.setOutputFormat("xls");
htmlRenderOption.setOutputFileName("c:/birtTest/ExcelFile.xlsx");
runAndRenderTask.setRenderOption(htmlRenderOption);
runAndRenderTask.run();
runAndRenderTask.close();

Does not seem to be a valid Excel file.

Thanks,
Rita

ninkibah

Rita,
I haven't done this in a few months, so my memory may be playing up. But, you need the special emitter for XLS files. Read my comments from earlier in the thread, and look at the instructions given in the tribix link.

BTW, if you are trying to use the same BIRT design file for both XLS and some other format, don't! I think it is easier to have two templates, one for XLS and one for PDF/HTML. Layout and formating in XLS will be easier if you don't have to worry about the other emitters.

Ciao,
Jonathan

anitat

Hi

I am trying to use XLS emitter as I want to export a report in Excel binary format.
I have downloaded and placed all the files in their respective paths:
1. I have extracted the XLS emitter plugin to the $BIRT_HOME/plugins directory

2. The POI library is placed in lib directory inside the emitter plugin:
$BIRT_HOME/plugins/org.uguess.birt.report.engine.emitter.xls_2.3.0.200807241457/lib

My error:

Can not instantiate class org.uguess.birt.report.engine.emitter.xls.XlsEmitter with property class.
Report engine can not create xls emitter.
org.eclipse.birt.report.engine.api.EngineException: Report engine fails to initialize xls emitter, please make sure required libraries for this emitter are installed.
at org.eclipse.birt.report.engine.api.impl.EngineTask.createContentEmitter(EngineTask.java:1111)
at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.doRun(RunAndRenderTask.java:88)
at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.run(RunAndRenderTask.java:68)

Help me, thanks!

anitat

Hi

Sorry. I forgot to mention the emitter versions am using.

XLS emitter version : I tried with both 2.3.0 and 2.3.1

BIRT run time: 2.5.0

Apached POI : 3.2 final and 3.5 final

Thanks

anitat

I stopped and started apache tomcat after I got the exception. Still no change.

Actually am using birt run time in my web application. I have extracted the XLS emitter plugin to the WEB-INF/platform/plugins directory The POI library is placed in lib directory inside the emitter plugin: WEB-INF/platform/plugins/org.uguess.birt.report.engine.emitter.xls2.3.0.200807241457/lib

I am not sure whether what I did was right or not. I think it is still searching for the old emitter plugin (excel prototype) which I deleted and it is not able to find.

why it is not able to load the new emitter plugin?

One more observation: In eclipse when I open plugin.xml inside org.uguess.birt.report.engine.common folder , in Dependencies tab->Required Plug-ins, there are two in the list: org.eclipse.core.runtime and org.uguess.birt.report.engine.common.

I see some error(red cross) on the name "org.uguess.birt.report.engine.common". But no error is displayed. I cannot open this where as I can open the org.eclipse.core.runtime

akash

Hi, 
 
I'm unable to open the xls file I export using Birt 2.5. 
 
When I tried opening the file through Open office, i get the file as xml. 
 
When i try using MS Excel viewer, I get an error 
 
Microsoft Excel viewer cannot open files of this type. 
 
Please help. 
Also, will Jasper be better for Excel than birt???? 
 
Thanks 
Akash