Python操作excel-读取、表格填充颜色区分

1.场景分析

遇到一个需要读取本地excel数据，处理后打入到数据库的场景，使用java比较重，python很好的解决了这类问题

2.重难点

本场景遇到的重难点在于：

1. 需要根据表格内的背景颜色对数据进行筛选

2. 读取非默认Sheet

3. 总是出现Values must be of type <class ‘str’>问题，后来分析改用cell_fill.start_color.type来判断即可

3.问题解决

from openpyxl import load_workbook
filename = "metadata_comment_dir"  # 读取excel
table="tablename"
workbook = load_workbook(filename)
worksheet = workbook.get_sheet_by_name("SheetName")  # 读取Sheet
#总共有多少行
rows = worksheet.max_row
#总共多少行、多少列并分别赋值
# rows, cols = worksheet.max_row, worksheet.max_column
field_comment = {}
flag =0
for i in range(1, rows+1):
    flag=1
    ce1 = worksheet.cell(row=i, column=1)
    field_comment[table]=worksheet.cell(row=i, column=2).value
    # print(ce1.value)
    if flag==0:
        for j in range(i+2, rows):
            ce2 = worksheet.cell(row=j, column=1)
            if ce2.value != None:
                ce3 = worksheet.cell(row=j, column=3)
                cell_fill=ce2.fill
                # print(ce1.fill.start_color)
                # 值为：rgb=None, indexed=None, auto=None, theme=8, tint=0.8, type='theme'
                if "theme" == cell_fill.start_color.type:
                    # 颜色使用背景颜色
                    cell_color = cell_fill.start_color.theme
                elif "auto" == cell_fill.start_color.type:
                    # 颜色使用自动颜色
                    cell_color = 'Automatic'
                elif "indexed" == cell_fill.start_color.type:
                    # 颜色使用索引颜色
                    cell_color = cell_fill.start_color.indexed
                else  :
                    # 颜色使用rgb颜色
                    cell_color = cell_fill.start_color.rgb
                    field_comment[ce2.value]=ce3.value
                print(field_comment)
            else:
                print(field_comment)
                break
    elif flag==1:
        print(field_comment)
        break