/// <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();
}
}
public static void fixChartData(ChartPart cc, string nomSheet, List <int> elems, string colValeur, string colLegende)
{
Chart.Values v = cc.ChartSpace.Descendants <Chart.Values>().First();
//Formule de mon piechart
Chart.Formula f = v.Descendants <Chart.Formula>().First();
string formulaBis = "\'" + nomSheet + "\'!$" + colValeur + "$" + elems.ElementAt(0);
foreach (int i in elems.Skip(1))
{
formulaBis += ",\'" + nomSheet + "\'!$" + colValeur + "$" + i;
}
f.Text = formulaBis;
//Formule pour les legendes
Chart.CategoryAxisData cad = cc.ChartSpace.Descendants <Chart.CategoryAxisData>().First();
//Formule des legendes de mon piechart
Chart.Formula f2 = cad.Descendants <Chart.Formula>().First();
string formula2Bis = "\'" + nomSheet + "\'!$" + colLegende + "$" + elems.ElementAt(0);
foreach (int i in elems.Skip(1))
{
formula2Bis += ",\'" + nomSheet + "\'!$" + colLegende + "$" + i;
}
f2.Text = formula2Bis;
Chart.SeriesText st = cc.ChartSpace.Descendants <Chart.SeriesText>().First();
st.StringReference.Formula.Text = "";
}
/// <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);
}
}
public static void fixChartData2(ChartPart cc, string nomSheet, List <int> elems, string colValeur, string colLegende)
{
//ChartPart cc = wsp.GetPartsOfType<DrawingsPart>().First().ChartParts.First<ChartPart>();
Charts.Values v = cc.ChartSpace.Descendants <Charts.Values>().First();
//Formule de mon piechart
Charts.Formula f = v.Descendants <Charts.Formula>().First();
//string id = wbp.GetIdOfPart(wsp);
//string nom = "'" + wbp.Workbook.Descendants<Sheet>().Where(s => s.Id.Value.Equals(id)).First().Name + "'!";
string formulaBis = "\'" + nomSheet + "\'!$" + colValeur + "$" + elems.ElementAt(0);
foreach (int i in elems.Skip(1))
{
formulaBis += ",\'" + nomSheet + "\'!$" + colValeur + "$" + i;
}
f.Text = formulaBis;
//Formule pour les legendes
Charts.CategoryAxisData cad = cc.ChartSpace.Descendants <Charts.CategoryAxisData>().First();
//Formule des legendes de mon piechart
Charts.Formula f2 = cad.Descendants <Charts.Formula>().First();
string formula2Bis = "\'" + nomSheet + "\'!$" + colLegende + "$" + elems.ElementAt(0);
foreach (int i in elems.Skip(1))
{
formula2Bis += ",\'" + nomSheet + "\'!$" + colLegende + "$" + i;
}
f2.Text = formula2Bis;
Charts.SeriesText st = cc.ChartSpace.Descendants <Charts.SeriesText>().First();
st.StringReference.Formula.Text = "";
}
