Disable multi-sheet generation in xlsx emitter

[SteveSchafer-Innovent]

I have a client that wants to disable multi-sheet generation in the xlsx emitter.

Coincidentally, this bug was just created yesterday: https://bugs.eclipse.org/bugs/show_bug.cgi?id=573117

The spudsoft emitter recognizes a render option "SINGLE_SHEET", which looks like it will do this but I can't find any provision in the viewer to set that option. It would be ideal if this option could be set globally in viewer.properties and also passed as a URL parameter such as __single_sheet=true/false or __xlsx_options=single_sheet/multi_sheet.

Please let me know if this capability already exists and I missed it, or if there's any reason not to implement it as I suggested.

[hvonbargen]

I don't know, but if it doesn't work with BIRT now, one should have a look at the newer sources of the emitter in my fork hvbtup/BIRT.
And regarding the question in the bug, the 40 (rows) is the default page break for lists and/or tables, so it is correct if the emitter creates a new sheet every 40 records. Unfortunately the maximum value was 9999, maybe this is still the case.
The emitter options can usually be set as user properties, but probably also in other easy. Unfortunately yaytay's bitbucket repository, which also contained documentation, is no longer available.
I'm pretty sure that any emitter option can be set in the URL query, but I don't use it myself, so I don't know exactly this works. Anyway, did you try the exact spelling SINGLE_SHEET=true?

[speckyspooky]

Hello SteveSchafer, there is an option to set this behavior on report level.

If you add on the root element the user property "ExcelEmitter.SingleSheet" (Boolean) and set the value to "true" then you avoid multi-page sheets.
But you have to handle the repeat of header if it is active due to usage on PDF-site.

The spudsoft-Emitter has further additional options which can be set on report root tag based on user properties like "ExcelEmitter.RemoveBlankRows" or "ExcelEmitter.DisableGrouping".

[SteveSchafer-Innovent]

I confirmed that speckyspooky's suggestion works. Unfortunately, having to modify all their reports isn't a very good option for my client. I'd still like to implement something at the viewer level. I suspect one sheet per page is undesirable for most people, especially ones that tend to have large reports. Being able to set the default value in viewer.properties would be great.

[speckyspooky]

I'm back with new details. By default there isn't a way to configure the xlsx-Emitter-behavior based on the viewer.properties.
To do this the java-component of the viewer (according property-handling) must be rewritten.

Therefore I used another way to solve your requirement.
You can change the viewer-export dialog directly:
C:\Program Files\Apache Software Foundation\Tomcat 9.0\webapps\birt\webcontent\birt\ajax\ui\dialo\BirtExportReportDialog.js

In this JS-File is a function definition " __exportAction : function( ) { ... }" and there I added the following sequence to manipulate the URL-call at the moment of export:

__exportAction : function( ) { ...

		// Delete page, pagerange and parameterpage settings in url if existed
		action = birtUtility.deleteURLParameter( action, Constants.PARAM_PAGE );
		action = birtUtility.deleteURLParameter( action, Constants.PARAM_PAGERANGE );
		action = birtUtility.deleteURLParameter( action, Constants.PARAM_PARAMETERPAGE );
		action = birtUtility.deleteURLParameter( action, Constants.PARAM_EMITTER_ID );

		if( format == "xlsx" ) {
			action = action + "&__ExcelEmitter.SingleSheet=true";
		}

...
}

In my test cases I got only Single-Sheet files with change. Be carefull with this change you haven't directly an Option to get Multi-Sheets. The option on report level won't be work like overwrite, the url settings have higher priority.

[SteveSchafer-Innovent]

@speckyspooky thanks very much for this solution. I'm going to try this in 4.8 since that's what my client is using at the moment.

Update: It works. Also the URL parameter works when going directly to xlsx. Due to my dyslexia (which is really lazy reading on my part) I had the parameter name wrong. So this solves the problem for me, and, as far as I'm concerned, it's no longer important to be able to set the default value in viewer.properties. However being able to set render option default values without having to modify the code would still be a valuable feature so I'll leave this issue active.

[speckyspooky]

The enhancement is included in PR #1483