internal C.XValues ToXValues() { var xv = new C.XValues(); if (UseMultiLevelStringReference) { xv.MultiLevelStringReference = MultiLevelStringReference.ToMultiLevelStringReference(); } if (UseNumberReference) { xv.NumberReference = NumberReference.ToNumberReference(); } if (UseNumberLiteral) { xv.NumberLiteral = NumberLiteral.ToNumberLiteral(); } if (UseStringReference) { xv.StringReference = StringReference.ToStringReference(); } if (UseStringLiteral) { xv.StringLiteral = StringLiteral.ToStringLiteral(); } return(xv); }
/// <summary> /// Modify/Add data into chart XML /// </summary> /// <param name="column_index">Corresponds to the column index that needs to be modified in chart spreadsheet (Ex: A, B, C, ...)</param> /// <param name="row_index">Corresponds to the column index that needs to be modified in excel </param> /// <param name="new_value">Corresponds to the new value we need to insert to the cell </param> protected override void ModifyChartXML_Data(string column_index, uint row_index, string new_value) { LineChartSeries linechart_series = chart_part.ChartSpace.Descendants <LineChartSeries>().Where(s => string.Compare(s.InnerText, worksheet_name + "!$" + column_index + "$1", true) > 0).First(); DocumentFormat.OpenXml.Drawing.Charts.Values v = linechart_series.Descendants <DocumentFormat.OpenXml.Drawing.Charts.Values>().FirstOrDefault(); NumberReference nr = v.Descendants <NumberReference>().First(); NumberingCache nc = nr.Descendants <NumberingCache>().First(); try { NumericPoint np = nc.Descendants <NumericPoint>().ElementAt((int)row_index - 2); NumericValue nv = np.Descendants <NumericValue>().First(); nv.Text = new_value; } catch (Exception) { // Create new data and append to previous XML nc.PointCount.Val = nc.PointCount.Val + 1; NumericValue nv = new NumericValue(new_value); NumericPoint np = new NumericPoint(nv); np.Index = (uint)nc.Descendants <NumericPoint>().ToList().Count; nc.Append(np); // Change fomula range DocumentFormat.OpenXml.Drawing.Charts.Formula f = nr.Descendants <DocumentFormat.OpenXml.Drawing.Charts.Formula>().FirstOrDefault(); f.Text = worksheet_name + "!$" + column_index + "$2:$" + column_index + "$" + GetRowIndexByNum((int)row_index - 2).ToString(); } }
public NumberReference Build() { var numberReference = new NumberReference(); var formulaString = string.Concat(this.Options.Letter, "$", this.Options.RowStart, ":", this.Options.Letter, this.Options.RowEnd); numberReference.Formula = new Formula(formulaString); numberReference.NumberingCache = new NumberingCache(); numberReference.NumberingCache.FormatCode = new FormatCode(this.Options.FormatCode); numberReference.NumberingCache.PointCount = new PointCount { Val = (uint)this.Options.Values.Count }; for (var index = 0; index < this.Options.Values.Count; index++) { var numericPoint = new NumericPoint(); numericPoint.Index = (uint)index; numericPoint.NumericValue = new NumericValue(this.Options.Values[index]); numberReference.NumberingCache.Append(numericPoint); } return(numberReference); }
internal C.CategoryAxisData ToCategoryAxisData() { var cad = new C.CategoryAxisData(); if (UseMultiLevelStringReference) { cad.MultiLevelStringReference = MultiLevelStringReference.ToMultiLevelStringReference(); } if (UseNumberReference) { cad.NumberReference = NumberReference.ToNumberReference(); } if (UseNumberLiteral) { cad.NumberLiteral = NumberLiteral.ToNumberLiteral(); } if (UseStringReference) { cad.StringReference = StringReference.ToStringReference(); } if (UseStringLiteral) { cad.StringLiteral = StringLiteral.ToStringLiteral(); } return(cad); }
/// <summary> /// Creates the number reference. /// </summary> /// <param name="values">The values.</param> /// <param name="formulaText">The formula text.</param> /// <returns></returns> protected static NumberReference CreateNumberReference(NumberDataSourceType values, string formulaText) { values.RemoveAllChildren <NumberReference>(); NumberReference numberReference = values.AppendChild <NumberReference>(new NumberReference()); numberReference.Formula = new Formula(); numberReference.Formula.Text = formulaText; return(numberReference); }
internal SLNumberDataSourceType Clone() { var ndst = new SLNumberDataSourceType(); ndst.bUseNumberReference = bUseNumberReference; ndst.NumberReference = NumberReference.Clone(); ndst.bUseNumberLiteral = bUseNumberLiteral; ndst.NumberLiteral = NumberLiteral.Clone(); return(ndst); }
/// <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); } }
internal C.Plus ToPlus() { var plus = new C.Plus(); if (UseNumberReference) { plus.NumberReference = NumberReference.ToNumberReference(); } if (UseNumberLiteral) { plus.NumberLiteral = NumberLiteral.ToNumberLiteral(); } return(plus); }
internal C.Minus ToMinus() { var minus = new C.Minus(); if (UseNumberReference) { minus.NumberReference = NumberReference.ToNumberReference(); } if (UseNumberLiteral) { minus.NumberLiteral = NumberLiteral.ToNumberLiteral(); } return(minus); }
internal C.BubbleSize ToBubbleSize() { var bs = new C.BubbleSize(); if (UseNumberReference) { bs.NumberReference = NumberReference.ToNumberReference(); } if (UseNumberLiteral) { bs.NumberLiteral = NumberLiteral.ToNumberLiteral(); } return(bs); }
internal C.YValues ToYValues() { var yv = new C.YValues(); if (UseNumberReference) { yv.NumberReference = NumberReference.ToNumberReference(); } if (UseNumberLiteral) { yv.NumberLiteral = NumberLiteral.ToNumberLiteral(); } return(yv); }
internal C.Values ToValues() { var v = new C.Values(); if (UseNumberReference) { v.NumberReference = NumberReference.ToNumberReference(); } if (UseNumberLiteral) { v.NumberLiteral = NumberLiteral.ToNumberLiteral(); } return(v); }
/// <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; } } } }
internal SLAxisDataSourceType Clone() { var adst = new SLAxisDataSourceType(); adst.bUseMultiLevelStringReference = bUseMultiLevelStringReference; adst.bUseNumberLiteral = bUseNumberLiteral; adst.bUseNumberReference = bUseNumberReference; adst.bUseStringLiteral = bUseStringLiteral; adst.bUseStringReference = bUseStringReference; adst.MultiLevelStringReference = MultiLevelStringReference.Clone(); adst.NumberLiteral = NumberLiteral.Clone(); adst.NumberReference = NumberReference.Clone(); adst.StringLiteral = StringLiteral.Clone(); adst.StringReference = StringReference.Clone(); return(adst); }
/// <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 <ScatterChartSeries>(); ScatterChartSeries scatterChartSeries = chart.AppendChild <ScatterChartSeries>(new ScatterChartSeries()); Outline outline = new Outline() { Width = 28575 }; NoFill noFill = new NoFill(); outline.Append(noFill); scatterChartSeries.ChartShapeProperties = new ChartShapeProperties(outline); UpdateSeriesText(sheetName, scatterChartSeries, "B", 1, chartData.yColumnName); XValues xValues = scatterChartSeries.AppendChild <XValues>(new XValues()); YValues yValues = scatterChartSeries.AppendChild <YValues>(new YValues()); NumberReference referenceX = CreateNumberReference(xValues, sheetName + "!$A$2:$A$" + (chartData.Count + 1).ToString()); NumberReference referenceY = CreateNumberReference(yValues, sheetName + "!$B$2:$B$" + (chartData.Count + 1).ToString()); NumberingCache ncX; PointCount ptXCount; NumberingCache ncY; PointCount ptYCount; SetNumberingCache(referenceX, out ncX, out ptXCount); SetNumberingCache(referenceY, out ncY, out ptYCount); int rowIndex = 0; foreach (var xToY in chartData.xValToYValMap) { AddNumericPoint(ncX, ptXCount, rowIndex, xToY.Key.ToString()); AddNumericPoint(ncY, ptYCount, rowIndex, xToY.Value.ToString()); rowIndex += 1; } } }
private void FillNumberReference(NumberReference valuesNumberReference, ChartSeriesElement newSeriesItem, Column dataColumn) { int dataCount = dataColumn.Data.Count; UInt32Value pointCount = new UInt32Value((uint)dataCount); valuesNumberReference.Formula.Text = newSeriesItem.ValuesAddress; valuesNumberReference.NumberingCache.RemoveAllChildren <NumericPoint>(); valuesNumberReference.NumberingCache.PointCount.Val = pointCount; for (int rowNo = 0; rowNo < dataColumn.Data.Count; rowNo++) { if (dataColumn.Data[rowNo] != null) { var point = new NumericPoint() { Index = new UInt32Value((uint)rowNo) }; point.NumericValue = new NumericValue(dataColumn.Data[rowNo] == null ? "0" : string.Format(System.Globalization.CultureInfo.InvariantCulture, "{0}", dataColumn.Data[rowNo])); valuesNumberReference.NumberingCache.Append(point); } } }
/// <summary> /// Sets the numbering cache. /// </summary> /// <param name="numberReference">The number reference.</param> /// <param name="numberingCache">The numbering cache.</param> /// <param name="pointCount">The point count.</param> protected static void SetNumberingCache(NumberReference numberReference, out NumberingCache numberingCache, out PointCount pointCount) { numberingCache = numberReference.Descendants <NumberingCache>().FirstOrDefault(); if (numberingCache == null) { numberingCache = new NumberingCache(); numberReference.AppendChild <NumberingCache>(numberingCache); } pointCount = numberingCache.Descendants <PointCount>().FirstOrDefault(); if (pointCount == null) { pointCount = new PointCount(); numberingCache.AppendChild <PointCount>(pointCount); } pointCount.Val = new UInt32Value((uint)0); numberingCache.FormatCode = new FormatCode("General"); }
/// <summary> /// 指定した数値numをセットする /// </summary> /// <param name="num">数値</param> /// <returns>解放後、参照カウンタを1下げた値を返す。0の場合は、完全に解放されたことを示す。</returns> public ulong Acquire(ref ulong num) { NumberReference Tmp = new NumberReference(); num = default(ulong); //------------------------------------------- // 存在する場合はカウントを+1する。 //------------------------------------------- if (num != default(ulong)) { if (m_useData.ContainsKey(num)) { ++m_useData[num].refCnt; return(m_useData[num].refCnt); } } //------------------------------------------- // フリーリストが空なら、新しいものを追加し、 // そうでなければ、使用済みの値を使う。 //------------------------------------------- if (m_freeSlots.Count == 0) { // コンテナが空 num = ++m_numCount; Tmp.id = m_numCount; Tmp.refCnt = 1; m_useData.Add(Tmp.id, Tmp); } else { Tmp = m_freeSlots.Dequeue(); num = Tmp.id; Tmp.refCnt = 1; m_useData.Add(Tmp.id, Tmp); } return(Tmp.refCnt); }
/// <summary> /// 指定した数値numを解放する /// </summary> /// <param name="num">数値</param> /// <returns>解放後、参照カウンタを1下げた値を返す。0の場合は、完全に解放されたことを示す。</returns> public ulong Release(ulong num) { NumberReference Tmp = new NumberReference(); // どれか? // それが有効なことを確認 if (!m_useData.ContainsKey(num)) { Debug.Assert(false); return(0); } if (--m_useData[num].refCnt <= 0) { // 削除 & 未使用のフリーリストに追加 m_freeSlots.Enqueue(m_useData[num]); m_useData.Remove(num); return(0); } return(m_useData[num].refCnt); }
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 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(); } }
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()); } ; }
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); }