Chart Workbook
Set Chart Data from Workbook
Aspose.Slides provides the ReadWorkbookStream and WriteWorkbookStream methods that allow you to read and write chart data workbooks (containing chart data edited with Aspose.Cells). Note that the chart data has to be organized in the same manner or must have a structure similar to the source.
auto pres = System::MakeObject<Presentation>(u"chart.pptx");
auto chart = System::ExplicitCast<Chart>(pres->get_Slides()->idx_get(0)->get_Shapes()->idx_get(0));
auto data = chart->get_ChartData();
System::SharedPtr<System::IO::MemoryStream> stream = data->ReadWorkbookStream();
data->get_Series()->Clear();
data->get_Categories()->Clear();
stream->set_Position(0);
data->WriteWorkbookStream(stream);
This C++ code demonstrates the operation to set a chart data workbook:
auto pres = System::MakeObject<Presentation>(u"Test.pptx");
auto chart = pres->get_Slides()->idx_get(0)->get_Shapes()->AddChart(Charts::ChartType::Pie, 50.0f, 50.0f, 500.0f, 400.0f);
chart->get_ChartData()->get_ChartDataWorkbook()->Clear(0);
intrusive_ptr<Aspose::Cells::IWorkbook> workbook;
try
{
workbook = Aspose::Cells::Factory::CreateIWorkbook(new String("a1.xlsx"));
}
catch (Aspose::Cells::Systems::Exception& ex)
{
System::Console::Write(System::String::FromWCS(ex.GetMessageExp()->value()));
}
intrusive_ptr<MemoryStream> cellsOutputStream = new Aspose::Cells::Systems::IO::MemoryStream();
workbook->Save(cellsOutputStream, Aspose::Cells::SaveFormat_Xlsx);
cellsOutputStream->SetPosition(0);
System::SharedPtr<System::IO::MemoryStream> msout = ToSlidesMemoryStream(cellsOutputStream);
chart->get_ChartData()->WriteWorkbookStream(msout);
chart->get_ChartData()->SetRange(u"Sheet1!$A$1:$B$9");
auto series = chart->get_ChartData()->get_Series()->idx_get(0);
series->get_ParentSeriesGroup()->set_IsColorVaried(true);
pres->Save(u"response2.pptx", Export::SaveFormat::Pptx);
Set WorkBook Cell as Chart DataLabel
- Create an instance of the Presentation class.
- Get a slide’s reference through its index.
- Add a Bubble chart with some data.
- Access the chart series.
- Set the workbook cell as a data label.
- Save the presentation.
This C++ code shows you to set a workbook cell as a chart data label:
System::String lbl0 = u"Label 0 cell value";
System::String lbl1 = u"Label 1 cell value";
System::String lbl2 = u"Label 2 cell value";
// Instantiates a Presentation class that represents a presentation file
auto pres = System::MakeObject<Presentation>(u"chart2.pptx");
auto slide = pres->get_Slides()->idx_get(0);
auto chart = pres->get_Slides()->idx_get(0)->get_Shapes()->AddChart(ChartType::Bubble, 50.0f, 50.0f, 600.0f, 400.0f, true);
auto series = chart->get_ChartData()->get_Series();
series->idx_get(0)->get_Labels()->get_DefaultDataLabelFormat()->set_ShowLabelValueFromCell(true);
auto wb = chart->get_ChartData()->get_ChartDataWorkbook();
series->idx_get(0)->get_Labels()->idx_get(0)->set_ValueFromCell(wb->GetCell(0, u"A10", System::ObjectExt::Box<System::String>(lbl0)));
series->idx_get(0)->get_Labels()->idx_get(1)->set_ValueFromCell(wb->GetCell(0, u"A11", System::ObjectExt::Box<System::String>(lbl1)));
series->idx_get(0)->get_Labels()->idx_get(2)->set_ValueFromCell(wb->GetCell(0, u"A12", System::ObjectExt::Box<System::String>(lbl2)));
pres->Save(u"resultchart.pptx", SaveFormat::Pptx);
Manage Worksheets
This C++ code demonstrates an operation where the IChartDataWorkbook.Worksheets property is used to access a worksheet collection:
auto pres = System::MakeObject<Presentation>();
auto slide = pres->get_Slides()->idx_get(0);
auto chart = slide->get_Shapes()->AddChart(ChartType::Pie, 50.0f, 50.0f, 400.0f, 500.0f);
auto workbook = chart->get_ChartData()->get_ChartDataWorkbook();
auto worksheets = workbook->get_Worksheets();
for (auto ws : System::IterateOver(worksheets))
System::Console::WriteLine(ws->get_Name());
Specify Data Source Type
This C++ code shows you how to specify a type for a data source:
auto pres = System::MakeObject<Presentation>();
auto chart = pres->get_Slides()->idx_get(0)->get_Shapes()->AddChart(ChartType::Column3D, 50.0f, 50.0f, 600.0f, 400.0f, true);
auto chartData = chart->get_ChartData();
auto val = chart->get_ChartData()->get_Series()->idx_get(0)->get_Name();
val->set_DataSourceType(DataSourceType::StringLiterals);
val->set_Data(System::ObjectExt::Box<System::String>(u"LiteralString"));
val = chartData->get_Series()->idx_get(1)->get_Name();
val->set_Data(chartData->get_ChartDataWorkbook()->GetCell(0, u"B1", System::ObjectExt::Box<System::String>(u"NewCell")));
pres->Save(u"pres.pptx", SaveFormat::Pptx);
External Workbook
Create External Workbook
Using the ReadWorkbookStream
and SetExternalWorkbook
methods, you can either create an external workbook from scratch or make an internal workbook external.
This C++ code demonstrates the external workbook creation process:
auto pres = System::MakeObject<Presentation>();
const System::String workbookPath = u"externalWorkbook1.xlsx";
auto chart = pres->get_Slides()->idx_get(0)->get_Shapes()->AddChart(ChartType::Pie, 50.0f, 50.0f, 400.0f, 600.0f);
auto chartData = chart->get_ChartData();
{
System::SharedPtr<System::IO::FileStream> fileStream = System::MakeObject<System::IO::FileStream>(workbookPath, System::IO::FileMode::Create);
System::ArrayPtr<uint8_t> workbookData = chartData->ReadWorkbookStream()->ToArray();
fileStream->Write(workbookData, 0, workbookData->get_Length());
}
chartData->SetExternalWorkbook(System::IO::Path::GetFullPath(workbookPath));
pres->Save(u"externalWorkbook.pptx", SaveFormat::Pptx);
Set External Workbook
Using the IChartData.SetExternalWorkbook
method, you can assign an external workbook to a chart as its data source. This method can also be used to update a path to the external workbook (if the latter has been moved).
While you cannot edit the data in workbooks stored in remote locations or resources, you can still use such workbooks as an external data source. If the relative path for an external workbook is provided, it gets converted to a full path automatically.
This C++ code shows you how to set an external workbook:
auto pres = System::MakeObject<Presentation>();
auto chart = pres->get_Slides()->idx_get(0)->get_Shapes()->AddChart(ChartType::Pie, 50.0f, 50.0f, 400.0f, 600.0f, false);
auto chartData = chart->get_ChartData();
chartData->SetExternalWorkbook(System::IO::Path::GetFullPath(u"externalWorkbook.xlsx"));
chartData->get_Series()->Add(chartData->get_ChartDataWorkbook()->GetCell(0, u"B1"), ChartType::Pie);
auto dataPoints = chartData->get_Series()->idx_get(0)->get_DataPoints();
auto workbook = chartData->get_ChartDataWorkbook();
dataPoints->AddDataPointForPieSeries(workbook->GetCell(0, u"B2"));
dataPoints->AddDataPointForPieSeries(workbook->GetCell(0, u"B3"));
dataPoints->AddDataPointForPieSeries(workbook->GetCell(0, u"B4"));
auto categories = chartData->get_Categories();
categories->Add(workbook->GetCell(0, u"A2"));
categories->Add(workbook->GetCell(0, u"A3"));
categories->Add(workbook->GetCell(0, u"A4"));
pres->Save(u"Presentation_with_externalWorkbook.pptx", SaveFormat::Pptx);
The updateChartData
parameter (under the SetExternalWorkbook
method) is used to specify whether an excel workbook will be loaded or not.
- When
updateChartData
value is set tofalse
, only the workbook path gets updated—the chart data will not be loaded or updated from the target workbook. You may want to use this setting when in a situation where the target workbook is nonexistent or unavailable. - When
updateChartData
value is set totrue
, the chart data gets updated from the target workbook.
auto pres = System::MakeObject<Presentation>();
auto slide = pres->get_Slides()->idx_get(0);
auto chart = slide->get_Shapes()->AddChart(ChartType::Pie, 50.0f, 50.0f, 400.0f, 600.0f, true);
System::SharedPtr<IChartData> chartData = chart->get_ChartData();
System::SharedPtr<ChartData> concreteChartData = System::AsCast<ChartData>(chartData);
concreteChartData->SetExternalWorkbook(u"http://path/doesnt/exists", false);
pres->Save(u"SetExternalWorkbookWithUpdateChartData.pptx", SaveFormat::Pptx);
Get Chart External Data Source Workbook Path
- Create an instance of the Presentation class.
- Get a slide’s reference through its index.
- Create an object for the chart shape.
- Create an object for the source (
ChartDataSourceType
) type that represents the chart’s data source. - Specify the relevant condition based on the source type being the same as the external workbook data source type.
This C++ code demonstrates the operation:
auto pres = System::MakeObject<Presentation>(u"pres.pptx");
auto slide = pres->get_Slides()->idx_get(1);
auto chart = System::ExplicitCast<IChart>(slide->get_Shapes()->idx_get(0));
ChartDataSourceType sourceType = chart->get_ChartData()->get_DataSourceType();
if (sourceType == ChartDataSourceType::ExternalWorkbook)
{
System::String path = chart->get_ChartData()->get_ExternalWorkbookPath();
}
// Saves the presentation
pres->Save(u"Result.pptx", SaveFormat::Pptx);
Edit Chart Data
You can edit the data in external workbooks the same way you make changes to the contents of internal workbooks. When an external workbook cannot be loaded, an exception is thrown.
This C++ code is an implementation of the described process:
const String templatePath = u"../templates/presentation.pptx";
const String outPath = u"../out/presentation-out.pptx";
System::SharedPtr<Presentation> pres = System::MakeObject<Presentation>(templatePath);
System::SharedPtr<Aspose::Slides::Charts::IChart> chart = System::AsCast<Aspose::Slides::Charts::IChart>(pres->get_Slides()->idx_get(0)->get_Shapes()->idx_get(0));
System::SharedPtr<Aspose::Slides::Charts::ChartData> chartData = System::ExplicitCast<Aspose::Slides::Charts::ChartData>(chart->get_ChartData());
chartData->get_Series()->idx_get(0)->get_DataPoints()->idx_get(0)->get_Value()->get_AsCell()->set_Value(System::ObjectExt::Box<int32_t>(100));
pres->Save(outPath, Aspose::Slides::Export::SaveFormat::Pptx);