Hi,
I need help with reading embedded Excel data from Powerpoint presentation. Note that I’ve tried the most obvious approach and there was no success.
Can you please give me a minimal code block which I can use to achieve this?
@Test
public void embedded() throws Exception {
String path = "Sample PPT with embedded Excel.pptx"; //$NON-NLS-1$
com.aspose.slides.Presentation presentation;
try (InputStream testStream = new FileInputStream(path)) {
presentation = new com.aspose.slides.Presentation(testStream);
}
com.aspose.slides.ISlideCollection slides = presentation.getSlides();
for (com.aspose.slides.ISlide slide: slides) {
com.aspose.slides.IShapeCollection shapes = slide.getShapes();
for (com.aspose.slides.IShape shape: shapes) {
if ((shape instanceof com.aspose.slides.IOleObjectFrame)) {
com.aspose.slides.IOleObjectFrame ole = (com.aspose.slides.IOleObjectFrame) shape;
if(!ole.isObjectLink()
&& ole.getObjectProgId() != null
&& ole.getObjectProgId().startsWith("Excel.")){
byte[] bytes = ole.getEmbeddedData().getEmbeddedFileData();
try (InputStream excelStream = new ByteArrayInputStream(bytes)) {
com.aspose.cells.Workbook workbook = new com.aspose.cells.Workbook(excelStream);
com.aspose.cells.WorksheetCollection worksheets = workbook.getWorksheets();
for (com.aspose.cells.Worksheet worksheet: worksheets) {
com.aspose.cells.Cells cells = worksheet.getCells();
int maxRowIndex = cells.getMaxRow();
int maxColumnIndex = cells.getMaxColumn();
for (int i = 0; i < maxRowIndex; i++) {
for (int j = 0; j < maxColumnIndex; j++) {
com.aspose.cells.Cell cell = cells.get(i, j);
System.out.println(cell.getStringValue());
}
}
}
} catch (Exception e) {
throw new RuntimeException(e);
}
}
}
}
}
}
Your code is almost correct, and I was able to get it working with a few adjustments (see the example below).
One important observation: the presentation you shared does not contain any embedded Excel OLE objects. Therefore, the OLE-based code doesn’t find anything to process.
To verify this, I added an embedded Excel OLE object to the presentation, and the code successfully extracted its workbook.
However, your presentation does contain several charts. If your goal is to read the data behind those charts, the chart data API is the appropriate approach. The second example demonstrates that.
OLE Objects example:
package com.example;
import java.io.ByteArrayInputStream;
import java.io.FileInputStream;
import java.io.InputStream;
public class App {
public static void main(String[] args) throws Exception {
String path = "Sample PPT with embedded Excel.pptx"; //$NON-NLS-1$
com.aspose.slides.Presentation presentation;
try (InputStream testStream = new FileInputStream(path)) {
presentation = new com.aspose.slides.Presentation(testStream);
}
try {
com.aspose.slides.ISlideCollection slides = presentation.getSlides();
for (com.aspose.slides.ISlide slide: slides) {
com.aspose.slides.IShapeCollection shapes = slide.getShapes();
for (com.aspose.slides.IShape shape: shapes) {
if ((shape instanceof com.aspose.slides.IOleObjectFrame)) {
com.aspose.slides.IOleObjectFrame ole = (com.aspose.slides.IOleObjectFrame) shape;
if(!ole.isObjectLink()
&& ole.getObjectProgId() != null
&& ole.getObjectProgId().startsWith("Excel.")){
byte[] bytes = ole.getEmbeddedData().getEmbeddedFileData();
try (InputStream excelStream = new ByteArrayInputStream(bytes)) {
com.aspose.cells.Workbook workbook = new com.aspose.cells.Workbook(excelStream);
com.aspose.cells.WorksheetCollection worksheets = workbook.getWorksheets();
for (int w = 0; w < worksheets.getCount(); w++) {
com.aspose.cells.Worksheet worksheet = worksheets.get(w);
com.aspose.cells.Cells cells = worksheet.getCells();
int maxRowIndex = cells.getMaxRow();
int maxColumnIndex = cells.getMaxColumn();
for (int i = 0; i <= maxRowIndex; i++) {
for (int j = 0; j <= maxColumnIndex; j++) {
com.aspose.cells.Cell cell = cells.get(i, j);
System.out.println(cell.getStringValue());
}
}
}
} catch (Exception e) {
throw new RuntimeException(e);
}
}
}
}
}
} finally {
presentation.dispose();
}
}
}
Charts example:
package com.example;
import java.io.FileInputStream;
import java.io.InputStream;
public class App {
public static void main(String[] args) throws Exception {
String path = "Sample PPT with embedded Excel.pptx"; //$NON-NLS-1$
com.aspose.slides.Presentation presentation;
try (InputStream testStream = new FileInputStream(path)) {
presentation = new com.aspose.slides.Presentation(testStream);
}
try {
for (com.aspose.slides.ISlide slide : presentation.getSlides()) {
for (com.aspose.slides.IShape shape : slide.getShapes()) {
if (shape instanceof com.aspose.slides.IChart) {
com.aspose.slides.IChart chart = (com.aspose.slides.IChart) shape;
try {
com.aspose.slides.IChartDataWorkbook workbook = chart.getChartData().getChartDataWorkbook();
com.aspose.slides.IChartDataWorksheetCollection worksheets = workbook.getWorksheets();
for (com.aspose.slides.IChartDataWorksheet worksheet : worksheets) {
System.out.println("=== Slide " + slide.getSlideNumber()
+ " worksheet: " + worksheet.getName() + " ===");
int wsIndex = worksheet.getIndex();
for (int row = 0; row < 200; row++) {
Object[] rowData = new Object[50];
boolean hasData = false;
int lastNonNull = -1;
for (int col = 0; col < 50; col++) {
Object value = workbook.getCell(wsIndex, row, col).getValue();
rowData[col] = value;
if (value != null) {
hasData = true;
lastNonNull = col;
}
}
if (!hasData) break;
StringBuilder sb = new StringBuilder();
for (int col = 0; col <= lastNonNull; col++) {
if (col > 0) sb.append("\t");
sb.append(rowData[col]);
}
System.out.println(sb);
}
}
} catch (com.aspose.slides.exceptions.InvalidOperationException e) {
String extPath = chart.getChartData().getExternalWorkbookPath();
System.out.println("Slide " + slide.getSlideNumber()
+ " chart skipped (external): " + extPath);
}
}
}
}
} finally {
presentation.dispose();
}
}
}
I hope it helps.
Please contact us in case of any further questions.
Thank you so much @putrov , it does help and it works.
Now I have a few additional important questions:
Can I get com.aspose.cells.Workbook somehow from the chart that were in my presentation originally, as I really need it that way?
Is there a way to distinguish in code if IChart is backed by IChartDataWorkbook, so I can decide which additional code I should trigger to read the data behind the chart?
Since this charts are not embedded Excel OLE object, I would kindly ask you to give me some hint how do they even got created, what it is called as a feature of Powerpoint app? As if I knew that in first place, I would never try to read it the way I did. Before your answer, I knew for two types of charts or tables backed by excel data, one with external excel file link, and one with internal excel content, and both should be handled by OLE API approach.
Use chart.getChartData().readWorkbookStream() to obtain the binary workbook, then create a new com.aspose.cells.Workbook from a ByteArrayInputStream:
byte[] data = chart.getChartData().readWorkbookStream();
Workbook wb = new Workbook(new java.io.ByteArrayInputStream(data));
Determine whether the chart uses an embedded or external workbook with chart.getChartData().getDataSourceType(); it returns ChartDataSourceType.EmbeddedWorkbook for internal data and ChartDataSourceType.ExternalWorkbook for a linked file.
PowerPoint stores chart data in an embedded workbook part, not as an OLE object, which is why the data must be accessed via the workbook stream rather than OLE APIs.
Note: This is an automated response. If it doesn’t fully resolve your question, our support team will review your post and provide further assistance.
Here us a complete example of extracting chart data using readWorkbookStream method to get the workbook binary data, then creating a com.aspose.cells.Workbook object from it and retrieving info from the object:
I didn’t actually succeed in reading external workbooks with this code and your file, because I have just the pptx with no external files, but the embedded workbooks were retrieved successfully. So you can use this pattern if you really need using com.aspose.cells.Workbook.
Sorry if I got your last question wrong, if you mean creating charts in Powerpoint Presentations, you can refer to Microsoft docs, e.g. this one:
I still cannot access all the chart data, like chart on the right of first slide (Stock 1 to 20), then right chart on the third slide (two writings on the top), also chart on slide 6 text on top as well… Attached is presentation before and after edit using the code below
The charts that still cannot be fully accessed are not backed by embedded Excel workbooks inside the presentation. They are linked to external Excel files instead, for example:
Slide 1 right chart: external FCCRSLS_marketing_graphs_data.xlsx
Slide 3 right chart: external Fund Module Charts.xlsx
Slide 6 chart: external Telus_Short Position case study.xlsx
The current code only handles charts where chart.getChartData().readWorkbookStream() returns an embedded workbook. For these externally linked charts, that call throws InvalidOperationException, and the code immediately does continue, so the chart is skipped completely. As a result, cached chart labels such as Stock 1..20, series names, legends, and some top/callout texts are not modified.
There is also a small loop issue: getMaxRow() and getMaxColumn() return the last row/column indexes, so the loops should use <=; otherwise, the last row and last column are skipped.
To support these charts, the code needs a separate path for charts linked to external workbooks: either open the workbook from getExternalWorkbookPath() if accessible, or embed/replace the workbook in the presentation first. Also, slide-level text shapes, such as some callouts on slide 6, must be processed through slide.getShapes(), not only through chart.getUserShapes().
Yes, all clear now, thank you!
I was mislead by a fact that the chart on the right side of the first slide looks exactly as the one inside the embedded excel file for the chart on the left side, so I was confused why the right one was not modified as well.
@zpredojevic
Glad to hear everything is clear now. It’s easy to mix up charts that look identical but are sourced differently. If any new questions arise, we’re here to help.