Facing issue while linking external excel file to pre-generated charts

Aspose.Total Product Family
,
1

Hi Aspose Support Team,

I have a couple of queries related to Aspose.Slides and Aspose.Cells:

  1. Linking an External Excel File to a Pre-Generated Chart: I am using the setExternalWorkbook function to link an external Excel file to a chart. This works perfectly when the chart is generated programmatically using Aspose.Slides. However, when I try to use the same function to link an external Excel file to a pre-generated chart (already present in the PowerPoint slide), PowerPoint throws an error upon opening the file. The chart is removed from the slide, and PowerPoint indicates that the chart caused an issue. Could you please help me understand why this might be happening and how I can resolve this issue?

  2. Modifying Embedded Workbooks in Charts Generated by a Third-Party Tool (Seismic): We are using a third-party vendor, Seismic, to generate charts in PowerPoint. These charts have embedded workbooks. I need to access the embedded workbook of a Seismic-generated chart and perform the following actions:

    • Add multiple sheets to the embedded workbook.
    • Add additional data to the existing sheets in the workbook.
      Could you guide me on how to achieve this using Aspose.Slides (or Aspose.Cells, if required)?

Thank you for your support!

Best regards,
Manal Singla

2

@singlma

Can you please provide more details about the error message you receive when linking the external Excel file to the pre-generated chart in PowerPoint?

3

The following error is caused upon opening the powerpoint file:

“PowerPoint found a problem with content in “Sample_PPTX”. Powerpoint can attempt to repair the presentation. If you trust the source of this presentation, click repair.”

And post clicking repair, the chart from the slide is removed.

4

@singlma,

  1. We kindly request you to share a standalone Java code or application, along with the necessary resource files, to help us understand and address the issue. We will review it as soon as possible.

  2. Please see the document: Manage OLE|Aspose.Slides Documentation for your reference on how to change underlying (OLE) data for your reference. In case, you find any issue, please provide a sample PowerPoint PPT/PPTX file that contains the embedded workbook(s)? We will evaluate it soon.

P.S. Kindly zip the sample application and resource files before attaching them here.

5
  1. Following is the code to link an external excel file:
Presentation pres = new Presentation("C:\\Users\\singlma\\Downloads\\sample.pptx");
ISlide slide = pres.getSlides().get_Item(0);
String excelFilePath = "H:\\chart-generation-service\\src\\main\\resources\\ExternalWorkbook.xlsx";

IChart chart = null;
for (IShape shape : slide.getShapes()) {
    if (shape instanceof IChart) {
        chart = (IChart) shape;
        System.out.println("chart found.");
        break;
    }
}

if (chart == null) {
    System.out.println("No chart found.");
    return;
}

IChartData chartData = chart.getChartData();
chartData.setExternalWorkbook(excelFilePath, true);
pres.save(outputPath, com.aspose.slides.SaveFormat.Pptx);

The above code works when i create a presentation and add a chart using aspose but when i try to access an existing chart, it fails.

  1. The object on the slide is an instance of “IChart” not an (IOleObjectFrame), so I am trying to add some additional data to the embedded workbook of the chart using

readWorkbookStream() and writeWorkbookStream()

and writeWorkbookStream() takes quite a long time to update the workbook and upon opening the presentation and trying to access the embeddd workbook by clicking on “Edit Data” of the chart, i get the following error:

The linked file isn’t available. This error can occur if the linked file has been moved or hasn’t been saved. Embedding the data instead of linking it can help avoid this error, but the data won’t be updated automatically if it changes the source file.

6

@singlma,

Thanks for providing further details and code snippet.

One of my colleagues from Aspose.Slides team will evaluate your issue and get back to you with details soon, @andrey.potapov FYI. In the meantime, could you please zip and attach your sample files here. This would help us to evaluate your issue precisely and to consequently figure it out soon.

7

@singlma,
Please share the PowerPoint and Excel files, and specify the version of Aspose.Slides you used.

8

Could you just confirm, whether the IChart’s “setExternalWorkbook” function of Aspose.slides in Java(25.3) works for pre-generated Powerpoint charts? If yes, could you please provide a working code snippet for the same?

Below is the code I have used which is causing the above mentioned error:

String excelFilePath = "H:\\chart-generation-service\\src\\main\\resources\\ExternalWorkbook.xlsx";
Presentation pres = new Presentation("C:\\Users\\singlma\\Downloads\\samples\\powerpoint_sample_chart.pptx");

IChart chart = null;
for (IShape shape : slide.getShapes()) {
    if (shape instanceof IChart) {
        chart = (IChart) shape;
        System.out.println("chart found.");
        break;
    }
}

if (chart == null) {
    System.out.println("No chart found.");
    return;
}
IChartData chartData = chart.getChartData();
chartData.getSeries().clear();
chartData.getCategories().clear();
chartData.setExternalWorkbook(excelFilePath, true);

chartData.getCategories().add(chartData.getChartDataWorkbook().getCell(0, "A2"));
chartData.getCategories().add(chartData.getChartDataWorkbook().getCell(0, "A3"));
chartData.getCategories().add(chartData.getChartDataWorkbook().getCell(0, "A4"));
chartData.getCategories().add(chartData.getChartDataWorkbook().getCell(0, "A5"));

IChartSeries series1 = chartData.getSeries().add(chartData.getChartDataWorkbook().getCell(0, "B1"), chart.getType());
series1.getDataPoints().addDataPointForLineSeries(chartData.getChartDataWorkbook().getCell(0, "B2"));
series1.getDataPoints().addDataPointForLineSeries(chartData.getChartDataWorkbook().getCell(0, "B3"));
series1.getDataPoints().addDataPointForLineSeries(chartData.getChartDataWorkbook().getCell(0, "B4"));
series1.getDataPoints().addDataPointForLineSeries(chartData.getChartDataWorkbook().getCell(0, "B5"));

IChartSeries series2 = chartData.getSeries().add(chartData.getChartDataWorkbook().getCell(0, "C1"), chart.getType());
series2.getDataPoints().addDataPointForLineSeries(chartData.getChartDataWorkbook().getCell(0, "C2"));
series2.getDataPoints().addDataPointForLineSeries(chartData.getChartDataWorkbook().getCell(0, "C3"));
series2.getDataPoints().addDataPointForLineSeries(chartData.getChartDataWorkbook().getCell(0, "C4"));
series2.getDataPoints().addDataPointForLineSeries(chartData.getChartDataWorkbook().getCell(0, "C5"));

IChartSeries series3 = chartData.getSeries().add(chartData.getChartDataWorkbook().getCell(0, "D1"), chart.getType());
series3.getDataPoints().addDataPointForLineSeries(chartData.getChartDataWorkbook().getCell(0, "D2"));
series3.getDataPoints().addDataPointForLineSeries(chartData.getChartDataWorkbook().getCell(0, "D3"));
series3.getDataPoints().addDataPointForLineSeries(chartData.getChartDataWorkbook().getCell(0, "D4"));
series3.getDataPoints().addDataPointForLineSeries(chartData.getChartDataWorkbook().getCell(0, "D5"));



// Save the PowerPoint presentation
String basePath = "C:\\Users\\singlma\\Downloads\\";
String timestamp = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMdd_HHmmss"));
String outputPath = basePath + "random_test" + timestamp + ".pptx";
pres.save(outputPath, com.aspose.slides.SaveFormat.Pptx);

System.out.println("Presentation with external workbook linked to chart created successfully: " + outputPath);
9

@singlma,
I need some time to answer your question. I will get back to you as soon as possible.

10

@singlma,
Thank you for your patience. Unfortunately, I’ve found some issues related to the setExternalWorkbook method and am not yet ready to provide a working code example.

We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): SLIDESJAVA-39661

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

You can extract the workbook from the chart data using Aspose.Slides, change the workbook using Aspose.Cells, and rewrite the chart data using Aspose.Slides.

IChartData chartData = chart.getChartData();
byte[] workbookData = chartData.readWorkbookStream();

// Change the workbook data using Aspose.Cells here.

chartData.writeWorkbookStream(workbookData);

My colleagues from Aspose.Cells team will assist you in adding multiple sheets and additional data to the workbook.
@amjad.sahi FYI

11

Thankyou for your continued support. Please update me when you have a working example of setExternalWorkbook function for a pre-generated PowerPoint charts. I will await the resolution as per the terms outlined in your Free Support Policies.

12

@singlma,
We will keep you updated.

13

@singlma,

See the document on how to add or manage worksheets in the workbook via Aspose.Cells for Java for your reference.
https://docs.aspose.com/cells/java/manage-worksheets/

Moreover, see and browse the documents on how to add, update or manage data to the worksheet cells for your reference.
https://docs.aspose.com/cells/java/data/

14

@singlma,
Our developers have investigated the issue under ticket SLIDESJAVA-39661. Unfortunately, using the setExternalWorkbook method for the previously created charts with embedded workbooks is impossible. We suggest using the writeWorkbookStream method instead of setExternalWorkbook.

Presentation presentation = null;
try {
    presentation = new Presentation("sample.pptx");
    ISlide slide = presentation.getSlides().get_Item(0);
    IChart chart = (IChart) slide.getShapes().get_Item(0);

    byte[] xlsxData = Files.readAllBytes(Paths.get("data.xlsx"));

    IChartData chartData = chart.getChartData();
    chartData.writeWorkbookStream(xlsxData);

    presentation.save("output.pptx", SaveFormat.Pptx);
} finally {
    if (presentation != null) presentation.dispose();
}
15

Thanks for the clarification and the code snippet.

Understood that linking an external workbook to a pre-generated chart is currently not supported, and that writeWorkbookStream is the recommended approach for modifying the embedded workbook instead.

One final question before closing the thread- do you anticipate support for linking external workbooks to pre-existing charts being added in future releases of Aspose.Slides?

16

@singlma,
Unfortunately, the setExternalWorkbook method works only for new charts, as it does in PowerPoint. Remaking an old chart is difficult, and errors of the type you described are likely to occur.