adding workbook to chart as an embeddedPackagePart

[hulkParticipant]

I have some data, I need to create workbook (it could be in memory, no need to create it physically on a drive) and later add it to chartpart as an EmbeddedPackagePart, could anyone post some sample code how to do it? I know how to get proper chart part, I know how add data to workbook using open xml, but I am not sure how to create workbook/spreadsheetpackage from scratch and later how to connect it (relations etc) properly with chart (and powerpoint presentation)

• This topic was modified 8 years, 8 months ago by  hulk.

[Eric WhiteKeymaster]

In general, you should never create a workbook / spreadsheet package from scratch. It is far better to start with an existing, blank workbook, or a workbook that has a prototype chart. You can keep the ‘template’ workbook in a .NET resource, or you can include it directly in your code encoded in a string as base64Ascii.

Screen-Cast: Converting Open XML files to/from Base 64 Encoded ASCII by using PowerTools for Open XML

While the following content is about WordprocessingML and PresentationML, the principles in it apply to updating charts in SpreadsheetML. Much of the code could be re-purposed to update charts in SpreadsheetML.

Updating Data for an Embedded Chart in an Open XML WordprocessingML Document

[hulkParticipant]

My workbook is now working properly.

I know how to update data in embedded workbooks, how to do it in external workbooks, but unfortunately I have to embed/attache one to the chart.

I managed to attach file into presentation package, unfortunately its name is “package.bin” (but when I unzip it then it looks ok), and I still do not know how to set appropriate relations. I have to also delete the old relations to external/embedded workbooks, if they exists.

I am using below code to attache workbook into presentation package:

            EmbeddedPackagePart embeddedObjectPart =chartPart.AddEmbeddedPackagePart(@"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

            embeddedObjectPart.FeedData(File.Open(tempDirectory + "\\sos.xlsx", FileMode.Open,FileAccess.ReadWrite));

[hulkParticipant]

Ok, it looks that it is working now, I’ve used below code:

List<ExternalRelationship> references = chartPart.ExternalRelationships.ToList();
            for (int i = chartPart.ExternalRelationships.Count() - 1; i >= 0; i--)
            {
                ReferenceRelationship refer = chartPart.ExternalRelationships.ElementAt(i);
                chartPart.DeleteReferenceRelationship(refer);
            }
            chartPart.ChartSpace.RemoveAllChildren<ExternalData>();

            string relationID = "rId99999";
            EmbeddedPackagePart embeddedPackagePart1 = chartPart.AddNewPart<EmbeddedPackagePart>("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", relationID);
            memoryStream.Seek(0, SeekOrigin.Begin);

            embeddedPackagePart1.FeedData(memoryStream);

            chartPart.CreateRelationshipToPart(embeddedPackagePart1, relationID);
            ExternalData externalData1 = new ExternalData() { Id = relationID };
            AutoUpdate autoUpdate1 = new AutoUpdate() { Val = false };
            externalData1.Append(autoUpdate1);
            chartPart.ChartSpace.Append(externalData1);

• This reply was modified 8 years, 8 months ago by  hulk.
• This reply was modified 8 years, 8 months ago by  hulk.
• This reply was modified 8 years, 8 months ago by  hulk.

[Eric WhiteKeymaster]

Regarding the issue that the name is “package.bin”, the name of the file in the zip is irrelevant. Open XML does not specify, nor does it care. The only thing that matters are the relationship type and content type. When you do this call:

`chartPart.AddNewPart(“application/vnd.openxmlformats-officedocument.spreadsheetml.sheet”, relationID)

This sets the relationship and content types correctly.

[Anonymous]

I’ve used below code:

List<ExternalRelationship> references = chartPart.ExternalRelationships.ToList();
for (int i = chartPart.ExternalRelationships.Count() – 1; i >= 0; i–)
{
ReferenceRelationship refer = chartPart.ExternalRelationships.ElementAt(i);
chartPart.DeleteReferenceRelationship(refer);
}
chartPart.ChartSpace.RemoveAllChildren<ExternalData>();

string relationID = “rId99999”;
EmbeddedPackagePart embeddedPackagePart1 = chartPart.AddNewPart<EmbeddedPackagePart>(“application/vnd.openxmlformats-officedocument.spreadsheetml.sheet”, relationID);
memoryStream.Seek(0, SeekOrigin.Begin);

embeddedPackagePart1.FeedData(memoryStream);

chartPart.CreateRelationshipToPart(embeddedPackagePart1, relationID);
ExternalData externalData1 = new ExternalData() { Id = relationID };
AutoUpdate autoUpdate1 = new AutoUpdate() { Val = false };
externalData1.Append(autoUpdate1);
chartPart.ChartSpace.Append(externalData1);
and it works now.

[Author]

Posts