How to add chart data in Open XML C# for Word?
<p>Am trying to change data for an already existing word file. I've had success in manipulating data in embedded Excel file, but somehow, this data is not being used in Word file. Moreover, in Excel file, I see the green marker before the last row, probably signifying that the last row is still not being considered for the data. Can someone please help in handling the issue?</p>

<p>I did referred to lot of links from ericwhite.com / openxmldeveloper.org but had no luck.</p>

<p>The code is below:</p>

<pre><code>    public void ReplaceChartValues(string placeholderCaption, System.Data.DataTable chartData, string newCaption)
    {
        if (wordDocument != null)
        {
            try
            {
                ChartPart target = mainDocumentPart
                    .ChartParts
                    .Where(r =&gt; r
                        .ChartSpace
                        .GetFirstChild&lt;Chart&gt;()
                        .Title
                        .InnerText
                        .StartsWith(placeholderCaption)
                    )
                    .FirstOrDefault();

                if (target != null)
                {
                    target
                        .ChartSpace
                        .GetFirstChild&lt;Chart&gt;()
                        .Title
                        .ChartText
                        .RichText
                        .GetFirstChild&lt;DocumentFormat.OpenXml.Drawing.Paragraph&gt;()
                        .GetFirstChild&lt;DocumentFormat.OpenXml.Drawing.Run&gt;()
                        .Text
                        .Text = newCaption;

                    ExternalData externalData =
                        target
                        .ChartSpace
                        .Elements&lt;ExternalData&gt;()
                        .FirstOrDefault();

                    if (externalData != null)
                    {
                        EmbeddedPackagePart embeddedPackagePart =
                            (EmbeddedPackagePart)
                            target
                            .Parts
                            .Where(r =&gt; r.RelationshipId == externalData.Id)
                            .FirstOrDefault()
                            .OpenXmlPart;

                        if (embeddedPackagePart != null)
                        {
                            using (Stream stream = embeddedPackagePart.GetStream())
                            {
                                using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(stream, true))
                                {
                                    DocumentFormat.OpenXml.Spreadsheet.Sheet worksheet = (DocumentFormat.OpenXml.Spreadsheet.Sheet)spreadsheetDocument
                                        .WorkbookPart
                                        .Workbook
                                        .Sheets
                                        .FirstOrDefault();

                                    WorksheetPart worksheetPart = (WorksheetPart)
                                        spreadsheetDocument
                                        .WorkbookPart
                                        .Parts
                                        .Where(r =&gt; r.RelationshipId == worksheet.Id)
                                        .FirstOrDefault()
                                        .OpenXmlPart;

                                    DocumentFormat.OpenXml.Spreadsheet.SheetData sheetData =
                                        worksheetPart
                                        .Worksheet
                                        .Elements&lt;DocumentFormat.OpenXml.Spreadsheet.SheetData&gt;()
                                        .FirstOrDefault();

                                    var existingRows = sheetData
                                        .Elements&lt;DocumentFormat.OpenXml.Spreadsheet.Row&gt;()
                                        .Skip(1)
                                        .ToArray();

                                    for (int ctr = 0; ctr &lt; existingRows.Length; ctr++)
                                    {
                                        sheetData
                                            .RemoveChild&lt;DocumentFormat.OpenXml.Spreadsheet.Row&gt;(existingRows[ctr]);
                                    }

                                    for (int ctr = 0; ctr &lt; chartData.Rows.Count; ctr++)
                                    {
                                        DocumentFormat.OpenXml.Spreadsheet.Row newRecord = new DocumentFormat.OpenXml.Spreadsheet.Row();

                                        for (int ctr2 = 0; ctr2 &lt; chartData.Columns.Count; ctr2++)
                                        {
                                            DocumentFormat.OpenXml.Spreadsheet.Cell newCell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                                            DocumentFormat.OpenXml.Spreadsheet.CellValue newCellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue();

                                            if (chartData.Columns[ctr2].DataType == typeof(DateTime))
                                            {
                                                newCellValue.Text = Convert.ToDateTime(chartData.Rows[ctr][ctr2]).ToString("MM/dd/yyyy");
                                                newCell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Date;
                                            }
                                            else if (chartData.Columns[ctr2].DataType == typeof(decimal))
                                            {
                                                newCellValue.Text = chartData.Rows[ctr][ctr2].ToString();
                                                newCell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number;
                                            }
                                            else
                                            {
                                                newCellValue.Text = chartData.Rows[ctr][ctr2].ToString();
                                                newCell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                                            }
                                            newCell.AppendChild(newCellValue);

                                            newRecord.AppendChild(newCell);
                                        }

                                        sheetData.AppendChild(newRecord);
                                    }

                                    spreadsheetDocument.Save();
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception)
            {
            }
            finally
            {
                // Save the document.
                mainDocumentPart.Document.Save();
            }
        }
    }
</code></pre>

<p><a href="https://i.stack.imgur.com/mqvZD.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/mqvZD.png" alt="Embedded Excel not considering last line"></a></p>