Biegral Blog

最近碰上一个需求，需要给物流行业的Excel匹配派送费，具体完整需求较为复杂。其中涉及的一个麻烦点可以拿出来给大家讲讲。

这些物流订单表中，通过连续相同的颜色标识属于合票派送，并不像数据库有单独的字段标记。今天我们的实现目标是读取连续相同的颜色，标记同一个分组编号。

通过openpyxl读取xlsx格式的颜色比较简单不作演示了，读者也可以考虑先将xls格式先转换为xlsx格式再读取颜色。不过我今天演示的是使用xlrd库直接读取xls格式的Excel表，从而解析出颜色，并分析是否是连续相同的颜色，给一个唯一的分组编号。

举个例子，对于如下Excel表：

这4个颜色连续相同的的单元格，标记为同一个编号。

首先我们读取这个Excel表：

import xlrd

# 打开Excel，为了读取样式信息需要指定formatting_info=True
book = xlrd.open_workbook("test.xls", formatting_info=True)
# 获取第一张sheet
sheet = book.sheets()[0]
sheet

<xlrd.sheet.Sheet at 0x1bb232524c8>

首先我们先尝试读取第一个有颜色的单元格：

cell = sheet.cell(2, 1)
print(cell.value, cell.xf_index)

KSTD152 77

已经成功读取到具体的值和对应的样式索引。

**那么如果根据索引获取颜色值呢？**这时需要使用全局颜色定义表：

colour_index = book.xf_list[cell.xf_index].background.pattern_colour_index
book.colour_map[colour_index]

(255, 102, 0)

将以上过程封装起来，再测试一下读取另一个合票的颜色：

def get_cell_color(cell):
    colour_index = book.xf_list[cell.xf_index].background.pattern_colour_index
    return book.colour_map[colour_index]

get_cell_color(sheet.cell(8, 1))

(204, 204, 255)

那说明我们已经可以提取出指定单元格的颜色值了。

**如何批量读取数据？**使用get_rows生成器最简单：

import pandas as pd

rows = sheet.get_rows()
header = [cell.value for cell in next(rows)]
data = []
for row in rows:
    data.append([cell.value for cell in row])
df = pd.DataFrame(data, columns=header)
df.head(20)

基于以上代码，下面我们批量读取整个Excel的数据，并根据颜色值赋予一个合票编号：

import pandas as pd

rows = sheet.get_rows()
header = [cell.value for cell in next(rows)]
header.append("合票编号")
data = []
last_color = None
num = 0
for row in rows:
    t = [cell.value for cell in row]
    color = get_cell_color(row[1])
    if color and color != (255, 255, 255):
        if color != last_color:
            num += 1
        t.append(num)
    else:
        t.append(pd.NA)
    last_color = color
    data.append(t)
df = pd.DataFrame(data, columns=header)
df.head(20)

可以看到已经正确的给连续相同的颜色打上了相同的编号：

这样我们就解决了这个问题。完整代码如下：

import pandas as pd
import xlrd


def get_cell_color(cell):
    colour_index = book.xf_list[cell.xf_index].background.pattern_colour_index
    return book.colour_map[colour_index]


# 打开Excel，为了读取样式信息需要指定formatting_info=True
book = xlrd.open_workbook("test.xls", formatting_info=True)
# 获取第一张sheet
sheet = book.sheets()[0]

rows = sheet.get_rows()
header = [cell.value for cell in next(rows)]
header.append("合票编号")
data = []
last_color = None
num = 0
for row in rows:
    t = [cell.value for cell in row]
    color = get_cell_color(row[1])
    if color and color != (255, 255, 255):
        if color != last_color:
            num += 1
        t.append(num)
    else:
        t.append(pd.NA)
    last_color = color
    data.append(t)
df = pd.DataFrame(data, columns=header)
df