一、xlsxwriter简介

新建 xlsx 文件，插入数据、插入图标等表格操作。 只能新建xlsx后写入xlsx文件 。

二、xlsxwriter语法

1、模块安装

pip3 install xlsxwriter

2、模块导入

import xlsxwriter

3、创建工作簿、工作表

import xlsxwriter
# 创建工作簿
workbook = xlsxwriter.Workbook('测试文件.xlsx')  # 创建一个excel文件
# 创建工作表
worksheet = workbook.add_worksheet('这是sheet1')  # 在文件中创建一个名为这是sheet1的sheet,不加名字默认为sheet1
# 写入数据
worksheet.write(0, 0, '写点什么好')  # 第1行第1列（即A1）写入
workbook.close()

4、设置写入格式

4.1 写入

python（模块）xlsxwriter
举例：

import xlsxwriter
from datetime import datetime
# 创建工作簿
workbook = xlsxwriter.Workbook('测试文件.xlsx')  # 创建一个excel文件
# 创建工作表
worksheet = workbook.add_worksheet('这是sheet1')  # 在文件中创建一个名为这是sheet1的sheet,不加名字默认为sheet1
# 写入数据
worksheet.write(0, 0, '写点什么好')  # 写入字符串
worksheet.write(2, 0, '=SUM(B3:B4)')  # 写入excel公式
worksheet.write_formula(4, 0, '=SUM(B3:B4)')  # 写入excel公式
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()

写入公式效果：
python（模块）xlsxwriter
写入自定义时间、日期效果：

写入数据效果：
python（模块）xlsxwriter

举个例子：我想要写入excel的单元格格式是：货币
python（模块）xlsxwriter
1、先在excel写入数据并设置成货币类型

2、按照下图所示在excel设置成自定义，可以看到刚才设置的货币对应的样式：‘￥#,##0.00;￥-#,##0.00’

验证一下：

import xlsxwriter
from datetime import datetime
# 创建工作簿
workbook = xlsxwriter.Workbook('测试文件.xlsx')  # 创建一个excel文件
# 创建工作表
worksheet = workbook.add_worksheet('这是sheet1')  # 在文件中创建一个名为这是sheet1的sheet,不加名字默认为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',  # 垂直居中对齐
    # 'num_format': 'yyyy-mm-dd H:M:S',# 设置日期格式
    # 后面参数是线条宽度
    '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',  # 垂直居中对齐
    # 'num_format': 'yyyy-mm-dd H:M:S',# 设置日期格式
    # 后面参数是线条宽度
    'border': 1,  # 边框宽度
    'top': 1,  # 上边框
    'left': 1,  # 左边框
    'right': 1,  # 右边框
    'bottom': 1  # 底边框
title_style = workbook.add_format(title_format)  # 设置样式format是一个字典
style = workbook.add_format(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, '未合并') # 第一行第一列，A1写入'未合并'
worksheet.write(2, 0, '会被覆盖') # 第三行第一列，A3写入'会被覆盖'
worksheet.write(4, 0, '待合并') # 第五行第一列，A5写入'待合并'
worksheet.merge_range(1,2,3,4,'合并01') # 合并第二行-四行，第三列-五列，即：C2:E4
worksheet.merge_range('A3:B3','覆盖它') # 合并A3:B3，并写入'覆盖它'
worksheet.merge_range('A5:B5','') # 合并A5:B5，'待合并'并不被覆盖
workbook.close()

三、实例应用

功能描述：
从数据库取数，写入xlsx，并发送邮件。

代码实现：

# util为博主封装的模块
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):
    #创建excel文件
    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)#缺考分数为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()
    #html = convert_df_to_html(df)
    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)

效果：

四、参考资料

Python3之excel操作–xlsxwriter模块

【DEBUG】解决pandas与numpy版本不协调导致的：Unable to import required dependencies: numpy: DLL load failed找不到指定的模块 2023年3月10日上午8:56

目录