Excel Emitter - non xml?

Hello,

There are xls and xlsx emitters exist.

Free:
- Tribix BIRT Emitters. It can export to binary xls.

Commercial:
- Actuate BIRT. Can export to both xls and xlsx
- Arctorus BIRT Emitters. Can export to xlsx and other MS Office 2007-2010 OpenXML formats

Regards, Jeff

johnw

As part of the contest that just ended, someone released a native xls emitter. Check in the devshare and market.

Thanks for the replies. When I used the Tribix emitter I still got the same error message as it was an XML Excel I thought. I'll have to check again, perhaps there's a way to change the output.

I liked Actuates Spreadsheet designer, but It's a shame it doesn't integrate with your Eclipse Designed rptdesign report, so I'd basically have to create the report in Eclipse for the webview, and then in the Birt Designer for XLS output. Also the documentation on it was really lacking, I couldnt figure out how to do much with the Java API, as I getting an iServer License just to host this would be illogical and expensive (the designer says it requires it otherwise, to interneper the designers soi files or something).

john - i'll look through the devshare and report back if i find a good solution.

The only other ways I found to generate an XLS was to use a perl libraries and create cucstom scripts per report to convert the two, and that doesn't really sound fun when its dozens of different reports :-(

If anyone else has any more suggestions, let me know!

Thanks!

Tribix creates binary XLS spreadsheets but they are not native for Excel 2010 and you will always get warnings.

So what is wrong with Arctorus BIRT Emitters? Did you try it? It creates native Excel files so you won't get any warnings or errors. It works with any rptdesign reports and exports directly from Eclipse reportviewer and from BIRT runtime.

<blockquote class='ipsBlockquote' data-author="'jeffmor'" data-cid="84696" data-time="1319999088" data-date="30 October 2011 - 11:24 AM">
Tribix creates binary XLS spreadsheets but they are not native for Excel 2010 and you will always get warnings. 
 
So what is wrong with Arctorus BIRT Emitters? Did you try it? It creates native Excel files so you won't get any warnings or errors. It works with any rptdesign reports and exports directly from Eclipse reportviewer and from BIRT runtime. </blockquote>
 
Ohh "Actorous" - to be honest I read your first post too fast and I thought it read Actuate, lol. Lack of sleep. I will check them out right now. Thanks!

I think there are two new ones that johnw alluded to, <a class='bbc_url' href='http://www.birt-exchange.org/org/devshare/designing-birt-reports/1421-native-excel-emitter-plugin-for-birt/'>Ramesh's in DevShare</a> and <a class='bbc_url' href='http://www.spudsoft.co.uk/2011/10/the-spudsoft-birt-excel-emitters/'>mine</a> in <a class='bbc_url' href='http://www.birt-exchange.com/be/marketplace/app-showcase/?app=102'>marketplace</a> (I may have put it in the wrong place, it is free :rolleyes: ). 
 
I haven't tried Ramesh's or Actuate's commercial offering, so I don't know how they compare. 
 
I wrote mine after being frustrated with Tribix and Arctorus trying too hard to match the formatting of the report, with the result that the spreadsheets look great, but don't actually have things in the right columns (and have lots of blank rows/columns). 
My spreadsheets aim to be more useful as spreadsheets, but as a result they are absolutely not pixel-perfect renditions of the report (I cannot handle nested tables at all well, because they don't make sense in spreadsheets). 
 
Tribix definitely doesn't produce the warning message when its output is opened in Excel, the fact that you were getting that message implies that you hadn't swapped out the default emitter correctly.

<blockquote class='ipsBlockquote' data-author="'Yaytay'" data-cid="84700" data-time="1320007161" data-date="30 October 2011 - 01:39 PM">
I think there are two new ones that johnw alluded to, <a class='bbc_url' href='http://www.birt-exchange.org/org/devshare/designing-birt-reports/1421-native-excel-emitter-plugin-for-birt/'>Ramesh's in DevShare</a> and <a class='bbc_url' href='http://www.spudsoft.co.uk/2011/10/the-spudsoft-birt-excel-emitters/'>mine</a> in <a class='bbc_url' href='http://www.birt-exchange.com/be/marketplace/app-showcase/?app=102'>marketplace</a> (I may have put it in the wrong place, it is free :rolleyes: ). 
 
I haven't tried Ramesh's or Actuate's commercial offering, so I don't know how they compare. 
 
I wrote mine after being frustrated with Tribix and Arctorus trying too hard to match the formatting of the report, with the result that the spreadsheets look great, but don't actually have things in the right columns (and have lots of blank rows/columns). 
My spreadsheets aim to be more useful as spreadsheets, but as a result they are absolutely not pixel-perfect renditions of the report (I cannot handle nested tables at all well, because they don't make sense in spreadsheets). 
 
Tribix definitely doesn't produce the warning message when its output is opened in Excel, the fact that you were getting that message implies that you hadn't swapped out the default emitter correctly. </blockquote>
 
 
Thanks for the detailed response! 
 
Tribix - I checked it out again. I deleted the 'org.eclipse.birt.report.engine.emitter.config_****.jar' and 'org.eclipse.birt.report.engin.emitter.excel.config_****.jar' from '/var/lib/tomcat-7/webapps/birt-viewer/WEB-INF/platform/plugins` directory, then copied into it the 'org.uguess.birt.report.engine.common_****.jar', and the 'org.uguess.birt.report.engine.emitter.xls_****.jar', then restarted tomcat. Testing through my web site (didn't bother to copy it and try it out through the eclipse directories), It definitely generates an XML file; which from the sounds of it, it shouldnt be an XML and should be a birnary XLS? Here's the header of the file it created.. 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
<?xml version="1.0" encoding="UTF-8"?>
<?mso-application progid="Excel.Sheet"?>

<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
</pre>
 
And when trying to open in msexcel2010, i get the following error: "The file you are trying to open, test.xls, is in a different format than specified by the extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?" 
 
BTW; It's being downloaded/generated just from a link to the `run?___report=****&__format=xls` not sure if that matters, maybe i have use a different format name? 
 
It sounds like I'm doing something wrong there - I'm just not sure what. The instructions just said I needed to delete those two jars and copy those two into the WEB-INF/platforms/plugins .. If anyone has any input what I might be missing there let me know! 
 
I haven't gotten around to trying Actorus just yet, will give it a shot tonight. 
 
Yaytay - I'll give your (SpudSoft) emitter a try too. 
 
I guess which ever one can format my specific reports the best works. Kind of sounds like the reasoning behind several different ones, which ever one works the best for you lol. 
 
I appreciate your response yaytay, and I will certainly let you know how it works out. Thanks for the work and your reply! 
 
Yaytay - you know I recalled coming across your emitter yesterday and I wasn't sure why I turned it down, but after looking it over, I now recall: It's for Birt 3.7. I am embarrassed to admit I'm using the old Birt 2.5.2 because its the highest version available for Eclipse 3.5.1 which again is unfortunately the highest version available for my linux distro I develop on. I can update it from 3rd parties but I never had any luck getting the stupid WebViewer working in eclipse when xulrunner split to 2.0 and went to webm. I haven't messed around with it in a while but maybe I'll try to uprade eclipse/birt and figure out the whole webviewer mess again so I can give yours a shot. However, you wouldn't happen to know if it works on anything other then 3.7? 
 
Thanks!

That XML is very odd, it really looks like it's still using the default emitter.
What distro are you using? I'll set up a VM and have a play.

I haven't tried my emitter on anything prior to 3.7, but I don't know of any reason why it wouldn't work and could probably be persuaded to fix any reasons that do show up - so give it a go.
Again, if you let me know your distro I'll try it.

<blockquote class='ipsBlockquote' data-author="'Yaytay'" data-cid="84704" data-time="1320040089" data-date="30 October 2011 - 10:48 PM">
That XML is very odd, it really looks like it's still using the default emitter. 
What distro are you using? I'll set up a VM and have a play. 
 
I haven't tried my emitter on anything prior to 3.7, but I don't know of any reason why it wouldn't work and could probably be persuaded to fix any reasons that do show up - so give it a go. 
Again, if you let me know your distro I'll try it. </blockquote>
 
Hmm, would I have to replace those jars anywhere else then that directory to get it to work? 
 
I'm just using a Gentoo build. I tried updating eclipse again but it always breaks the darn webviewer, I can't seem to get eclipse to use webm right. It's been in bugzilla for a while with no working fix (for me at least). I might just go ahead and use it despite of the lack of webview, lol. 
 
I'll let you know if it works on 2.5.2 or not though, no sense in you building a VM to test. Especially since it can take some time to build a Gentoo system :-) . 
 
Thanks!! 
 
 
EDIT: 
 
Ahh there was a third file the install directions were missing that needed to be deleted too. 
'org.eclipse.birt.report.engine.emitter.prototype.excel_****.jar' Also in the WEB-INF/platform/plugins dir. After deleting that, Tribix worked. 
 
 
 
EDIT AGAIN: 
 
Okay so I used your SpudSoft emitter. I started with a fresh plugin directory, I deleted the *engine.emitter.excel.config* and the *engine.emitter.prototype.excel* and copied your *spudsoft.birt.emitters.excel* to the directory (used your latest revision). Afterwards evertime I tried to export as format XLS I'd get 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
+ org.eclipse.birt.report.service.api.ReportServiceException: The output format xls is not supported.
</pre>
 
I checked the viewer.properties and it definitely had the xls entries, I added the ones for xlsx per your documentation and I got the same error. I then tried to call the emitter directly, using this appended url, and get the following error: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
&__emitterid=uk.co.spudsoft.birt.emitters.excel&__format=xls

+ org.eclipse.birt.report.service.api.ReportServiceException: EmitterID uk.co.spudsoft.birt.emitters.excel for render option is invalid.
</pre>
 
Not sure if I'm missing a step there in setting that up right?

<blockquote class='ipsBlockquote' data-author="'Yaytay'" data-cid="84700" data-time="1320007161" data-date="30 October 2011 - 01:39 PM">
I wrote mine after being frustrated with Tribix and Arctorus trying too hard to match the formatting of the report, with the result that the spreadsheets look great, but don't actually have things in the right columns (and have lots of blank rows/columns). </blockquote>
 
Btw, we (Arctorus) gave up the Tribix'es idea to render pixel-perfect excel. We had nothing except complaints from customers about it. For huge reports it was always slow and required a lot of heap to store all tiny columns and rows used for margins formatting. And the most important it was almost not possible to post-process Excel documents. So since 3.0.0 release it's partially based on standard excel emitter and on our Documents library. 
 
Just tried your emitters. Very promising by description but unfortunately couldn't run it with BIRT runtime 3.7.1 and standard ProductCatalog demo report. There are logs for both xls and xlsx. 
 
Regards, Jeff

<blockquote class='ipsBlockquote' data-author="'the.root'" data-cid="84705" data-time="1320042085" data-date="30 October 2011 - 11:21 PM">
I checked the viewer.properties and it definitely had the xls entries, I added the ones for xlsx per your documentation and I got the same error. I then tried to call the emitter directly, using this appended url, and get the following error: </blockquote>
 
EmitterID should be 
"uk.co.spudsoft.birt.emitters.excel.XlsEmitter" for xls format 
"uk.co.spudsoft.birt.emitters.excel.XlsxEmitter" for xlsx

<blockquote class='ipsBlockquote' data-author="'jeffmor'" data-cid="84707" data-time="1320045353" data-date="31 October 2011 - 12:15 AM">
EmitterID should be 
"uk.co.spudsoft.birt.emitters.excel.XlsEmitter" for xls format 
"uk.co.spudsoft.birt.emitters.excel.XlsxEmitter" for xlsx </blockquote>
 
 
Thanks, per your suggestion I tried: 
 
&__emitterid=uk.co.spudsoft.birt.emitters.excel.XlsEmitter&__format=xls 
 
and still got: 
 
+ org.eclipse.birt.report.service.api.ReportServiceException: EmitterID uk.co.spudsoft.birt.emitters.excel.XlsEmitter for render option is invalid. 
 
 
Maybe its an issue with my outdated BIRT version :-/. I'll have to play around with it some more. I'll also check out your (Arctorus) emitters tomorrow, curious what benefit/features it might have..

I have tested SpudSoft and I'm afraid without changes it will work with 3.7.x only. We also have to offer separate emitter version for each major BIRT release 2.5, 2.6 and 3.7.

Regards, Jeff

<blockquote class='ipsBlockquote' data-author="'jeffmor'" data-cid="84709" data-time="1320049973" data-date="31 October 2011 - 01:32 AM">
I have tested SpudSoft and I'm afraid without changes it will work with 3.7.x only. We also have to offer separate emitter version for each major BIRT release 2.5, 2.6 and 3.7. 
 
Regards, Jeff </blockquote>
 
Jeff, 
You managed to make it work with the ProductCatalog report? 
I just tried it in the designer and it ran just fine (I was expecting it to make a total mess of the header, but it wasn't too bad, I'll have a go at making it better this evening - the uneven grid doesn't fit my model of translating directly to a spreadsheet). 
The error that you got seems to imply something environmental, how are you trying to run the report? 
I test within the designer, with some unit tests that use the ReportEngine directly, and within my own report server. 
 
As things stand my manifest is specifying report engine >3.7.0, which I could easily change, but I wonder what features I use that aren't in 2.5. 
 
Jim

<blockquote class='ipsBlockquote' data-author="'Yaytay'" data-cid="84711" data-time="1320051612" data-date="31 October 2011 - 02:00 AM">
You managed to make it work with the ProductCatalog report? </blockquote>
 
Jim, 
 
Yes, I exported ProductCatalog from designer but not from runtime. 
 
Just the list of problems I see. 
 
XLS 
1. Error when open workbook "File access error. Some number formats can be lost." 
2. Logo image is stretched. 
3. Lost information such us address from header and all model descriptions. 
4. Changed yellow background color similar to Tribix. POI issue? 
 
XLSX 
1. Error when open workbook "Workbook has content which can't be read", "Repaired records from /xl/styles.xml" 
2. There are several sheets. First one looks similar to XLS. Next sheets have 3 columns with information but width of these columns is by default. It can't be because of broken styles.xml since column width stored in sheetXX.xml 
 
Tried also SalesInvoice report but got NullPointerException 
 
Caused by: java.lang.NullPointerException 
at uk.co.spudsoft.birt.emitters.excel.ExcelEmitter.setCurrentCellContents(ExcelEmitter.java:802) 
at uk.co.spudsoft.birt.emitters.excel.ExcelEmitter.startData(ExcelEmitter.java:747) 
at org.eclipse.birt.report.engine.emitter.ContentEmitterUtil.startContent(ContentEmitterUtil.java:71) 
... 
 
 
To run it from runtime I use this code 
 
IReportDocument iReportDocument = engine.openReportDocument("reports/" + REPORT + ".rptdocument"); 
IRenderOption options = new RenderOption(); 
options.setOutputFormat("xls"); 
options.setEmitterID("uk.co.spudsoft.birt.emitters.excel.XlsEmitter"); 
options.setOutputFileName("output/" + REPORT + ".xls"); 
IRenderTask renderTask = engine.createRenderTask(iReportDocument); 
renderTask.setLocale(Locale.US); 
renderTask.setRenderOption(options); 
renderTask.render(); 
renderTask.close(); 
 
Regards, Jeff

Thanks Jeff, that's really helpful (that's not sarcasm).
I uploaded a new version today that either fixes or improves those issues - there are still some problems with nested tables/grids but nothing should be lost now.
I have a plan for making a further improvement to nested tables/grid, but my fundamental design aim is to ensure that table rows/columns line up as they need to without having blank/merged row/columns all over the place - and that design aim doesn't like the idea of nested tables.

A work in progress

Hi,

Looks much better now and starts from runtime but sheets except the first one are still unformatted.

By my opinion removing ALL merged columns is not the right way. I think it's better to find golden mean an minimize merging as much as possible. In your case it's impossible to export 2 and more tables with different number of columns to a single sheet. Example of the issue is SalesInvoice. You have to export table with customer/order details as text.

Btw, installation instructions https://bitbucket.org/yaytay/spudsoft-birt-excel-emitters/wiki/Home are not publicly available. At least, I couldn't open it even after registration on bitbucket.

Regards, Jeff

Hi Jeff, 
<blockquote class='ipsBlockquote' data-author="'jeffmor'" data-cid="84919" data-time="1320467666" data-date="04 November 2011 - 09:34 PM">
Looks much better now and starts from runtime but sheets except the first one are still unformatted. </blockquote>
That's my biggest concern - there shouldn't be any sheets except the first for either the ProductCatalog or the SalesInvoice. 
The emitter uses page-break-pagination, which works correctly from within the designer but does not work if separate Run and Render tasks are used. 
I've blogged about that <a class='bbc_url' href='http://www.spudsoft.co.uk/2011/10/lsned46-birt-page-break-pagination-broken/'>here</a>, and filed a <a class='bbc_url' href='https://bugs.eclipse.org/bugs/show_bug.cgi?id=362245'>bug</a>, but that should result in no pagination at all not too much pagination. 
Can you think of anything that might help me repro the problem? 
 
 
<blockquote class='ipsBlockquote' data-author="'jeffmor'" data-cid="84919" data-time="1320467666" data-date="04 November 2011 - 09:34 PM">
By my opinion removing ALL merged columns is not the right way. </blockquote>
I don't remove merged columns, I just don't add any more. 
So it is possible to design a report with two tables with different numbers of columns on a single sheet and to have them line up. 
What is not currently possible is the block in the SalesInvoice, where a nested grid is actually needed (because it needs to work within the group header). 
I have a theory to solve that, but I haven't coded it yet (haven't quite worked out how to

). 
 
What will never be possible is to have two tables next to each other horizontally (left and right) with different numbers of rows in them without reverting to RichText layout. 
My argument is that such a report is not a very spreadsheety report and is better off handled with another emitter. 
 
 
<blockquote class='ipsBlockquote' data-author="'jeffmor'" data-cid="84919" data-time="1320467666" data-date="04 November 2011 - 09:34 PM">
Btw, installation instructions <a class='bbc_url' href='https://bitbucket.org/yaytay/spudsoft-birt-excel-emitters/wiki/Home'>https://bitbucket.org/yaytay/spudsoft-birt-excel-emitters/wiki/Home</a> are not publicly available. </blockquote>
Thanks, it should work now. 
I'm kinda new to all this sharing stuff

Jim

<blockquote class='ipsBlockquote' data-author="'Yaytay'" data-cid="84925" data-time="1320485343" data-date="05 November 2011 - 02:29 AM">
That's my biggest concern - there shouldn't be any sheets except the first for either the ProductCatalog or the SalesInvoice. 
... 
Can you think of anything that might help me repro the problem? </blockquote>
 
Do you mean you can't reproduce it? When I export report from runtime everything is fine but if I export it from designer then there are several pages with wrong columns width. Only width, all other formatting is okay. 
 
What happens if you set HTML_PAGINATION property? In our case it works fine although some difference between documents exported from designer and runtime really exists. 
 
Regards, Jeff

<blockquote class='ipsBlockquote' data-author="'jeffmor'" data-cid="84987" data-time="1320691481" data-date="07 November 2011 - 11:44 AM">
Do you mean you can't reproduce it? 
 
What happens if you set HTML_PAGINATION property? In our case it works fine although some difference between documents exported from designer and runtime really exists. 
 
Regards, Jeff </blockquote>
Yes, I can't reproduce it. 
 
HTML_PAGINATION makes a mess of the spreadsheetiness of reports, so I don't use it myself, however a couple of tests show it behaving as I'd expect - breaks wherever the layout engine chooses with headers repeated on the new sheet (and column widths the same on every sheet). 
 
I wouldn't be surprised to find something going wrong with HTML_PAGINATION, but I can't find anything. 
 
I'm hesitant to ask you to do anything more when you've already been so helpful, but have you tried with the latest version (0.3.0.201111071736 or 0.3.0.201111041030). 
 
Thanks. 
Jim

<blockquote class='ipsBlockquote' data-author="'Yaytay'" data-cid="84990" data-time="1320695122" data-date="07 November 2011 - 12:45 PM">
I'm hesitant to ask you to do anything more when you've already been so helpful, but have you tried with the latest version (0.3.0.201111071736 or 0.3.0.201111041030). </blockquote>
 
I have exported reports with the latest version. Actually, I don't see any visible difference. Do you use standard ProductCatalog? I set Fixed Layout in the report properties instead of Auto Layout. May be that is the problem. Please find attached my test reports and generated documents. 
 
Regards, Jeff

Auto Layout also doesn't help. I will try to build your emitter from sources and check what is wrong.

Jeff

<blockquote class='ipsBlockquote' data-author="'jeffmor'" data-cid="85014" data-time="1320733903" data-date="07 November 2011 - 11:31 PM">
Auto Layout also doesn't help. I will try to build your emitter from sources and check what is wrong. 
 
Jeff </blockquote>
Ah, at last I have a sort-of repro. 
The problem was that the ProductCatalog as found in the BIRT sources has a filter that limits MSRP to between $95 and $100, so everything fitted on a single page. 
Taking away that filter gives me a report on three pages (not twelve) but the column widths are screwed up on the subsequent pages - so I have something to work on. 
Thanks again. 
Jim