/// <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 override void ModifyChartXML_Series(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).FirstOrDefault(); if (linechart_series != null) // There exists data on the series --> We only need to modify it { SeriesText st = linechart_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 }); */ } }
/// <summary> /// Задать название ряда ссылкой на ячейки /// </summary> /// <param name="series">Ряд значении линейного графика</param> /// <param name="newFormula">Формула-ссылка названия ряда</param> /// <returns>true - если название успешно задано, false - в обратном случае</returns> public static bool Label(this LineChartSeries series, Formula newFormula) { var seriesText = new SeriesText() { StringReference = new StringReference() { Formula = newFormula } }; series.SeriesText = seriesText; return(true); }
public AiSequence(IStreamReader reader) : base(reader) { //AI = BRAI [SeriesText] //BRAI this.BRAI = (BRAI)BiffRecord.ReadRecord(reader); //[SeriesText] if (BiffRecord.GetNextRecordType(reader) == RecordType.SeriesText) { this.SeriesText = (SeriesText)BiffRecord.ReadRecord(reader); } }
public TSelf SetText(IRange range) { StringCache stringCache = new StringCache(); if (range.Width == 1 && range.Height > 0) { stringCache.PointCount = new PointCount() { Val = (uint)range.Height.Value }; for (uint i = 0; i < range.Height; ++i) { stringCache.AppendChild(new StringPoint() { Index = i, NumericValue = new NumericValue(range[0, i].InnerValue) }); } } else if (range.Height == 1 && range.Width > 0) { stringCache.PointCount = new PointCount() { Val = (uint)range.Width.Value }; for (uint i = 0; i < range.Width; ++i) { stringCache.AppendChild(new StringPoint() { Index = i, NumericValue = new NumericValue(range[i, 0].InnerValue) }); } } else { throw new ArgumentException("Expected an one-dimensional range."); } SeriesText seriesText = series.GetFirstChild <SeriesText>() ?? series.AppendChild(new SeriesText()); seriesText.StringReference = new StringReference() { Formula = new Formula(range.Formula), StringCache = stringCache }; return(this.Self); }
private void SetSeriesText(OpenXmlCompositeElement seriesItem, ChartSeriesElement newSeriesItem, string seriesHeader) { SeriesText seriesText = seriesItem.Elements <SeriesText>().First(); seriesText.StringReference.Formula.Text = newSeriesItem.SeriesTextAddress; seriesText.StringReference.StringCache.RemoveAllChildren <StringPoint>(); StringPoint stringReferencePoint = new StringPoint() { Index = (UInt32Value)((uint)0) }; NumericValue stringReferenceNumericValue = new NumericValue() { Text = seriesHeader }; stringReferencePoint.Append(stringReferenceNumericValue); seriesText.StringReference.StringCache.Append(stringReferencePoint); }
/// <summary> /// Updates the series text. /// </summary> /// <param name="sheetName">Name of the sheet.</param> /// <param name="chartSeries">The chart series.</param> /// <param name="columnName">Name of the column.</param> /// <param name="pointCountVal">The point count val.</param> /// <param name="stringPointNumericVal">The string point numeric val.</param> protected static void UpdateSeriesText(string sheetName, OpenXmlCompositeElement chartSeries, string columnName, int pointCountVal, string stringPointNumericVal) { if (chartSeries != null) { SeriesText seriesText = chartSeries.Descendants <SeriesText>().FirstOrDefault(); if (seriesText == null) { seriesText = chartSeries.AppendChild <SeriesText>(new SeriesText()); } else { seriesText.RemoveAllChildren <StringReference>(); } StringReference stringReference = GetStringReference(sheetName + "!$" + columnName + "$1", pointCountVal); AddStringPoint(stringReference.StringCache, 0, stringPointNumericVal); seriesText.Append(stringReference); } }
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); }
/// <summary> /// draw the 2D bar chart /// index start from 1 /// </summary> /// <param name="startx">index start from 1 for row</param> /// <param name="starty">index start from 1 for column</param> /// <param name="columnCount"></param> /// <param name="rowCount"></param> public void InsertChartInSpreadsheet(WorksheetPart sheetpart, string sheetName, int startx, int starty, int columnCount, int rowCount, int chart_pointx, int chart_pointy) { WorksheetPart worksheetPart = CurrentWorksheetPart; #region SDK How to example code // Add a new drawing to the worksheet. DrawingsPart drawingsPart = worksheetPart.AddNewPart <DrawingsPart>(); worksheetPart.Worksheet.Append(new DocumentFormat.OpenXml.Spreadsheet.Drawing() { Id = worksheetPart.GetIdOfPart(drawingsPart) }); worksheetPart.Worksheet.Save(); // Add a new chart and set the chart language to English-US. ChartPart chartPart = drawingsPart.AddNewPart <ChartPart>(); chartPart.ChartSpace = new ChartSpace(); chartPart.ChartSpace.Append(new EditingLanguage() { Val = new StringValue("en-US") }); DocumentFormat.OpenXml.Drawing.Charts.Chart chart = chartPart.ChartSpace.AppendChild <DocumentFormat.OpenXml.Drawing.Charts.Chart>( new DocumentFormat.OpenXml.Drawing.Charts.Chart()); // Create a new clustered column chart. PlotArea plotArea = chart.AppendChild <PlotArea>(new PlotArea()); Layout layout = plotArea.AppendChild <Layout>(new Layout()); BarChart barChart = plotArea.AppendChild <BarChart>(new BarChart(new BarDirection() { Val = new EnumValue <BarDirectionValues>(BarDirectionValues.Column) }, new BarGrouping() { Val = new EnumValue <BarGroupingValues>(BarGroupingValues.Clustered) })); #endregion string sheetName = GetCurrentSheetName(); string columnName = GetColumnName(starty - 1); string formulaString = string.Format("{0}!${1}${2}:${3}${4}", sheetName, columnName, startx + 1, columnName, startx + rowCount - 1); CategoryAxisData cad = new CategoryAxisData(); cad.StringReference = new StringReference() { Formula = new DocumentFormat.OpenXml.Drawing.Charts.Formula(formulaString) }; uint i = 0; for (int sIndex = 1; sIndex < columnCount; sIndex++) { columnName = GetColumnName(starty + sIndex - 1); formulaString = string.Format("{0}!${1}${2}", sheetName, columnName, startx); SeriesText st = new SeriesText(); st.StringReference = new StringReference() { Formula = new DocumentFormat.OpenXml.Drawing.Charts.Formula(formulaString) }; formulaString = string.Format("{0}!${1}${2}:${3}${4}", sheetName, columnName, startx + 1, columnName, startx + rowCount - 1); DocumentFormat.OpenXml.Drawing.Charts.Values v = new DocumentFormat.OpenXml.Drawing.Charts.Values(); v.NumberReference = new NumberReference() { Formula = new DocumentFormat.OpenXml.Drawing.Charts.Formula(formulaString) }; BarChartSeries barChartSeries = barChart.AppendChild <BarChartSeries>(new BarChartSeries(new Index() { Val = new UInt32Value(i) }, new Order() { Val = new UInt32Value(i) }, st, v)); if (sIndex == 1) { barChartSeries.AppendChild(cad); } i++; } #region SDK how to example Code barChart.Append(new AxisId() { Val = new UInt32Value(48650112u) }); barChart.Append(new AxisId() { Val = new UInt32Value(48672768u) }); // Add the Category Axis. CategoryAxis catAx = plotArea.AppendChild <CategoryAxis>(new CategoryAxis(new AxisId() { Val = new UInt32Value(48650112u) }, new Scaling(new Orientation() { Val = new EnumValue <DocumentFormat.OpenXml.Drawing.Charts.OrientationValues>( DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax) }), new AxisPosition() { Val = new EnumValue <AxisPositionValues>(AxisPositionValues.Bottom) }, new TickLabelPosition() { Val = new EnumValue <TickLabelPositionValues>(TickLabelPositionValues.NextTo) }, new CrossingAxis() { Val = new UInt32Value(48672768U) }, new Crosses() { Val = new EnumValue <CrossesValues>(CrossesValues.AutoZero) }, new AutoLabeled() { Val = new BooleanValue(true) }, new LabelAlignment() { Val = new EnumValue <LabelAlignmentValues>(LabelAlignmentValues.Center) }, new LabelOffset() { Val = new UInt16Value((ushort)100) })); // Add the Value Axis. ValueAxis valAx = plotArea.AppendChild <ValueAxis>(new ValueAxis(new AxisId() { Val = new UInt32Value(48672768u) }, new Scaling(new Orientation() { Val = new EnumValue <DocumentFormat.OpenXml.Drawing.Charts.OrientationValues>( DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax) }), new AxisPosition() { Val = new EnumValue <AxisPositionValues>(AxisPositionValues.Left) }, new MajorGridlines(), new DocumentFormat.OpenXml.Drawing.Charts.NumberFormat() { FormatCode = new StringValue("General"), SourceLinked = new BooleanValue(true) }, new TickLabelPosition() { Val = new EnumValue <TickLabelPositionValues>(TickLabelPositionValues.NextTo) }, new CrossingAxis() { Val = new UInt32Value(48650112U) }, new Crosses() { Val = new EnumValue <CrossesValues>(CrossesValues.AutoZero) }, new CrossBetween() { Val = new EnumValue <CrossBetweenValues>(CrossBetweenValues.Between) })); // Add the chart Legend. Legend legend = chart.AppendChild <Legend>(new Legend(new LegendPosition() { Val = new EnumValue <LegendPositionValues>(LegendPositionValues.Right) }, new Layout())); chart.Append(new PlotVisibleOnly() { Val = new BooleanValue(true) }); // Save the chart part. chartPart.ChartSpace.Save(); // Position the chart on the worksheet using a TwoCellAnchor object. drawingsPart.WorksheetDrawing = new WorksheetDrawing(); TwoCellAnchor twoCellAnchor = drawingsPart.WorksheetDrawing.AppendChild <TwoCellAnchor>(new TwoCellAnchor()); twoCellAnchor.Append(new FromMarker(new ColumnId("9"), new ColumnOffset("581025"), new RowId("17"), new RowOffset("114300"))); twoCellAnchor.Append(new ToMarker(new ColumnId("17"), new ColumnOffset("276225"), new RowId("32"), new RowOffset("0"))); // Append a GraphicFrame to the TwoCellAnchor object. DocumentFormat.OpenXml.Drawing.Spreadsheet.GraphicFrame graphicFrame = twoCellAnchor.AppendChild <DocumentFormat.OpenXml.Drawing.Spreadsheet.GraphicFrame>( new DocumentFormat.OpenXml.Drawing.Spreadsheet.GraphicFrame()); graphicFrame.Macro = ""; graphicFrame.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualGraphicFrameProperties( new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties() { Id = new UInt32Value(2u), Name = "Chart 1" }, new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualGraphicFrameDrawingProperties())); graphicFrame.Append(new Transform(new Offset() { X = 0L, Y = 0L }, new Extents() { Cx = 0L, Cy = 0L })); graphicFrame.Append(new Graphic(new GraphicData(new ChartReference() { Id = drawingsPart.GetIdOfPart(chartPart) }) { Uri = "http://schemas.openxmlformats.org/drawingml/2006/chart" })); twoCellAnchor.Append(new ClientData()); // Save the WorksheetDrawing object. drawingsPart.WorksheetDrawing.Save(); #endregion }