Birt xls emitter generates big files

We have a problem where just 10K rows in a report is generating over 5 MB of excel file. We are using the standard xls emitter plugin that comes with Birt (we are on Birt 2.6.1). If I simply copy data from the generated excel and paste into a separate spreadsheet the new spreadsheet size just comes out to be around 300KB - so there is a huge difference in what Birt is generating and what the actual data size is.

Looks like the Birt xls emitter(org.eclipse.birt.report.engine.emitter.prototype.excel_2.6.1.v20100812.jar) generates file in xml and that takes up lot of space. We have also tried to use Tribix xls emitter plugin - it generated little smaller files (around 4 MB), but still too big. Also, the performance of Tribix was pretty bad for large report (around 10K rows).

Lots of our users like to export their report into Excel and the size has become a huge problem for us. Is there a work around or a fix that we can use to reduce the size of generate excel files?

Thanks,
-vivek

Find more posts tagged with

Comments

ahsiang

Hi, the open source BIRT do have this issue. You will have to open and resave the excel so that the XML tagging is removed by Microsoft Excel. The commercial Actuate BIRT (2.6.1.v20100926-0604) actually provide you the excel output as true excel without the XML tagging and it even has the capabilities of capturing the formulas into the excel output. You may consider using it. 
 
Regards 
Woo WS 
 
<blockquote class='ipsBlockquote' data-author="'vivash'" data-cid="73026" data-time="1297109550" data-date="07 February 2011 - 01:12 PM">
We have a problem where just 10K rows in a report is generating over 5 MB of excel file. We are using the standard xls emitter plugin that comes with Birt (we are on Birt 2.6.1). If I simply copy data from the generated excel and paste into a separate spreadsheet the new spreadsheet size just comes out to be around 300KB - so there is a huge difference in what Birt is generating and what the actual data size is. 
 
Looks like the Birt xls emitter(org.eclipse.birt.report.engine.emitter.prototype.excel_2.6.1.v20100812.jar) generates file in xml and that takes up lot of space. We have also tried to use Tribix xls emitter plugin - it generated little smaller files (around 4 MB), but still too big. Also, the performance of Tribix was pretty bad for large report (around 10K rows). 
 
Lots of our users like to export their report into Excel and the size has become a huge problem for us. Is there a work around or a fix that we can use to reduce the size of generate excel files? 
 
Thanks, 
-vivek </blockquote>

JasonW

Vivek,

I just did a simple listing report with 10k and I am getting 2.4 meg. Do you have a lot of styling?

Jason

vivash

Jason,

No I don't have much styling - except highlighting on each row (even without the highlight the file is pretty big, 3-4 MB). I do have around 10 columns with one of the column having up to 200 characters in value. My main issue is the vast difference between the true Excel (around 300-400 KB) and xls generated by Birt (around 3-4 MB).

I don't see any setting in the xls emitter to reduce the size. Is the commercial xls emitter of Actuate requires licensing? If so, then that might not be feasible for us at this time. If not, where do I get the xls emitter from Actuate?

One other alternative I was looking at supporting csv emitter - I'm hoping the file size should be much smaller compared to xml based excel. I looked at http://www.birt-exchange.org/org/forum/index.php/blog/10/entry-46-using-csv-emitter-with-birt-2-3-0/, but the link there to download CSV emitter is not working (404). Does anyone have any idea on how to support csv in Birt 2.6.1?

Thanks,
-vivek

JasonW

Understood. This is mainly because its all uncompressed XML. The Actuate xls emitter does require licensing. If you want those details use the contact us link http://www.birt-exchange.org/org/contactus/.

Jason

vivash

Thanks Jason - I'll try to get licensing info.

What about the csv emitter? How can I add that? As per article here it should be possible, but I can't find the plugin. I don't just want data extraction to csv, but add it as one of the export option to the viewer.

http://www.birt-exchange.org/org/forum/index.php/blog/10/entry-46-using-csv-emitter-with-birt-2-3-0/

-vivek

JasonW

Here is the link, if this is the one you are talking about.
http://www.actuate.com/products/resources/?articleid=11719

Jason

vivash

Thanks Jason. I'm trying to generate csv plugin out of that code using Birt 2.6.1 runtime files, but the plugin project doesn't seem to like it,

1) Inside manifest file, Require-Bundle: org.eclipse.birt.report.engine - this is complaining "Bundle org.eclipse.birt.report.engine" can not be resolved.
2) Inside plugin.xml, I get "Unknown Extension Point" error for "org.eclipse.birt.report.engine.emitter"

Would this csv plugin work with Birt 2.6.1? Is there already a csv emitter for Birt 2.6.x that I can use?

-vivek

JasonW

Vivek,

I just tried this with 2.6.1 and it worked fine. How are you exporting the project. Try adding the attached plugin to your plugins directory. Unzip it first.

Jason

manindra

The program throws following exception if we include attached csv jar file. 
 
org.eclipse.birt.report.engine.api.EngineException: Report engine fails to initialize csv emitter, please make sure required libraries for this emitter are installed. 
at org.eclipse.birt.report.engine.api.impl.EngineTask.createContentEmitter(EngineTask.java:1548) 
at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.doRun(RunAndRenderTask.java:105) 
at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.run(RunAndRenderTask.java:77) 
at cls.reports.CBirtInit.genReport(CBirtInit.java:368) 
at cls.reports.CBirtInit.main(CBirtInit.java:248) 
 
I have included the csv jar file and all other required jar files in the classpath of the program. 
 
Any guess whats going wrong... 
 
 
 
<blockquote class='ipsBlockquote' data-author="'JasonW'" data-cid="73193" data-time="1297368266" data-date="10 February 2011 - 01:04 PM">
Vivek, 
 
I just tried this with 2.6.1 and it worked fine. How are you exporting the project. Try adding the attached plugin to your plugins directory. Unzip it first. 
 
Jason </blockquote>

JasonW

That jar needs to be in a plugins directory not the classpath. Put it in your plugins folder where you set your birt home.

Jason

cloud

i have a problem with the birt report, i create a table and put all the data on it, when i export it to xls it do well but when i give format to the numbers of the data the size of the export report in xls is to large like 58 MB and it cannot be open to save it without the xml,can anyone help me please.-

JasonW

Can you post the report and what version you are using?

Jason

cloud

<blockquote class='ipsBlockquote' data-author="'JasonW'" data-cid="83464" data-time="1317228522" data-date="28 September 2011 - 09:48 AM">
Can you post the report and what version you are using? 
 
Jason </blockquote>
 
 
I see that when i used the format number on the data, the size of the xls is just less than 1 MB, but when i format the numbers with 1000 separator the size of the xls goes to 60 MB.- 
 
I just doing all the report again and the same problem.-

JasonW

This looks like a bug. Any chance you can log a bugzilla entry for this? As a work around instead of using the Format Number property, create a new style and set the number formatting in the style and then apply the style to the field(s). I noticed that this keeps roughly the same size.

Jason

cloud

<blockquote class='ipsBlockquote' data-author="'JasonW'" data-cid="83467" data-time="1317229911" data-date="28 September 2011 - 10:11 AM">
This looks like a bug. Any chance you can log a bugzilla entry for this? As a work around instead of using the Format Number property, create a new style and set the number formatting in the style and then apply the style to the field(s). I noticed that this keeps roughly the same size. 
 
Jason </blockquote>
 
Thanks Jason 
 
But I do that but when i make it run as xls the numbers doesn't see with the format i apply with the style.-

JasonW

You are correct. I just tried this and it had no effect. Can you log a bug for the size?

Jason

cloud

<blockquote class='ipsBlockquote' data-author="'JasonW'" data-cid="83475" data-time="1317237025" data-date="28 September 2011 - 12:10 PM">
You are correct. I just tried this and it had no effect. Can you log a bug for the size? 
 
Jason </blockquote>
 
There's no error, it generate the report but when you try to open it doesn't because of the size near 60 MB i don't know what change because when i use the 2.3.2 version of birt with jboss 2, now that i have the birt 2.6.2 and jboss 4 it's when it stop working well only that report, but i do it new and from zero but still doesn't work when i export as xls, in my data set i use the same function i used before.-

JasonW

That is what we need to get logged in a bugzilla entry. It does not mean it is an error, but the size is way to big. Most likely because meta data on the data element around number formatting. It can be reproduced with sample data.

Jason

cloud

<blockquote class='ipsBlockquote' data-author="'JasonW'" data-cid="83480" data-time="1317240241" data-date="28 September 2011 - 01:04 PM">
That is what we need to get logged in a bugzilla entry. It does not mean it is an error, but the size is way to big. Most likely because meta data on the data element around number formatting. It can be reproduced with sample data. 
 
Jason </blockquote>
 
 
hi jason 
 
i run the report in my eclipse no in the web, and it's in here where i get the trouble,i have other reports that export to xls and doesn't have a large size they're less than a 1 MB, could it be the function that i used in the data set, it's full of sub-select, but in the birt 2.3.2 doesn't have that problem it's only on the 2.6.2

JasonW

I think it probably is a combination of the extra metadata and the number of elements in the xls. If you could open it in Microsoft Excel and save it I think it would reduce the size.

Jason

RameshS

Hi vivash, 
 
You can try "Native Excel Emitter Plug-in for BIRT" 
 
<a class='bbc_url' href='http://www.birt-exchange.org/org/devshare/designing-birt-reports/1421-native-excel-emitter-plugin-for-birt/'>http://www.birt-exchange.org/org/devshare/designing-birt-reports/1421-native-excel-emitter-plugin-for-birt/</a> 
 
If you are facing any performance issue, Let me know. 
 
Good Luck. 
Ramesh

gauglerb

Forgive me if this thread only pertains to older versions of BIRT, but I have the same issue and started a new thread: http://www.birt-exchange.org/org/forum/index.php/topic/28410-xls-file-is-too-large-to-open-other-options-csv/

I have toyed around with all the ideas and plugins listed, but I am not sure the instructions are the same for BIRT 4.2+ as the directory structure is different now, and also I am using the Report Engine API to generate my reports. Also note, for sake of trying to get smaller XLS, I removed all formatting and just dropped data columns onto a grid and the XLS is still 23MB for 20,000 rows.