/// <summary>
/// Gets the <see cref="DrawingSpreadsheet.TwoCellAnchor"/> that is used to host the
/// <see cref="DrawingSpreadsheet.GraphicFrame"/> in which the <see cref="ChartPart"/> resides.
/// </summary>
/// <param name="chartPart">The <see cref="ChartPart"/></param>
/// <returns>The hosting <see cref="DrawingSpreadsheet.TwoCellAnchor"/></returns>
private static DrawingSpreadsheet.TwoCellAnchor GetHostingTwoCellAnchor(ChartPart chartPart)
{
// Get the chart reference id for the chartPart
string chartRefId = GetChartReferenceId(chartPart);
// DrawingPart is parent of ChartPart, and WorksheetPart parent of DrawingParts
var drawingsPart = (DrawingsPart)chartPart.GetParentParts().FirstOrDefault();
// Get chart reference which matches reference id of supplied chart part.
DrawingCharts.ChartReference chartRef = drawingsPart.WorksheetDrawing.Descendants <DrawingCharts.ChartReference>().FirstOrDefault(cr => cr.Id == chartRefId);
// Work bak up to, and return, TwoCellAnchor
DrawingSpreadsheet.TwoCellAnchor anchor = null;
if (chartRef != null)
{
anchor = chartRef.Ancestors <DrawingSpreadsheet.TwoCellAnchor>().FirstOrDefault();
}
return(anchor);
}
/// <summary>
/// Updates a Chart and its underlying embedded SpreadsheetDocument with new data.
/// </summary>
/// <param name="chartRef">
/// The reference to the chart to be updated</param>
/// <param name="doc">
/// The WordprocessingDocument containing the chart to be updated.</param>
/// <param name="sheetName">
/// The name of the Excel worksheet where the chart data originates from.
/// Used for updating the chart's cell references.</param>
/// <param name="json">
/// String in JSON format representing the tabular data for updating the Chart's cached data points.
/// The JSON object must contain a "fields" attribute as an array containing the field/column names.
/// The JSON object must contain a "rows" attribute as an array of arrays representing the rows and their values, with values matching the same order and cardinality of the field names.
/// This is the same data as the underlying Excel spreadsheet contents.</param>
/// <returns>Returns the updated WordprocessingDocument</returns>
public static void UpdateChart(drc.ChartReference chartRef, WordprocessingDocument doc, string sheetName, string json)
{
if ((json == null) || (json == String.Empty))
{
json = "{\"fields\": [], \"rows\": []}";
}
//Splunk JSON data is a series of objects consisting of multiple key(column)/value(row) pairs in the result attribute.
dynamic input = JsonConvert.DeserializeObject <dynamic>(json);
//No results for the chart so we will remove and replace it with an error.
if (input["rows"].Count == 0)
{
//Build a replacement for the Content Control's contents with an error message.
OpenXmlElement targetContent = new wp.Paragraph(new wp.Run(new wp.Text("Chart unavailable. No results found.")));
//Remove the chart from the document
doc.MainDocumentPart.DeletePart(chartRef.Id);
//Insert our replacement Content Control after
var cc = chartRef.Ancestors().Where(e => e.LocalName.StartsWith("sdt")).FirstOrDefault();
cc.RemoveAllChildren();
cc.Append(targetContent);
}
else
{
ChartPart chart = (ChartPart)doc.MainDocumentPart.GetPartById(chartRef.Id);
chart.ChartSpace.GetFirstChild <drc.ExternalData>().AutoUpdate.Val = true;
// Update Chart
chart = chart.Update(json, sheetName);
SpreadsheetDocument ssdoc = SpreadsheetDocument.Open(chart.EmbeddedPackagePart.GetStream(FileMode.Open, FileAccess.ReadWrite), true);
ssdoc.Update(json, sheetName);
ssdoc.WorkbookPart.Workbook.Save();
ssdoc.Close();
Debug.WriteLine(String.Format("{0}: Chart and Spreadsheet updated successfully.", sheetName));
}
}
