Python Excel工具类封装, 给excel表头搞点颜色.md

2022/6/13 Python小技巧

# 封装Excel工具类

我们常用的excel工具类,读有xlrd,写有xlwt。有读有写,新一代库有pandas,openpyxl等等。

大家用法都差不多,今天博主就介绍新手最爱,我也爱的xlrdxlwt。(不过xlwt似乎最多只支持65535条数据,此乃一坑)

# 缘起

老板给博主安排了一个导出excel的任务,而这个新项目里面还尚未编写此类公共方法,于是就想来一波尝试。

主要是想把xlwt和xlrd做一个整合,但并不是说要对一个文件读和写,因为应用场景一般是导入或者导出

# 封装ExcelHelper类

# 读数据

我们需要先接受一个filename(文件的全路径),初始化helper对象,所以我们可以顺手写出这样的代码:

class ExcelHelper(object):
    def __init__(self, filename):
        self.filename = filename
1
2
3

接着我们编写xlrd的部分,虽然网上的例子一大把,但是有特色的还是少。我决定支持读数组,也支持读json数组。

啥子是json数组,因为我们excel一般都是表头+表数据组成,如果光给一条数据,你不知道他属于哪一列,那还得往上找它的表头,和之对应起来。

比如我们经常读出的数据是:

data = [
   ['姓名', '电话'],
   ['三毛', '17800000000']
]
1
2
3
4

当列的数据比较多的时候,我们给你一个三毛,你知道它的表头是姓名吗?还是小名?

如果是这样的数据呢?

data = [
   {"姓名": "三毛", "电话": "17800000000"}
]
1
2
3

两种数据,不同的展示方式,其实内容相差无几。

  • 编写read_data方法
    def read_data(self):
        """
        获取数据,不区分表头
        :return:
        """
        book = xlrd.open_workbook(self.filename)
        sheets = book.sheets()
        for s in sheets:
            yield (s.row_values(i) for i in range(s.nrows))
1
2
3
4
5
6
7
8
9

这边用了yield关键字,之所以不用list,还是考虑到数据表数据如果很多,那么全部放到list,会占用很大内存空间,所以用了yield节省内存空间

这里如果有疑问,大家可以查下生成器相关资料。

  • 编写read_json相关方法
    @staticmethod
    def read_json_item(sheet):
        """
        获取json数据
        :param sheet:
        :return:
        """
        if sheet.nrows <= 1:
            return (sheet.row_values(i) for i in range(sheet.nrows))
        # 否则说明数据大于1行
        header = sheet.row_values(0)
        for i in range(1, sheet.nrows):
            row_data = dict()
            for j in range(sheet.ncols):
                row_data[header[j]] = sheet.cell_value(i, j)
            yield row_data

    def read_json_data(self):
        """
        获取List[dict]数据,也就是JSON数组
        :return:
        """
        book = xlrd.open_workbook(self.filename)
        sheets = book.sheets()
        for st in sheets:
            yield ExcelHelper.read_json_item(st)
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

比较常规,excel可能会有多个sheet,所以我们遍历sheets。接着去每个sheet中拿到每行数据,由于要求json数组模式,所以我们需要判断下行数。

如果就1行,那就最多一个表头,没啥意义,所以我们直接切换到原生模式,一行一行读数据。

# 写数据

写数据的demo比较简单,考虑到传入json数组的时候,万一有小可爱传这样的数据,其实我们是不太好支持的:

a = [
 {"名字": "张三", "称号": "法外狂徒"},
 {"性格": "闷骚", "称号": "秒杀光环"}
]
1
2
3
4

可以看到2条数据对不上~所以不打算支持这样的数据。

  • 编写write_data方法

    我们知道,表头是个很重要的数据,我们要让她与众不同一点,所以我们可以设置下它的样式。

    @staticmethod
    def get_style():
        style = xlwt.XFStyle()
        pattern = xlwt.Pattern()
        pattern.pattern = xlwt.Pattern.SOLID_PATTERN
        pattern.pattern_fore_colour = xlwt.Style.colour_map['sky_blue']
        style.pattern = pattern
        return style
1
2
3
4
5
6
7
8

创建style,设置背景色为纯洁的天蓝色,最后返回style。

这个style有什么用呢,我们在写入数据的时候可以指定单元格的样式。

接着编写write方法:

    def write_excel_data(self, header, row_data, sheet_name="sheet1"):
        wb = xlwt.Workbook()
        ws = wb.add_sheet(sheet_name)
        # 写入表头
        for i, h in enumerate(header):
            ws.write(0, i, h, self.style)
        # 写入数据
        for line, row in enumerate(row_data):
            for c, item in enumerate(row):
                ws.write(line + 1, c, str(item))
        wb.save(self.filename)
1
2
3
4
5
6
7
8
9
10
11

接受参数是表头(数组),row_data(二维数组),写入完毕后调用save方法。

如果有需要对多个sheet写入,请自行改造。

本节总体来说,只是写了一个excel的读写方法,亮点在于读json数组表头搞颜色

颜色你们可以随便定哈

# 下次聊聊FastApi下载文件以及删除下载后的文件。

完整代码如下(其实是给我自己备份):

import xlrd
import xlwt


class ExcelHelper(object):
    def __init__(self, filename):
        self.filename = filename
        self.style = ExcelHelper.get_style()

    @staticmethod
    def get_style():
        style = xlwt.XFStyle()
        pattern = xlwt.Pattern()
        pattern.pattern = xlwt.Pattern.SOLID_PATTERN
        pattern.pattern_fore_colour = xlwt.Style.colour_map['sky_blue']
        style.pattern = pattern
        return style

    def read_data(self):
        """
        获取数据,不区分表头
        :return:
        """
        book = xlrd.open_workbook(self.filename)
        sheets = book.sheets()
        for s in sheets:
            yield (s.row_values(i) for i in range(s.nrows))

    @staticmethod
    def read_json_item(sheet):
        """
        获取json数据
        :param sheet:
        :return:
        """
        if sheet.nrows <= 1:
            return (sheet.row_values(i) for i in range(sheet.nrows))
        # 否则说明数据大于1行
        header = sheet.row_values(0)
        for i in range(1, sheet.nrows):
            row_data = dict()
            for j in range(sheet.ncols):
                row_data[header[j]] = sheet.cell_value(i, j)
            yield row_data

    def read_json_data(self):
        """
        获取List[dict]数据,也就是JSON数组
        :return:
        """
        book = xlrd.open_workbook(self.filename)
        sheets = book.sheets()
        for st in sheets:
            yield ExcelHelper.read_json_item(st)

    def write_excel_data(self, header, row_data, sheet_name="sheet1"):
        wb = xlwt.Workbook()
        ws = wb.add_sheet(sheet_name)
        # 写入表头
        for i, h in enumerate(header):
            ws.write(0, i, h, self.style)
        # 写入数据
        for line, row in enumerate(row_data):
            for c, item in enumerate(row):
                ws.write(line + 1, c, str(item))
        wb.save(self.filename)

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
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67