[C# Helper]
Index Books FAQ Contact About Rod
[Essential Algorithms, Second Edition]

[The Modern C# Challenge]

[WPF 3d, Three-Dimensional Graphics with WPF and C#]

[The C# Helper Top 100]

[Interview Puzzles Dissected]

[C# 24-Hour Trainer]

[Beginning Software Engineering]

[C# 5.0 Programmer's Reference]

[MCSD Certification Toolkit (Exam 70-483): Programming in C#]

Title: Make an Excel chart in C#

[Make an Excel chart in C#]

This example uses the following code to create an Excel chart in a new workbook.

private void btnCreateChart_Click( object sender, EventArgs e) { // Get the Excel application object. Excel.Application excel_app = new Excel.ApplicationClass(); // Make Excel visible (optional). excel_app.Visible = true; // Create the workbook. Excel.Workbook workbook = excel_app.Workbooks.Add(Type.Missing); // Get the first worksheet. Excel.Worksheet sheet = (Excel.Worksheet)workbook.Sheets[1]; // Add some random data to a range of cells. object[,] values = { { "Salesperson", 2005, 2006, 2007, 2008, 2009, 2010}, { "Ann", 0, 0, 0, 0, 0, 0}, { "Bob", 0, 0, 0, 0, 0, 0}, { "Cat", 0, 0, 0, 0, 0, 0}, { "Don", 0, 0, 0, 0, 0, 0}, }; Random rand = new Random(); for (int i = 1; i < 5; i++) { for (int j = 1; j < 7; j++) { values[i, j] = rand.Next(60, 101); } } Excel.Range value_range = sheet.get_Range("A1", "G5"); value_range.Value2 = values; Excel.Range colA = (Excel.Range)sheet.Columns[1, Type.Missing]; colA.ColumnWidth = 12; // Create the chart. Excel.Shape chart_shape = sheet.Shapes.AddChart( Excel.XlChartType.xlLine, 400, 5, 300, 200); Excel.Chart chart = chart_shape.Chart; // Set the data. Excel.Range chart_range = sheet.get_Range("A2", "G5"); chart.SetSourceData(chart_range, Excel.XlRowCol.xlRows); // Set the X axis labels. Excel.Range axis_range = sheet.get_Range("B1", "G1"); Excel.Series series = (Excel.Series)chart.SeriesCollection(1); series.XValues = axis_range; // Delete the saved file if it already exists. string filename = Application.StartupPath + "\\Chart.xlsx"; System.IO.File.Delete(filename); // Save the changes and close the workbook. workbook.SaveAs(filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); workbook.Close(true, Type.Missing, Type.Missing); // Close the Excel server. excel_app.Quit(); MessageBox.Show("Done"); }

The code starts by creating an Excel application object. It uses that object's Workbooks.Add method to create a new workbook. It then gets that workbook's first worksheet.

Next the code defines an array containing the data that it will add to the worksheet. It starts with some initial data and then uses a Random object to randomize some sales figures. The code then creates a Range object representing cells A1..G5 and sets their values equal to the values in the array. The code also resizes the first column to make it wide enough to display the data.

Next the code uses the worksheet's Shapes.AddChart method to create a new chart object with type xlLine. The new chart is 400 pixels from the left edge of the worksheet, 5 pixels from the top edge, has width 300, and height 300.

The Shapes.AddChart method returns a Shape object. That object's Chart property is a Chart object. The code saves that object in variable chart so it can work with it more easily.

Next the program uses the chart object's SetSourceData method to tell it what data to display. The code gets the chart's first series (the X axis) and sets it to display the year numbers below the X axis.

At this point the program is ready to save the new workbook. It first deletes any previously created workbook with the same name and then calls the workbook's SaveAs method to save the workbook with the desired name. (Note that the program saves the workbook in exclusive mode. If you save it in shared mode, then you can't manipulate the chart if you open it later.)

Finally the program closes the workbook and the Excel application object.

Download the example to experiment with it and to see additional details.

© 2009-2022 Rocky Mountain Computer Consulting, Inc. All rights reserved.