Eric White » Topic: adding workbook to chart as an embeddedPackagePart

adding workbook to chart as an embeddedPackagePart

Tagged: chart

This topic contains 5 replies, has 3 voices, and was last updated by Anonymous 4 years, 2 months ago.

Viewing 6 posts - 1 through 6 (of 6 total)

Author

Posts
March 7, 2016 at 11:44 am #2483
hulk
Participant
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, 9 months ago by hulk.
March 9, 2016 at 3:49 am #2493

Eric White
Keymaster

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

March 9, 2016 at 12:37 pm #2496
hulk
Participant
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));
```
March 9, 2016 at 4:09 pm #2497
hulk
Participant
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, 9 months ago by hulk.
- This reply was modified 8 years, 9 months ago by hulk.
- This reply was modified 8 years, 9 months ago by hulk.
March 11, 2016 at 7:54 am #2509

Eric White
Keymaster

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.

October 20, 2020 at 12:40 pm #9820

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