Accurately identify fill color

[bkucz...@ucsb.edu]

Hi folks, 

Thanks for creating this package. It's very helpful. But I am new to python, and I'm having a hard time understanding the details of how styles in existing workbooks are reported.

I'm working with a spreadsheet where the author has encoded important information using the cell's fill color. I would like to accurately determine the both the text color and the fill color (in RGB, ideally) and also determine whether either one has been modified from the default. 

I assumed default would be "white", but when I execute the following on an empty cell:

>>> M = openpyxl.load_workbook(/path/to/file...)

>>> type(M)

openpyxl.workbook.workbook.Workbook

>>> c=M.active['A10']

>>> getattr(c.fill.fgColor, c.fill.fgColor.type)

'00000000'

it looks like black.  The cell in question is empty and unmodified (and looks white).

So, three questions:

1- am I doing the right thing to access the cell's fill color? how is fgColor different from bgColor? Is it correct to dereference the *Color.type to get the *Color value?

2- how do I interpret *color.type 'theme'? I see a COLOR_INDEX for color.type 'index' but nothing for theme.

3- What should I expect to see in fill for empty cells, so that I can accurately detect them?

Thanks in advance,

Brandon

[Charlie Clark]

Am .12.2015, 22:10 Uhr, schrieb <bkucz...@ucsb.edu>:

> Hi folks,

Hiya Brandon,

> Thanks for creating this package. It's very helpful.

Glad to hear it!

> But I am new to
> python, and I'm having a hard time understanding the details of how
> styles in existing workbooks are reported.

Join the club! ;-)

> I'm working with a spreadsheet where the author has encoded important
> information using the cell's fill color. I would like to accurately
> determine the both the text color and the fill color (in RGB, ideally)
> and also determine whether either one has been modified from the default.
>
> I assumed default would be "white", but when I execute the following on
> an
> empty cell:
>>>> M = openpyxl.load_workbook(/path/to/file...)
>>>> type(M)
> openpyxl.workbook.workbook.Workbook
>
>>>> c=M.active['A10']
>>>> getattr(c.fill.fgColor, c.fill.fgColor.type)
> '00000000'
>
> it looks like black. The cell in question is empty and unmodified (and
> looks white).

Looks like you're getting the default fill. For pattern fills the type is
important and if this is not set the fill will not be applied.

> So, three questions:
> 1- am I doing the right thing to access the cell's fill color? how is
> fgColor different from bgColor? Is it correct to dereference the
> *Color.type to get the *Color value?

Foreground colour vs. background colour. This makes little sense for a
solid fill but think of a cross-hatched filling.

> 2- how do I interpret *color.type 'theme'? I see a COLOR_INDEX for
> color.type 'index' but nothing for theme.

You'll have to look at the specification:

"""
20.1.6.2 clrScheme (Color Scheme)
This element defines a set of colors which are referred to as a color
scheme. The color scheme is responsible for defining a list of twelve
colors. The twelve colors consist of six accent colors, two dark colors,
two light colors and a color for each of a hyperlink and followed
hyperlink.
The Color Scheme Color elements appear in a sequence. The following
listing shows the index value and corresponding Color Name.

"""

> 3- What should I expect to see in fill for empty cells, so that I can
> accurately detect them?

Not sure what you mean by this. I'd check for empty cells by content but I
suspect you want to check whether the fill.type is not None.

Charlie
--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Kronenstr. 27a
Düsseldorf
D- 40217
Tel: +49-211-600-3657
Mobile: +49-178-782-6226

[bkucz...@ucsb.edu]

Thanks for your reply...

On Thursday, December 3, 2015 at 12:18:12 AM UTC-8, Charlie Clark wrote:

> 2- how do I interpret *color.type 'theme'? I see a COLOR_INDEX for
> color.type 'index' but nothing for theme.

You'll have to look at the specification:

"""
20.1.6.2 clrScheme (Color Scheme)
This element defines a set of colors which are referred to as a color  
scheme. The color scheme is responsible for defining a list of twelve  
colors. The twelve colors consist of six accent colors, two dark colors,  
two light colors and a color for each of a hyperlink and followed  
hyperlink.
The Color Scheme Color elements appear in a sequence. The following  
listing shows the index value and corresponding Color Name.
"""

Hold it right there-- where did you find that?? I have a feeling a spec would help me very much, but I haven't found anything like one on readthedocs.

The other points you made don't make sense to me.... I don't care what fill will be "applied"-- In my case I want to know what (if any) fill has already been applied by the spreadsheet author.

 

> 3- What should I expect to see in fill for empty cells, so that I can
> accurately detect them?

Not sure what you mean by this. I'd check for empty cells by content but I  
suspect you want to check whether the fill.type is not None.

C.fill doesn't have an attribute 'type'... the C.fill.color.type is not None for any cell I've seen. In the cell in my example, the color type is 'rgb' and the rgb fgColor is '00000000', but again, the cell is actually colored white, not black.

Thanks,
Brandon

[Charlie Clark]

> Hold it right there-- where did you find that?? I have a feeling a spec
> would help me very much, but I haven't found anything like one on
> readthedocs.

http://www.ecma-international.org/publications/standards/Ecma-376.htm -
Part 1

> The other points you made don't make sense to me.... I don't care what
> fill
> will be "applied"-- In my case I want to know what (if any) fill has
> already been applied by the spreadsheet author.

Well, strictly you can't: All cells have the default style which has a
patternFill which no pattern type.

>> > 3- What should I expect to see in fill for empty cells, so that I can
>> > accurately detect them?
>>
>> Not sure what you mean by this. I'd check for empty cells by content
>> but I
>> suspect you want to check whether the fill.type is not None.
>>
>
> C.fill doesn't have an attribute 'type'... the C.fill.color.type is not
> None for any cell I've seen. In the cell in my example, the color type is
> 'rgb' and the rgb fgColor is '00000000', but again, the cell is actually
> colored white, not black.

C.patternType is the correct attribute. Sorry, I don't keep all the
attribute names in my head.

[bkucz...@ucsb.edu]

Got it, got it. Thanks- that should help.