Writing more than 65000 rows into excel

Hi All,

We have integrated BIRT in our code, it generates an xml file with extension xls(for excel) which can be opened in excel. I'm not sure whether this is the intended way or we have implemented that way in our code. Anyways it was solving our purpose.

I'm designing a report, which in real time scenarios will have about 200,000 records. The output format is excel.

Now, whenever I'm generating a report with more than 65k rows, the report file(xml) is getting generated but with some tags missing like workbook, worksheet, table etc and hence excel is unable to read it.

Later I found out that the problem is coming up when BIRT tries to write the remaining data(approx after 65k rows) into a fresh sheet.

The code snippet that we use in the framework is attached for further reference. Since the code is quite old, I strongly believe that there has been some miss from our end in the code else there is problem with the BIRT engine while writing the data.

Also, excel 2007 onwards has extension xlsx, does BIRT support this?

I'm in a big fix as I really prefer to implement it using BIRT. Anyone having any idea are always welcomed.

regards
Arpan

Find more posts tagged with

Comments

JasonW

What version of BIRT are you using?

In 2.5.2 you can use the EXCELRenderOption class like:
EXCELRenderOption options = new EXCELRenderOption();

options.setOutputFormat("xls");
options.setOutputFileName("output/resample/customers.xls");
options.setWrappingText(true);
task.setRenderOption(options);

I just tried 100k rows and it worked for me.

Jason

Megha Nidhi Dahal

<blockquote class='ipsBlockquote' data-author="'JasonW'" data-cid="68186" data-time="1283874848" data-date="07 September 2010 - 08:54 AM">
What version of BIRT are you using? 
 
In 2.5.2 you can use the EXCELRenderOption class like: 
EXCELRenderOption options = new EXCELRenderOption(); 
 
options.setOutputFormat("xls"); 
options.setOutputFileName("output/resample/customers.xls"); 
options.setWrappingText(true); 
task.setRenderOption(options); 
 
I just tried 100k rows and it worked for me. 
 
Jason </blockquote>
 
Hi Jason, 
 
I'm using BIRT 2.5.1. I changed the code and used what you have suggested, still I'm getting the same error. Please find attached the error log, and the changed code snippet. I'm supplying a tsv with more than 65k rows to a designer which simply displays it in xls format. 
 
I'm seriously stuck with this. Not able to proceed

regards 
Arpan

JasonW

Can you attach the tsv? Any chance you can try 2.5.2 or 2.6?

Jason

Megha Nidhi Dahal

Hi Jason,

PFA the tsv.

regards
Arpan

JasonW

Looks like this may be an issue with 2.5.1. I tried a simple report with the data and 2.5.2 and it worked.

Jason

Megha Nidhi Dahal

<blockquote class='ipsBlockquote' data-author="'JasonW'" data-cid="68370" data-time="1284386526" data-date="13 September 2010 - 07:02 AM">
Looks like this may be an issue with 2.5.1. I tried a simple report with the data and 2.5.2 and it worked. 
 
Jason </blockquote>
 
Hi Jason, 
 
Ya that seems to be an issue with BIRT 2.5. Is there any work around that I could do or should I give up until we move to 2.5.2 or later? 
 
The best option as of now for me would be to deliver the report as csv or tsv. Though the formatting would be sacrificed. 
 
Need help. 
 
regards 
Arpan

JasonW

I do not know of a work around. You could try just deploying the 2.5.2 xls emitter to the 2.5.1. I am not certain this will work, but may be worth a try.

Jason

Megha Nidhi Dahal

<blockquote class='ipsBlockquote' data-author="'JasonW'" data-cid="68407" data-time="1284486086" data-date="14 September 2010 - 10:41 AM">
I do not know of a work around. You could try just deploying the 2.5.2 xls emitter to the 2.5.1. I am not certain this will work, but may be worth a try. 
 
Jason </blockquote>
Jason, 
 
I tried using BIRT 2.5.2. I replaced all the jars from 2.5.1 with the jars from 2.5.2 and set BIRT_HOME to 2.5.2 location. But I'm getting and error of this kind: 
 
java.lang.NoSuchMethodError: org.eclipse.birt.report.engine.api.EmitterInfo.setOverridePriority(I)V 
at org.eclipse.birt.report.engine.extension.internal.ExtensionManager.loadEmitterExtensionDefns(ExtensionManager.java:629) 
at org.eclipse.birt.report.engine.extension.internal.ExtensionManager.<init>(ExtensionManager.java:148) 
at org.eclipse.birt.report.engine.extension.internal.ExtensionManager.createInstance(ExtensionManager.java:161) 
at org.eclipse.birt.report.engine.extension.internal.ExtensionManager.getInstance(ExtensionManager.java:170) 
at org.eclipse.birt.report.engine.api.impl.ReportEngineHelper.<init>(ReportEngineHelper.java:81) 
at org.eclipse.birt.report.engine.api.impl.ReportEngine.<init>(ReportEngine.java:136) 
at org.eclipse.birt.report.engine.api.impl.ReportEngineFactory$1.run(ReportEngineFactory.java:18) 
at org.eclipse.birt.report.engine.api.impl.ReportEngineFactory$1.run(ReportEngineFactory.java:1) 
at java.security.AccessController.doPrivileged(Native Method) 
at org.eclipse.birt.report.engine.api.impl.ReportEngineFactory.createReportEngine(ReportEngineFactory.java:14) 
at com.subex.sitespecific.sitehelper.taskcontroller.birtreporthelper.RunBillTask.runBill(RunBillTask.java:46) 
at com.subex.sitespecific.sitehelper.taskcontroller.tasks.BirtReportTask.runTask(BirtReportTask.java:89) 
 
I'm sure I have missed out something, but I'm unable to understand what. Anything that you are aware of? 
 
regards 
Arpan

JasonW

If you are using the report engine api you need to replace the jars in the reportenginedownload/birt-runtime-2_5_x\ReportEngine\lib directory and replace the
birt-runtime-2_5_x\ReportEngine\plugins directory also verify that you only have the config.ini in the birt-runtime-2_5_x\ReportEngine\configuration directory.

If you have a workspace directory in birt-runtime-2_5_x\ReportEngine you can delete it as well.

Jason

Megha Nidhi Dahal

Jason,

Just as a try I integrated 2.6.0 and as expected the problem is solved. But since this change has to go into the framework which will be used by many products I'm not sure whether 2.6.0 is a stable version that I can stick to. Well I hope its stable, please make me aware if there are some other 'things' to be taken care.

Also, the text size in pdf generated by 2.6.0 is significantly greater than what 2.5.1 was generating. Well it should be normal too.

Thanks for the support.

regards
Arpan

BarryMcM

Hi guys,

Apologies for jumping on on your thread, but here goes:

Our company are migrating to Office 2010 and so far testing with our BIRT 2.5.1 implementation has produced a warning popup about incompatibility when opening the excel-rendered output. Basically Excel 2010 is complaining about opening an Excel 2003 file.

Will BIRT 2.6 address this issue?

If not, what is the best way to move forward? I'm really getting it in the neck to sort out this before the end of the year.
I've read of using Trixbix, or using Actuate BIRT 11, but I really need to be pointed in the right direction so I can go back to my powers to be with some options.

Thanks,

-Barry

JasonW

Barry,

Is this the bug?
https://bugs.eclipse.org/bugs/show_bug.cgi?id=267988

Jason

BarryMcM

Hi Jason,

Yes, this is the bug okay, and I already included a link to the bug in a document to management a few months ago to highlight that we've no coding solution to get around the issue.

My problem is, I keep reading about Tribix and Actuate BIRT 11 and BIRT 2.6 as potential solutions to the issue and TBH I am getting pretty confused. Obviously our company will not want to commit the time and money to potential solutions unless they have clear reassurance that the solutions will work. Yes; chickens and eggs, I know.
They will be happy enough if I tell them definitively that there is NO soultion to the issue, but I just need to be 100% sure myself before I confirm this with 'upper upper' management.

Thanks,

-Barry

JasonW

Barry,

I do not have 2010 but you could download Actuate BIRT trial and see if it works.

Jason