C# OpenXML Embed Spreadsheet Chart in Word Document

Question

I have generated a spreadsheet with data and a chart based on the data using C# OpenXML.

I am now wanting to now embed this chart into a word document, so that when these reports are sent out, the charts can be edited if need be (Not an external link).

I have looked around for hours and can't find any consistent documentation for articles which achieve this. Most leads seem to talk about 'EmbeddedPackageParts'.

If anyone has any helpful articles or can provide some clarity it would be appreciated.

Cheers, Duncan.

Waddington

Create a very simple document with a chart. Save it. Insert a chart and save to a different name. Open the first file using the Open XML SDK Productivity Tool. Use the "compare" feature to compare to the second file. This will generate the code necessary to create the chart in the file; you can also see the underlying Office Open XML. That's how to start... – Olio

Answer 1

I ended up figuring this out!

Steps:

1. Generate xlsx file with my data in it.

2. Save the xlsx file locally

3. In my word document create a new chart part and generate the graph contents

   ChartPart wordChartPart = document.MainDocumentPart.AddNewPart<ChartPart>();
   string wordChartId = document.MainDocumentPart.GetIdOfPart(wordChartPart);
   WordDocumentBuilder.Workflows.SpreadsheetUtils.GenerateBarChartPart(wordChartPart, categories, dataRows);
   

4. Embed the spreadsheet in the ChartPart

   EmbeddedPackagePart embeddedObjectPart = wordChartPart.AddEmbeddedPackagePart(@"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
   
   using (FileStream stream = new FileStream(file, FileMode.Open))
   {
       byte[] documentBytes = new byte[stream.Length];
   
       stream.Read(documentBytes, 0, documentBytes.Length);
   
       using (BinaryWriter writer = new BinaryWriter(embeddedObjectPart.GetStream()))
       {
           writer.Write(documentBytes);
           writer.Flush();
       }
   }
   

5. I could then insert an Inline Drawing into my word document which referenced the chart part.

Waddington

@SachinthaNayanajith WordDocumentBuilder is a component I built to help faciliate the generation of the Word document. – Waddington

Could you outline what you're doing inside GenerateBarChartPart and how that's necessary for step 4? Also, is step 5 necessary to see the chart in the generated document? (Sorry if these are obvious to folks with OpenXML experience. I'm just getting started with DocumentFormat.OpenXML, doing some prototyping to see if my team can reasonably replace our use of EPPlus and Xceed DocX.) – Gossett

@DuncanPalmer - May I ask that you share your full code for this? I have tried the Open XML SDK Productivity Tool and various other paths. – Lastly

Answer 2

you can open both the new word document and the excel sheet that your charts are included currently. You can add some rich text content controls to the word document and insert the Chartparts of the excel sheet in those places. Make sure that your charts have titles. You can read the charts from the titles To append them to the word document you need to create a paragraph object and append it to a Run object and also you need to define an Inline also in order to append the charts. I hope you can make the things I said clear by looking at the following example.

private string WordReportGeneration(string docPath, string excelPath)
    {
        
        string[] chartTitles = new string[] {"","","","","","",.... };//Chart titles
        string[] bookMark = new string[] { "C1", "C2", "C3",..... };//rich text controls of the word doc

        for (int i = 0; i < chartTitles.Length; i++) //going through the chart title array
        {
            using (WordprocessingDocument myWordDoc = WordprocessingDocument.Open(docPath, true))
            {

                MainDocumentPart mainPart = myWordDoc.MainDocumentPart;
               
                SdtBlock sdt = null;
                mainPart.Document.Descendants<SdtBlock>().ToList().ForEach(b => {
                    var child = b.SdtProperties.GetFirstChild<Tag>();
                    if (child != null && child.Val.Equals(bookMark[i]))
                        sdt = b;
                });

                Paragraph p = sdt.SdtContentBlock.GetFirstChild<Paragraph>();
                p.RemoveAllChildren();

                Run r = new Run();
                p.Append(r);
                Drawing drawing = new Drawing();
                r.Append(drawing);


                Inline inline = new Inline(
                    new Extent()

                    { Cx = 5486400, Cy = 3200400 });

                using (SpreadsheetDocument mySpreadsheet = SpreadsheetDocument.Open(excelPath, true))
                {
                    WorkbookPart workbookPart = mySpreadsheet.WorkbookPart;
                    Sheet theSheet = workbookPart.Workbook.Descendants<Sheet>().FirstOrDefault(s => s.Name == "Report");

                    WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(theSheet.Id);

                    DrawingsPart drawingPart = worksheetPart.DrawingsPart;

                    ChartPart chartPart = (ChartPart)drawingPart.ChartParts.FirstOrDefault(x =>
                    x.ChartSpace.ChildElements[4].FirstChild.InnerText.Trim() == chartTitles[i]);

                    ChartPart importedChartPart = mainPart.AddPart<ChartPart>(chartPart);

                    string relId = mainPart.GetIdOfPart(importedChartPart);

                    DocumentFormat.OpenXml.Drawing.Spreadsheet.GraphicFrame frame = drawingPart.WorksheetDrawing.Descendants<DocumentFormat.OpenXml.Drawing.Spreadsheet.GraphicFrame>().First();

                    Graphic clonedGraphic = (Graphic)frame.Graphic.CloneNode(true);

                    ChartReference c = clonedGraphic.GraphicData.GetFirstChild<ChartReference>();

                    c.Id = relId;

                    DocProperties docPr = new DocProperties();

                    docPr.Name = "XXX";

                    docPr.Id = GetMaxDocPrId(mainPart) + 1;

                    inline.Append(docPr, clonedGraphic);
                    drawing.Append(inline);


                }
                myWordDoc.Save();
                myWordDoc.Close();
            }

        }

        return docPath;
    }

Lacuna