Make an Excel chart in C#

[Excel chart]

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 Example   Follow me on Twitter   RSS feed   Donate




This entry was posted in Excel, interoperability, Office and tagged , , , , , , , , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *