Hello,
I have a problem concerning adding formats to an excel file. I have a report with conditional formatting via the "format cell" command. So i defined various fomats on the config sheet. The background color of the formats are stored inside a database. Because these colors can change, I wrote an application that synchronizes the background colors in the database with the colors of the formats inside the excel file. For this task, I am using the FlexCel library like this, where rgb (and red, green, blue respectively) are the colors from the database:
format := TFlxFormat.Create;
format.FillPattern.BgColor := TExcelColor.Automatic;
format.FillPattern.Pattern := TFlxPatternStyle.Solid;
format.FillPattern.FgColor := TExcelColor.FromArgb(red, green, blue, 0);
if colorIsLight(rgb) then
begin
rgbBytes := RGBToBytes(TColors.Black) ;
format.Font.Color := TExcelColor.FromArgb(rgbBytes[0], rgbBytes[1], rgbBytes[2], 0);
end
else
begin
rgbBytes := RGBToBytes(TColors.White) ;
format.Font.Color := TExcelColor.FromArgb(rgbBytes[0], rgbBytes[1], rgbBytes[2], 0) ;
end;
xf := excelFile.AddFormat(format);
excelFile.SetCellFormat(row, col + 1, xf);
Most of the time i simply override the exisiting formats without any changes because the colors don't change very often. The problem is that the excel file is constantly growing. With the help of the debugger I found out that a new format is created and added every time, even if the background color (and so the foreground color) has not changed. According to the documentation i would expect that FlexCel recognises that this format already exists and that it dont need to create and add a new one every time.
This application runs 2 times a day and synchronizes approximately 300 colors (300 formats respectively). So the excel file grows 100 Kilobyte at every run.
Is this a problem in the FlexCel library or am I doing something wrong? I am using version 6.8
Kind Regards,
Christian Sack
Hi,
Can you recheck you are using 6.8? There was a bug in 6.7.8 which could cause this:
from http://www.tmssoftware.biz/flexcel/docs/vcl/whatsnew.htm: (6.7.8 section)
It seems very similar to the issue you are having, so before investigating further it makes sense to make sure it isn't already fixed.
You can check the version actually being used with:
showmessage(FlexCelVersion);
If you are in the latest, can you send me some file showing the problem to adrian@tmssoftware.com , as indeed, the formats shouldn't grow forever except if the colors are actually always a little different (say rgb(100,100, 100) is different from rgb(100, 100, 101) even if they look the same).
Side note: if you are doing this approach, it might make sense not to override the file each time, no matter if this bug is fixed. Just have a "template_master.xlsx" which you never modify, and have the sync app to always open template_master.xlsx and save "template.xlsx", then for the rest of the process use template.xlsx.