Excel output in multiple sheets

Hi there,

I recently ran into this problem that Excel 2003 doesn't support more than some 64k rows, by then we were using BIRT 2.5.1. Later we upgraded to 2.6.0 which generated excel file with multiple sheets if there were more than 64k rows and the problem was solved. The first sheet with around 64k rows and the later sheets with the number of rows remaining.

But, this was an implicit splitting into multiple sheets. Is there any way that I can tell BIRT to give me output in multiple sheets, something like page break? I may want to show different information in different sheets.

Would be a great help if anyone could point me towards the solution.

regards
Arpan

Find more posts tagged with

Comments

mwilliams

Hi Arpan,

I believe that the Tribix XLS emitter starts a new page when there is a page break. I could be misremembering on this though. It's been a little bit since I've had it installed. You can find the Tribix emitters in the devShare or Marketplace.

thuston

I know that Actuate 11 (BIRT 2.6) has the ability to export to multiple sheets based on pagebreaks.
View in Web Viewer then export content to EXCEL and check the multisheet checkbox.

You can even specify the sheet name by setting this page variable:
Use in OnPageBreak
reportContext.setPageVariable( reportContext.PAGE_VAR_PAGE_LABEL, this.getValue() );

Megha Nidhi Dahal

<blockquote class='ipsBlockquote' data-author="'thuston'" data-cid="70258" data-time="1289570631" data-date="12 November 2010 - 07:03 AM">
I know that Actuate 11 (BIRT 2.6) has the ability to export to multiple sheets based on pagebreaks. 
View in Web Viewer then export content to EXCEL and check the multisheet checkbox. 
 
You can even specify the sheet name by setting this page variable: 
Use in OnPageBreak 
reportContext.setPageVariable( reportContext.PAGE_VAR_PAGE_LABEL, this.getValue() ); </blockquote>
 
Thutson, 
 
I'm slightly confused when you mentioned Actuate 11 (Birt 2.6), does this mean both are same or has similar features? I was in an impression that Actuate 11 is a commercial offering whereas Birt 2.6 is open source. 
 
I downloaded the latest open source birt release from <a class='bbc_url' href='http://download.eclipse.org/birt/downloads/'>Eclipse Birt</a>, using the classic model db I made a dummy report and gave page break interval as 5. Whenever I'm exporting it to xls format there are no multiple sheets whereas there are page breaks in pdf output. Also I'm unable to locate the option to export to multiple sheet in the web viewer. 
 
Please help me get through it. 
 
regards 
Arpan

Megha Nidhi Dahal

Hi All,

Just for a try I used tribix xls emitter. I got the output in multiple sheets, but the formatting is gone for a toss. There are unnecessary cell merging, and empty rows in the report.

Attached is the sample designer and the output I got using tribix.

I was very much hoping that I could get this done using standard BIRT jars, but it seems there is no other option

Anyone please go through the attachment and please suggest some ways to make the xls output without extra cell merges and formatting.

regards
Arpan

mwilliams

Actuate BIRT 11 is BIRT 2.6 plus the added features that the commercial version brings.

As for the Tribix issues, the merging of cells is caused by the report design. It's trying to match your setup exactly. The less complex your report design is, the less cell merging that will be done. Short of writing your own emitter, you'll probably just have to find the best way to design your reports to work with the current emitter's issues.

Megha Nidhi Dahal

Michael,

Sorry for digging up the issue so late, actually I was engaged in something else and couldn't respond in time. As far as complexity of the design is concerned as you have pointed out, I would say that my test design was very simple and straight forward, it had no highlighting, nor any groupings or dynamic columns or so, but still the formatting is all messed up.
Only thing we can do is to hope that there comes better emitters in the future. The best thing would be open source BIRT itself start supporting it. I have requested for an enhancement in eclipse community. Lets keep the fingers crossed

regards
Arpan

mwilliams

Arpan,

If you put the request information in here, others can click on it and vote for it if they have the same issue.

Megha Nidhi Dahal

Michael, 
 
That is a very good point. 
 
Request to all BIRT(ers) to please vote for this topic: 
<a class='bbc_url' href='https://bugs.eclipse.org/bugs/show_bug.cgi?id=331197'>Eclipse Enhancement(<a class='bbc_url' href='https://bugs.eclipse.org/bugs/show_bug.cgi?id=331197'>https://bugs.eclipse.org/bugs/show_bug.cgi?id=331197</a>)</a> 
I see this as a very required feature and would be great if it gets implemented. 
 
regards 
Arpan

thuston

If run the report in Web Viewer, then choose to Export Content to Excel, the dialog has the option for multiple sheets.

Megha Nidhi Dahal

elvis_tu

<blockquote class='ipsBlockquote' data-author="'thuston'" data-cid="70950" data-time="1291299825" data-date="02 December 2010 - 07:23 AM">
If run the report in Web Viewer, then choose to Export Content to Excel, the dialog has the option for multiple sheets. </blockquote>
 
Hi, 
 
How could I add the option for "output to multiple sheets" in url such as: 
<a class='bbc_url' href='http://.../executereport.do?...&__format=xls&'>http://.../executereport.do?...&__format=xls&</a>__multiplesheets=true 
 
Thanks a lot.

Anita Mathew

When I export using Tribix emitter, new sheet is created for each page break. I am using BIRT 2.5.2 with emitter 2.5.1. Is there a configuration parameter to turn this off?

mwilliams

Anita,

I don't use the Tribix emitter too often, but I don't think there is a way to turn it off with the Tribix emitter besides when you output to XLS not page breaking.

g2kuong

<blockquote class='ipsBlockquote' data-author="'Anita Mathew'" data-cid="74946" data-time="1300831864" data-date="22 March 2011 - 03:11 PM">
When I export using Tribix emitter, new sheet is created for each page break. I am using BIRT 2.5.2 with emitter 2.5.1. Is there a configuration parameter to turn this off? </blockquote>
 
Yes there is a away - see XlsEmitterConfig.java in the project. 
 
/** 
* Represents if export all pages into a single sheet.The value should be a 
* Boolean object. 
*/ 
public final static String KEY_EXPORT_SINGLE_SHEET = "export_single_page"; //$NON-NLS-1$ 
 
 
so the following will do the trick: 
 
EXCELRenderOption options = new EXCELRenderOption(); 
options.setOption ("export_single_page", true); 
task.setRenderOption(options);

mwilliams

Awesome! Thanks for the info, g2kuong!

For Birt

Hi

i need to put page break in excel with BIRT out put,can u help me in this ASAP....

SRGray

slievain

Hi all,

I understand that by exporting in Excel from the web viewer with the multiple sheet option, it is possible to have a multiple tab excel spreadsheet (based on page break rules).
Nevertheless, I don't know how to achieve it directly from the Designer when running the report directly in XLSX. Is there any variable/configuration to set in the Designer?

I'm using Acutate 11.0.3 by the way.

Thanks.

SRGray

Hello, I am trying to export to XLS or XLSX but based on table of contents rather than on page break. Is this a possibility and if so, can I have some assistance on the script?

Put another way, I have a custom TOC based on elements in the report (3 table report). I would like the export to put each of the three tables onto three different worksheets.

Thanks - SRG

Yaytay

<blockquote class='ipsBlockquote' data-author="'SRGray'" data-cid="109258" data-time="1347481934" data-date="12 September 2012 - 01:32 PM">
Hello, I am trying to export to XLS or XLSX but based on table of contents rather than on page break. Is this a possibility and if so, can I have some assistance on the script? 
 
Put another way, I have a custom TOC based on elements in the report (3 table report). I would like the export to put each of the three tables onto three different worksheets. 
 
Thanks - SRG </blockquote>
 
Why not just make each item also have a page break after? 
 
Jim

SRGray

Thanks Jim that was my thought as well. Each table though seems to have page breaks within it at a certain threshold, cannot figure out how to prevent those. Perhaps I have to just reset the page settings for this report.

Yaytay

<blockquote class='ipsBlockquote' data-author="'SRGray'" data-cid="109360" data-time="1347630826" data-date="14 September 2012 - 06:53 AM">
Thanks Jim that was my thought as well. Each table though seems to have page breaks within it at a certain threshold, cannot figure out how to prevent those. Perhaps I have to just reset the page settings for this report. </blockquote>
There are a couple of things that I know of that can cause that: 
<ul class='bbc'><li> The "Page Break Interval" property of the table - make sure this is explicitly set to 0. 
If the interval isn't set it defaults to 40, which will insert a page break after 40 rows of content.</li><li> Some, but not all, emitters output new sheets based on the page size. 
To stop them from doing so just make your page huge (3m x 3m), or use a different emitter.</li></ul>
 
Jim