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
- 简单柱状图
#添加柱状图
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)
- 柱状叠图
#添加柱状图叠图子类型
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)
- 饼图
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()