Cell Color

How do you get the color of a cell?


Thanks

Dave Craggs

Hi,

First of all let me explain that this can be a little more complex than what you might expect. I will use this post to try to explain the difficulties, so you know what to ignore and what to use. 

First it depends on the color you want to get. A cell has 2 colors: A background color and a font color. The background color also can be a solid color, a pattern (like red lines over blue background) or a gradient. 
Then the color can be a theme color, an indexed color or a rgb color (see http://www.tmssoftware.biz/flexcel/doc/vcl/guides/api-developer-guide.html#colors )

And finally, the color of the cell can be affected by a conditional format or by being inside a table. Say a cell might have a red background, but it has a conditional format rule that says that if the value is less than 3 the background should be blue. So if the cell has a 1, the cell will be red, but Excel will show it as blue.

The code below should get you a TColor with the color of cell A1, considering the conditional formattings or tables applied, if the cell has a solid color:



program Project63;


{$APPTYPE CONSOLE}


{$R *.res}


uses
  System.SysUtils, VCL.FlexCel.Core, FlexCel.XlsAdapter, UITypes;


var
  xls: TXlsFile;
  fmt: TFlxFormat;
  CellColor: TExcelColor;
  UIColor: TUIColor;
  DelphiColor: TColor;
  ExtraInfo: IDrawingConditionalFormat;
begin
  xls := TXlsFile.Create('..\..\test.xlsx');
  try
    fmt := xls.GetCellVisibleFormatDef(1, 1);
    fmt := xls.ConditionallyModifyFormat(fmt, 1, 1, true, extraInfo);
    if fmt.FillPattern.Pattern = TFlxPatternStyle.Solid then
    begin
      CellColor := fmt.FillPattern.FgColor; //When style is solid, only FgColor is used and BgColor is ignored.
      UIColor := CellColor.ToColor(xls);
      DelphiColor := UIColor;
    end;


  finally
    xls.Free;
  end;
end.




Some things to notice:
1. This returns the color of the cell background. If you wanted the font color, you could use fmt.Font.Color instead.

2. As said, while most cell colors are solid, you might have a pattern or a gradient in the cell. The code above only reports solid colors (if fmt.FillPattern.Pattern = TFlxPatternStyle.Solid ) but you might report the others too, it is just that you need a more complex color record. (A simple TColor can't describe a gradient or a pattern). 

3. When the color is solid, the color used is fmt.FillPattern.FgColor Not fmt.FillPattern.BgColor. This sometimes is confusing, because you are checking the Background color of the cell, but here we don't refer to the background of the cell, but the background of the fill pattern. The "foreground" of the cell is the font color. In a pattern where you have red lines over a blue background, FgColor would be red and BgColor blue. But in a solid pattern where you have a single color, only FgColor is used. you can think of it as if the "solid" pattern was like the "red lines over blue background" pattern, where the red lines got os big that completely hide the blue background. So the color you set in BgColor doesn't matter, as you can only see the FgColor.

4. TExcelColor has the "Excel definition of a color". That is, if it has a theme color, indexed color, etc. TUIColor has a platform-independent definition of a RGB color. So it has the red, green and blue components of the color, no matter if the TExcelColor wasn't a RGB color and it was say a theme color.
Finally, if you need a TColor, it converts automatically from TUIColor. But you might use the R, G and B properties of TUIColor directly.

5. The line that calls xls.ConditionallyModifyFormat is the one that takes in account how a conditional format might change the cell. If you want the actual color stored in the cell and not the final color after applying the conditional format, you can comment that line.

Wow - thanks for that.


Will have a go, but given the complexity, we may look at an alternative approach.

Dave