Manage Chart Workbooks in Presentations with Python

Set Chart Data from a Workbook

Aspose.Slides provides methods to read and write chart data workbooks (which contain chart data edited with Aspose.Cells). Note: The chart data must be organized in the same way or have a structure similar to the source.

The following Python code demonstrates a sample operation:

import aspose.slides as slides

with slides.Presentation("chart.pptx") as presentation:
    chart = presentation.slides[0].shapes[0]

    data_stream = chart.chart_data.read_workbook_stream()

    chart.chart_data.series.clear()
    chart.chart_data.categories.clear()

    data_stream.seek(0)
    chart.chart_data.write_workbook_stream(data_stream)

Set a WorkBook Cell as a Chart Data Label

Sometimes you need chart labels that come directly from cells in the underlying data workbook. Aspose.Slides allows you to bind data labels to specific workbook cells so the label text always reflects the cell’s value. The example below shows how to enable value-from-cell labels and point selected labels to custom cells in the chart’s workbook.

  1. Create an instance of the Presentation class.
  2. Get a reference to the slide by index.
  3. Add a bubble chart with sample data.
  4. Access the chart series.
  5. Use a workbook cell as a data label.
  6. Save the presentation.

The following Python code shows how to set a workbook cell as a chart data label:

import aspose.slides as slides
import aspose.slides.charts as charts

# Instantiate the Presentation class that represents a presentation file.
with slides.Presentation() as presentation:
    slide = presentation.slides[0]

    chart = slide.shapes.add_chart(charts.ChartType.BUBBLE, 50, 50, 600, 400, True)

    series = chart.chart_data.series[0]

    series.labels.default_data_label_format.show_label_value_from_cell = True

    workbook = chart.chart_data.chart_data_workbook

    series.labels[0].value_from_cell = workbook.get_cell(0, "A10", "Label 0")
    series.labels[1].value_from_cell = workbook.get_cell(0, "A11", "Label 1")
    series.labels[2].value_from_cell = workbook.get_cell(0, "A12", "Label 2")

    presentation.save("chart.pptx", slides.export.SaveFormat.PPTX)

Manage Worksheets

The following Python code demonstrates how to use the worksheets property to access the worksheet collection:

import aspose.slides as slides
import aspose.slides.charts as charts

with slides.Presentation() as presentation:
    slide = presentation.slides[0]

    chart = slide.shapes.add_chart(charts.ChartType.PIE, 50, 50, 400, 500)

    workbook = chart.chart_data.chart_data_workbook
    for i in range(len(workbook.worksheets)):
        print(workbook.worksheets[i].name)

Specify the Data Source Type

The following Python code shows how to specify a data source type:

import aspose.slides as slides
import aspose.slides.charts as charts

with slides.Presentation() as presentation:
    slide = presentation.slides[0]

    chart = slide.shapes.add_chart(charts.ChartType.COLUMN_3D, 50, 50, 600, 400, True)

    series_name = chart.chart_data.series[0].name
    series_name.data_source_type = slides.charts.DataSourceType.STRING_LITERALS
    series_name.data = "LiteralString"

    series_name = chart.chart_data.series[1].name
    series_name.data = chart.chart_data.chart_data_workbook.get_cell(0, "B1", "NewCell")

    presentation.save("output.pptx", slides.export.SaveFormat.PPTX)

External Workbooks

Aspose.Slides supports using external workbooks as a data source for charts.

Set External Workbooks

By using the ChartData.set_external_workbook method, you can assign an external workbook to a chart as its data source. This method can also update the path to an external workbook if it has been moved.

Although you cannot edit data in workbooks stored on remote locations or resources, you can still use those workbooks as external data sources. If you provide a relative path for an external workbook, it is automatically converted to a full path.

The following Python code shows how to set an external workbook:

import aspose.slides as slides
import aspose.slides.charts as charts

with slides.Presentation() as presentation:
    slide = presentation.slides[0]

    chart = slide.shapes.add_chart(charts.ChartType.PIE, 50, 50, 400, 600, False)
    chart.chart_data.set_external_workbook("external_workbook.xlsx")

    presentation.save("chart_with_external_workbook.pptx", slides.export.SaveFormat.PPTX)

The update_chart_data parameter of the set_external_workbook method specifies whether the Excel workbook will be loaded.

  • When update_chart_data is set to False, only the workbook path is updated; the chart data is not loaded or refreshed from the target workbook. Use this setting when the target workbook does not exist or is unavailable.
  • When update_chart_data is set to True, the chart data is loaded and updated from the target workbook.

Create External Workbooks

By using the read_workbook_stream and set_external_workbook methods, you can either create an external workbook from scratch or convert an internal workbook to an external one.

This Python code demonstrates the external workbook creation process:

import pathlib
import aspose.slides as slides
import aspose.slides.charts as charts

workbook_path = "external_workbook.xlsx"

with slides.Presentation() as presentation:
    slide = presentation.slides[0]

    chart = slide.shapes.add_chart(charts.ChartType.PIE, 50, 50, 400, 600)

    workbook_data = chart.chart_data.read_workbook_stream().read()

    with open(workbook_path, "wb") as file_stream:
        file_stream.write(workbook_data)

    full_path = str(pathlib.Path(workbook_path).resolve())
    chart.chart_data.set_external_workbook(full_path)

    presentation.save("chart_with_external_workbook.pptx", slides.export.SaveFormat.PPTX)

Get the External Data Source Workbook Path for a Chart

Sometimes a chart’s data is linked to an external Excel workbook rather than the presentation’s embedded data. With Aspose.Slides, you can inspect the chart’s data source and, if it’s an external workbook, read the full workbook path.

  1. Create an instance of the Presentation class.
  2. Get a reference to the slide by its index.
  3. Get a reference to the chart shape.
  4. Obtain the source (ChartDataSourceType) that represents the chart’s data source.
  5. Check whether the source type matches the external workbook data source type.

The following Python code demonstrates the operation:

import aspose.slides as slides
import aspose.slides.charts as charts

with slides.Presentation("chart_with_external_workbook.pptx") as presentation:
    chart = presentation.slides[0].shapes[0]
    source_type = chart.chart_data.data_source_type
    if source_type == charts.ChartDataSourceType.EXTERNAL_WORKBOOK:
        print(chart.chart_data.external_workbook_path)

Edit Chart Data

You can edit data in external workbooks the same way you edit data in internal workbooks. If an external workbook cannot be loaded, an exception is thrown.

import aspose.slides as slides

with slides.Presentation("sample.pptx") as presentation:
    chart = presentation.slides[0].shapes[0]
    chart.chart_data.series[0].data_points[0].value.as_cell.value = 100
    presentation.save("output.pptx", slides.export.SaveFormat.PPTX)
Format Charts in Presentations Using Python Animate PowerPoint Charts in Python