Python 3——xlsxwriter生成图表

1. xlsxwriter模块介绍

• xlsxwriter为Python第三方模块，用于向生成的Excel表格插入数据、图表等操作。
• 安装：pip install xlsxwriter
• 导入：import xlsxwriter

2. xlsxwriter的优缺点

• xlsxwriter较其他模块支持更多的Excel功能
• 100%兼容Excel xlsx文件，支持Excel 2003、Excel 2007等版本
• xlsxwriter处理速度更快，支持大文件写入
• 不支持修改Excel内容

3. xlsxwriter模块使用

3.1 写入文本、图片、超链接

import xlsxwriter

wb = xlsxwriter.Workbook("data.xlsx")
# 创建sheet
sheet = wb.add_worksheet("newsheet")
# 写入
sheet.write(0, 0, "2021年度") # 写入单元格数据
sheet.merge_range(1, 0, 2, 2, "第一季度销售统计")
data = (
    ["一月份", 500, 450],
    ["二月份", 600, 550],
    ["三月份", 650, 600]
)
sheet.write_row(3, 0, ["月份", "预期销售额", "实际销售额"])
# 写入数据
for index, item in enumerate(data):
    sheet.write_row(index+4, 0, item)
# 写入excel公式
sheet.write(7, 1, "=sum(B5:B7)")
sheet.write(7, 2, "=sum(C5:C7)")
sheet.write_url(9, 0, "http://www.baidu.com", string = "更多数据")
sheet.insert_image(10, 0, "4.jpg")  # 插入图片
wb.close()

执行效果：

3.2 写入格式化配置

import xlsxwriter

wb = xlsxwriter.Workbook("data.xlsx")
# 设置格式的两种方法 1.钩子函数
cell_format = wb.add_format({'bold' : True})  # 创建格式对象
cell_format1 = wb.add_format()  # 格式对象
cell_format1.set_bold() # 设置加粗
cell_format1.set_font_color("red") # 设置文本字体颜色
cell_format1.set_font_size(14) # 设置字体大小
cell_format1.set_align("center") # 设置对齐方式 自动对齐
cell_format2 = wb.add_format() # 格式对象
cell_format2.set_bg_color("#808080") # 设置背景颜色


# 创建sheet
sheet = wb.add_worksheet("newsheet")
# 写入
sheet.write(0, 0, "2021年度", cell_format) # 写入单元格数据
sheet.merge_range(1, 0, 2, 2, "第一季度销售统计", cell_format1)
data = (
    ["一月份", 500, 450],
    ["二月份", 600, 550],
    ["三月份", 650, 600]
)
sheet.write_row(3, 0, ["月份", "预期销售额", "实际销售额"], cell_format2)
# 写入数据
for index, item in enumerate(data):
    sheet.write_row(index+4, 0, item)
# 写入excel公式
# write(self, row, col, *args)
sheet.write(7, 1, "=sum(B5:B7)")
sheet.write(7, 2, "=sum(C5:C7)")
# 插入超链接
sheet.write_url(9, 0, "http://www.baidu.com", string="更多数据")
sheet.insert_image(10, 0, "4.jpg")  # 插入图片
wb.close()

执行效果：

3.3 写入图表到Excel

import xlsxwriter

wb = xlsxwriter.Workbook("data.xlsx")
# 设置格式的两种方法 1.钩子函数
cell_format = wb.add_format({'bold' : True})  # 创建格式对象
cell_format1 = wb.add_format()  # 格式对象
cell_format1.set_bold() # 设置加粗
cell_format1.set_font_color("red") # 设置文本字体颜色
cell_format1.set_font_size(14) # 设置字体大小
cell_format1.set_align("center") # 设置对齐方式 自动对齐
cell_format2 = wb.add_format() # 格式对象
cell_format2.set_bg_color("#808080") # 设置背景颜色


# 创建sheet
sheet = wb.add_worksheet("newsheet")
# 写入
sheet.write(0, 0, "2021年度", cell_format) # 写入单元格数据
sheet.merge_range(1, 0, 2, 2, "第一季度销售统计", cell_format1)
data = (
    ["一月份", 500, 450],
    ["二月份", 600, 550],
    ["三月份", 650, 600]
)
sheet.write_row(3, 0, ["月份", "预期销售额", "实际销售额"], cell_format2)
# 写入数据
for index, item in enumerate(data):
    sheet.write_row(index+4, 0, item)
# 写入excel公式
# write(self, row, col, *args)
sheet.write(7, 1, "=sum(B5:B7)")
sheet.write(7, 2, "=sum(C5:C7)")
# 插入超链接
sheet.write_url(9, 0, "http://www.baidu.com", string="更多数据")
sheet.insert_image(10, 0, "4.jpg")  # 插入图片


# 写入
chart = wb.add_chart({'type' : 'column'})  # column：柱状图，line：折线图
chart.set_title({'name':'第一季度销售统计'})
# X Y 描述信息
chart.set_x_axis({'name':'月份'})
chart.set_y_axis({'name':'销售额'})
# 数据
chart.add_series({
    'name' : '预期销售额',
    'categories' : '=newsheet!$A$5:$A$7', # 通过单元格标识获取数据
    'values' : ['newsheet', 4, 1, 6, 1],            # 通过索引获取
    'data_labels': {'value':True} # 显示数字
})
chart.add_series({
    'name' : '实际销售额',
    'categories' : '=newsheet!$A$5:$A$7', # 通过单元格标识获取数据
    'values' : ['newsheet', 4, 2, 6, 2],             # 通过索引获取
    'data_labels': {'value':True} # 显示数字
})
sheet.insert_chart('A19', chart)
wb.close()

执行效果：

---

4. 综合实战：某培训机构就业数据分析

4.1 实现步骤

• 步骤一：xlrd模块读取Excel数据
• 步骤二：xlsxwriter模块生成就业数据图表
• 步骤三：smtplib模块发送附件邮件
  • smtlip模块对smtp协议进行了封装，提供更便捷的方式发送电子邮件

4.2 综合实战：某培训机构就业数据分析

import xlrd
import xlsxwriter
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.mime.application import MIMEApplication


# 1.读取
data = xlrd.open_workbook('info.xls')
classInfo = []
for sheet in data.sheets():
    dict = {'name':sheet.name, 'avgsalary':0} # 班级信息
    sum = 0 # 存储薪资
    for i in range(sheet.nrows):
        # 从第3行开始才是有用的数据
        if i > 1:
            sum += float(sheet.cell(i, 5).value) # 得到薪资数据
    dict['avgsalary'] = sum / (sheet.nrows-2)
    classInfo.append(dict)
print(classInfo)

# 2.写入exccel
workbook = xlsxwriter.Workbook('newInfo.xlsx')
sheet = workbook.add_worksheet() # 创建工作表
# 写入班级数据
nameInfo = []
salaryInfo = []
for item in classInfo:
    nameInfo.append(item['name'])
    salaryInfo.append(item['avgsalary'])
sheet.write_column('A1', nameInfo)
sheet.write_column('B1', salaryInfo)
# 写入图表
chart = workbook.add_chart({'type':'column'})
# 标题
chart.set_title({'name':'平均就业薪资'})
# 数据源
chart.add_series({
    'name':'班级',
    'categories':'=Sheet1!$A$1:$A$3',
    'values':'=Sheet1!$B$1:$B$3',
})
sheet.insert_chart('A7', chart)
workbook.close()

# 3.发送邮件
host_server = 'smtp.163.com' # 主机地址
# 发件人邮箱
sender = "xxx@163.com"
# 发件人邮箱密码、授权码
code = "123456"
# 收件人
user1 = "xxx@qq.com"
# 准备邮件数据
# 邮件标题
mail_title = "!!!1月份平均就业薪资"
# 内容
mail_content = "1月份平均就业薪资，请具体查看附件"
# 构建附件
attachment = MIMEApplication(open('newInfo.xlsx', 'rb').read())
attachment.add_header('Content-Disposition', 'attachment', filename='data.xlsx')

# SMTP
smtp = smtplib.SMTP(host_server)
# 登录
smtp.login(sender, code)
# 发送
msg = MIMEMultipart() # 带附件的实例
msg['Subject'] = mail_title
msg['From'] = sender
msg['To'] = user1
msg.attach(MIMEText(mail_content))
msg.attach(attachment)
smtp.sendmail(sender, user1, msg.as_string())

info.xls：

执行效果：

---

课程地址：
https://www.imooc.com/learn/1239