调用Python的openpyxl包对Excel表格进行美化

Excel , 评论

Python中运用openpyxl包对Excel表格进行美化，包括字体样式调整、单元格对齐方式调整、单元格边框调整、单元格背景颜色调整、行高和列宽调整。




使用的Python中openpyxl包的版本为3.0.5

先看实际美化前后的效果对比

image-20211027111105037

详细的开发代码如下，代码当中对关键信息进行了说明。

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
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
# =====================================================================
# @Author : Shuxian.Qu
# @Email : qushuxian@gmail.com
# @File : beautiful_excel.py
# @CreateTime : 2021-10-26 10:13
# @Software : PyCharm
# @Comment : 美化Excel
# =====================================================================


from openpyxl import load_workbook, utils
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment


class BeautifulExcel:
    def __init__(self, path, sheet_name):
        self.path = path
        self.sheet_name = sheet_name
        self.excel_df = load_workbook(path)

    def insert_row(self):
        """
        在Excel中插入指定行
        """
        self.excel_df[self.sheet_name].insert_rows(1)

    def insert_value(self, row, start_row, end_row, value):
        """
        在Excel的插入指定行中插入指定值

        :param row: 行号
        :param start_row: 开始插入行单元格的坐标
        :param end_row: 结束插入行单元格的坐标
        :param value:  插入的值
        :return:
        """
        # 指定位置插入指定值
        for i in range(int(start_row), int(end_row)+1):
            self.excel_df[self.sheet_name].cell(row=int(row), column=i).value = value

    def merge_cells(self, start_row, start_column, end_row, end_column):
        """
        合并单元格

        :param start_row: 合并单元格起始行号
        :param start_column: 合并单元格起始行号中的起始列号
        :param end_row: 合并单元格结束行号
        :param end_column: 合并单元格结束行号的结束列号
        :return:
        """
        self.excel_df[self.sheet_name].merge_cells(start_row=start_row, start_column=start_column, end_row=end_row, end_column=end_column)

    def _styles(self):
        # 获取表格有多少行？多少列？
        rows, columns = self.excel_df[self.sheet_name].max_row, self.excel_df[self.sheet_name].max_column

        # 定义字体样式
        fonts = Font(name='微软雅黑', size=12, color='000000')

        # 定义单元格对齐方式
        alignments = Alignment(horizontal='center', vertical='center')

        # 定义单元格边框颜色
        sides = Side(border_style='thin', color='797979')
        borders = Border(left=sides, right=sides, top=sides, bottom=sides)

        # 定义单元格背景颜色
        fills = PatternFill("solid", fgColor="FFBB02")

        # 定义行高和列宽
        heights, widths = 15, 20

        for i in range(1, int(rows)+1):
            for r in range(1, int(columns)+1):
                self.excel_df[self.sheet_name].cell(row=i, column=r).font = fonts
                self.excel_df[self.sheet_name].cell(row=i, column=r).alignment = alignments
                self.excel_df[self.sheet_name].cell(row=i, column=r).border = borders
                self.excel_df[self.sheet_name].cell(row=i, column=r).fill = fills

        for h in range(1, int(rows)+1):
            self.excel_df[self.sheet_name].row_dimensions[h].height = heights
        for w in range(1, int(columns)+1):
            self.excel_df[self.sheet_name].column_dimensions[utils.get_column_letter(w)].width = widths

    def save(self):
        self._styles()
        self.excel_df.save(self.path)


be = BeautifulExcel(path='/Users/qushuxian/Downloads/test.xlsx', sheet_name='Sheet1')
be.insert_row()
be.insert_value(row=1, start_row=1, end_row=4, value='每日销售额统计')
be.merge_cells(1, 1, 1, 4)
be.save()

曲书贤Machine Learning & Business Analyst

一名持续进化中的数据分析师。