Create a Chart in Excel

using OpenXML SDK

The following assembly directives are required to compile the code in this example.

#!c#

using System.Collections.Generic;
using System.Linq;
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;

After opening the spreadsheet file for read/write access, the code verifies if the specified worksheet exists. It then adds a new DrawingsPart object using the AddNewPart method, appends it to the worksheet, and saves the worksheet part. The code then adds a new ChartPart object, appends a new ChartSpace object to the ChartPart object, and then appends a new EditingLanguage object to the ChartSpace object that specifies the language for the chart is English-US.

#!c#

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());

The code creates a new clustered column chart by creating a new BarChart object with BarDirectionValues object set to Column and BarGroupingValues object set to Clustered. The code then iterates through each key in the Dictionary class. For each key, it appends a BarChartSeries object to the BarChart object and sets the SeriesText object of the BarChartSeries object to equal the key. For each key, it appends a NumberLiteral object to the Values collection of the BarChartSeries object and sets the NumberLiteral object to equal the Dictionary class value corresponding to the key.

#!c#

// 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++;
}

The code adds the CategoryAxis object and ValueAxis object to the chart and sets the value of the following properties: Scaling, AxisPosition, TickLabelPosition, CrossingAxis, Crosses, AutoLabeled, LabelAlignment, and LabelOffset. It also adds the Legend object to the chart and saves the chart part.

#!c#

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();

The code positions the chart on the worksheet by creating a WorksheetDrawing object and appending a TwoCellAnchor object. The TwoCellAnchor object specifies how to move or resize the chart if you move the rows and columns between the FromMarker and ToMarker anchors. The code then creates a GraphicFrame object to contain the chart and names the chart "Chart 1," and saves the worksheet drawing.

#!c#

// 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();

The following is the complete sample code

#!c#

// 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();
    }

}

using Aspose SDK

Things to Know Before Creating a Chart Before we start demonstrating the creation of charts, it's important to discuss some basic concepts that would be very helpful for the developers to create any kind of charts using Aspose.Cells.

Charting Objects

Aspose.Cells provides some special set of classes in Aspose.Cells.Charts namepsace that are used to create all kinds of charts supported by Aspose.Cells. These classes are used to create Charting Objects , which act as the building blocks of a chart. These charting objects are listed below:

Series , represents a single data series in a chart

Axis , represents a chart's axis

Chart , represents a single Excel chart

ChartArea , represents the chart area in the worksheet

ChartDataTable , represents a chart data table

ChartFrame , represents the frame object in a chart

ChartPoint , represents a single point in a series in a chart

ChartPointCollection , represents a collection that contains all the points in one series

Charts , represents a collection of Chart objects

DataLabels , represents a collection of all the DataLabel objects for the specified Series

FillFormat , represents fill format for a shape

Floor , represents the floor of a 3D chart

Legend , represents the chart legend

Line , represents the chart line

SeriesCollection , represents a collection of Series objects

TickLabels , represents the tick mark labels associated with tick marks on a chart axis

Title , represents the title of a chart or axis

Trendline , represents a trendline in a chart

TrendlineCollection , represents a collection of all Trendline objects for the specified data series

Walls , represents the walls of a 3D chart

Creating a Simple Chart

Developers can create many types of charts using Aspose.Cells. All types of standard charts supported by Aspose.Cells are pre-defined in an enumeration named as Aspose.Cells.Charts. ChartType.

To create a chart using Aspose.Cells, please follow the steps below:

Add some data to worksheet cells by using the PutValue method of a Cell object. This data will be used as a data source for the chart.

*Add a chart to the worksheet by calling the Add method of Charts collection, which is encapsulated in the Worksheet object. You will also specify the type of chart to add in the worksheet using the ChartType enumeration. For example, in the example given below, we have selected ChartType.Pyramid value as a chart type.

Access the newly added Chart object from the Charts collection by passing the chart index.
Use any of the Charting Objects (explained in the above section) encapsulated in the Chart object to manage your chart. For example, in the example given below, we have used SeriesCollection charting object to specify the data source for the chart.
When adding source data to chart, the data source can be a range of cells (such as "A1:C3"), or a sequence of non-contiguous cells (such as "A1, A3, A5"), or a sequence of values (such as "1,2,3").

#!c#

using System;
using System.IO;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Aspose.Cells;

namespace CreateCharts
{
    class Program
    {
        static void Main(string[] args)
        {
            string projectFiles = Path.GetFullPath("../../Files/");

            //Instantiating a Workbook object
            Workbook workbook = new Workbook();

            //Adding a new worksheet to the Excel object
            int sheetIndex = workbook.Worksheets.Add();

            //Obtaining the reference of the newly added worksheet by passing its sheet index
            Worksheet worksheet = workbook.Worksheets[sheetIndex];

            //Adding a sample value to "A1" cell
            worksheet.Cells["A1"].PutValue(50);

            //Adding a sample value to "A2" cell
            worksheet.Cells["A2"].PutValue(100);

            //Adding a sample value to "A3" cell
            worksheet.Cells["A3"].PutValue(150);

            //Adding a sample value to "B1" cell
            worksheet.Cells["B1"].PutValue(4);

            //Adding a sample value to "B2" cell
            worksheet.Cells["B2"].PutValue(20);

            //Adding a sample value to "B3" cell
            worksheet.Cells["B3"].PutValue(50);

            //Adding a chart to the worksheet
            int chartIndex = worksheet.Charts.Add(Aspose.Cells.Charts.ChartType.Pyramid, 5, 0, 15, 5);

            //Accessing the instance of the newly added chart
            Aspose.Cells.Charts.Chart chart = worksheet.Charts[chartIndex];

            //Adding SeriesCollection (chart data source) to the chart ranging from "A1" cell to "B3"
            chart.NSeries.Add("A1:B3", true);

            //Saving the Excel file
            workbook.Save( projectFiles + "output.xls");
            Console.WriteLine("File Saved Successfully.");
            Console.ReadKey();

        }
    }
}

Source: https://bitbucket.org/asposemarketplace/aspose-for-openxml/src/ffec3cde9d87/Aspose.Cells/CreateCharts/?at=master

Wiki

Aspose for OpenXML / Create a Chart in Excel

Create a Chart in Excel

using OpenXML SDK

using Aspose SDK