Python 数据处理之柱状图--Echarts

一、读取数据并写入excel

# -*- coding:utf-8 -*-
import xlsxwriter


def add_series(i):
    j = len(data[0]) + 1
    chart_col.add_series({
        # 这里的sheet1是默认的值，因为我们在新建sheet时没有指定sheet名
        # 如果我们新建sheet时设置了sheet名，这里就要设置成相应的值
        'name': '=Sheet1!%s1' % i,
        'categories': '=Sheet1!A2:A%s' % j,
        'values': '=Sheet1!%s2:%s%s' % (i, i, j),
        'line': {'color': 'black'},
    })


# 创建一个excel
workbook = xlsxwriter.Workbook("chart_column.xlsx")
# 创建一个sheet
worksheet = workbook.add_worksheet()

# 自定义样式，加粗
bold = workbook.add_format({'bold': 1})

# --------1、准备数据并写入excel---------------
# 向excel中写入数据，建立图标时要用到
headings = ['', '张三', '李四', '王五']
data = [
    ['一月', '二月', '三月', '四月', '五月', '六月'],
    [10, 40, 50, 20, 10, 50],
    [30, 60, 70, 50, 40, 30],
    [30, 40, 50, 60, 70, 80],
]

# 写入表头
worksheet.write_row('A1', headings, bold)

# 写入数据
worksheet.write_column('A2', data[0])
worksheet.write_column('B2', data[1])
worksheet.write_column('C2', data[2])
worksheet.write_column('D2', data[3])

# --------2、生成图表并插入到excel---------------
# 创建一个柱状图(column chart)
chart_col = workbook.add_chart({'type': 'column'})

# 配置数据
add_series('B')
add_series('C')
add_series('D')

# 设置图表的title 和 x，y轴信息
chart_col.set_title({'name': 'xxx公司上半年销售额'})
#chart_col.set_x_axis({'name': '姓名'})
chart_col.set_y_axis({'name':  '销售额'})
chart_col.set_table({'show_keys': True})

# 设置图表的风格
chart_col.set_style(2)

# 把图表插入到worksheet以及偏移
worksheet.insert_chart('A10', chart_col)

# 将excel文件保存关闭
workbook.close()

'''
参考：https://www.cnblogs.com/puresoul/p/7520246.html
'''

二、利用openpyxl.chart读取excel并画图

from openpyxl import load_workbook
from openpyxl.chart import BarChart, Reference

wb = load_workbook('Demo.xlsx')
ws = wb['Sheet1']

chart1 = BarChart()
chart1.type = "col"
chart1.style = 10
chart1.title = "Bar Chart"
chart1.y_axis.title = 'Test number'
chart1.x_axis.title = 'Sample length (mm)'

data = Reference(ws, min_col=2, min_row=1, max_row=7, max_col=4)
cats = Reference(ws, min_col=1, min_row=2, max_row=7)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
chart1.shape = 4
ws.add_chart(chart1, "A10")

from copy import deepcopy

chart2 = deepcopy(chart1)
chart2.style = 11
chart2.type = "bar"
chart2.title = "Horizontal Bar Chart"
ws.add_chart(chart2, "J10")

chart3 = deepcopy(chart1)
chart3.type = "col"
chart3.style = 12
chart3.grouping = "stacked"
chart3.overlap = 100
chart3.title = 'Stacked Chart'
ws.add_chart(chart3, "A27")

chart4 = deepcopy(chart1)
chart4.type = "bar"
chart4.style = 13
chart4.grouping = "percentStacked"
chart4.overlap = 100
chart4.title = 'Percent Stacked Chart'
ws.add_chart(chart4, "J27")

wb.save('Demo.xlsx')

参考：http://openpyxl.readthedocs.io/en/stable/charts/bar.html