Scripting Issue: Dynamic Table Exporting Numbers as Strings in Excel file type

[raviprsd18]

Hello BIRTians,

I’m seeking some guidance on a challenge I’m facing with BIRT.

I’m working on a report that requires a dynamic table, where the number of columns changes based on user configurations. The table can have a minimum of one column and potentially many more. While I’m aware that this can be achieved using a BIRT CrossTab, I’m unable to use that approach due to certain constraints (which I prefer not to discuss here).

To work around this, I’ve implemented a solution using JavaScript in the beforeFactory event, and I’ve managed to get most of it working. However, I’ve encountered an issue when exporting the report to Excel (.xlsx format): Numeric values are being treated as strings, and Excel prompts users to convert them manually (please refer to the attached screenshot).

I understand this is happening because I’m using labels to display the data, which causes Excel to interpret all values as text. My question is: Is there a way to ensure that numeric values are recognized as numbers in Excel via BIRT scripting ?

Any suggestions or workarounds would be greatly appreciated. I’ve attached the .rptdesign file, the exported Excel file, and relevant screenshots for reference. Please let me know if any additional information is needed.

Tagging @speckyspooky and @hvbtup for their insights, your input would be incredibly helpful!

Thanks in advance for your support!

Numbers treated as string issue snapshot

Rptdesign, excel files
DynamicTablePOC.zip

[speckyspooky]

Hello @raviprsd18,
I looked into your report and the special topic is here your element "Label" because thus type is by default a string type and cannot be converted to a numeric value on xlsx side. The excel-emitter must know if it is a string-value or a numeric-value.

For exampe the dynamic text can switch based on the content type between numeric- (content type: plain) and string-value (content type: html) for excel.

Here is my solution how you can handle your problem:

	var elemValue	= arrayData[rowIndex][colIndex];
	var contentType = "plain";
				
	if (typeof elemValue === "undefined" || typeof elemValue === "string") {
	//	=> BIRT label
		if (typeof elemValue === "undefined") {
			elemValue	= "undefined";
		}
		contentType = "html";
		label = elementFactory.newLabel("lbl_" + arrayData[0][colIndex] + "_" + rowIndex + "_" + colIndex);//table label
		label.setText(elemValue);
	} else {
	//	=> BIRT text item, numeric value (dynamic text)
		label = elementFactory.newTextData("lbl_" + arrayData[0][colIndex] + "_" + rowIndex + "_" + colIndex);
		label.setValueExpr(elemValue);
		label.setContentType(contentType);
	}
	cellData.getContent().add(label); // Add label to cell content

Here is the result screen based on your report with my change:

Report with the change: DynamicTablePOC_Changed_TG.zip

[raviprsd18]

Thank you so much, @speckyspooky ! I truly appreciate your help, your solution worked perfectly and everything is functioning as expected now. Really grateful for your support!

[raviprsd18]

@speckyspooky I encountered one more issue here. Can you help me in setting the number format to "##,##0.00"

I tried this but not working. And getting the error Cannot find function setUserProperty in object org.eclipse.birt.report.model.api.TextDataHandle@a893f39

Create the number format structure
var numberFormat = new NumberFormatValue();
numberFormat.category = "Custom";
numberFormat.pattern = "##,##0.00";

label.setUserProperty("numberFormat", numberFormat, "number-format");

[raviprsd18]

@speckyspooky gentle remainder on the number format.

[speckyspooky]

The problem here is that the element doesn't support the property numberFormat.
This property is only available for data-elements but not for text or label items. You can the it that the designer will display the number formatter option only on specific data elements.
But data elements doesn't support the "plain" option for numbering and the effect will be that the formatting will work because the content is a text.
The number formatted text of "numberFormat" is not the same like the formatting formular of the excel output.

Why you doesn't use standard datasource & dataset handling in combination with a dynamic table?
Because then the handling would be much easier and you can use special excel-option which are documented here:
ExcelEmitter special options

And for excel there exists the special formatter option ExcelEmitter.CustomNumberFormat. May be this would work for you.

Example of the user property:

[raviprsd18]

@speckyspooky thanks for the help. I tried using dataset and datascript via scripting but I faced lot of issues and it didn't succeeded. If possible can you share me the sample code.
Just recap on my requirement I won't be know the exact table columns, so that is the reason I went with dynamic table creation in code and then binding to the grid control. Basically I can't design the dataset with static columns everything should be dynamic.