private void FillCategoryAxisData(CategoryAxisData categoryAxisData, ChartSeriesElement newSeriesItem, Column dataColumn) { int dataCount = dataColumn.Data.Count; UInt32Value pointCount = new UInt32Value((uint)dataCount); if (categoryAxisData != null && categoryAxisData.StringReference != null) { categoryAxisData.StringReference.StringCache.RemoveAllChildren <StringPoint>(); categoryAxisData.StringReference.StringCache.PointCount.Val = pointCount; categoryAxisData.StringReference.Formula.Text = newSeriesItem.CategoryAxisDataAddress; for (int rowNo = 0; rowNo < element.Data.Rows.Count; rowNo++) { StringPoint categoryAxisDataStringPoint = new StringPoint() { Index = (UInt32Value)((uint)rowNo) }; NumericValue categoryAxisDataNumericValue = new NumericValue() { Text = element.Data.Rows[rowNo].GetHeader() }; categoryAxisDataStringPoint.Append(categoryAxisDataNumericValue); categoryAxisData.StringReference.StringCache.Append(categoryAxisDataStringPoint); } } }
protected static StringReference CreateStringReference(CategoryAxisData categoryAxisData, string formulaText = "") { categoryAxisData.RemoveAllChildren <StringReference>(); var stringReference = categoryAxisData.AppendChild(new StringReference()); if (!string.IsNullOrWhiteSpace(formulaText)) { stringReference.Formula = new Formula { Text = formulaText }; } return(stringReference); }
/// <summary> /// Задать формулу значении (горизонтальной) оси /// </summary> /// <param name="series">Ряд значении линейного графика</param> /// <param name="newFormula">Новая формула значении</param> /// <returns>true - если формула успешно задана, false - в обратном случае</returns> public static bool AxisValues(this LineChartSeries series, Formula newFormula) { var oldValues = series.FirstDescendant <CategoryAxisData>(); var newV = new CategoryAxisData() { NumberReference = new NumberReference() { Formula = newFormula } }; var newElem = oldValues.ReplaceBy(newV); return(newElem.SameAs(newV)); }
/// <summary> /// Updates the chart. /// </summary> /// <param name="chart">The chart.</param> /// <param name="sheetName">Name of the sheet.</param> protected override void UpdateChart(OpenXmlCompositeElement chart, string sheetName) { if (chart != null) { chart.RemoveAllChildren <LineChartSeries>(); // Index 0 is for category axis data for (int index = 1; index < chartData.columnNameToSeries.Count(); index++) { string columnName = GetExcelColumnName(index); LineChartSeries lineChartSeries = chart.AppendChild <LineChartSeries>(new LineChartSeries()); UpdateSeriesText(sheetName, lineChartSeries, columnName, 1, chartData.columnNameToSeries.Skip(index).FirstOrDefault().Key); // Update Category Axis data CategoryAxisData catAxisData = new CategoryAxisData(); catAxisData.RemoveAllChildren <StringReference>(); catAxisData.RemoveAllChildren <NumberReference>(); StringReference catStringReference = GetStringReference(sheetName + "!$A$2:$A$" + (chartData.Count + 1).ToString(), chartData.Count); // Series 0 is for category axis data foreach (string cat in chartData.columnNameToSeries.First().Value) { AddStringPoint(catStringReference.StringCache, catStringReference.StringCache.Descendants <StringPoint>().Count(), cat); } catAxisData.Append(catStringReference); // Update Values NumberingCache numberingCache; PointCount pointCount; Values values = lineChartSeries.AppendChild <Values>(new Values()); NumberReference reference = CreateNumberReference(values, sheetName + "!$" + columnName + "$2:$" + columnName + "$" + (chartData.Count + 1).ToString()); SetNumberingCache(reference, out numberingCache, out pointCount); int rowIndex = 0; foreach (var point in chartData.columnNameToSeries.Skip(index).FirstOrDefault().Value) { AddNumericPoint(numberingCache, pointCount, rowIndex, point.ToString()); rowIndex += 1; } } } }
public TSelf SetCategoryAxis(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."); } CategoryAxisData categoryAxisData = series.GetFirstChild <CategoryAxisData>() ?? series.AppendChild(new CategoryAxisData()); categoryAxisData.StringReference = new StringReference() { Formula = new Formula(range.Formula), StringCache = stringCache }; return(this.Self); }
/// <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(); } } } }
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()); } ; }
public void CreateExcelDoc(string fileName) { List <Student> students = new List <Student>(); Initizalize(students); using (SpreadsheetDocument document = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook)) { WorkbookPart workbookPart = document.AddWorkbookPart(); workbookPart.Workbook = new Workbook(); WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>(); worksheetPart.Worksheet = new Worksheet(); Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets()); Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Students" }; SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData()); // Add drawing part to WorksheetPart DrawingsPart drawingsPart = worksheetPart.AddNewPart <DrawingsPart>(); worksheetPart.Worksheet.Append(new Drawing() { Id = worksheetPart.GetIdOfPart(drawingsPart) }); worksheetPart.Worksheet.Save(); drawingsPart.WorksheetDrawing = new WorksheetDrawing(); sheets.Append(sheet); workbookPart.Workbook.Save(); // Add a new chart and set the chart language ChartPart chartPart = drawingsPart.AddNewPart <ChartPart>(); chartPart.ChartSpace = new ChartSpace(); chartPart.ChartSpace.AppendChild(new EditingLanguage() { Val = "en-US" }); Chart chart = chartPart.ChartSpace.AppendChild(new Chart()); chart.AppendChild(new AutoTitleDeleted() { Val = true }); // We don't want to show the chart title // Create a new Clustered Column Chart PlotArea plotArea = chart.AppendChild(new PlotArea()); Layout layout = plotArea.AppendChild(new Layout()); BarChart barChart = plotArea.AppendChild(new BarChart( new BarDirection() { Val = new EnumValue <BarDirectionValues>(BarDirectionValues.Column) }, new BarGrouping() { Val = new EnumValue <BarGroupingValues>(BarGroupingValues.Clustered) }, new VaryColors() { Val = false } )); // Constructing header Row row = new Row(); int rowIndex = 1; row.AppendChild(ConstructCell(string.Empty, CellValues.String)); foreach (var month in Months.Short) { row.AppendChild(ConstructCell(month, CellValues.String)); } // Insert the header row to the Sheet Data sheetData.AppendChild(row); rowIndex++; // Create chart series for (int i = 0; i < students.Count; i++) { BarChartSeries barChartSeries = barChart.AppendChild(new BarChartSeries( new Index() { Val = (uint)i }, new Order() { Val = (uint)i }, new SeriesText(new NumericValue() { Text = students[i].Name }) )); // Adding category axis to the chart CategoryAxisData categoryAxisData = barChartSeries.AppendChild(new CategoryAxisData()); // Category // Constructing the chart category string formulaCat = "Students!$B$1:$G$1"; StringReference stringReference = categoryAxisData.AppendChild(new StringReference() { Formula = new DocumentFormat.OpenXml.Drawing.Charts.Formula() { Text = formulaCat } }); StringCache stringCache = stringReference.AppendChild(new StringCache()); stringCache.Append(new PointCount() { Val = (uint)Months.Short.Length }); for (int j = 0; j < Months.Short.Length; j++) { stringCache.AppendChild(new NumericPoint() { Index = (uint)j }).Append(new NumericValue(Months.Short[j])); } } var chartSeries = barChart.Elements <BarChartSeries>().GetEnumerator(); for (int i = 0; i < students.Count; i++) { row = new Row(); row.AppendChild(ConstructCell(students[i].Name, CellValues.String)); chartSeries.MoveNext(); string formulaVal = string.Format("Students!$B${0}:$G${0}", rowIndex); DocumentFormat.OpenXml.Drawing.Charts.Values values = chartSeries.Current.AppendChild(new DocumentFormat.OpenXml.Drawing.Charts.Values()); NumberReference numberReference = values.AppendChild(new NumberReference() { Formula = new DocumentFormat.OpenXml.Drawing.Charts.Formula() { Text = formulaVal } }); NumberingCache numberingCache = numberReference.AppendChild(new NumberingCache()); numberingCache.Append(new PointCount() { Val = (uint)Months.Short.Length }); for (uint j = 0; j < students[i].Values.Length; j++) { var value = students[i].Values[j]; row.AppendChild(ConstructCell(value.ToString(), CellValues.Number)); numberingCache.AppendChild(new NumericPoint() { Index = j }).Append(new NumericValue(value.ToString())); } sheetData.AppendChild(row); rowIndex++; } barChart.AppendChild(new DataLabels( new ShowLegendKey() { Val = false }, new ShowValue() { Val = false }, new ShowCategoryName() { Val = false }, new ShowSeriesName() { Val = false }, new ShowPercent() { Val = false }, new ShowBubbleSize() { Val = false } )); barChart.Append(new AxisId() { Val = 48650112u }); barChart.Append(new AxisId() { Val = 48672768u }); // Adding Category Axis plotArea.AppendChild( new CategoryAxis( new AxisId() { Val = 48650112u }, new Scaling(new Orientation() { Val = new EnumValue <DocumentFormat.OpenXml.Drawing.Charts.OrientationValues>(DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax) }), new Delete() { Val = false }, new AxisPosition() { Val = new EnumValue <AxisPositionValues>(AxisPositionValues.Bottom) }, new TickLabelPosition() { Val = new EnumValue <TickLabelPositionValues>(TickLabelPositionValues.NextTo) }, new CrossingAxis() { Val = 48672768u }, new Crosses() { Val = new EnumValue <CrossesValues>(CrossesValues.AutoZero) }, new AutoLabeled() { Val = true }, new LabelAlignment() { Val = new EnumValue <LabelAlignmentValues>(LabelAlignmentValues.Center) } )); // Adding Value Axis plotArea.AppendChild( new ValueAxis( new AxisId() { Val = 48672768u }, new Scaling(new Orientation() { Val = new EnumValue <DocumentFormat.OpenXml.Drawing.Charts.OrientationValues>(DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax) }), new Delete() { Val = false }, new AxisPosition() { Val = new EnumValue <AxisPositionValues>(AxisPositionValues.Left) }, new MajorGridlines(), new DocumentFormat.OpenXml.Drawing.Charts.NumberingFormat() { FormatCode = "General", SourceLinked = true }, new TickLabelPosition() { Val = new EnumValue <TickLabelPositionValues>(TickLabelPositionValues.NextTo) }, new CrossingAxis() { Val = 48650112u }, new Crosses() { Val = new EnumValue <CrossesValues>(CrossesValues.AutoZero) }, new CrossBetween() { Val = new EnumValue <CrossBetweenValues>(CrossBetweenValues.Between) } )); chart.Append( new PlotVisibleOnly() { Val = true }, new DisplayBlanksAs() { Val = new EnumValue <DisplayBlanksAsValues>(DisplayBlanksAsValues.Gap) }, new ShowDataLabelsOverMaximum() { Val = false } ); chartPart.ChartSpace.Save(); // Positioning the chart on the spreadsheet TwoCellAnchor twoCellAnchor = drawingsPart.WorksheetDrawing.AppendChild(new TwoCellAnchor()); twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.FromMarker( new ColumnId("0"), new ColumnOffset("0"), new RowId((rowIndex + 2).ToString()), new RowOffset("0") )); twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.ToMarker( new ColumnId("8"), new ColumnOffset("0"), new RowId((rowIndex + 12).ToString()), new RowOffset("0") )); // Append GraphicFrame to TwoCellAnchor GraphicFrame graphicFrame = twoCellAnchor.AppendChild(new GraphicFrame()); graphicFrame.Macro = string.Empty; graphicFrame.Append(new NonVisualGraphicFrameProperties( new NonVisualDrawingProperties() { Id = 2u, Name = "Sample Chart" }, new NonVisualGraphicFrameDrawingProperties() )); graphicFrame.Append(new Transform( new DocumentFormat.OpenXml.Drawing.Offset() { X = 0L, Y = 0L }, new DocumentFormat.OpenXml.Drawing.Extents() { Cx = 0L, Cy = 0L } )); graphicFrame.Append(new DocumentFormat.OpenXml.Drawing.Graphic( new DocumentFormat.OpenXml.Drawing.GraphicData( new ChartReference() { Id = drawingsPart.GetIdOfPart(chartPart) } ) { Uri = "http://schemas.openxmlformats.org/drawingml/2006/chart" } )); twoCellAnchor.Append(new ClientData()); drawingsPart.WorksheetDrawing.Save(); worksheetPart.Worksheet.Save(); } }
/// <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 }