XLSX、XLS Emitter not correctly consider the column visibility property

[shengjhen]

BIRT Version: 4.19.0

Description: When exporting a BIRT report to XLSX or XLS formats, columns set to "hide" in a crosstab table result in blank rows appearing in the exported file. While the hidden columns are not visible, the export creates blank rows where those columns would have been. This issue does not occur in previous versions of BIRT, such as 4.6.0, where the visibility property worked correctly without leaving blank rows.

Steps to Reproduce:

Create a crosstab table in a BIRT report.

Set the column visibility property for each column in the first row of the crosstab table to "hide."

Export the report to the XLSX or XLS format.

Observe that the hidden columns are not visible, but blank rows appear in the exported file in place of the hidden columns.

Expected Behavior: The visibility property for the columns in the crosstab table should be respected across all export formats, including XLSX and XLS. Specifically, columns set to "hide" should not only be invisible but also not leave any blank rows in the exported file.

Actual Behavior: The column visibility property is partially respected in the XLSX/XLS exports — the hidden columns are not visible. However, blank rows are inserted in the place of the hidden columns, which was not the case in BIRT 4.6.0.

Version Comparison:

BIRT 4.6.0: The column visibility property works as expected — hidden columns are not visible, and no blank rows are inserted.

BIRT 4.19.0: The hidden columns are not visible, but blank rows are left in the exported XLSX/XLS file.

Workaround: Currently, no workaround exists to fully address this issue in BIRT 4.19.0 for XLSX/XLS exports. Other formats like PDF, HTML, and CSV are unaffected.

Additional Information:

This issue is specific to BIRT 4.19.0 and the XLSX/XLS emitters.

The issue does not occur in BIRT 4.6.0, where the visibility property works as expected.

The problem seems to be a regression in BIRT 4.19.0, affecting only XLSX/XLS exports.

Environment:

BIRT Version: 4.19.0

Export Formats Affected: XLSX, XLS

Report Type: Crosstab Table

[speckyspooky]

Could you please Provider an example because cross table isn't the most used Feature on my side.

And some images would be great.

[shengjhen]

For some reason, I couldn't provide the example directly, but there is a showcase below.

• Crosstab

• First row have two column one is column title one is column data which need hidden (column for sorting purpose)

• HTML Output

• DOCX Output

• XLSX Output

• XLS Output

[speckyspooky]

Could you please create a testexample based on the "classic car model"-db which is included directly on BIRT.
I understand that you cannot provide a productiv example. With the demo-db we haven't this problem and I would be able to have the same behavior on my side.
And so we would have the identical test case for all.

Have you tried the latest nighly build version of 4.20 because there are added additional changes for excel?

[shengjhen]

The link with rptdesign file show what it happend.
Report File

[speckyspooky]

I started a research based on the posted test report with different BIRT-versions,
here are the results:

• BIRT 4.6: error, xlsx created, but file combatibility conflict message with start of MS excel
• BIRT 4.8: error, xlsx created, but file combatibility conflict message with start of MS excel (according BIRT 4.6)
• BIRT 4.9: error, xlsx cannot be crated, java exception occur due to a merge conflict
• BIRT 4.10: error, xlsx cannot be crated, java exception occur due to a merge conflict (according BIRT 4.9)
• BIRT 4.12: error, xlsx cannot be crated, java exception occur due to a merge conflict (according BIRT 4.9)
• BIRT 4.13: error, xlsx cannot be crated, java exception occur due to a merge conflict (according BIRT 4.9)
• BIRT 4.14: ok, xlsx created, field values hidden empty row displayed
• BIRT 4.15: xlsx created, field values hidden empty row displayed (according BIRT 4.14)
• BIRT 4.16: error, report cannot be displayed and created
• BIRT 4.17: ok, xlsx created, field values hidden empty row displayed (according BIRT 4.14)
• BIRT 4.18: ok, xlsx created, field values hidden empty row displayed (according BIRT 4.14)
• BIRT 4.19: ok, xlsx created, field values hidden empty row displayed (according BIRT 4.14)
• BIRT 4.20 (nightly 2025-04-06): ok, xlsx created, field values hidden empty row displayed (according BIRT 4.14)

The report cannot created like old excel format "xls" due to to much columns (more like 255).
Only the report results of BIRT 4.6 & 4.8 after the fixing through MS excel himself the excel xlsx can be started and avoid the leading row.
But over all the report was not perfectly working with excel-emitter (based on Spudsoft).