For the current project I'm working on, one of the requirements is that we have a PPT template complete with existing charts, text, formatting etc. Upon exporting the PPT from the web app, I need to programmatically update the charts with specific data.
I have it to the point where I'm manipulating the chart.xml on each chart, but in order to allow the ability to edit the actual data I'm going to need to update the embedded Excel workbook on each chart as well. As it stands, upon trying to edit the data, it pulls in the data from the embedded Excel doc and overwrites all the data I just swapped in to the chart.xml.
Googling around, it appears the only way I'm seeing to do this is at runtime, create a new Excel doc in the directory, populate that with the data I want, and then swap the new Excel doc for the old embedded Excel doc on the given chart.
Is there a way to do this without having to create an external Excel doc? Either way, would someone be able to post some sample code that would point me in the right direction to be able to figure out how to do this?
Why isn't " it pulls in the data from the embedded Excel doc and overwrites all the data I just swapped in to the chart.xml" a good thing? Especially given the fact that the chart should reflect what's in the embedded Excel spreadsheet. If you only change the embedded Excel doc, the chart (chart.xml) will rehydrate with when the pptx is opened in the PowerPoint client.
It's not a good thing in the context of how I'm only updating the chart.xml currently. The users want the ability to edit the data in the chart, and since I'm not currently updating the spreadsheet, the data I brought in from SQL gets overwritten.
I've recognized that updating the spreadsheet is the ideal way to go now, however, so I'm just looking around for some example to get started on how to do that programmatically as opposed to just directly updating the chart.xml. Googling around hasn't yielded anything helpful, so I was hoping to get some guidance with that.
Sorry for the confusion :) it's definitely not a bad thing in the grand scope of things, it's just bad in the context in the way I'm currently tackling the problem which, more or less, is the wrong way to go about doing what I want to ultimately accomplish.
Private Const documentRelationshipType As String = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument"
Private Const chartRelationshipType As String = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/chart"
Private Const packageRelationshipType As String = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/package"
Dim pptxFilePath = "C:\Users\me\Documents\Presentation1.pptx"
Using presentationPackage As Package = Package.Open(pptxFilePath, FileMode.OpenOrCreate, FileAccess.ReadWrite)
Dim documentRelationship As PackageRelationship = presentationPackage.GetRelationshipsByType(documentRelationshipType).FirstOrDefault
Dim documentUri As Uri = PackUriHelper.ResolvePartUri(New Uri("/", UriKind.Relative), documentRelationship.TargetUri)
Dim documentPart As PackagePart = presentationPackage.GetPart(documentUri)
Dim presentation = XElement.Load(New StreamReader(documentPart.GetStream))
Dim slideRIDs = presentation.<p:sldIdLst>.<p:sldId>.Select(Function(f) f.@r:id.ToString).ToArray
For i = 0 To slideRIDs.Count - 1
Dim slidePart As PackagePart = presentationPackage.GetPart(PackUriHelper.ResolvePartUri(documentPart.Uri, documentPart.GetRelationship(slideRIDs(i)).TargetUri))
Dim firstChartPartRelationship As PackageRelationship = slidePart.GetRelationshipsByType(chartRelationshipType).SingleOrDefault
If firstChartPartRelationship IsNot Nothing Then
Dim firstChartPart = presentationPackage.GetPart(PackUriHelper.ResolvePartUri(slidePart.Uri, slidePart.GetRelationship(firstChartPartRelationship.Id).TargetUri))
Dim excelPackagePartRelationship As PackageRelationship = firstChartPart.GetRelationshipsByType(packageRelationshipType).SingleOrDefault
Dim excelPackagePart = presentationPackage.GetPart(PackUriHelper.ResolvePartUri(firstChartPart.Uri, firstChartPart.GetRelationship(excelPackagePartRelationship.Id).TargetUri))
Using excelPackage = Package.Open(excelPackagePart.GetStream, FileMode.OpenOrCreate, FileAccess.ReadWrite)
documentRelationship = excelPackage.GetRelationshipsByType(documentRelationshipType).FirstOrDefault
documentUri = PackUriHelper.ResolvePartUri(New Uri("/", UriKind.Relative), documentRelationship.TargetUri)
documentPart = excelPackage.GetPart(documentUri)
Dim spreadsheet = XElement.Load(New StreamReader(documentPart.GetStream))
Dim firstWorksheetRID = spreadsheet.<s:sheets>.<s:sheet>.FirstOrDefault.@r:id
Dim firstWorksheetPart As PackagePart = excelPackage.GetPart(PackUriHelper.ResolvePartUri(documentPart.Uri, documentPart.GetRelationship(firstWorksheetRID).TargetUri))
Dim firstWorksheet = XElement.Load(firstWorksheetPart.GetStream)
Dim secondRowSecondColumn = firstWorksheet.<s:sheetData>.<s:row>.Skip(1).<s:c>.Skip(1).FirstOrDefault
secondRowSecondColumn.<s:v>.Value = 11
Using xw As XmlWriter = XmlWriter.Create(firstWorksheetPart.GetStream(FileMode.Create, FileAccess.Write))
Thank you! I use C# but this is more than enough to clue me in into the structure and method calls I need to make. Thanks again :) I'll take a stab at this today and let you know how it goes.