python xlsxwriter模块生成EXCEL柱状图、饼图

安装xlsxwriter

pip3 install xlsxwriter

导入包

import xlsxwriter

代码及说明

• 生成表格数据

import xlsxwriter
path_Name =  'test.xlsx'
# 路径及文件名
workbook = xlsxwriter.Workbook('/Users/xyt/Desktop/'+path_Name)
# 定义基本样式
base_style = workbook.add_format({"align": "left",'border': 0, 'font_size': 12,'text_wrap': 1,'valign': 'vcenter'})
# 定义表格中的文本内容为自动换行格式
base_style.set_text_wrap()

headings = ['姓名','1的数量','2的数量']
data = [['A',12,10],['B',12,15],['C',12,20],['D',12,25]]

# 创建一个工作表，名为sheetOne
worksheet = workbook.add_worksheet("sheetOne")
# 合并单元格
worksheet.merge_range('A1:C2', 'Mytable')
worksheet.write_row("A3",headings,base_style)

# 将第一列设为红色（此处仅为了展示如何定义单个单元格样式的例子）
for row in range(0,len(data)):
    for col in range(0,len(data[row])):
        if col==0:
            worksheet.write(row+3, col, data[row][col], workbook.add_format({"fg_color": "red",'border': 1, 'font_size': 12,'text_wrap': 1,'valign': 'vcenter'}))
        else:
            worksheet.write(row+3, col, data[row][col],base_style)

# 无特殊样式设定，直接插入数据
# for i in range(0,len(data)):
#     # 从A3开始插入
#     worksheet.write_row("A{}".format(i+3),data[i],base_style)

• 设置某一单元格的宽度

# 设置某一单元格宽
for j in range(len(headings)):
    if j==0:
        worksheet.set_column(j, j, width=20)

• 批量设置单元格宽度

worksheet.set_column('B:D', 12)#设置B-D的单元格宽度为12

1. 简单柱状图

#添加柱状图
chartShow1 = workbook.add_chart({"type":"column"})
t = 'B'

for i in range(len(headings)-1):
    chartShow1.add_series({
        "name":"=sheetOne"+"!$"+t+"$3",
        "categories":"=sheetOne"+"!$A$4:$A$7",
        "values":"=sheetOne"+"!$"+t+"$4:$"+t+"$7",
    })
    t=chr(ord(t)+1)

#添加柱状图标题
chartShow1.set_title({"name":"柱状图"})
#X轴名称
chartShow1.set_x_axis({"name":"姓名"})
#Y轴名称
chartShow1.set_y_axis({"name":"总数"})
#图表样式
chartShow1.set_style(11)
chartShow1.set_size({'width':450,'height':250})

worksheet.insert_chart("H2",chartShow1)

2. 柱状叠图

#添加柱状图叠图子类型
chartShow2 = workbook.add_chart({"type":"column","subtype":"stacked"})
t = 'B'
for i in range(len(headings)-1):
    chartShow2.add_series({
        "name":"=sheetOne"+"!$"+t+"$3",
        "categories":"=sheetOne"+"!$A$4:$A$7",
        "values":"=sheetOne"+"!$"+t+"$4:$"+t+"$7",
        # "fill":   {'color': 'red'}, # 设置填充颜色
    })
    t=chr(ord(t)+1)

chartShow2.set_title({"name":"叠图"})
chartShow2.set_x_axis({"name":"姓名"})
chartShow2.set_y_axis({"name":"总数"})
chartShow2.set_style(12)
chartShow2.set_size({'width':450,'height':250})

worksheet.insert_chart("H20",chartShow2)

3. 饼图

chartShow3 = workbook.add_chart({"type":"pie"})

chartShow3.add_series({
    #"name":"饼形图",
    "categories":"=sheetOne!$A$4:$A$7",
    "values":"=sheetOne!$C$4:$C$7",
    #定义各饼块的颜色
    "points":[
        {"fill":{"color":"yellow"}},
        {"fill":{"color":"blue"}},
        {"fill":{"color":"red"}},
        {"fill":{"color":"green"}}
    ],
    'data_labels': {
        # 显示数值
		'value': True, 
        # 显示百分比
        'percentage':True,
		'leader_lines': True, 
		'category': True,
		'separator': "\n",
		# 'border': {'color': '#7F7F7F','transparency': 50},	#使用transparency关键字来设置透明度
		# 'fill':   {'color': '#D9D9D9','transparency': 30},    #数据显示块颜色
        # 'font': {'rotation':-45}  # 文字倾斜度
	}, #显示标签
})

chartShow3.set_style(10)
chartShow3.set_size({'width':400,'height':250})
# 插入
worksheet.insert_chart("A20",chartShow3)

• 关闭workbook

workbook.close()

运行结果