1. Jim Talbut
  2. SpudSoft BIRT Excel Emitters
  3. Issues

Issues

Issue #75 wontfix

Support for single sheet output

Anonymous created an issue

Is there a way to set export_single_page=true when using the default birt viewer?

I have tried the following but it still outputs a sheet for every page:

localhost:8080/birt/run?format=xls&export_single_page=true&emitterid=uk.co.spudsoft.birt.emitters.excel.XlsEmitter&__report=test100page.rptdesign

Comments (24)

  1. Jim Talbut repo owner

    Firstly export_single_page is an option for the Tribix emitter, not mine.
    The equivalent for the SpudSoft emitters would be either ExcelEmitter.SingleSheet or ExcelEmitter.SingleSheetWithPageBreaks.

    Secondly, do you want the report to sometimes be in a single worksheet and sometimes not? If you always want the report on a single worksheet just create a UserProperty on the report called ExcelEmitter.SingleSheet (WithPageBreaks if you want) and set it to true.

    Almost all of the properties that the SpudSoft emitter uses can be set either as RenderOptions or as UserProperties on a report item - and for added convenience it checks the hierarchy of report items too, so a UserProperty on an ancestor counts for the behaviour of the child. This gives a lot of control to both the report designer and the hosting platform.

    So, if you really need to be able to choose at runtime whether or not the report is output on a single sheet then there are two things to do: Firstly, use this devshare article that I wrote (http://www.birt-exchange.org/org/devshare/designing-birt-reports/1540-obtaining-contextual-information-from-a-report-server./) to see how the query string parameters are made available to the report. Then add a script, probably in the BeforeFactory to pick up the query string parameter and put it in the Report UserProperties.

    I could add code to explicitly check the query string parameters, but I don't think I will because the facilities already there enable you to do what you want without being specific to any one hosting platform.

    Jim

  2. Jim Talbut repo owner

    I don't need to write code in order to enable my options to be set from the query string - it can easily (and more flexibly) be written in the reports themselves or in the container.

  3. Gary Benade

    Firstly export_single_page is an option for the Tribix emitter, not mine. I know, that's why I am asking you to add it to yours please :)

    I don't need to write code in order to enable my options to be set from the query string - it can easily (and more flexibly) be written in the reports themselves or in the container. I has a look at your example report, envious of your BIRT scripting skills, but I don't see how to set the single page option. Some of my reports are 90 pages long and 90 worksheets in excel are impossible to use

    Can you please reconsider adding the command line option, or provide a scripting example of how to do it in beforeFactory?

    I assume it will be something like: xlsConfig.put( "export_single_page", Boolean.TRUE ); but I can't find any examples of how to do this online

    Thanks, I really appreciate your effort and time

  4. Jim Talbut repo owner

    Ah, now I understand - sorry, didn't mean to be unhelpful. It'll be something like:

    reportContext.getRenderOption().setOption("ExcelEmitter.SingleSheet", true );

    I'll get you a full example later today, but before I do, please can you answer a few questions? There are lots of different ways to control sheet output in my emitter, and it'll help me to point you in the right direction if I know a bit more about what you are trying to do - the ExcelEmitter.SingleSheet option is rarely what people actually want.

    1. What formats do you intend to output your reports as? If you are only outputing to XLS[X] there are much simpler options.
    2. Do you want the pagination to be different between those different formats? There are three different ways an emitter can handle pagination: not at all; by page size; by explicit command - my emitters do the last of these, PDF goes by page size. What really matters is whether you want the Page Break Interval to be different for different formats.

    Regardless of your answers I'll get you a scripted example just so you can see it.

    The reason I tend to direct people away from ExcelEmitter.SingleSheet is that if you have a non-zero value set for Page Break Interval using ExcelEmitter.SingleSheet won't stop the headers/footers being repeated.

    Thanks.

  5. Gary Benade
    1. I will only be outputting to XLS
    2. I don't want any pagination, the clients need to work with this data in Excel and they find it easier if the data is in 1 big sheet

    Having the headers and footers repeated will be a problem, if ExcelEmitter.SingleSheet is not the way to go can you please point me in the right direction?

    Thanks again for your time, hugely appreciated

  6. Jim Talbut repo owner

    What you need to do is work out why the report isn't on just one page.

    The thing that trips most people up is the "Page Break Interval" property of tables. This has a default value of 40, which shows up in the designer, but doesn't appear in the XML of the rptdesign file unless you set it to something other than 40. This property causes BIRT to output a page break after that many rows of a table - and my emitter does see those page breaks.

    Check the other options on the Page Break properties tab for all the elements on your report - as long as don't have them set to "Always" I think you'll be OK, but try setting them to "Never".

    If you can attach your rptdesign I can give more specific advice.

    Jim

  7. Jim Talbut repo owner

    Thanks for the promotion, but do please note that using that option is usually not the best approach because it will still cause the output to have columns headers where the page breaks would occur. The better approach is usually to tell BIRT not to output any page breaks, by setting the Page Break Interval to 0 on all tables.

  8. Biniam Asnake

    Jim Talbut The reason I did not want to set the Page Break Interval is I have so many already designed and to be designed reports. So, it is not recommended to set the property on every report (as a programmer).

    I have a service class where if it is an Excel file, it should export it to a Single sheet with only one header.

    I spent three whole days to figure this out (not using BIRT but your emitter /SpudSoft). If there is still a better way, I'm still ready to accept comments.

    BZW, Nice emitter and neat coding. I saw your source code and if I havenot come across this technique, I was going for extending your emitter or writing my own.

  9. Jim Talbut repo owner

    Well it depends on whether you are bothered by the headers.

    If you aren't bothered by the headers then the RenderOption/UserProperty has the benefit of needing very little code. If you are bothered by the headers you can change things on the fly dependent upon the output emitter - your Java code can easily modify the design of each table before running the report, but that's still a chunk more work than just using the option.

  10. Biniam Asnake

    The headers bother me since they are shown after every 40th line. So, how can I programatically set the number of Page Break Interval to 0? I still want to use your emitter rather than BIRT for Excel. And thanks for the fast reply.

  11. Jim Talbut repo owner

    What follows isn't compilable, it's a bunch of functions that I hope it'll show you what you need to look up:

    IReportRunnable reportRunnable = reportEngine.openReportDesign();
    ReportDesignHandle designHandle = (ReportDesignHandle)reportRunnable.getDesignHandle();
    
    for(Iterator<DesignElementHandle> iter = designHandle.getBody().iterator(); iter.hasNext() ) {
      DesignElementHandle designElement = iter.next();
      if( designElement instanceof TableHandle ) {
        ((TableHandle)designElement).setPageBreakInterval(0);
      }
    
    }
    

    Something like that.

  12. Biniam Asnake

    Thanks a lot. I used your code and changed some things so it could compile. The following code works!!!

    Please incorporate this option in the next release of SpudSoft as the default setting of "ExcelEmitter.SingleSheet" option. i.e. PageBreakInterval = 0 and RepeatHeader = False.

    I don't want the page break for Excel. So,

    IRenderOption options = null;
    
    options = new EXCELRenderOption(outputFormat: "xls");
    
    //SpudSoft Emitter
    options.setEmitterID("uk.co.spudsoft.birt.emitters.excel.XlsEmitter");
    options.setOption("ExcelEmitter.SingleSheetWithPageBreaks", true);
    
    // reportFileFullPath is a String that contains absolute path of the file.
    IReportRunnable reportDesign = reportEngine.openReportDesign(reportFileFullPath);
    
    ReportDesignHandle designHandle = (ReportDesignHandle) reportDesign.getDesignHandle();
    
    DesignElementHandle designElement =  designHandle.getBody().get(0);
    
    if( designElement instanceof TableHandle ) {
        ((TableHandle)designElement).setPageBreakInterval(0);
        ((TableHandle)designElement).setRepeatHeader(false);
    }
    
  13. Jim Talbut repo owner

    Unfortunately it's not possible to get it into the emitter - it would only work if the emitter was aware of the report before the report had been run, which it isn't.

  14. Jan Briel

    Hi, I have a report that runs and displays my results in html format the way that I want it with pagination. Now when the user exports the report each page is on a new tab. I understand why but I need the export to be on one page.

    I am using Birt 4.3 using the xls_spudsoft option when exporting. We are not using the API I am calling the report with an url.

    I tried the adding the following to beforeFactory

    if( reportContext.getOutputFormat().equalsIgnoreCase("xls") || reportContext.getOutputFormat().equalsIgnoreCase("xlsx")) { reportContext.getReportRunnable().designHandle.getDesignHandle().findElement("Data").setPageBreakInterval(0) }

    where "Data" is my table

    I also tried adding this.setUserPropertyValue("ExcelEmitter.SingleSheetWithPageBreaks", true); in the on Render property for the table.

    None of the above worked I can add my report design if needed.

  15. Jim Talbut repo owner

    Hi,

    I think your first approach (conditionally setting the page break interval to 0) is failing because you are comparing the output format with "xls" - I think they made it xls_spudsoft for BIRT 4.3.0. Does it work if you just change the page break interval to 0 without any conditions? That will tell you if the emitter is doing what you want, then it's just a case of getting the script right.

    For you second approach you don't need any script, just create a user property on the REPORT and set it. You can't set it on the table, that's too late for that option. Note that this approach will duplicate the headers for each page, so I tend not to recommend it.

    Jim

  16. Jan Briel

    Hi thx for the reply, When I set the page break interval to 0 I do get the expected result. I tried comparing the output format with "xls_spudsoft " and still no luck. The weird thing is that when I print out the output format for the xls_spudsoft option for export it prints out "html". It would prefer to use this method rather then the second as you recommended. Any idea on how to test if the output format is xls_spudsoft on the before factory or any other event?

  17. iskohl

    Hi, could you make default value of ExcelEmitter.SingleSheet true or how can I make it true? Should I modify source code? I think for the statistic it would be better all data in one sheet. Thanks.

  18. 亮 阳

    Hi, emergency Yes, I change my code like Jim's

    What follows isn't compilable, it's a bunch of functions that I hope it'll show you what you need to look up:
    IReportRunnable reportRunnable = reportEngine.openReportDesign();
    ReportDesignHandle designHandle = (ReportDesignHandle)reportRunnable.getDesignHandle();
    
    for(Iterator<DesignElementHandle> iter = designHandle.getBody().iterator(); iter.hasNext() ) {
      DesignElementHandle designElement = iter.next();
      if( designElement instanceof TableHandle ) {
        ((TableHandle)designElement).setPageBreakInterval(0);
      }
    
    }
    

    every thing like nice, just one sheet. unfortunately, the xls shows strange when the table result more than 10000 rows,, it will be more sheets, and the row numbers of every sheet is very strange, one xls file is 45 rows, the other file of another report is 10000 rows. what should I do? If you could give me some advice, I would appreciate that.

  19. 亮 阳

    Hi, I update the comment from me since I got the link from https://bugs.eclipse.org/bugs/show_bug.cgi?id=413607. 1. So, I resolve my problem that there are just 9999 rows lost one row when ((TableHandle)designElement).setPageBreakInterval(0); I change the ((TableHandle)designElement).setPageBreakInterval(10000); then one sheet 10000 rows. 2. the issue: one xls file is 45 rows / sheet. the cause reason is set fixed layout of layout preference, It should be set auto layout. thanks everyone help me. hope to help someone Paul Yang

  20. Log in to comment