Python: Excel files – determining cell background colour using `xlrd`

Posted: , Updated: Category: Computers

I recently needed to process some Excel files in Python. I used the xlrd library.

I needed to take special action if the background colour of a particular cell was solid cyan (RGB (0,255,255)).

Here is a function that determines if a particular cell from a given workbook has a solid cyan background. Note that the cell itself doesn’t contain its own formatting information - it has an “XF style” index, which points into a list of “XF Styles” held by the workbook.xf_list, which in turn points into tables of colours.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

def cell_background_is_cyan ( workbook, cell ):
    # Returns TRUE if the given cell from the given workbook has a solid cyan RGB (0,255,255) background.
    # Note that the workbook must be opened with formatting_info = True, i.e.
    #     xlrd.open_workbook(xls_filename, formatting_info=True)
    assert type (cell) is xlrd.sheet.Cell
    assert type (workbook) is xlrd.book.Book

    xf_index = cell.xf_index
    xf_style = workbook.xf_list[xf_index]
    xf_background = xf_style.background

    fill_pattern = xf_background.fill_pattern
    pattern_colour_index = xf_background.pattern_colour_index
    background_colour_index = xf_background.background_colour_index

    pattern_colour = workbook.colour_map[pattern_colour_index]
    background_colour = workbook.colour_map[background_colour_index]

    # If the cell has a solid cyan background, then:
    #  - fill_pattern will be 0x01
    #  - pattern_colour will be cyan (0,255,255)
    #  - background_colour is not used with fill pattern 0x01. (undefined value)
    #    So despite the name, for a solid fill, the background colour is not actually the background colour.
    # Refer https://www.openoffice.org/sc/excelfileformat.pdf S. 2.5.12 'Patterns for Cell and Chart Background Area'
    if fill_pattern == 0x01 and pattern_colour == (0,255,255):
        return True
    return False