P10.编程生成Excel内图表.md
#插入图片
#openpyxl插入图片
openpyxl.drawing.image
sheet.add_image()
例:
from openpyxl.drawing.image import Image
from openpyxl import load_workbook
workbook = load_workbook(filename='这是一个表格.xlsx')
sheet = workbook.active
logo = Image('梅克豆.png')
logo.height = 100
logo.width = 100
sheet.add_image(logo,'C1')
workbook.save(filename='这是一个表格.xlsx')
#生成图表
#插入柱状图
BarChart() Reference()
例:
from openpyxl import load_workbook
from openpyxl.chart import BarChart, Reference
workbook = load_workbook(filename='简单数据.xlsx')
sheet = workbook.active
#新建一个柱状图
chart = BarChart()
#设定数据引用范围
data = Reference(worksheet=sheet, min_row=1, max_row=7, min_col=2, max_col=3)#
#设定X轴项目名称
categories = Reference(sheet, min_col=1, min_row=2, max_row=7)
#给柱状图添加数据,titles_from_data数据源中是否有标题
chart.add_data(data, titles_from_data=True)
#设定x轴类目
chart.set_categories(categories)
sheet.add_chart(chart, 'E2')
workbook.save('简单数据.xlsx')
#插入折线图
Linechart()
例:
from openpyxl import load_workbook
from openpyxl.chart import LineChart, Reference
workbook = load_workbook(filename='简单数据.xlsx')
sheet = workbook.active
#新建一个折线图
chart = LineChart()
#设定数据引用范围
data = Reference(worksheet=sheet, min_row=20, max_row=21, min_col=1, max_col=13)#
#设定X轴项目名称
categories = Reference(sheet, min_col=19, min_row=2, max_row=13)
#给折线添加数据,from_rows数据源是横向的,titles_from_data数据源中是否有标题
chart.add_data(data, from_rows=True,titles_from_data=True)
#设定x轴类目
chart.set_categories(categories)
sheet.add_chart(chart, 'B23')
workbook.save('简单数据.xlsx')
#插入散点图
ScatterChart()
例:
from openpyxl.chart import Reference,ScatterChart,Series
chart_xy = ScatterChart()#散点图
chart_xy.title = "XY坐标散点图"
chart_xy.y_axis.title = 'x'
chart_xy.x_axis.title = 'y'
chart_xy.legend = None
chart_xy.x_axis.scaling.min = -5
chart_xy.y_axis.scaling.min = -3
chart_xy.x_axis.scaling.max = 30
chart_xy.y_axis.scaling.max = 3
chart_xy.height = 10
chart_xy.width = 20
x = Reference(sheet, min_col=3, min_row=table_row_start+1, max_row=row_index)
y = Reference(sheet, min_col=4, min_row=table_row_start+1, max_row=row_index)
s = Series(y, xvalues=x)
chart_xy.append(s)
s.marker.symbol = "circle"#'square', 'x', 'star', 'plus', 'diamond', 'circle', 'triangle', 'dash', 'auto', 'picture', 'dot'
s.marker.graphicalProperties.solidFill = "FF0000" # Marker filling
s.marker.graphicalProperties.line.solidFill = "FF0000" # Marker outline
s.marker.size = 3
s.graphicalProperties.line.noFill = True#是否连线
position = f"B2"
sheet.add_chart(chart_xy, position)
#列坐标字母和行数之间进行装换
get_column_letter(数字)
column_index_from_string(字母)
例:
from openpyxl.utils import get_column_letter, column_index_from_string
print(get_column_letter(2)) #b
print(column_index_from_string('B')) #2