/// <summary>
/// Modify/Add data into chart XML
/// </summary>
/// <param name="column_index">Corresponds to the column index that needs to be modified in chart spreadsheet (Ex: A, B, C, ...)</param>
/// <param name="row_index">Corresponds to the column index that needs to be modified in excel </param>
/// <param name="new_value">Corresponds to the new value we need to insert to the cell </param>
protected override void ModifyChartXML_Data(string column_index, uint row_index, string new_value)
{
LineChartSeries linechart_series = chart_part.ChartSpace.Descendants <LineChartSeries>().Where(s => string.Compare(s.InnerText, worksheet_name + "!$" + column_index + "$1", true) > 0).First();
DocumentFormat.OpenXml.Drawing.Charts.Values v = linechart_series.Descendants <DocumentFormat.OpenXml.Drawing.Charts.Values>().FirstOrDefault();
NumberReference nr = v.Descendants <NumberReference>().First();
NumberingCache nc = nr.Descendants <NumberingCache>().First();
try
{
NumericPoint np = nc.Descendants <NumericPoint>().ElementAt((int)row_index - 2);
NumericValue nv = np.Descendants <NumericValue>().First();
nv.Text = new_value;
}
catch (Exception)
{
// Create new data and append to previous XML
nc.PointCount.Val = nc.PointCount.Val + 1;
NumericValue nv = new NumericValue(new_value);
NumericPoint np = new NumericPoint(nv);
np.Index = (uint)nc.Descendants <NumericPoint>().ToList().Count;
nc.Append(np);
// Change fomula range
DocumentFormat.OpenXml.Drawing.Charts.Formula f = nr.Descendants <DocumentFormat.OpenXml.Drawing.Charts.Formula>().FirstOrDefault();
f.Text = worksheet_name + "!$" + column_index + "$2:$" + column_index + "$" + GetRowIndexByNum((int)row_index - 2).ToString();
}
}
/// <summary>
/// Modify the worksheet (embedded into chart) range values
/// </summary>
/// <param name="result">The DataTable instance represent a table</param>
protected void ModifyChartRange(System.Data.DataTable result)
{
ChartSpace chartspace = chart_part.ChartSpace;
for (int i = 1; i < result.Columns.Count; i++)
{
BarChartSeries barchart_series = chart_part.ChartSpace.Descendants <BarChartSeries>().Where(s => string.Compare(s.InnerText, worksheet_name + "!$" + GetColumnIndexByNum(i) + "$1", true) > 0).First();
DocumentFormat.OpenXml.Drawing.Charts.Values val = barchart_series.Descendants <DocumentFormat.OpenXml.Drawing.Charts.Values>().FirstOrDefault();
NumberReference nr = val.Descendants <NumberReference>().First();
DocumentFormat.OpenXml.Drawing.Charts.Formula f = nr.Descendants <DocumentFormat.OpenXml.Drawing.Charts.Formula>().First();
f.Text = worksheet_name + "!$" + GetColumnIndexByNum(i) + "$2:$" + GetColumnIndexByNum(i) + "$" + GetRowIndexByNum(result.Rows.Count - 1);
}
}
/// <summary>
/// Sets the numbering cache.
/// </summary>
/// <param name="numberReference">The number reference.</param>
/// <param name="numberingCache">The numbering cache.</param>
/// <param name="pointCount">The point count.</param>
protected static void SetNumberingCache(NumberReference numberReference, out NumberingCache numberingCache, out PointCount pointCount)
{
numberingCache = numberReference.Descendants <NumberingCache>().FirstOrDefault();
if (numberingCache == null)
{
numberingCache = new NumberingCache();
numberReference.AppendChild <NumberingCache>(numberingCache);
}
pointCount = numberingCache.Descendants <PointCount>().FirstOrDefault();
if (pointCount == null)
{
pointCount = new PointCount();
numberingCache.AppendChild <PointCount>(pointCount);
}
pointCount.Val = new UInt32Value((uint)0);
numberingCache.FormatCode = new FormatCode("General");
}