Wiki
Clone wikiAspose for OpenXML / Insert a chart
OpenXML Excel:
using DocumentFormat.OpenXml; using DocumentFormat.OpenXml.Packaging; using DocumentFormat.OpenXml.Spreadsheet; using DocumentFormat.OpenXml.Drawing; using DocumentFormat.OpenXml.Drawing.Charts; using DocumentFormat.OpenXml.Drawing.Spreadsheet; class Program { static void Main(string[] args) { string docName ="Insert a chart.xlsx"; string worksheetName = "Sheet1"; string title = "New Chart"; Dictionary<string, int> data = new Dictionary<string, int>(); data.Add("abc", 1); InsertChartInSpreadsheet(docName, worksheetName, title, data); } // Given a document name, a worksheet name, a chart title, and a Dictionary collection of text keys // and corresponding integer data, creates a column chart with the text as the series and the integers as the values. 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(title)); 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("9"), new ColumnOffset("581025"), new RowId("17"), new RowOffset("114300"))); twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.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(); } } }
Aspose.Cells:
class Program { static void Main(string[] args) { string docName = "Insert a chart.xlsx"; string worksheetName = "Sheet2"; string title = "New Chart"; string dataRange = "A1:B3"; InsertChartInSpreadsheet(docName, worksheetName, title, dataRange); } private static void InsertChartInSpreadsheet(string docName, string worksheetName, string title, string dataRange) { //Instantiating a Workbook object Workbook workbook = new Workbook(docName); //Obtaining the reference of the worksheet by passing its Name Worksheet worksheet = workbook.Worksheets[worksheetName]; //Adding a chart to the worksheet int chartIndex = worksheet.Charts.Add(Aspose.Cells.Charts.ChartType.Bar, 5, 0, 15, 5); //Accessing the instance of the newly added chart Aspose.Cells.Charts.Chart chart = worksheet.Charts[chartIndex]; //Set title of the chart chart.Name = title; //Adding SeriesCollection (chart data source) to the chart ranging from "A1" cell to "B3" chart.NSeries.Add(dataRange, true); //Saving the Excel file workbook.Save(docName); } }
Download
Updated