/// <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 = ""; }