Show Data in One Excel (XLSX) Tab

Mr_Data · January 3, 2017

<p><span style="color:rgb(0,0,0);font-family:'Open Sans', 'Helvetica Neue', Helvetica, Arial, sans-serif;font-size:13px;background-color:rgb(244,244,244);">Hi,</span><br><br><span style="color:rgb(0,0,0);font-family:'Open Sans', 'Helvetica Neue', Helvetica, Arial, sans-serif;font-size:13px;background-color:rgb(244,244,244);">How can we get BIRT's data in one EXCEL sheet in BIRT 4.6.0.v2 ?</span><br><br><span style="color:rgb(0,0,0);font-family:'Open Sans', 'Helvetica Neue', Helvetica, Arial, sans-serif;font-size:13px;background-color:rgb(244,244,244);">I set up 40 rows in pagebreak intervals. I should see data as 40 rows in html but I need to show data in one Excel sheet.</span><br><br><span style="color:rgb(0,0,0);font-family:'Open Sans', 'Helvetica Neue', Helvetica, Arial, sans-serif;font-size:13px;background-color:rgb(244,244,244);">BIRT seperates the data into multiple tabs in XLSX.</span><br><br><span style="color:rgb(0,0,0);font-family:'Open Sans', 'Helvetica Neue', Helvetica, Arial, sans-serif;font-size:13px;background-color:rgb(244,244,244);">reportContext.getOutputFormat() function always shows HTML. So I could not use that function.</span><br><br><span style="color:rgb(0,0,0);font-family:'Open Sans', 'Helvetica Neue', Helvetica, Arial, sans-serif;font-size:13px;background-color:rgb(244,244,244);">Regardingly,</span></p>

micajblock · January 3, 2017

<p>change the page break to 0. If you still need a page break for HTML, use 2 tables. One with visibility only in Excel with a page break of 0.</p>

Mr_Data · January 4, 2017

<p>Hi Mblock,</p>
<p> </p>
<p>Thanks for reply. Is there any other way from using 2 tables.</p>
<p> </p>
<p>For example I wrote below code to BeforeFactory but I was not be successful because "<span style="font-family:arial, sans-serif;font-size:12.8px;"><em>reportContext.getOutputFormat</em>" function always returns "<em>html</em>".</span></p>
<p> </p>
<div style="font-family:arial, sans-serif;font-size:12.8px;"><em>if( reportContext.getOutputFormat() == "xlsx" ){</em></div>
<div style="font-family:arial, sans-serif;font-size:12.8px;"><em>reportContext.getReportRunnable().designHandle.getDesignHandle().findElement("table_name").setProperty("pageBreakInterval", 0);</em></div>
<div style="font-family:arial, sans-serif;font-size:12.8px;"><em>}</em></div>
<div style="font-family:arial, sans-serif;font-size:12.8px;"> </div>
<div style="font-family:arial, sans-serif;font-size:12.8px;">Is there any other formula to be able to get XLSX value returns when we use to export XLSX?</div>
<div style="font-family:arial, sans-serif;font-size:12.8px;">Or Is there any fixpack to fix the bug of <em>reportContext.getOutputFormat()</em><span style="font-size:12.8px;">?</span></div>
<div style="font-family:arial, sans-serif;font-size:12.8px;"> </div>
<div style="font-family:arial, sans-serif;font-size:12.8px;">Thanks,</div>
<div style="font-family:arial, sans-serif;font-size:12.8px;">My best regards,</div>

micajblock · January 4, 2017

<p>Not that I am aware of. getOutputFormat should only work in beforeRender. By that time it is too late to set the pageBreakInterval</p>
<p> </p>
<p>P.S. In commercial BIRT there is a setting for this.</p>

shamo · January 5, 2017

<p>When exporting, go to the page settings and un tick the output to multiple sheets. Hope it helps</p>

micajblock · January 5, 2017

<p>If I am not mistaken this is an option for commercial BIRT only (not an option for OS BIRT).,</p>

Mr_Data · January 6, 2017

<p>Hi Shamo and Mblock,</p>
<p> </p>
<p>There is not an option to be able to untick output multiple sheets in Open Source BIRT.</p>
<p> </p>
<p>I only have PageBreak Options property and it includes PageBreak Intervals value.</p>
<p> </p>
<p>Actually I can bring one Excel Sheet by writing some codes in <strong><em>ExportReportDialogFragment.js</em></strong> file even if PageBreak Intervals shows 40. But , Excel sheet shows headers for every 40 rows in Excel. It repeats headers. I need repeating headers in html but not in Excel.</p>
<p> </p>
<p>It would be better if I found a formula without adding two tables. Please share me if you find other method.</p>
<p> </p>
<p>Thanks,</p>
<p>Best Regards,</p>

micajblock · January 6, 2017

<p>I am not aware of any other solution than using commercial BIRT.</p>

shamo · January 6, 2017

<p>Sorry. I'm using commercial birt.</p>

JayanthiS · January 9, 2017

<p>Hi Mr_Data,</p>
<p> </p>
<p>WE have one solution, please try with below script in beforeRender event in your report design,</p>
<p> </p>
<div>if( reportContext.getOutputFormat() == "xls" || reportContext.getOutputFormat() == "xlsx" )</div>
<div> </div>
<div>{</div>
<div>reportContext.getRenderOption().getOptions().put("excelRenderOption.hideGridlines", true);</div>
<div>reportContext.getRenderOption().getOptions().put("excelRenderOption.multipleSheet", false);</div>
<div> </div>
<div>}</div>
<div> </div>
<div>Please try this and let me know.</div>
<div> </div>
<div>Thanks,</div>
<div>Jayanthi s</div>

Mr_Data · January 13, 2017

<p>Hi JayanthiS,</p>
<p> </p>
<p>I try your script in BeforeRender but it did not work.</p>
<p> </p>
<p>We solved our problem like below.</p>
<p> </p>
<p>1- <strong>BirtExportReportDialog.js</strong> is in the</p>
<p>YOUR BIRT PATH\plugins\org.eclipse.birt.report.viewer_4.6.0.v201606072122\birt\webcontent\birt\ajax\ui\dialog</p>
<p> </p>
<p>We add a script in it like below.</p>
<p> </p>
<div><strong><em>if( format=="xlsx" )</em></strong></div>
<div><strong><em>{<span> </span></em></strong></div>
<div><strong><em>action = action + "&__emitterid=uk.co.spudsoft.birt.emitters.excel.XlsxEmitter&__ExcelEmitter.DisplayRowColHeadings=false&__ExcelEmitter.SingleSheet=true;</em></strong></div>
<div><strong><em>}</em></strong></div>
<p> </p>
<p>This provides us in one EXCEL Sheet. But Headers repeated in every 40 rows in one Excel sheet. So we have done second script to delete repeated headers like below.</p>
<p> </p>
<p>2- We added below script onRender in header. So We have only one header and one Excel Sheet.</p>
<p> </p>
<div><strong>var format=reportContext.getOutputFormat();</strong></div>
<div> </div>
<div><strong> if(format=="xlsx")</strong></div>
<div><strong> {</strong></div>
<div><strong> if(pageNumber>1)</strong></div>
<div><strong> {</strong></div>
<div><strong> this.getStyle().display="none";</strong></div>
<div><strong> }</strong></div>
<div><strong> } </strong></div>
<div> </div>
<div>Thanks for your reply and helps,</div>
<div>Best Regards,</div>

IsmailSh · February 7, 2018

Hi @Mr_Data ,

I have the same requirement of hiding header repeat in excel and tried your solution but still header is repeated on page break. Can you please tell if pageNumber is some variable or it actually gives the current page number?

IsmailSh · February 8, 2018

Worked for me.

Note for future visitors
In action, ** __ExcelEmitter.DisplayRowColHeadings=false** disables column resizing in excel. So it can be omitted if someone faces this issue.

prince_iam · June 13, 2019

Dear Mates,

I have joined this discussion today, The proposed solution is absolutely working fine on local birt installation when we run the report from the server where the birt viewer has installed the desired result in one excel sheet is not been achieved despite updating the BirtExportReportDialog.js at the server birt viewer installation. Could you please help to sort out this issue.

wwilliams · March 19, 2021

One solution, it works from Maximo.

In the beforeFactory

reportContext.getAppContext().put("MAX_PAGE_BREAK_INTERVAL",1000000);

Then in the header

if(reportContext.getOutputFormat()=="xlsx"{

if(pageNumber>1) {

this.getStyle().display="none";

}

For the Page Break page break interval: enter 1000000

Show Data in One Excel (XLSX) Tab

Comments

Categories