XLS Data export generates cells with invalid colors

ThomasGoulet73

I recently upgraded from Report Builder 12 to Report Builder 22.03 and I noticed that the colors were invalid when exporting to XLS Data file. The colors displayed are fine but it seems that there are weird colors embedded in the cells that are not the displayed color and it affects copying a cell from the exported file to a separate Excel file (It pastes using the wrong color).

To reproduce:
1. Create a new report and add a label with the color clBlack.
2. Export this report using XLS Data.
3. Open the exported file using Excel, select the cell for the label (Which should be black) and open the color picker. It will show the color red instead of black.
4. Another way to reproduce this is to copy the black cell into a separate Excel file. It will copy the cell with the color red instead of black.

Let me know if you need more information.

Thanks

nardmoseley

Hi Thomas,

I researched this.

Exporting to XlsxData - the test works.

Exporting to XlsData. Select the cell, right mouse and select Format Cells... to display the dialog. Then select the Fill page. The color is black. Copy/Paste the cell within the spreadsheet, the color is black. But you are correct in that the Font Fill color picker on the toolbar does not reflect this.

XlsxData and XlsData are both using Format Styles to specify the cell attributes. Perhaps a limitation of how Excel processes the legacy XlsData format (or a bug in Excel.)

I recommend using the XlsxData export.

ThomasGoulet73

Hi Nard,

I forgot to mention this but I am using Delphi 2007 (Without any plan to upgrade in the near future) so I cannot use Xlsx.

This all used to work with Report Builder 12 so I don't see how it could be a bug with Excel. If I do the same steps that I described above in Report Builder 12 I cannot reproduce the bug so to me this is a regression from Report Builder 12 -> Report Builder 22.03.

Thanks

nardmoseley

Hi Thomas,

I tested with D2007 and RB 12.

1. The Xls file when opened, results in Excel defaulting to read-only mode and warning about the file's validity. I recall that MS introduced a XLS (BIF) validator to increase security. For RB 14 we modified the RB Xls export to produce XLS files that pass the validator.

2. Similar to RB 22.03, the copy/paste honors the fill color within the spreadsheet, but when pasted to a new spreadsheet the fill color is mapped to the wrong colors, just in a slightly different way.

ThomasGoulet73

Hi Nard,

When I export to Xls using D2007 + RB 22.03 and I copy the value from one spreadsheet to a new one, the color is not ignored when pasted. It pastes using a random color (I noticed that it depends on the color of the source text but it will always be only Red, Green, Blue in RGB without mixing. Like 255 0 0, 0 255 0 or 0 0 255).

I tried to clarify the steps to reproduce the copy/paste color change:
1. Create a new report and add a label with the color clBlack.
2. Export this report using XLS Data.
3. Open the exported file using Excel.
4. Open a new Excel instance and start a new spreadsheet.
5. Copy a cell from the exported spreadsheet to the new spreadsheet. The pasted cell be red instead of black.

Note: Copying from a spreadsheet exported from RB to a separate spreadsheet exported from RB will not reproduce the bug.

Thanks

ThomasGoulet73

Hi Nard,

Do you have any news ?

Thanks

nardmoseley

Hi Thomas,

RB 22.04 contains mods to use Excel's 8 built-in color values for the basic 8 color palette Excel defines. This solution provides a work around for the Excel limitation with the old XLS format.

ThomasGoulet73

Hi Nard,

I just tested RB 22.04 and it improved the situation but it is not fully fixed.

RB 22.04 fixed cases where the color in the report is either 0 0 0, 255 0 0, 0 255 0 or 0 0 255. Any other colors will get weird color when pasted using the same steps that I included in my message earlier.

In my tests I tried using the color clTeal (Which is RGB 0 128 128) and when pasted it was converted to RGB 0 128 0.

Thanks

ThomasGoulet73

Hi Nard,

I also noticed that RB 22.04 broke colors that previously worked.

In RB 22.03, clWindowText gets pasted as RGB 0 0 0 (Correct)
While in RB 22.04, clWindowText gets pasted as RGB 0 255 255 (Incorrect)

Thanks