PPT Chart data

andrew.coan · January 28, 2013, 7:50am

Hi,

We have an existing solution that uses automation of PowerPoint 2003 to update the datasheet of an embedded Chart (i.e. the standard chart object that is the default when you do Insert/Chart in PowerPoint).

Here is the code I use:

oDataSheet = oGraph.Application.DataSheet

With rstResult.Tables(0) '-- Recordset returned by a SQL query

iColumns = .Columns.Count

'-- Calculate the number of records in the set

iRows = .Rows.Count

End With

'Create an array and populate with data

Dim arrResult(iRows + 2, iColumns)

With rstResult.Tables(0)

'If the result hasn't been written

If bHeadings = False Then

For intCount = 0 To iColumns - 1

scurRow = .Columns(intCount).ColumnName

arrResult(1, 1 + intCount) = scurRow

Next intCount

bHeadings = True

intCurrent = 2

End If

For intRecord = 0 To iRows - 1

curRow = .Rows(intRecord)

For intCount = 0 To iColumns - 1

scurRow = curRow(intCount).ToString

If scurRow <> "" Then

arrResult(intCurrent + intRecord, 1 + intCount) = scurRow

Else

arrResult(intCurrent + intRecord, 1 + intCount) = " "

End If

Next intCount

Next intRecord

End With

With oDataSheet

If intPlotBy = 1 Then 'Chart is plotted by rows

For intColumns = 1 To iRows + 1

arrFormating(intColumns) = .Rows(intColumns).NumberFormat

If arrFormating(intColumns) = "" Then arrFormating(intColumns) = "General"

Next intColumns

Else 'Plotted by Columns

For intColumns = 1 To iColumns

arrFormating(intColumns) = .Columns(intColumns).NumberFormat

If arrFormating(intColumns) = "" Then arrFormating(intColumns) = "General"

Next intColumns

End If

.Cells.Clear()

For intRows = 1 To intCurrent - 1

For intColumns = 1 To intColMax

If intColumns = 1 Then

'.Cells(intRows, intColumns).NumberFormat = strNumForm

End If

If arrResult(intRows, intColumns) <> "" Then

.Cells(intRows, intColumns).Value = arrResult(intRows, intColumns)

Else

If intColumns = 1 Then

.Cells(intRows, intColumns).Value = ""

End If

'.Cells(intRows, intColumns).NumberFormat = strNumForm

Next intColumns

Next intRows

This solution has always been unstable due to the nature of automating a program that doesn't really want to be automated. I would really like to replace this with something like ASPOSE Slides.

My question is - will ASPOSE Slides work with my existing PowerPoint files and reference the objects - I have shed loads of existing objects

mudassir.fayyaz · January 28, 2013, 12:39pm

Hi Andrew,

Thanks for your interest in Aspose.Slides.

I have observed your requirements and like to share that you can access the charts or excel sheet data added as Ole frame using Aspose.Slides. Then using Aspose.Cells you can modify the data and update that in slide using Aspose.Slides. I have attached the sample application for your convenience that will help you understanding the concept. Please share, if I may help you further in this regard.

Many Thanks,

andrew.coan · January 29, 2013, 3:50am

Howdy,

That's really interesting! Does it only work with pptx files that have the excel style embedded charts of PowerPoint 2007 or can it be done in PowerPoint 2003 with the old MSChart embedded charts?

Kind regards

Andy

mudassir.fayyaz · January 29, 2013, 4:06am

Hi Andy,

Please observe the sample project shared. There are following two methods in it that deals with accessing and modifying the embedded ole objects in the presentation.

ChangePPTOleFrameData() - This is realted to PPT

ChangeOleFrameData() - This is related to PPTX

Many Thanks,

andrew.coan · January 29, 2013, 4:28am

OK, Thank you very much for getting back to me.

I created a test file and it finds the chart in my presentation!

But throws the error "Object reference not set to an instance of an object." when it gets to the line with an ***

'Reading object data in Workbook

Dim Wb As Workbook = Nothing

Dim msln As MemoryStream = New MemoryStream(ole.ObjectData)

msln.Position = 0

Wb = New Workbook(msln) ***

When I've run the code in debug mode I can see that msln memorystream has content and the ole object is a MS.Chart - see the dump here:

? ole

{Aspose.Slides.OleObjectFrame}

AlternativeText: ""

Anchor: {, Header:EsHeader, Type:F010, Version:0, Instance:0, Length:8}

AnimationSettings: {Aspose.Slides.AnimationSettings}

BaseShapeElements: {Length=0}

Brightness: 50

ColorType: Automatic {0}

ConnectionDirections: Nothing

ConnectionPoints: {Length=4}

ConnectionPointsDefined: False

ConnectionPointsInternal: {Length=4}

ConnectionSites: {Length=4}

ConnectionSitesRaw: {Length=4}

Contrast: 50

CropBottom: 0.0

CropLeft: 0.0

CropRight: 0.0

CropTop: 0.0

defaultConnectionPoints: {Length=4}

emptyConnectionPoints: {Length=0}

emptyShape: {Length=0}

FillFormat: {Aspose.Slides.FillFormat}

FlipH: False

FlipHorizontal: False

FlipV: False

FlipVertical: False

FollowColorScheme: Scheme {1}

Frame: {Œ Start Self: Œ, Header:EsHeader, Type:F004, Version:15, Instance:0, Length:210 ‹, Header:EsHeader, Type:F00A, Version:2, Instance:75, Length:8 ƒ, Header:EsHeader, Type:F00B, Version:3, Instance:13, Length:122 , Header:EsHeader, Type:F010, Version:0, Instance:0, Length:8 , Header:EsHeader, Type:F011, Version:15, Instance:0, Length:40 Œ End }

geoBottom: 21600

geoRight: 21600

HasExternalData: True

Height: 2560

HeightImpl: 2560

Hidden: False

IsMasterTextHolder: False

IsObjectIcon: False

IsObjectLink: False

IsPictureLinked: False

IsTextHolder: True

LineFormat: {Aspose.Slides.LineFormat}

Link: Nothing

LinkPathLong: ""

LinkPathShort: ""

m_animationSettings: {Aspose.Slides.AnimationSettings}

m_fillFormat: {Aspose.Slides.FillFormat}

m_lineFormat: {Aspose.Slides.LineFormat}

m_link: Nothing

m_parent: {Aspose.Slides.Slide}

m_shadowFormat: {Aspose.Slides.ShadowFormat}

m_tags: {Aspose.Slides.TagCollection}

MasterShape: {Aspose.Slides.Rectangle}

MasterShapeId: 2051

Name: "Content Placeholder 3"

ObjectClassName: "Microsoft Graph Chart"

ObjectData: {Length=26624}

ObjectIsIcon: False

ObjectIsLink: False

ObjectName: "Chart"

ObjectProgId: "MSGraph.Chart.8"

ObjectType: MicrosoftGraph {4}

Parent: {Aspose.Slides.Slide}

ParentSlide: {Aspose.Slides.Slide}

PictureFileName: ""

PictureId: 1

PictureLinked: False

Placeholder: Nothing

Protection: LockText {4}

Recolor: {Aspose.Slides.RecolorTable}

Rotation: 0

Rotation0_360: 0

ShadowFormat: {Aspose.Slides.ShadowFormat}

ShapeId: 1026

ShapeRectangle: {X = 975 Y = 1153 Width = 3811 Height = 2562}

SrcHeight: 2560

SrcWidth: 3809

SrcX: 975

SrcY: 1153

Tags: {Aspose.Slides.TagCollection}

TextFrame: Nothing

ThreeDFormat: {Aspose.Slides.ThreeDFormat}

TransparentColor: "{Name=0, ARGB=(0, 0, 0, 0)}"

Version: 0

Width: 3809

WidthImpl: 3809

X: 975

XImpl: 975

Y: 1153

YImpl: 1153

ZOrderPosition: 1

mudassir.fayyaz · January 29, 2013, 8:17am

Hi Andy,

Please share the sample presentation with embedded ole object in it for which you are getting the issue. Please also share the code in the form of running sample code if possible. It will help in delivering expedite response.

Many Thanks,

andrew.coan · January 29, 2013, 8:27am

Howdy,

File attached and here's my code (which is really your code from the ConsoleApplication1 sample)

Public Sub ChangePPTOleFrameData()

'Loading presentation with Ole frame

Dim pres As Presentation = New Presentation("c:\\bin\\Aspose Data\\CoanTest.ppt")

'Accessing slide with Ole frame

Dim slide As Slide = pres.Slides(0)

Dim ole As OleObjectFrame = Nothing

Dim shape As Shape = Nothing

'Traversing all shapes for Ole frame

For i As Integer = 0 To slide.Shapes.Count - 1

shape = slide.Shapes(i)

If TypeOf shape Is OleObjectFrame Then

ole = CType(shape, OleObjectFrame)

End If

'Reading object data in Workbook

Dim Wb As Workbook = Nothing

Dim msln As MemoryStream = New MemoryStream(ole.ObjectData)

msln.Position = 0

Wb = New Workbook(msln) 'This is where it goes bang.

'Modifying the workbook data

Wb.Worksheets(0).Cells.GetCell(0, 4).Value = "E"

Wb.Worksheets(0).Cells.GetCell(1, 4).Value = 12

Wb.Worksheets(0).Cells.GetCell(2, 4).Value = 14

Wb.Worksheets(0).Cells.GetCell(3, 4).Value = 15

Dim so1 As Aspose.Cells.OoxmlSaveOptions = New Aspose.Cells.OoxmlSaveOptions(Aspose.Cells.SaveFormat.Excel97To2003)

Dim msout As MemoryStream = New MemoryStream()

'Saving modified excel sheet

Wb.Save(msout, so1)

msout.Position = 0

'Changing Ole frame object data

ole.ObjectData = msout.ToArray()

End If

pres.Write("c:\bin\Aspose Data\CoanTest.ppt")

End Sub

mudassir.fayyaz · January 30, 2013, 12:06am

Hi Andy,

Thanks for sharing the sample code and presentation. I have observed the issue while loading the ole frame data in Workbook object. This issue lies in domain of Aspose.Cells and I am requesting my the concerned support team members to share his repose in this regard to help you further. They will share their feedback shortly.

Many Thanks,

amjad.sahi · January 30, 2013, 2:34am

Hi,

I am a representative of Aspose.Cells and would like to reply you regarding your issue/query.

I have tested your case/scenario using your file and I found your chart inside the slide is not an Excel chart. The chart is rather Microsoft Graph chart and cannot be opened in MS Excel either. To confirm this, you may add a line and try to save the Excel file, e.g

…

Dim msln As MemoryStream = New MemoryStream(ole.ObjectData)

File.WriteAllBytes(“e:\test2\mygraphchart.xls”,ole.ObjectData)

…

When you will open the file into Ms Excel, e.g 2007, it will not be opened rather it will give you an error:

“Cannot open Microsoft Graph chart gallery file”.

Aspose.Cells is originally desired to work with MS Excel files and relevant objects, so we do not support Microsoft Graph chart or OLE chart at the moment. If your chart is native Excel chart, then it will be read fine by Aspose.Cells component.

Thank you.

andrew.coan · January 30, 2013, 2:46am

Hi,

Yes I am well aware that these objects are not excel charts but the native to PowerPoint 2003 MS Chart objects. I had hoped to automate the update of their datasheet with ASPOSE. I did find a work around on this forum that uses the MSChart as a COM component which is not ideal. I shall try to update my example to use a PowerPoint 2007 file format that uses a Excel chart as it's default to see if that mets my needs and would make sense to purchase your products.

Cheers

Andy

mudassir.fayyaz · January 30, 2013, 3:28am

Hi Andy,

I also like to suggest that Aspose.Slides also supports creating charts as they are available in PowerPoint. You may also explore this area as well if it suits your needs. Please visit this documentation link section for your convenience.

Many Thanks,