使用Python读取Excel数据在PPT中创建图表

目录

• 引言
• 读取Excel数据在PPT中创建图表
• 将Excel图表以图片形式插入到幻灯片

from spire.presentation import Presentation, FileFormat, SlideSizeType, RectangleF, ChartType, ChartStyle, FillFormatType
from spire.xls import Workbook

# 创建Presentation实例
presentation = Presentation()

# 设置幻灯片尺寸
presentation.SlideSize.Type = SlideSizeType.Screen16x9

# 创建Workbook实例
workbook = Workbook()
# 加载Excel文件
workbook.LoadFromFile("示例.xlsx")

# 获取第一个工作表
sheet = workbook.Worksheets.get_Item(0)

# 向第一张幻灯片添加图表
rect = RectangleF.FromLTRB(null, 100, presentation.SlideSize.Size.Width - 50, presentation.SlideSize.Size.Height - 50)
slide = presentation.Slides.get_Item(0)
chart = slide.Shapes.AppendChart(ChartType.Area, rect)

# 清除默认的虚拟数据
chart.ChartData.Clear(null, 0, 5, 5)

# 将工作表的标题列和标题行复制到图表
for i in range(sheet.AllocatedRange.ColumnCount):
    chart.ChartData[0, i].Text = sheet.AllocatedRange[1, i + 1].Text
for j in range(sheet.AllocatedRange.RowCount - 1):
    chart.ChartData[j + 1, 0].Text = sheet.AllocatedRange.get_Item(j + 2, 1).Text

# 遍历数据行
for k in range(sheet.AllocatedRange.RowCount - 1):
    # 遍历数据列
    for l in range(sheet.AllocatedRange.ColumnCount - 1):
        # 设置图表数据
        chart.ChartData[k + 1, l + 1].NumberValue = sheet.AllocatedRange[k + 2, l + 2].NumberValue

# 设置图表标题
chart.ChartTitle.TextProperties.Text = sheet.Name
chart.ChartTitle.TextProperties.IsCentered = True
chart.ChartTitle.Height = 30
chart.HasTitle = True

# 设置系列标签和类别标签
chart.Series.SeriesLabel = chart.ChartData["B1", "C1"]
chart.Categories.CategoryLabels = chart.ChartData["A2", "A" + str(sheet.AllocatedRange.RowCount)]

# 设置系列值
chart.Series.get_Item(0).Values = chart.ChartData["B2", "B" + str(sheet.AllocatedRange.RowCount)]
chart.Series.get_Item(1).Values = chart.ChartData["C2", "C" + str(sheet.AllocatedRange.RowCount)]

# 设置数据轴的数字格式
chart.PrimaryValueAxis.NumberFormat = sheet.AllocatedRange[2, 2].NumberFormat

# 设置图表样式
chart.ChartStyle = ChartStyle.Style5

# 设置重叠和间距宽度
chart.OverLap = 50
chart.GapWidth = 200

# 保存演示文稿
presentation.SaveToFile("output/PresentationChartFromExcelData.pptx", FileFormat.Pptx2019)
presentation.Dispose()
workbook.Dispose()

from spire.presentation import Presentation, FileFormat, SlideSizeType, RectangleF, ShapeType, Stream
from spire.xls import Workbook

# 创建Presentation实例
presentation = Presentation()

# 设置幻灯片尺寸
presentation.SlideSize.Type = SlideSizeType.Screen16x9

# 创建Workbook实例
workbook = Workbook()
# 加载Excel文件
workbook.LoadFromFile("示例.xlsx")

# 获取第一个工作表
worksheet = workbook.Worksheets.get_Item(0)

# 将第一个工作表中的第一个图表保存为图像
imageStream = Stream(workbook.SaveChartAsImage(worksheet, 0).ToArray())

# 将图像嵌入到演示文稿中
imageData = presentation.Images.AppendStream(imageStream)

# 获取第一张幻灯片
slide = presentation.Slides.get_Item(0)

# 向幻灯片添加图像形状
rect = RectangleF.FromLTRB(null, 100, presentation.SlideSize.Size.Width - 50, presentation.SlideSize.Size.Height - 50)
slide.Shapes.AppendEmbedImageByImageData(ShapeType.Rectangle, imageData, rect)

# 保存演示文稿
presentation.SaveToFile("output/InsertExcelChartToPresentation.pptx", FileFormat.Pptx2019)
presentation.Dispose()
workbook.Dispose()