/// <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 NumberReference Build() { var numberReference = new NumberReference(); var formulaString = string.Concat(this.Options.Letter, "$", this.Options.RowStart, ":", this.Options.Letter, this.Options.RowEnd); numberReference.Formula = new Formula(formulaString); numberReference.NumberingCache = new NumberingCache(); numberReference.NumberingCache.FormatCode = new FormatCode(this.Options.FormatCode); numberReference.NumberingCache.PointCount = new PointCount { Val = (uint)this.Options.Values.Count }; for (var index = 0; index < this.Options.Values.Count; index++) { var numericPoint = new NumericPoint(); numericPoint.Index = (uint)index; numericPoint.NumericValue = new NumericValue(this.Options.Values[index]); numberReference.NumberingCache.Append(numericPoint); } return(numberReference); }
/// <summary> /// Adds the numeric point. /// </summary> /// <param name="numberingCache">The numbering cache.</param> /// <param name="pointCount">The point count.</param> /// <param name="rowIndex">Index of the row.</param> /// <param name="numericValueText">The numeric value text.</param> protected static void AddNumericPoint(NumberingCache numberingCache, PointCount pointCount, int rowIndex, string numericValueText) { NumericPoint numericPount = new NumericPoint(); numericPount.Index = new UInt32Value((uint)rowIndex); numericPount.NumericValue = new NumericValue(numericValueText); numberingCache.AppendChild <NumericPoint>(numericPount); pointCount.Val = new UInt32Value((UInt32)(pointCount.Val.Value + 1)); }
private void FillNumberReference(NumberReference valuesNumberReference, ChartSeriesElement newSeriesItem, Column dataColumn) { int dataCount = dataColumn.Data.Count; UInt32Value pointCount = new UInt32Value((uint)dataCount); valuesNumberReference.Formula.Text = newSeriesItem.ValuesAddress; valuesNumberReference.NumberingCache.RemoveAllChildren <NumericPoint>(); valuesNumberReference.NumberingCache.PointCount.Val = pointCount; for (int rowNo = 0; rowNo < dataColumn.Data.Count; rowNo++) { if (dataColumn.Data[rowNo] != null) { var point = new NumericPoint() { Index = new UInt32Value((uint)rowNo) }; point.NumericValue = new NumericValue(dataColumn.Data[rowNo] == null ? "0" : string.Format(System.Globalization.CultureInfo.InvariantCulture, "{0}", dataColumn.Data[rowNo])); valuesNumberReference.NumberingCache.Append(point); } } }
void setChartData(ChartPart chartPart1, ChartDataHolder[] ChartDataSlide) { ChartSpace chartSpace = chartPart1.ChartSpace; DocumentFormat.OpenXml.Drawing.Charts.Chart chart1 = chartSpace.GetFirstChild <DocumentFormat.OpenXml.Drawing.Charts.Chart>(); PlotArea plotArea1 = chart1.GetFirstChild <PlotArea>(); BarChart barChart1 = plotArea1.GetFirstChild <BarChart>(); //BarChartSeries barChartSeries1 = barChart1.Elements<BarChartSeries>().ElementAtOrDefault(2); for (int i = 0; i < barChart1.Elements <BarChartSeries>().Count(); i++) { BarChartSeries barChartSeries = barChart1.Elements <BarChartSeries>().ElementAtOrDefault(i); ChartDataSlide dataModel = ChartDataSlide.ElementAtOrDefault(i).chartData; SeriesText seriesText = barChartSeries.Elements <SeriesText>().ElementAtOrDefault(0); if (seriesText != null) { var stringReference = seriesText.Descendants <StringReference>().FirstOrDefault(); var stringCache = stringReference.Descendants <StringCache>().FirstOrDefault(); var stringPoint = stringCache.Descendants <StringPoint>().FirstOrDefault(); var barLabel = stringPoint.GetFirstChild <NumericValue>(); barLabel.Text = ChartDataSlide.ElementAtOrDefault(i).seriesText; } if (barChartSeries != null) { Values values1 = barChartSeries.GetFirstChild <Values>(); NumberReference numberReference1 = values1.GetFirstChild <NumberReference>(); NumberingCache numberingCache1 = numberReference1.GetFirstChild <NumberingCache>(); NumericPoint numericPoint1 = numberingCache1.GetFirstChild <NumericPoint>(); NumericPoint numericPoint2 = numberingCache1.Elements <NumericPoint>().ElementAt(1); NumericPoint numericPoint3 = numberingCache1.Elements <NumericPoint>().ElementAt(2); NumericValue numericValue1 = numericPoint1.GetFirstChild <NumericValue>(); //numericValue1.Text = ".50"; if (numericValue1 != null) { numericValue1.Text = dataModel.Interaction.ToString(); } NumericValue numericValue2 = numericPoint2.GetFirstChild <NumericValue>(); //numericValue2.Text = ".10"; if (numericValue2 != null) { numericValue2.Text = dataModel.Knowlegde.ToString(); } NumericValue numericValue3 = numericPoint3.GetFirstChild <NumericValue>(); //numericValue3.Text = ".40"; if (numericValue3 != null) { numericValue3.Text = dataModel.Image.ToString(); } } } chartSpace.Save(); }
public PieChartSeries GeneratePieChartSeries(string[] labels, double[] data) { PieChartSeries pieChartSeries1 = new PieChartSeries(); Index index1 = new Index() { Val = (UInt32Value)0U }; Order order1 = new Order() { Val = (UInt32Value)0U }; SeriesText seriesText1 = new SeriesText(); NumericValue numericValue1 = new NumericValue(); numericValue1.Text = "sreie 1"; seriesText1.Append(numericValue1); CategoryAxisData categoryAxisData1 = new CategoryAxisData(); StringLiteral stringLiteral1 = new StringLiteral(); PointCount pointCount1 = new PointCount() { Val = (uint)labels.Length }; //StringPoint stringPoint1 = new StringPoint() { Index = (UInt32Value)0U }; //NumericValue numericValue2 = new NumericValue(); //numericValue2.Text = "a"; //stringPoint1.Append(numericValue2); //StringPoint stringPoint2 = new StringPoint() { Index = (UInt32Value)1U }; //NumericValue numericValue3 = new NumericValue(); //numericValue3.Text = "n"; //stringPoint2.Append(numericValue3); //StringPoint stringPoint3 = new StringPoint() { Index = (UInt32Value)2U }; //NumericValue numericValue4 = new NumericValue(); //numericValue4.Text = "c"; //stringPoint3.Append(numericValue4); //StringPoint stringPoint4 = new StringPoint() { Index = (UInt32Value)3U }; //NumericValue numericValue5 = new NumericValue(); //numericValue5.Text = "d"; //stringPoint4.Append(numericValue5); //Ajout des etiquette de legendes for (int i = 0; i < labels.Length; i++) { StringPoint stringPoint = new StringPoint() { Index = (uint)i }; NumericValue numericValue = new NumericValue(); numericValue.Text = labels[i]; stringPoint.Append(numericValue); stringLiteral1.Append(stringPoint); } stringLiteral1.Append(pointCount1); //stringLiteral1.Append(stringPoint1); //stringLiteral1.Append(stringPoint2); //stringLiteral1.Append(stringPoint3); //stringLiteral1.Append(stringPoint4); categoryAxisData1.Append(stringLiteral1); DocumentFormat.OpenXml.Drawing.Charts.Values values1 = new DocumentFormat.OpenXml.Drawing.Charts.Values(); NumberLiteral numberLiteral1 = new NumberLiteral(); FormatCode formatCode1 = new FormatCode(); formatCode1.Text = "General"; PointCount pointCount2 = new PointCount() { Val = (uint)data.Length }; //NumericPoint numericPoint1 = new NumericPoint() { Index = (UInt32Value)0U }; //NumericValue numericValue6 = new NumericValue(); //numericValue6.Text = "1"; //numericPoint1.Append(numericValue6); //NumericPoint numericPoint2 = new NumericPoint() { Index = (UInt32Value)1U }; //NumericValue numericValue7 = new NumericValue(); //numericValue7.Text = "2"; //numericPoint2.Append(numericValue7); //NumericPoint numericPoint3 = new NumericPoint() { Index = (UInt32Value)2U }; //NumericValue numericValue8 = new NumericValue(); //numericValue8.Text = "3"; //numericPoint3.Append(numericValue8); //NumericPoint numericPoint4 = new NumericPoint() { Index = (UInt32Value)3U }; //NumericValue numericValue9 = new NumericValue(); //numericValue9.Text = "5"; //numericPoint4.Append(numericValue9); for (int i = 0; i < data.Length; i++) { NumericPoint numericPoint = new NumericPoint() { Index = (uint)i }; NumericValue numericValue = new NumericValue(); numericValue.Text = data[i].ToString(); numericPoint.Append(numericValue); numberLiteral1.Append(numericPoint); } numberLiteral1.Append(formatCode1); numberLiteral1.Append(pointCount2); //numberLiteral1.Append(numericPoint1); //numberLiteral1.Append(numericPoint2); //numberLiteral1.Append(numericPoint3); //numberLiteral1.Append(numericPoint4); values1.Append(numberLiteral1); pieChartSeries1.Append(index1); pieChartSeries1.Append(order1); pieChartSeries1.Append(seriesText1); pieChartSeries1.Append(categoryAxisData1); pieChartSeries1.Append(values1); return(pieChartSeries1); }
protected void modificaChartData(string FormatoValori, string titoloSerie, out SeriesText seriesText1, out CategoryAxisData categoryAxisData1, out Values values1) { seriesText1 = new SeriesText(); StringReference stringReference1 = new StringReference(); Formula formula1 = new Formula(); formula1.Text = "Foglio1!$B$1"; StringCache stringCache1 = new StringCache(); PointCount pointCount1 = new PointCount() { Val = (UInt32Value)1U }; StringPoint stringPoint1 = new StringPoint() { Index = (UInt32Value)0U }; NumericValue numericValue1 = new NumericValue(); numericValue1.Text = titoloSerie; stringPoint1.Append(numericValue1); stringCache1.Append(pointCount1); stringCache1.Append(stringPoint1); stringReference1.Append(formula1); stringReference1.Append(stringCache1); seriesText1.Append(stringReference1); DataPoint dataPoint1 = new DataPoint(); Index index2 = new Index() { Val = (UInt32Value)2U }; dataPoint1.Append(index2); //################################i testi #################################### categoryAxisData1 = new CategoryAxisData(); StringReference stringReference2 = new StringReference(); Formula formula2 = new Formula(); formula2.Text = string.Format("Foglio1!$A$2:$A${0}", valori.Count + 2); StringCache stringCache2 = new StringCache(); UInt32Value nValori = Convert.ToUInt32(valori.Count); PointCount pointCount2 = new PointCount() { Val = nValori }; StringPoint[] stringPoints = new StringPoint[nValori]; UInt32Value n = 0; foreach (KeyValuePair <string, double> item in valori) { stringPoints[n] = new StringPoint() { Index = (UInt32Value)n }; NumericValue numericValue2 = new NumericValue(); numericValue2.Text = item.Key; stringPoints[n].Append(numericValue2); n += 1; } stringCache2.Append(pointCount2); for (int i = 0; i < n; i++) { stringCache2.Append(stringPoints[i]); } stringReference2.Append(formula2); stringReference2.Append(stringCache2); categoryAxisData1.Append(stringReference2); //################################i valori#################################### values1 = new Values(); NumberReference numberReference1 = new NumberReference(); Formula formula3 = new Formula(); formula3.Text = string.Format("Foglio1!$B$2:$B${0}", valori.Count + 2); NumberingCache numberingCache1 = new NumberingCache(); FormatCode formatCode1 = new FormatCode(); formatCode1.Text = FormatoValori; //<----------------------------------------------------------- PointCount pointCount3 = new PointCount() { Val = nValori }; NumericPoint[] numericPoints = new NumericPoint[nValori]; n = 0; foreach (KeyValuePair <string, double> item in valori) { numericPoints[n] = new NumericPoint() { Index = (UInt32Value)n }; NumericValue numericValue = new NumericValue(); numericValue.Text = item.Value.ToString(); numericValue.Text = numericValue.Text.Replace(",", "."); // devo forzare il formato americano numericPoints[n].Append(numericValue); n += 1; } numberingCache1.Append(formatCode1); numberingCache1.Append(pointCount3); for (int i = 0; i < n; i++) { numberingCache1.Append(numericPoints[i]); } numberReference1.Append(formula3); numberReference1.Append(numberingCache1); values1.Append(numberReference1); }
private void FillPoints(string baseFormula, String mode, List <String> data) { int idx = data.Count; ChartPart cp = Document.MainDocumentPart.ChartParts.FirstOrDefault(); Chart chart = cp.ChartSpace.Elements <Chart>().FirstOrDefault(); BarChart barchart = chart.PlotArea.Elements <BarChart>().FirstOrDefault(); BarChartSeries series = barchart.Elements <BarChartSeries>().FirstOrDefault(); CategoryAxisData labels = new CategoryAxisData(); DocumentFormat.OpenXml.Drawing.Charts.Values values = new DocumentFormat.OpenXml.Drawing.Charts.Values(); NumberReference nref = new NumberReference(); string formula = baseFormula + (idx + 1); DocumentFormat.OpenXml.Drawing.Charts.Formula f = new DocumentFormat.OpenXml.Drawing.Charts.Formula(); f.Text = formula; Log.Info(formula); nref.Formula = f; NumberingCache nc = new NumberingCache();//nref.Descendants<NumberingCache>().First(); nc.PointCount = new PointCount(); nc.PointCount.Val = (uint)idx; int pointIndex = 0; foreach (string val in data) { NumericPoint point = new NumericPoint(); point.Index = (uint)pointIndex; NumericValue value = new NumericValue(); if ("LABELS".Equals(mode)) { value.Text = val; } else if ("VALUES".Equals(mode)) { if (val != "0") { float valuePerc = float.Parse(val, CultureInfo.InvariantCulture.NumberFormat) * 100; value.Text = valuePerc.ToString(CultureInfo.InvariantCulture); } else { value.Text = ""; } } point.AppendChild(value); nc.AppendChild(point); pointIndex++; } nref.AppendChild(nc); if ("LABELS".Equals(mode)) { labels.AppendChild(nref); series.ReplaceChild <CategoryAxisData>(labels, series.Elements <CategoryAxisData>().FirstOrDefault()); } else if ("VALUES".Equals(mode)) { values.AppendChild(nref); series.ReplaceChild <DocumentFormat.OpenXml.Drawing.Charts.Values>(values, series.Elements <DocumentFormat.OpenXml.Drawing.Charts.Values>().FirstOrDefault()); } ; }