openpyxl获取表格字体颜色输出Values must be of type ＜class ‘str‘＞_values must be of type

python - 从 .xlsx 获取单元格字体颜色

我正在使用 openpyxl 读取 Excel 文件，想从“.xlsx”文件中获取单元格字体颜色，尝试这样获取：

from openpyxl import load_workbook
# 打开Excel文件
workbook = load_workbook('xxxxx.xlsx')

# 获取Sheet1工作表中的A1单元格
worksheet = workbook['Sheet1']
cell = worksheet['A1']

# 获取单元格颜色
cell_color = cell.font.color.rgb

# 打印结果
print("单元格A1字体颜色：", cell_color)
1
2
3
4
5
6
7
8
9
10
11
12
13

输出：
单元格A1字体颜色： Values must be of type <class ‘str’>

我确定单元格里的文字是有颜色的！

问题解决：

后来才知道单元格使用的是内置颜色索引。查询颜色时先获取字体颜色类型，有rgb、indexed、theme等

# Default Color Index as per 18.8.27 of ECMA Part 4
COLOR_INDEX = (
    '00000000', '00FFFFFF', '00FF0000', '0000FF00', '000000FF', #0-4
    '00FFFF00', '00FF00FF', '0000FFFF', '00000000', '00FFFFFF', #5-9
    '00FF0000', '0000FF00', '000000FF', '00FFFF00', '00FF00FF', #10-14
    '0000FFFF', '00800000', '00008000', '00000080', '00808000', #15-19
    '00800080', '00008080', '00C0C0C0', '00808080', '009999FF', #20-24
    '00993366', '00FFFFCC', '00CCFFFF', '00660066', '00FF8080', #25-29
    '000066CC', '00CCCCFF', '00000080', '00FF00FF', '00FFFF00', #30-34
    '0000FFFF', '00800080', '00800000', '00008080', '000000FF', #35-39
    '0000CCFF', '00CCFFFF', '00CCFFCC', '00FFFF99', '0099CCFF', #40-44
    '00FF99CC', '00CC99FF', '00FFCC99', '003366FF', '0033CCCC', #45-49
    '0099CC00', '00FFCC00', '00FF9900', '00FF6600', '00666699', #50-54
    '00969696', '00003366', '00339966', '00003300', '00333300', #55-59
    '00993300', '00993366', '00333399', '00333333',  #60-63
)
# indices 64 and 65 are reserved for the system foreground and background colours respectively
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

参考链接: openpyxl.styles.colors

实践代码：

import openpyxl

COLOR_INDEXED = {
    0: '00000000', 1: '00FFFFFF', 2: '00FF0000', 3: '0000FF00', 4: '000000FF',
    5: '00FFFF00', 6: '00FF00FF', 7: '0000FFFF', 8: '00000000', 9: '00FFFFFF',
    10: '00FF0000', 11: '0000FF00', 12: '000000FF', 13: '00FFFF00', 14: '00FF00FF',
    15: '0000FFFF', 16: '00800000', 17: '00008000', 18: '00000080', 19: '00808000',
    20: '00800080', 21: '00008080', 22: '00C0C0C0', 23: '00808080', 24: '009999FF',
    25: '00993366', 26: '00FFFFCC', 27: '00CCFFFF', 28: '00660066', 29: '00FF8080',
    30: '000066CC', 31: '00CCCCFF', 32: '00000080', 33: '00FF00FF', 34: '00FFFF00',
    35: '0000FFFF', 36: '00800080', 37: '00800000', 38: '00008080', 39: '000000FF',
    40: '0000CCFF', 41: '00CCFFFF', 42: '00CCFFCC', 43: '00FFFF99', 44: '0099CCFF',
    45: '00FF99CC', 46: '00CC99FF', 47: '00FFCC99', 48: '003366FF', 49: '0033CCCC',
    50: '0099CC00', 51: '00FFCC00', 52: '00FF9900', 53: '00FF6600', 54: '00666699',
    55: '00969696', 56: '00003366', 57: '00339966', 58: '00003300', 59: '00333300',
    60: '00993300', 61: '00993366', 62: '00333399', 63: '00333333', 64: 'System Foreground', 65: 'System Background'
}

# 打开Excel文件
workbook = openpyxl.load_workbook('xxxxx.xlsx')

# 获取Sheet1工作表中的A1单元格
worksheet = workbook['Sheet1']
cell = worksheet['A1']

# 获取单元格内容及颜色
cell_value = cell.value
cell_color = None
try:
    types = cell.font.color.type
    if types == 'indexed':
        cell_color = COLOR_INDEXED[cell.font.color.indexed]
    elif types == 'rgb':
        cell_color = cell.font.color.rgb
    else:
        # 还有其他类型不做深究，先按黑色处理
        cell_color = COLOR_INDEXED[0]
except AttributeError:
    cell_color = COLOR_INDEXED[0]
print("单元格A1内容：", cell_value)
print("单元格A1字体颜色：", cell_color)
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
28
29
30
31
32
33
34
35
36
37
38
39
40
41

写在最后：

  评论美三代，点赞富一生~