Is there a way to read a template excel file in python including background color formatting?

interkin3tic 0
Viewed 42

I frequently need to generate similar looking excel sheets for humans to read. Background colors and formatting should be similar. I'm looking to be able to read a template into python and have the values and cells filled in in Python.

It does not appear that xlsxwriter can read background color and formatting. It can output formatting, but it's taking a long time to code one template in manually.

openpyxl does not appear to have that function either.

I'm looking for a solution that would be able to read through a worksheet and say "A1 has a background color of red (or hex value), is bold, and has 'this is a template' in it." Does such a module exist?

xlsxwriter formatting openpyxl excel python
2 Answers

There were no openstack answers I could find about reading existing background color formatting. The answers I did find were about formatting of the cell into things like percentage or currency.

Here is a solution I've found for background cell color from the openpyxl documentation, though fill color was not explicit in what I read.

from openpyxl import load_workbook
from openpyxl.styles import Fill

wb = load_workbook("test.xlsx") # your specific template workbook name
ws = wb["Sheet1"] # your specific sheet name

style_dictionary = {}

for row in ws.rows:
    for cell in row:
        style_dictionary[cell] = cell.fill

style_dictionary

The background color will be under parameters rgb = ####.

I'm hopeful this dictionary can be used to template the background and pattern fill for other worksheets but I haven't gotten that far yet.

interkin3tic0

Fill color is fgColor per the OOXML specs "For solid cell fills (no pattern), fgColor is used".
You can get the color from about three attributes, all should provide the same hex value unless the fill is grey in which case the index/value is 0 and the grey content is determined by tint

for cell in ws['A']:
    print(cell)
    if cell.fill.fgColor.index != 0:
        print(cell.fill.fgColor.index)
        print(cell.fill.fgColor.rgb)
        print(cell.fill.fgColor.value)
    else:
        print(cell.fill.fgColor.tint)

    print(cell.fill.patternType)

    print("-------------")
moken0
Related