Python 3——xlsxwriter生成图表

￡神魔★判官ぃ 2022-10-12 01:32 203阅读 0赞

# 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()

执行效果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3VzZXJuYW1lNjY2_size_16_color_FFFFFF_t_70]

## 3.2 写入格式化配置 ##

执行效果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3VzZXJuYW1lNjY2_size_16_color_FFFFFF_t_70 1]

## 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()

执行效果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3VzZXJuYW1lNjY2_size_16_color_FFFFFF_t_70 2]

--------------------

# 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：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3VzZXJuYW1lNjY2_size_16_color_FFFFFF_t_70 3]

执行效果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3VzZXJuYW1lNjY2_size_16_color_FFFFFF_t_70 4]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3VzZXJuYW1lNjY2_size_16_color_FFFFFF_t_70 5]

--------------------

**课程地址：**  
[https://www.imooc.com/learn/1239][https_www.imooc.com_learn_1239]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3VzZXJuYW1lNjY2_size_16_color_FFFFFF_t_70]: /images/20221005/b1ce3b1dd99b40c8844abc6fea8ad92f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3VzZXJuYW1lNjY2_size_16_color_FFFFFF_t_70 1]: /images/20221005/7bc6cf4191744c198bc2cc566d019309.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3VzZXJuYW1lNjY2_size_16_color_FFFFFF_t_70 2]: /images/20221005/67fb5f4f8af5464e99ad1e6fcd3900da.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3VzZXJuYW1lNjY2_size_16_color_FFFFFF_t_70 3]: /images/20221005/17f3a75ebb20458c92c9828e81aecbc3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3VzZXJuYW1lNjY2_size_16_color_FFFFFF_t_70 4]: /images/20221005/aba100a8cb4f4f8aa631dc7ce4e0041f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3VzZXJuYW1lNjY2_size_16_color_FFFFFF_t_70 5]: /images/20221005/dca56a5e66f847d4b8d6a842e13edcee.png
[https_www.imooc.com_learn_1239]: https://www.imooc.com/learn/1239