Show Data in One Excel (XLSX) Tab

Hi,

How can we get BIRT's data in one EXCEL sheet in BIRT 4.6.0.v2 ?

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.

BIRT seperates the data into multiple tabs in XLSX.

reportContext.getOutputFormat() function always shows HTML. So I could not use  that function.

Regardingly,


Comments

  • 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.


  • edited January 4, 2017

    Hi Mblock,


     


    Thanks for reply. Is there any other way from using 2 tables.


     


    For example I wrote below code to BeforeFactory but I was not be successful because "reportContext.getOutputFormat" function always returns "html".


     


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

    reportContext.getReportRunnable().designHandle.getDesignHandle().findElement("table_name").setProperty("pageBreakInterval", 0);

    }

     

    Is there any other formula to be able to get XLSX value returns when we use to export XLSX?

    Or Is there any fixpack to fix the bug of reportContext.getOutputFormat()?

     

    Thanks,

    My best regards,

  • Not that I am aware of. getOutputFormat should only work in beforeRender. By that time it is too late to set the pageBreakInterval


     


    P.S. In commercial BIRT there is a setting for this.


  • When exporting, go to the page settings and un tick the output to multiple sheets. Hope it helps


  • If I am not mistaken this is an option for commercial BIRT only (not an option for OS BIRT).,


  • edited January 6, 2017

    Hi Shamo and Mblock,


     


    There is not an option to be able to untick output multiple sheets in Open Source BIRT.


     


    I only have PageBreak Options property and it includes PageBreak Intervals value.


     


    Actually I can bring one Excel Sheet by writing some codes in ExportReportDialogFragment.js 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.


     


    It would be better if I found a formula without adding two tables. Please share me if you find other method.


     


    Thanks,


    Best Regards,


  • I am not aware of any other solution than using commercial BIRT.


  • Sorry. I'm using commercial birt.


  • Hi Mr_Data,


     


    WE have one solution, please try with below script in beforeRender event in your report design,


     


    if( reportContext.getOutputFormat() == "xls" || reportContext.getOutputFormat() == "xlsx" )

     

    {

    reportContext.getRenderOption().getOptions().put("excelRenderOption.hideGridlines", true);

    reportContext.getRenderOption().getOptions().put("excelRenderOption.multipleSheet", false);

     

    }

     

    Please try this and let me know.

     

    Thanks,

    Jayanthi s

  • Hi JayanthiS,


     


    I try your script in BeforeRender but it did not work.


     


    We solved our problem like below.


     


    1- BirtExportReportDialog.js is in the


    YOUR BIRT PATH\plugins\org.eclipse.birt.report.viewer_4.6.0.v201606072122\birt\webcontent\birt\ajax\ui\dialog


     


    We add a script in it like below.


     


    if( format=="xlsx" )

    {

    action = action + "&__emitterid=uk.co.spudsoft.birt.emitters.excel.XlsxEmitter&__ExcelEmitter.DisplayRowColHeadings=false&__ExcelEmitter.SingleSheet=true;

    }

     


    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.


     


    2-  We added below script onRender in header. So We have only one header and one Excel Sheet.


     


    var format=reportContext.getOutputFormat();

     

        if(format=="xlsx")

        {

            if(pageNumber>1)

            {

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

            }

        } 

     

    Thanks for your reply and helps,

    Best Regards,

  • 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?

  • 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_iamprince_iam BIRT & Oracle Developer

    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.

Sign In or Register to comment.