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: