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 : [email protected]
# @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()
|
