py 之xlsxwriter 操作生成柱状图，饼图。

import xlsxwriter
import time

def charts(product_name, dict_data, crash_data_list, report_path):
    if len(dict_data) <= 0 or len(crash_data_list) <= 0:
        return


    current_time = time.localtime(time.time())
    current_time = time.strftime("%Y%m%d%H%M%S", current_time)    
    
    plot_file_path = 'product_plot_{}.xlsx' 11     workbook = xlsxwriter.Workbook(plot_file_path)
    worksheet = workbook.add_worksheet(product_name)

    worksheet_date = workbook.add_worksheet('date')
    worksheet_date_headings = ['date', 'date_count']
    bold = workbook.add_format({'bold': 1}) 　　　　　　#设置格式
     19     data = list()
    date_list = list()
    date_crash_list = list()   

    sort_dump_dict = {}
    for i in sorted (dict_data):
        sort_dump_dict[i] = dict_data[i]

    for k, v in sort_dump_dict.items():
        date_list.append(k)
        date_crash_list.append(len(v))

    data.append(date_list)
    data.append(date_crash_list)
    data.append([crash_data_list[0]])
    data.append([crash_data_list[1]])
    data.append([crash_data_list[2]])
    data.append([crash_data_list[3]])
    data.append([crash_data_list[4]])
    data.append([crash_data_list[5]])

    format = workbook.add_format()
    format.set_text_wrap()
    worksheet.set_column('A:A', 10, format)
        #上述构造数据

    print('exlce_data: ', data)
    worksheet.write_column('A100', data[0])
    worksheet.write_column('B100', data[1])

    worksheet_date.write_row('A1', worksheet_date_headings, bold)
    worksheet_date.write_column('A2', data[0])
    worksheet_date.write_column('B2', data[1])

    data_format = {
        'align': 'right',  # 水平位置设置：右对齐
        'valign': 'vcenter'  # 垂直位置设置，居中
    }
    right_format = workbook.add_format(data_format)
    worksheet.write_row('A1', ["product_name"], bold)    
    worksheet.write_row('A2', ["effective_data"], bold)
    worksheet.write_row('A3', ["other_data"], bold)
    worksheet.write_row('A4', ["count"], bold)
    worksheet.write_row('A5', ["point"], bold)
    worksheet.write_row('A6', ["DUA"], bold)
    worksheet.write_row('A7', ["rate"], bold)
    
        #行写入数据 注意第二参数是list，第三参数的格式需要添加进工作簿才能生效
    worksheet.write_row('B1', [product_name], right_format)
    worksheet.write_row('B2', [crash_data_list[0]], right_format)
    worksheet.write_row('B3', [crash_data_list[1]], right_format)
    worksheet.write_row('B4', [crash_data_list[2]], right_format)
    worksheet.write_row('B5', [crash_data_list[3]], right_format)
    worksheet.write_row('B6', [crash_data_list[4]], right_format)
    worksheet.write_row('B7', [crash_data_list[5]], right_format)
    
    # 创建一个图表，类型是column
    columnar_chart = workbook.add_chart({'type': 'column'})
    line_chart = workbook.add_chart({'type': 'line'})

    # 配置series,这个和前面worksheet是有关系的。
    #     指定图表的数据范围
    columnar_chart.add_series({
        'name': '={}!$B$1'.format(product_name),
        'categories': '={}!$A$100:$A${}'.format(product_name, 100 + len(date_list)),
        'values': '={}!$B$100:$B${}'.format(product_name, 100 + len(date_crash_list)),
        'data_labels': {'value': True},
    })
    #设置列宽 列宽自适应 像素点转磅
    worksheet.set_column('A1:A{}'.format(len(date_list)), 33)
    worksheet.set_column('B1:B{}'.format(len(date_crash_list)), 13)        
    worksheet_date.set_column('A1:A{}'.format(len(date_list)), 15)
    worksheet_date.set_column('B1:B{}'.format(len(date_crash_list)), 24)   

    # 添加图表标题和标签
    columnar_chart.set_title({'name': 'Results of datga analysis'})
    columnar_chart.set_x_axis({'name': 'datas analysis result'})
    columnar_chart.set_y_axis({'name': 'Sample length (mm)'})

    # 设置图表风格
    columnar_chart.set_style(11)
    columnar_chart.set_size({'width': 750, 'height': 400})
    columnar_chart.set_legend({'position': 'top'})
    columnar_chart.set_y_axis({
        'major_gridlines': {
            'visible': False,
            'line': {'none': True}
        },
        'line': {'none': True},
        'num_font': {'color': '#FFFFFF'},

    })
    # 在D2单元格插入图表（带偏移）
    columnar_chart.combine(line_chart)    
    worksheet.insert_chart('F1', columnar_chart)

    pie_chart = workbook.add_chart({'type': 'pie'})
    pie_chart.set_style(3)
    pie_chart.set_size({'width': 500, 'height': 420})
    pie_chart.set_legend({'position': 'bottom'})
    pie_chart.add_series({
        # "name":"饼形图",
        "categories": "={}!$A$2:$A$3".format(product_name),
        "values": "={}!$B$2:$B$3".format(product_name),
        # 定义各饼块的颜色
        "points": [
            {"fill": {"color": "red"}},
            {"fill": {"color": "green"}}
        ], 
        'data_labels': {
            # 显示数值
            'value': True, 
            # 显示百分比
            'percentage':True,
            'leader_lines': True, 
            'category': True,
            'separator': "\n",
            'border': {'color': '#7F7F7F','transparency': 100},    #使用transparency关键字来设置透明度            
        }, #显示标签
    })
    pie_chart.set_title({"name": "data_type"})    
    worksheet.insert_chart('H21', pie_chart)
    workbook.close()