/// <summary> /// Updates the colour of a <see cref="BarChartSeries" /> so it matches a <see cref="SolidColorBrush" /> /// </summary> /// <param name="series">The series.</param> /// <param name="brush">The brush.</param> private static void UpdateSeriesColour(this BarChartSeries series, SolidColorBrush brush) { var chartShapeProperties = series.Descendants <ChartShapeProperties>().FirstOrDefault(); if (chartShapeProperties == null) { chartShapeProperties = new ChartShapeProperties(); // if there's a series text then insert afterwards // the series title, this is the name of the column header var seriesText = series.Descendants <SeriesText>().FirstOrDefault(); if (seriesText == null) { series.InsertAt <ChartShapeProperties>(chartShapeProperties, 0); } else { series.InsertAfter <ChartShapeProperties>(chartShapeProperties, seriesText); } } // Determine if the series has a SolidFill SolidFill seriesSolidFill = chartShapeProperties.Elements <SolidFill>().FirstOrDefault(); if (seriesSolidFill == null) { // No fill, so create one so we can set the colour seriesSolidFill = new SolidFill(); seriesSolidFill.UpdateSolidFill((SolidColorBrush)brush); chartShapeProperties.InsertAt(seriesSolidFill, 0); } else { seriesSolidFill.UpdateSolidFill((SolidColorBrush)brush); } }
/// <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 virtual void ModifyChartXML_Data(string column_index, uint row_index, string new_value) { BarChartSeries barchart_series = chart_part.ChartSpace.Descendants <BarChartSeries>().Where(s => string.Compare(s.InnerText, worksheet_name + "!$" + column_index + "$1", true) > 0).First(); DocumentFormat.OpenXml.Drawing.Charts.Values v = barchart_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/Add series 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 virtual void ModifyChartXML_Series(string column_index, uint row_index, string new_value) { BarChartSeries barchart_series = chart_part.ChartSpace.Descendants <BarChartSeries>().Where(s => string.Compare(s.InnerText, worksheet_name + "!$" + column_index + "$1", true) > 0).FirstOrDefault(); if (barchart_series != null) // There exists data on the series --> We only need to modify it { SeriesText st = barchart_series.Descendants <SeriesText>().FirstOrDefault(); StringReference sr = st.Descendants <StringReference>().First(); StringCache sc = sr.Descendants <StringCache>().First(); StringPoint sp = sc.Descendants <StringPoint>().First(); NumericValue nv = sp.Descendants <NumericValue>().First(); nv.Text = new_value; } else // No such series exists --> Consider create a new series { /* * // Find location in XML to append the BarChartSeries * Chart chart = chart_part.ChartSpace.Descendants<Chart>().FirstOrDefault(); * PlotArea plot = chart.PlotArea; * * // Create new BarChartSeries * barchart_series = new BarChartSeries(); * uint index = (uint)plot.Descendants<BarChartSeries>().ToList().Count; * * barchart_series.Append(new Index() { Val = index }); * barchart_series.Append(new Order() { Val = index }); * * SeriesText seriesText = new SeriesText(); * seriesText.Append(new NumericValue() { Text = new_value }); * * barchart_series.Append(seriesText); * * * // Append data * Bar3DChart bar_3dchart = plot.Descendants<Bar3DChart>().FirstOrDefault(); * if (bar_3dchart != null) // Chart is 3D * bar_3dchart.Append(barchart_series); * else // Chart is not 3d * { * BarChart barchart = plot.Descendants<BarChart>().FirstOrDefault(); * barchart.Append(barchart_series); * } * * // Append other settings * BarChartSeries barchart_series_template = chart_part.ChartSpace.Descendants<BarChartSeries>().LastOrDefault(); * * CategoryAxisData cateAxisData = new CategoryAxisData(); * StringReference string_ref = new StringReference(); * string_ref.Append(new DocumentFormat.OpenXml.Drawing.Charts.Formula() { Text = barchart_series.Descendants<DocumentFormat.OpenXml.Drawing.Charts.Formula>().FirstOrDefault().Text}); * StringCache string_cache = new StringCache(); * string_cache.Append(new PointCount() { Val = count }); */ } }
//----------------------------------------------------------------------------------- //Tests sur les charts public void exec(string formuleVal, string formuleLegende) { string label = "Feuil1!D5:J5"; string serie = "Feuil1!C12"; string val = "Feuil1!D12:J12"; //euro mmp mut2m cmav sapem quatrem auxia System.IO.File.Copy(template, copie, true); SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open(copie, true); ChartPart cc = XcelWin.cloneChart(XcelWin.getWorksheetPartByName(myWorkbook, "Feuil1"), "Tests", 0, 0, 10, 10); BarChart bc = cc.ChartSpace.Descendants <BarChart>().First(); for (int i = 0; i < 6; i++) { BarChartSeries newSerie = (BarChartSeries)bc.Elements <BarChartSeries>().First().CloneNode(true); string form = val.Replace("12", (6 + i).ToString()); newSerie.SeriesText.StringReference.Formula.Text = serie; //newSerie.Descendants<CategoryAxisData>().First().Remove(); newSerie.Descendants <CategoryAxisData>().First().NumberReference.Formula.Text = label; newSerie.Descendants <Charts.Values>().First().NumberReference.Formula.Text = form; newSerie.Index.Val = (uint)i; newSerie.Order.Val = (uint)i; bc.Append(newSerie); } bc.Elements <BarChartSeries>().First().Remove(); myWorkbook.WorkbookPart.Workbook.Save(); myWorkbook.Close(); }
/// <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> /// Modify/Add categories 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_Categories(string column_index, uint row_index, string new_value) { foreach (LineChartSeries linechart_series in chart_part.ChartSpace.Descendants <LineChartSeries>().ToList()) { CategoryAxisData category_axis_data = linechart_series.Descendants <CategoryAxisData>().FirstOrDefault(); if (category_axis_data == null) { // If no StringReference --> Clone one from the 1st (usually we go in this when we create a new BarChartSeries) BarChartSeries template_barchartseries = chart_part.ChartSpace.Descendants <BarChartSeries>().FirstOrDefault(); CategoryAxisData template_categoryaxisdata = template_barchartseries.Descendants <CategoryAxisData>().FirstOrDefault(); CategoryAxisData new_categoryaxisdata = new CategoryAxisData(template_categoryaxisdata.OuterXml); linechart_series.Append(new_categoryaxisdata); } else { StringReference sr = category_axis_data.Descendants <StringReference>().FirstOrDefault(); // If there is a StringReference --> Update its values StringCache sc = sr.Descendants <StringCache>().First(); try { StringPoint sp = sc.Descendants <StringPoint>().ElementAt((int)row_index - 2); NumericValue nv = sp.Descendants <NumericValue>().First(); nv.Text = new_value; } catch (Exception) { // Create new data and append to previous XML sc.PointCount.Val = sc.PointCount.Val + 1; NumericValue nv = new NumericValue(new_value); StringPoint sp = new StringPoint(nv); sp.Index = (uint)sc.Descendants <StringPoint>().ToList().Count; sc.Append(sp); // Change fomula range DocumentFormat.OpenXml.Drawing.Charts.Formula f = sr.Descendants <DocumentFormat.OpenXml.Drawing.Charts.Formula>().FirstOrDefault(); f.Text = worksheet_name + "!$A$2:$A$" + GetRowIndexByNum((int)row_index - 2).ToString(); } } } }