protected override string ReplaceValuesInChartInSlide(ChartPart chartPart, string categoryTitle, int nSerie) { ChartSpace chartSpace = chartPart.ChartSpace; GraficoBarre myBarre = new GraficoBarre2d(); myBarre.BarChartSpace = chartSpace; myBarre.getBarre(); if (myBarre.Barre != null) { BarChartSeries barChartSeries1 = myBarre.barChartSeries1; SeriesText seriesText1; CategoryAxisData categoryAxisData1; Values values1; modificaChartData("0,0", "", out seriesText1, out categoryAxisData1, out values1); barChartSeries1.SeriesText = seriesText1; barChartSeries1.Append(categoryAxisData1); barChartSeries1.Append(values1); return("ok"); } else { return("non trovo il grafico a barre!"); } }
/// <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(); } }
private void BindBarChart() { zgSales.Visible = false; bcSales.Visible = true; if (salesByMonthData == null) { salesByMonthData = CommerceReport.GetSalesByYearMonthByModule(moduleGuid); } StringBuilder categories = new StringBuilder(); string comma = string.Empty; List <decimal> revenue = new List <decimal>(); // original data is sorted descending on Y, M resorting here DataRow[] result = salesByMonthData.Select(string.Empty, "Y ASC, M ASC"); int spaceInterval = 0; int totalItems = result.Length; int itemsAdded = 0; if (totalItems > 12) { spaceInterval = 4; } int nextItemToShow = 0; foreach (DataRow row in result) { categories.Append(comma); if (itemsAdded == nextItemToShow) { categories.Append(row["Y"].ToString()); categories.Append("-"); categories.Append(row["M"].ToString()); nextItemToShow = itemsAdded + spaceInterval; } comma = ","; revenue.Add(Convert.ToDecimal(row["Sales"])); itemsAdded += 1; } bcSales.ChartTitle = Resource.SalesByMonthChartLabel; bcSales.CategoriesAxis = categories.ToString(); BarChartSeries series = new BarChartSeries(); //series.Name = Resource.SalesByMonthChartSalesLabel; series.Data = revenue.ToArray(); bcSales.Series.Add(series); //bcSales.CategoriesAxis //bcSales.Series. }
/// <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 }); */ } }
/// <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); } }
private void loadGenderChart() { BarChartSeries Male = new BarChartSeries(); Male.BarColor = "#0000FF"; Male.Name = "Male"; BarChartSeries Female = new BarChartSeries(); Female.BarColor = "#FF00FF"; Female.Name = "Female"; string strCountMale = "SELECT COUNT (*) AS 'COUNT' FROM Tenants WHERE Gender = 1"; string strCountFemale = "SELECT COUNT (*) AS 'COUNT' FROM Tenants WHERE Gender = 2"; int MaleCount = int.Parse(DataAccess.ReturnData(strCountMale, conString, "COUNT")); int FemaleCount = int.Parse(DataAccess.ReturnData(strCountFemale, conString, "COUNT")); Decimal[] MaleNumbers = { Convert.ToDecimal(MaleCount) }; Decimal[] FemaleNumbers = { Convert.ToDecimal(FemaleCount) }; Male.Data = MaleNumbers; Female.Data = FemaleNumbers; BarChartSeries[] GenderCountCollection = { Male, Female }; barchartGender.Series.AddRange(GenderCountCollection); barchartGender.DataBind(); lblTotalTenants.Text = (FemaleCount + MaleCount).ToString(); }
private void BindCorpClassesChart() { BarChartSeries yearSeries; DEWSReference.DeptEstadoDataSoapClient DEWS = new DeptEstadoDataSoapClient(); foreach (DataRow dRow in DEWS.GetCorpClassesByYearSeries().Rows) { yearSeries = new BarChartSeries(); yearSeries.Name = dRow["CorpClass"].ToString(); yearSeries.Data = new decimal[] { Convert.ToDecimal(dRow["CorpTotal2008"]), Convert.ToDecimal(dRow["CorpTotal2009"]), Convert.ToDecimal(dRow["CorpTotal2010"]), Convert.ToDecimal(dRow["CorpTotal2011"]), Convert.ToDecimal(dRow["CorpTotal2012"]) }; bcCorpClassesByYear.Series.Add(yearSeries); } }
//----------------------------------------------------------------------------------- //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/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(); } } } }
private void SetWaterfallStructure(BarChartSeries seriesItem, Column dataColumn) { var dataPoints = seriesItem.Elements <DataPoint>(); var labels = seriesItem.FirstElement <DataLabels>(); bool isFirstBar = dataColumn.Data[0] != null; if (!isFirstBar) { int valueToHideInd = dataColumn.Data.IndexOf(dataColumn.Data.First(v => v != null)); DataPoint dataPoint = dataPoints.FirstOrDefault(p => p.Index != null && p.Index.Val != null && p.Index.Val.Value == valueToHideInd); dataPoint.RemoveAllChildren <ChartShapeProperties>(); ChartShapeProperties chartShapeProperties = new ChartShapeProperties(); chartShapeProperties.Append(new A.NoFill()); dataPoint.Append(chartShapeProperties); DataLabel label = labels.Elements <DataLabel>().FirstOrDefault(p => p.Index != null && p.Index.Val != null && p.Index.Val.Value == valueToHideInd); if (label != null) { label.FirstElement <ShowValue>().Val.Value = false; } } labels.FirstElement <ShowValue>().Val.Value = false; }
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(); }
//Here be dragons internal override void AddData(Statistics stat) { // 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. var chartPart = CreateChartPart(drawingsPart); DocumentFormat.OpenXml.Drawing.Charts.Chart chart = chartPart.ChartSpace.AppendChild(new DocumentFormat.OpenXml.Drawing.Charts.Chart()); // Create a new clustered column chart. PlotArea plotArea = chart.AppendChild <PlotArea>(new PlotArea()); 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) }, new VaryColors() { Val = false } )); // Iterate through each key in the Dictionary collection and add the key to the chart Series // and add the corresponding value to the chart Values. BarChartSeries barChartSeries = barChart.AppendChild <BarChartSeries>(new BarChartSeries(new Index() { Val = new UInt32Value((uint)0) }, new Order() { Val = new UInt32Value((uint)0) }, new SeriesText(new NumericValue() { Text = "Timeline" }))); StringLiteral strLit = barChartSeries.AppendChild <CategoryAxisData>(new CategoryAxisData()) .AppendChild <StringLiteral>(new StringLiteral()); strLit.Append(new PointCount() { Val = new UInt32Value((uint)stat.Diffs.Count) }); NumberLiteral numLit = barChartSeries.AppendChild <DocumentFormat.OpenXml.Drawing.Charts.Values>( new DocumentFormat.OpenXml.Drawing.Charts.Values()) .AppendChild <NumberLiteral>(new NumberLiteral()); numLit.Append(new FormatCode("General")); numLit.Append(new PointCount() { Val = new UInt32Value((uint)stat.Diffs.Count) }); uint i = 0; foreach (var diff in stat.Diffs) { strLit.AppendChild <StringPoint>(new StringPoint() { Index = new UInt32Value(i) }) .Append(new NumericValue(diff.TimeStamp.ToString())); numLit.AppendChild <NumericPoint>(new NumericPoint() { Index = new UInt32Value(i) }) .Append(new NumericValue(diff.Value.ToString())); i++; } barChart.Append(new AxisId() { Val = new UInt32Value(48650112u) }); barChart.Append(new AxisId() { Val = new UInt32Value(48672768u) }); AppendCategoryAxis(plotArea, 48650112u, "Time, ms", 48672768U); AppendValueAxis(plotArea, 48672768u, "Duration, ms", 48650112U); // 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) }); // Position the chart on the worksheet using a TwoCellAnchor object. drawingsPart.WorksheetDrawing = new WorksheetDrawing(); AppendGraphicFrame(drawingsPart, chartPart); // Save the WorksheetDrawing object. drawingsPart.WorksheetDrawing.Save(); }
public IBarChart InitializeFromRange(IRange labelRange, IRange categoryRange) { uint orderStart = (uint)this.chartSpace.Charts.TakeWhile(c => c != this).Sum(c => c.SeriesCount); this.barChart.RemoveAllChildren <BarChartSeries>(); IWorksheet worksheet = labelRange.Worksheet; if (labelRange.Width == 1 && labelRange.Height > 0) { if (categoryRange.Height == 1 && categoryRange.Width > 0) { for (uint labelIndex = 0; labelIndex < labelRange.Height; ++labelIndex) { BarChartSeries series = this.barChart.AppendChild(new BarChartSeries() { Index = new Index() { Val = labelIndex }, Order = new Order() { Val = orderStart + labelIndex } }); series.AppendChild( new SeriesText().AppendChildFluent( new StringReference() { Formula = new Formula(labelRange[0, labelIndex].Reference), StringCache = new StringCache() .AppendChildFluent(new PointCount() { Val = 1 }) .AppendChildFluent(new StringPoint() { Index = 0, NumericValue = new NumericValue() { Text = labelRange[0, labelIndex].InnerValue } }) } ) ); series.AppendChild( new CategoryAxisData().AppendChildFluent( new StringReference() { Formula = new Formula(categoryRange.Formula), StringCache = new StringCache() .AppendChildFluent(new PointCount() { Val = (uint)categoryRange.Width.Value }) .AppendFluent(Enumerable.Range(0, categoryRange.Width.Value).Select(categoryIndex => new StringPoint() { Index = (uint)categoryIndex, NumericValue = new NumericValue() { Text = categoryRange[(uint)categoryIndex, 0].InnerValue } })) } ) ); string valuesFormula = ReferenceEncoder.EncodeRangeReference( worksheet.Name, categoryRange.StartColumn, false, labelRange.StartRow + labelIndex, false, categoryRange.EndColumn, false, labelRange.StartRow + labelIndex, false ); series.AppendChild( new Values().AppendChildFluent( new NumberReference() { Formula = new Formula(valuesFormula), NumberingCache = new NumberingCache() .AppendChildFluent(new PointCount() { Val = (uint)categoryRange.Width.Value }) .AppendFluent(Enumerable.Range(0, categoryRange.Width.Value).Select(categoryIndex => new NumericPoint() { Index = (uint)categoryIndex, NumericValue = new NumericValue() { Text = worksheet.Cells[categoryRange.StartColumn.Value + (uint)categoryIndex, labelRange.StartRow.Value + labelIndex].InnerValue } })) } ) ); } } else { throw new ArgumentException(); } } else if (labelRange.Height == 1 && labelRange.Width > 0) { if (categoryRange.Width == 1 && categoryRange.Height > 0) { for (uint labelIndex = 0; labelIndex < labelRange.Width; ++labelIndex) { BarChartSeries series = this.barChart.AppendChild(new BarChartSeries() { Index = new Index() { Val = labelIndex }, Order = new Order() { Val = orderStart + labelIndex } }); series.AppendChild( new SeriesText().AppendChildFluent( new StringReference() { Formula = new Formula(labelRange[labelIndex, 0].Reference), StringCache = new StringCache() .AppendChildFluent(new PointCount() { Val = 1 }) .AppendChildFluent(new StringPoint() { Index = 0, NumericValue = new NumericValue() { Text = labelRange[labelIndex, 0].InnerValue } }) } ) ); series.AppendChild( new CategoryAxisData().AppendChildFluent( new StringReference() { Formula = new Formula(categoryRange.Formula), StringCache = new StringCache() .AppendChildFluent(new PointCount() { Val = (uint)categoryRange.Height.Value }) .AppendFluent(Enumerable.Range(0, categoryRange.Height.Value).Select(categoryIndex => new StringPoint() { Index = (uint)categoryIndex, NumericValue = new NumericValue() { Text = categoryRange[0, (uint)categoryIndex].InnerValue } })) } ) ); string valuesFormula = ReferenceEncoder.EncodeRangeReference( worksheet.Name, labelRange.StartColumn + labelIndex, false, categoryRange.StartRow, false, labelRange.StartColumn + labelIndex, false, categoryRange.EndRow, false ); series.AppendChild( new Values().AppendChildFluent( new NumberReference() { Formula = new Formula(valuesFormula), NumberingCache = new NumberingCache() .AppendChildFluent(new PointCount() { Val = (uint)categoryRange.Height.Value }) .AppendFluent(Enumerable.Range(0, categoryRange.Height.Value).Select(categoryIndex => new NumericPoint() { Index = (uint)categoryIndex, NumericValue = new NumericValue() { Text = worksheet.Cells[labelRange.StartColumn.Value + labelIndex, categoryRange.StartRow.Value + (uint)categoryIndex].InnerValue } })) } ) ); } } else { throw new ArgumentException(); } } else { throw new ArgumentException(); } return(this); }
internal override void CreateChart(OpenXmlWriter writer, WorksheetPart part, SpreadsheetLocation location) { DrawingsPart drawingsPart = part.AddNewPart <DrawingsPart>(); writer.WriteStartElement(new Drawing() { Id = part.GetIdOfPart(drawingsPart) }); writer.WriteEndElement(); ChartPart chartPart = drawingsPart.AddNewPart <ChartPart>(); chartPart.ChartSpace = new ChartSpace(); chartPart.ChartSpace.Append(new EditingLanguage() { Val = new StringValue("en-US") }); Chart chartContainer = chartPart.ChartSpace.AppendChild <Chart>(new Chart()); chartContainer.AppendChild <AutoTitleDeleted>(new AutoTitleDeleted() { Val = false }); // Create a new clustered column chart. PlotArea plotArea = chartContainer.AppendChild <PlotArea>(new PlotArea()); Layout layout1 = plotArea.AppendChild <Layout>(new Layout()); BarChart barChart = plotArea.AppendChild <BarChart>(new BarChart()); barChart.Append(new BarDirection() { Val = BarDirectionValues.Bar }); barChart.Append(new BarGrouping() { Val = BarGroupingValues.Stacked }); barChart.Append(new GapWidth() { Val = (UInt16Value)75U }); barChart.Append(new Overlap() { Val = 100 }); GanttTypeChart ganttChart = new GanttTypeChart(UserSettings); var groupedData = GanttData .GroupBy(x => x.Name); List <GanttDataPairedSeries> ganttDataWithSeries = new List <GanttDataPairedSeries>(); for (int i = 0; i < groupedData.Max(x => x.Count()); i++) { // For each series create a hidden one for spacing. BarChartSeries barChartSeriesHidden = barChart.AppendChild <BarChartSeries>(new BarChartSeries( new Index() { Val = new UInt32Value((uint)(i * 2)) }, new Order() { Val = new UInt32Value((uint)(i * 2)) }, new SeriesText(new NumericValue() { Text = "Not Active" }))); BarChartSeries barChartSeriesValue = barChart.AppendChild <BarChartSeries>(new BarChartSeries( new Index() { Val = new UInt32Value((uint)(i * 2) + 1) }, new Order() { Val = new UInt32Value((uint)(i * 2) + 1) }, new SeriesText(new NumericValue() { Text = "Time Spent" }))); ganttChart.SetChartShapeProperties(barChartSeriesHidden, visible: false); ganttChart.SetChartShapeProperties(barChartSeriesValue, colorPoints: (uint)GanttData.Count); var ganttData = new List <GanttData>(); foreach (var data in groupedData.Where(x => x.Count() >= i + 1)) { ganttData.Add(data.ElementAt(i)); } ganttDataWithSeries.Add(new GanttDataPairedSeries() { BarChartSeriesHidden = barChartSeriesHidden, BarChartSeriesValue = barChartSeriesValue, Values = ganttData }); } ganttChart.SetChartAxis(ganttDataWithSeries, groupedData.ToList()); barChart.Append(new AxisId() { Val = new UInt32Value(48650112u) }); barChart.Append(new AxisId() { Val = new UInt32Value(48672768u) }); // Add the Category Axis (X axis). ganttChart.SetGanttCategoryAxis(plotArea); // Add the Value Axis (Y axis). ganttChart.SetGanttValueAxis(plotArea, GanttData.Min(x => x.Start), GanttData.Max(x => x.End)); chartContainer.Append(new PlotVisibleOnly() { Val = new BooleanValue(true) }); ganttChart.SetChartLocation(drawingsPart, chartPart, location); }
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> /// Sets the color of the series using a solidcolor brush /// If a null brush is supplied any color is removed so the color will be automatic /// </summary> /// <param name="line">The line.</param> /// <param name="brush">The brush.</param> public static void UpdateLineBrush(this OpenXmlCompositeElement line, Brush brush) { if (line == null) { return; } // If we have a BarChart, we really want tp update the SolidFill (not the Outline.SolidFill) BarChartSeries barChartSeries = line as BarChartSeries; if (barChartSeries != null) { // For BarCharts, we update the SolidFill barChartSeries.UpdateSeriesColour((SolidColorBrush)brush); } else { // Update the Outline.SolidFill + set the SolidFill to the same colour var chartShapeProperties = line.Descendants <ChartShapeProperties>().FirstOrDefault(); if (brush == null && !(brush is SolidColorBrush)) { if (chartShapeProperties != null) { line.RemoveChild <ChartShapeProperties>(chartShapeProperties); } return; } // the series title, this is the name of the column header var seriesText = line.Descendants <SeriesText>().FirstOrDefault(); if (chartShapeProperties == null) { chartShapeProperties = new ChartShapeProperties(); // if there's a series text then insert afterwards if (seriesText == null) { line.InsertAt <ChartShapeProperties>(chartShapeProperties, 0); } else { line.InsertAfter <ChartShapeProperties>(chartShapeProperties, seriesText); } } var outline = chartShapeProperties.Descendants <Outline>().FirstOrDefault(); if (outline == null) { outline = new Outline(); chartShapeProperties.InsertAt(outline, 0); } var outlineSolidFill = outline.Descendants <SolidFill>().FirstOrDefault(); if (outlineSolidFill == null) { outlineSolidFill = new SolidFill(); outline.Append(outlineSolidFill); } // Update the fill with the supplied brush colour outlineSolidFill.UpdateSolidFill((SolidColorBrush)brush); // Clones the OutlineSolidFill as the SolidFill of the series... var solidFill = chartShapeProperties.GetFirstChild <SolidFill>(); if (solidFill != null) { chartShapeProperties.RemoveChild(solidFill); } chartShapeProperties.InsertAt(outlineSolidFill.CloneNode(true), 0); } }
private void CreateHistogram(PlotArea plotArea, Statistics stat, uint index, uint categoryAxisId, uint valueAxisId) { BarChart barChart = plotArea.AppendChild <BarChart>( new BarChart( new BarDirection() { Val = new EnumValue <BarDirectionValues>(BarDirectionValues.Column) }, new BarGrouping() { Val = new EnumValue <BarGroupingValues>(BarGroupingValues.Clustered) }, new VaryColors() { Val = false } )); BarChartSeries barChartSeries = barChart.AppendChild(new BarChartSeries(new Index() { Val = new UInt32Value(index) }, new Order() { Val = new UInt32Value(index) }, new SeriesText(new NumericValue() { Text = "Histogram" }))); StringLiteral strLit = barChartSeries.AppendChild <CategoryAxisData>(new CategoryAxisData()) .AppendChild <StringLiteral>(new StringLiteral()); strLit.Append(new PointCount() { Val = new UInt32Value((uint)stat.Frequencies.Count) }); NumberLiteral numLit = barChartSeries.AppendChild <DocumentFormat.OpenXml.Drawing.Charts.Values>( new DocumentFormat.OpenXml.Drawing.Charts.Values()) .AppendChild <NumberLiteral>(new NumberLiteral()); numLit.Append(new FormatCode("General")); numLit.Append(new PointCount() { Val = new UInt32Value((uint)stat.Frequencies.Count) }); for (uint i = 0; i < stat.Frequencies.Count; i++) { strLit.AppendChild <StringPoint>(new StringPoint() { Index = new UInt32Value(i) }) .Append(new NumericValue(stat.Frequencies[(int)i].Value.ToString())); numLit.AppendChild <NumericPoint>(new NumericPoint() { Index = new UInt32Value(i) }) .Append(new NumericValue(stat.Frequencies[(int)i].Count.ToString())); } barChart.Append(new AxisId() { Val = new UInt32Value(categoryAxisId) }); barChart.Append(new AxisId() { Val = new UInt32Value(valueAxisId) }); AppendCategoryAxis(plotArea, categoryAxisId, "Time, ms", valueAxisId); AppendValueAxis(plotArea, valueAxisId, "Number of samples", categoryAxisId); }
private static void InsertChartInSpreadsheet(string docName, string worksheetName, string title, Dictionary <string, int> data) { // Open the document for editing. using (SpreadsheetDocument document = SpreadsheetDocument.Open(docName, true)) { IEnumerable <Sheet> sheets = document.WorkbookPart.Workbook.Descendants <Sheet>(). Where(s => s.Name == worksheetName); if (sheets.Count() == 0) { // The specified worksheet does not exist. return; } WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id); // 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) })); uint i = 0; // Iterate through each key in the Dictionary collection and add the key to the chart Series // and add the corresponding value to the chart Values. foreach (string key in data.Keys) { BarChartSeries barChartSeries = barChart.AppendChild <BarChartSeries>(new BarChartSeries(new Index() { Val = new UInt32Value(i) }, new Order() { Val = new UInt32Value(i) }, new SeriesText(new NumericValue() { Text = key }))); StringLiteral strLit = barChartSeries.AppendChild <CategoryAxisData>(new CategoryAxisData()).AppendChild <StringLiteral>(new StringLiteral()); strLit.Append(new PointCount() { Val = new UInt32Value(1U) }); strLit.AppendChild <StringPoint>(new StringPoint() { Index = new UInt32Value(0U) }).Append(new NumericValue(key)); NumberLiteral numLit = barChartSeries.AppendChild <DocumentFormat.OpenXml.Drawing.Charts.Values>( new DocumentFormat.OpenXml.Drawing.Charts.Values()).AppendChild <NumberLiteral>(new NumberLiteral()); numLit.Append(new FormatCode("General")); numLit.Append(new PointCount() { Val = new UInt32Value(1U) }); numLit.AppendChild <NumericPoint>(new NumericPoint() { Index = new UInt32Value(0u) }) .Append(new NumericValue(data[key].ToString())); i++; } 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.NumberingFormat() { 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 DocumentFormat.OpenXml.Drawing.Spreadsheet.FromMarker(new ColumnId("1"), new ColumnOffset("581025"), new RowId("1"), new RowOffset("114300"))); twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.ToMarker(new ColumnId("10"), new ColumnOffset("276225"), new RowId("16"), 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(); } }
/// <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 }
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()); } ; }