目录
一、xlsxwriter简介
新建 xlsx 文件,插入数据、插入图标等表格操作。 只能新建xlsx后写入xlsx文件。
二、xlsxwriter语法
1、模块安装
pip3 install xlsxwriter
2、模块导入
import xlsxwriter
3、创建工作簿、工作表
import xlsxwriter
workbook = xlsxwriter.Workbook('测试文件.xlsx')
worksheet = workbook.add_worksheet('这是sheet1')
worksheet.write(0, 0, '写点什么好')
workbook.close()
4、设置写入格式
4.1 写入
举例:
import xlsxwriter
from datetime import datetime
workbook = xlsxwriter.Workbook('测试文件.xlsx')
worksheet = workbook.add_worksheet('这是sheet1')
worksheet.write(0, 0, '写点什么好')
worksheet.write(2, 0, '=SUM(B3:B4)')
worksheet.write_formula(4, 0, '=SUM(B3:B4)')
date_format = workbook.add_format({'num_format': 'yyyy-mm-dd H:M:S'})
date_format1 = workbook.add_format({'num_format': 'yyyy/m/d;@'})
worksheet.write_datetime(6, 0, datetime.today(),date_format )
worksheet.write_datetime(6, 2, datetime.today(),date_format1 )
num_format = workbook.add_format({'num_format': '0.00_);[Red](0.00)'})
worksheet.write_number(8, 0, 1001)
worksheet.write_number(8, 2, 1001,num_format)
worksheet.write_row(row = 1 ,col = 3, data = ['嘿嘿','哈哈','呵呵'])
workbook.close()
写入公式效果:
写入自定义时间、日期效果:
写入数据效果:
重要!!!怎么知道想要的格式如何表示?
举个例子:我想要写入excel的单元格格式是:货币
1、先在excel写入数据并设置成货币类型
2、按照下图所示在excel设置成自定义,可以看到刚才设置的货币对应的样式:’¥#,##0.00;¥-#,##0.00′
验证一下:
import xlsxwriter
from datetime import datetime
workbook = xlsxwriter.Workbook('测试文件.xlsx')
worksheet = workbook.add_worksheet('这是sheet1')
num_format = workbook.add_format({'num_format': '¥#,##0.00;¥-#,##0.00'})
worksheet.write(8, 0, 120,num_format)
workbook.close()
4.2 设置单元格样式
import xlsxwriter
workbook = xlsxwriter.Workbook('测试文件.xlsx')
worksheet = workbook.add_worksheet('这是sheet1')
title_format = {
'font_name' : '微软雅黑',
'font_size': 12,
'font_color': 'black',
'bold': True,
'bg_color': '#101010',
'fg_color': '#00FF00',
'align': 'center',
'valign': 'vcenter',
'border': 1,
'top': 1,
'left': 1,
'right': 1,
'bottom': 1
}
format = {
'font_size': 10,
'font_color': 'blue',
'bold': False,
'bg_color': '#101010',
'fg_color': '#00FF00',
'align': 'center',
'valign': 'vcenter',
'border': 1,
'top': 1,
'left': 1,
'right': 1,
'bottom': 1
}
title_style = workbook.add_format(title_format)
style = workbook.add_format(format)
worksheet.write_row(0, 0, ['表头1','表头2'], title_style)
worksheet.write_row(1, 0, ['数据1','数据2'], style)
workbook.close()
4.3 设置工作表标签颜色
import xlsxwriter
workbook = xlsxwriter.Workbook('测试文件.xlsx')
worksheet1 = workbook.add_worksheet('这是sheet1')
worksheet1.set_tab_color('#0000FF')
worksheet2 = workbook.add_worksheet('这是sheet2')
workbook.close()
4.4 列宽和行高
import xlsxwriter
workbook = xlsxwriter.Workbook('测试文件.xlsx')
worksheet = workbook.add_worksheet('这是sheet1')
worksheet.write(0, 0, '这是个很长的字段')
worksheet.write(2, 2, '这是个特别长的字段')
worksheet.set_row(0, 60)
worksheet.set_column(1, 2, 30)
workbook.close()
4.5 合并单元格
语法:
merge_range(first_row, first_col, last_row, last_col, data[, cell_format])
举例:
import xlsxwriter
from datetime import datetime
workbook = xlsxwriter.Workbook('测试文件.xlsx')
worksheet = workbook.add_worksheet('这是sheet1')
worksheet.write(0, 0, '未合并')
worksheet.write(2, 0, '会被覆盖')
worksheet.write(4, 0, '待合并')
worksheet.merge_range(1,2,3,4,'合并01')
worksheet.merge_range('A3:B3','覆盖它')
worksheet.merge_range('A5:B5','')
workbook.close()
三、实例应用
功能描述:
从数据库取数,写入xlsx,并发送邮件。
代码实现:
from util import config
from util.gmail import Mail
from util.gstring import convert_df_to_html
from util.logger import log
from util.db import get_db
import pandas as pd
import os
from datetime import datetime
import xlsxwriter
def run(init_date):
new_excel = "6(1)班考试成绩单-" + init_date +".xlsx"
if os.path.exists(new_excel) :
os.remove(new_excel)
workbook = xlsxwriter.Workbook(new_excel)
db = get_db("dbcenter")
log.info('开始从数据库取数...')
math_sql = f'''
select '张三' as name
,'6(1)班' as class
,'数学' as subject
,95 as score
,{init_date} as init_date
union all
select '李四' as name
,'6(1)班' as class
,'数学' as subject
,98 as score
,{init_date} as init_date
union all
select '王五' as name
,'6(1)班' as class
,'数学' as subject
,null as score
,{init_date} as init_date
'''
chinese_sql = f'''
select '张三' as name
,'6(1)班' as class
,'语文' as subject
,90 as score
,{init_date} as init_date
union all
select '李四' as name
,'6(1)班' as class
,'语文' as subject
,88 as score
,{init_date} as init_date
union all
select '王五' as name
,'6(1)班' as class
,'语文' as subject
,70 as score
,{init_date} as init_date
'''
sqls = [math_sql,chinese_sql]
sheets = ['数学成绩单','语文成绩单']
title_format = {
'font_name': '宋',
'font_size': 10,
'font_color': 'black',
'bold': True,
'align': 'center',
'valign': 'vcenter'
}
format = {
'font_name': '宋',
'font_size': 10,
'font_color': 'black',
'bold': False,
'align': 'center',
'valign': 'vcenter'
}
date_format = {
'font_name': '宋',
'font_size': 10,
'font_color': 'black',
'align': 'center',
'valign': 'vcenter',
'num_format': 'yyyy/mm/dd'
}
title_style = workbook.add_format(title_format)
style = workbook.add_format(format)
date_style = workbook.add_format(date_format)
for i in range(0,len(sqls)) :
datas = db.query(sqls[i], {init_date : init_date})
df = pd.DataFrame(datas, columns=['name', 'class', 'subject', 'score', 'init_date'])
df["班主任"] = '王老师'
df["考试日期"] = datetime.strptime(init_date, '%Y%m%d').date()
df = df.rename(columns= {"name":"姓名","class":"班级","subject":"科目","score":"分数"})
order = ["考试日期","班级","班主任","姓名","科目","分数"]
df = df[order].fillna(0)
log.info(df)
datas = [tuple(xi) for xi in df.values]
excel_data = [tuple(order)] + datas
worksheet = workbook.add_worksheet(sheets[i])
for i in range(0,len(excel_data)):
for j in range(0,len(excel_data[i])):
if i == 0:
worksheet.write(i, j, excel_data[i][j], title_style)
elif j == 0:
worksheet.write_datetime(i, j, datetime.strptime(init_date, '%Y%m%d').date(), date_style)
else:
worksheet.write(i, j, excel_data[i][j], style)
workbook.close()
mail_config = config.get_config("email.send_fxm")
mail = Mail(mail_config)
title = '6(1)班考试成绩单-' + init_date
to_list = 'xxx.com'
mail.send_email(to_list, title,content_text = '各位家长你们好,6(1)班'+ init_date + '日考试成绩单详见附件!', attachment_list=[new_excel])
if __name__ == "__main__":
init_date = '20220412'
run(init_date)
效果:
四、参考资料
Original: https://blog.csdn.net/shammy_feng/article/details/124149896
Author: shammy
Title: python(模块)xlsxwriter
原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/670164/
转载文章受原作者版权保护。转载请注明原作者出处!