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.

Answer 1

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;
    }

Answer 2

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.