一、背景描述
在上篇和中篇，我们已经实现从数据库查询到数据，根据指定条件将处理后的数据写入到excel中，并解决了excel中数据格式异常的问题。而目前生成的excel表格的宽度、表格的样式、字体、单元格背景色等均是默认的，excel结果文件是需要交给运营同学查看的，也就是说表格样式要稍微做下美化。
下面就来介绍使用Python设置excel表格样式
二、步骤讲解
下面是之前生成的excel表格示例：
Step1 创建样式设置基础方法
创建一个ExcelStyle类，方便后续直接继承调用。使用xlwt模块的XFStyle，设置字体、表格边框、表格背景、列宽、对齐方式
注意：这里使用了@staticmethod静态方法装饰器，所以不需要初始化和self参数，只需要通过se=ExcelStyle()来调用就可以了
class ExcelStyle:
@staticmethod
def styles():
"""设置单元格的样式的基础方法"""
style = xlwt.XFStyle()
return style
@staticmethod
def set_font(style, bold=True, name='宋体', height=11):
"""
设置字体，默认宋体加粗，高度11
:param style:
:param bold:
:param name:
:param height:
:return:
"""
style.font.bold = bold
style.font.name = name
style.font.height = 20 * height
@staticmethod
def set_border(style, status=1):
"""
设置边框
:param style:
:param status:
:return:
"""
style.borders.left = status
style.borders.right = status
style.borders.top = status
style.borders.bottom = status
@staticmethod
def set_pattern(style, color=23):
"""
设置表格背景颜色，默认深灰
0 = 黑, 1 = 白, 2 = 红, 3 = 绿, 4 = 蓝, 5 = 黄, 6 = 品红, 7 = 蓝绿,
16 = 褐红, 17 = 深绿, 18 = 深蓝, 19 = 棕色, 20 = 暗洋红, 21 = 蓝绿色, 22 = 浅灰, 23 = 深灰......
:param style:
:param color:
:return:
"""
style.pattern.pattern = xlwt.Pattern.SOLID_PATTERN
style.pattern.pattern_fore_colour = color
@staticmethod
def set_widths(worksheet, size):
"""
设置宽度
:param worksheet:
:param size:
:return:
"""
for i, v in enumerate(size):
worksheet.col(i).width = v * 256
@staticmethod
def set_alignments(style, wrap=1, **kwargs):
"""
设置对齐方式，默认自动换行
中心对齐参数：{"horz": "CENTER", "vert": "CENTER"}
horz（水平）：CENTER（居中）,DISTRIBUTED（两端）,GENERAL,CENTER_ACROSS_SEL（分散）,RIGHT（右）,LEFT（左）
vert（垂直）：CENTER（居中）,DISTRIBUTED（两端）,BOTTOM(下),TOP（上）
"""
if "horz" in kwargs.keys():
style.alignment.horz = eval（f"xlwt.Alignment.HORZ_{kwargs['horz'].upper()}")
if "vert" in kwargs.keys():
style.alignment.vert = eval（f"xlwt.Alignment.VERT_{kwargs['vert'].upper()}")
style.alignment.wrap = wrap # 设置自动换行Step2 创建表格样式模板
表头样式模板
注意：需要定义表头每一列的宽度，也就是说size参数是个列表
@staticmethod
def style_head(worksheet, size, wrap=0, is_bg=0, color=22):
"""
表头的样式
:param worksheet: 表格
:param size: 表格列宽
:param wrap: 1--自动换行，默认不换行
:param is_bg: 1--设置背景色，默认不设置
:param color: 默认浅灰色背景
:return:
"""
se = ExcelStyle()
style = se.styles()
se.set_font(style, height=14)
se.set_border(style)
se.set_widths(worksheet, size)
dicts = {"horz": "CENTER", "vert": "CENTER"}
se.set_alignments(style, wrap=wrap, **dicts)
if is_bg == 1:
se.set_pattern(style, color=color)
return style表格内容样式模板
已经设置过表头的列宽了，表格内容就不需要再设置了
@staticmethod
def style_table(dicts: dict):
"""
普通表格内容样式
:param dicts: 对齐方式
:return:
"""
se = ExcelStyle()
style = se.styles()
se.set_font(style, bold=False)
se.set_border(style)
se.set_alignments(style, wrap=1, **dicts)
return styleStep3 应用表格样式模板
将原始代码封装成了一个类，直接继承ExcelStyle类，先定义表头和表格内容的样式，然后在写入表格内容时传入对应的样式即可
定义表格样式
se = QueryToExcel()
# 定义表头样式
size = [20, 20, 12, 12, 20, 12, 12, 12, 12, 12, 12] # 表头列宽列表
head_style = se.style_head(sheet, size, wrap=1, is_bg=1) # 定义表头样式
# 定义表格内容样式
normal_style = se.style_table({"horz": "CENTER", "vert": "CENTER"}) # 表格内容样式传入表格样式
# 【写入表头】
for col, val in enumerate(headers):
sheet.write(0, col, val, head_style)
#------------------------------略------------------------------
#【写入表格内容】
r2_k_list = [key for key in results2[0]]
new_val = convert_value(results2[0][r2_k_list[0]])
sheet.write(i + 1, 10, new_val, normal_style)
#------------------------------略------------------------------
sheet.write(i + 1, col, '是', normal_style)Step4 运行效果展示
这下表格好看多了，可直接发给运营小姐姐同学看啦
三、总结分析
1.通过创建ExcelStyle类来设置表格样式，便于继承调用
2.创建设置表格样式的基础方法，可设置常见的excel表格样式
3.创建表头和表格的样式模板，在xlwt.write写入excel内容时传入样式即可
4.封装了查询数据库和写入excel的基础代码
完整代码：
import datetime
import time
import pymysql
import xlwt
class ExcelStyle:
@staticmethod
def style_head(worksheet, size, wrap=0, is_bg=0, color=22):
"""
表头的样式
:param worksheet: 表格
:param size: 表格列宽
:param wrap: 1--自动换行，默认不换行
:param is_bg: 1--设置背景色，默认不设置
:param color: 默认浅灰色背景
:return:
"""
se = ExcelStyle()
style = se.styles()
se.set_font(style, height=14)
se.set_border(style)
se.set_widths(worksheet, size)
dicts = {"horz": "CENTER", "vert": "CENTER"}
se.set_alignments(style, wrap=wrap, **dicts)
if is_bg == 1:
se.set_pattern(style, color=color)
return style
@staticmethod
def style_table(dicts: dict):
"""
普通表格内容样式
:param dicts: 对齐方式
:return:
"""
se = ExcelStyle()
style = se.styles()
se.set_font(style, bold=False)
se.set_border(style)
se.set_alignments(style, wrap=1, **dicts)
return style
@staticmethod
def styles():
"""设置单元格的样式的基础方法"""
style = xlwt.XFStyle()
return style
@staticmethod
def set_font(style, bold=True, name='宋体', height=11):
"""
设置字体，默认宋体加粗，高度11
:param style:
:param bold:
:param name:
:param height:
:return:
"""
style.font.bold = bold
style.font.name = name
style.font.height = 20 * height
@staticmethod
def set_border(style, status=1):
"""
设置边框
:param style:
:param status:
:return:
"""
style.borders.left = status
style.borders.right = status
style.borders.top = status
style.borders.bottom = status
@staticmethod
def set_pattern(style, color=23):
"""
设置表格背景颜色，默认深灰
0 = 黑, 1 = 白, 2 = 红, 3 = 绿, 4 = 蓝, 5 = 黄, 6 = 品红, 7 = 蓝绿,
16 = 褐红, 17 = 深绿, 18 = 深蓝, 19 = 棕色, 20 = 暗洋红, 21 = 蓝绿色, 22 = 浅灰, 23 = 深灰......
:param style:
:param color:
:return:
"""
style.pattern.pattern = xlwt.Pattern.SOLID_PATTERN
style.pattern.pattern_fore_colour = color
@staticmethod
def set_widths(worksheet, size):
"""
设置宽度
:param worksheet:
:param size:
:return:
"""
for i, v in enumerate(size):
worksheet.col(i).width = v * 256
@staticmethod
def set_alignments(style, wrap=1, **kwargs):
"""
设置对齐方式，默认自动换行
中心对齐参数：{"horz": "CENTER", "vert": "CENTER"}
horz（水平）：CENTER（居中）,DISTRIBUTED（两端）,GENERAL,CENTER_ACROSS_SEL（分散）,RIGHT（右）,LEFT（左）
vert（垂直）：CENTER（居中）,DISTRIBUTED（两端）,BOTTOM(下),TOP（上）
"""
if "horz" in kwargs.keys():
style.alignment.horz = eval（f"xlwt.Alignment.HORZ_{kwargs['horz'].upper()}")
if "vert" in kwargs.keys():
style.alignment.vert = eval（f"xlwt.Alignment.VERT_{kwargs['vert'].upper()}")
style.alignment.wrap = wrap # 设置自动换行
class QueryToExcel(ExcelStyle):
@staticmethod
def to_excel(dbinfo, file):
se = QueryToExcel()
# 定义表头
headers = ['主业务id', '次业务id', '会员id', '是否完成', '进入时间', '分值', '是否重复操作', '重复操作次数', '是否首次操作', '失败次数', '总时长']
# 新建工作簿和工作表
book = xlwt.Workbook(encoding='utf-8') # 新建工作簿
sheet = book.add_sheet("new_sheet", cell_overwrite_ok=True) # 新建sheet
# 定义表头样式
size = [20, 20, 12, 12, 20, 12, 12, 12, 12, 12, 12] # 表头列宽列表
head_style = se.style_head(sheet, size, wrap=1, is_bg=1) # 定义表头样式
# 定义表格内容样式
normal_style = se.style_table({"horz": "CENTER", "vert": "CENTER"}) # 表格内容样式
# 写入表头
for col, val in enumerate(headers):
sheet.write(0, col, val, head_style)
# 查询表1的数据
a_ids = "1977****144,1748****456"
sql1 = f"select * from table1 where a_id in ({a_ids}) and m_id={100001}"
results1 = select_from_mysql(dbinfo, sql1)
# 根据表1查询结果查询并组装表2的数据
if results1 is not None:
for i, v in enumerate(results1):
r1_k_list = [key for key in v]
for col, k in enumerate(v):
if col == 1:
new_val = convert_value(v[r1_k_list[col]])
sheet.write(i + 1, col, new_val, normal_style)
# 查询表2的数据
sql2 = f"select * from table2 where a_id={v[r1_k_list[0]]} and mi_id={v[r1_k_list[col]]} and m_id={100001}"
results2 = select_from_mysql(dbinfo, sql2)
if results2 != ():
r2_k_list = [key for key in results2[0]]
new_val = convert_value(results2[0][r2_k_list[0]])
sheet.write(i + 1, 10, new_val, normal_style)
else:
sheet.write(i + 1, 10, '', normal_style)
elif col in [3, 6]:
if convert_value(v[r1_k_list[col]]) == '1':
sheet.write(i + 1, col, '是', normal_style)
else:
sheet.write(i + 1, col, '否', normal_style)
elif col == 8:
if convert_value(v[r1_k_list[3]]) == '1' and convert_value(v[r1_k_list[col]]) == '0':
sheet.write(i + 1, col, '是', normal_style)
else:
sheet.write(i + 1, col, '否', normal_style)
else:
sheet.write(i + 1, col, convert_value(v[r1_k_list[col]]), normal_style)
# 保存excel
book.save(f"{file}.xlsx")
def is_date(val):
"""
判断date_str是否为日期格式
"""
date_format = "%Y-%m-%d"
try:
if isinstance(val, str):
new_str = val[:10]
valid_date = time.strptime(new_str, date_format)
return True
else:
return False
except ValueError or TypeError as e:
return False
def convert_value(val):
"""转换数据格式为excel"""
if isinstance(val, bytes):
val = ord(val)
if is_date(val):
val = datetime.datetime.strptime(val, "%Y-%m-%d %H:%M:%S.%f")
new_val = str(val)
return new_val
def select_from_mysql(dbinfo, sql):
"""
连接数据库执行sql
"""
conn = pymysql.connect(**dbinfo)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
cursor.execute(sql)
results = cursor.fetchall()
conn.close()
return results
if __name__ == "__main__":
# 数据库信息
db_info = {
'host': "192.168.1.1",
'port': 3306,
'user': "test",
'passwd': "test",
'db': "test_db",
'charset': "utf8"
}
file_name = 'target'
QueryToExcel().to_excel(db_info, file_name)
