Excel Charts in C# Applications

Introduction

If you want to create advanced graphics and charts in C# applications, you can create them using C# and GDI+, or using third-party graphics and chart packages. However, you can achieve this goal by taking advantage of Microsoft Excel's built-in chart features directly in C# applications.

Microsoft Visual Studio .NET makes it possible for you to create rich Microsoft Excel-based applications based on the C# framework. You can take advantage of all of the functionality provided by Excel's large object models in C# applications. In this article, I will show you how to use the surface charts in your C# applications.

Background

In order for Excel to be used in C# applications, we need to resolve the issue of interoperation between C# and Excel. Excel application can be regarded as a COM server, so the basis of interoperation between C# and Excel applications is COM Interop. The .NET framework provides good support for interaction with COM components. To use Excel COM components in a C# project, you simply need to add this COM component to reference. This can be done by right clicking the project in the Solution Explorer and selecting Add Reference. Click the COM tab and select the appropriate type of Object library depending on the version of the Microsoft Office you are using:

· Office 97: Microsoft Excel 8.0 Object Library

· Office 2000: Microsoft Excel 9.0 Object Library

· Office XP: Microsoft Excel 10.0 Object Library

· Office 2003: Microsoft Excel 11.0 Object Library

After this step, you should find that references have been added for the Office Core and Excel as well. In my case i built my project using Visual Studio .NET 2005 and Mircosoft office XP. So my reference to Excel is Microsoft Excel 10.0 Object Library.

Next, I will the surface chart as an example to demonstrate how to involve Excel in a C# application. In this example, I will create a Windows Form application with two buttons on Form1. One button is used to start Plot, and the other to quit Excel and close Form1. Clicking the Plot button will launch Excel, add a new workbook to the collection of workbooks, get Active Sheet, and put data into Excel cells, and draw the surface chart.

Excel surface charts display multiple-series data on a surface using the category axis, value axis, and a third axis that displays the series name. Surface charts are used to show the optimum combination of category and series data.

Using the code

The project is implemented in a single Form1 class. In this class, we first create data using a 1D and 2D array and assign them to the Excel worksheet. This data must be structured in a certain format. The categories (used as the X axis) are in the first column. The second row represents the series names (used as the Y axis). Finally, the data values are formed by a 2D data array.

As long as the data is assigned to the Excel worksheet, you can create a surface chart by selecting the surface chart type:

xlChart.ChartType = XlChartType.xlSurface;

The rest of the code in the Form1 class is used to customize the axes, add axis labels and a title, and remove the legend from the chart.

This project produces the output of Figure 1. It can be seen that the chart is created by ranges of data values, which is evident by the fact that the colors vary with the values along the vertical (the Z) axis. The number of colors on the surface chart is based on the major unit of the Z axis.

Figure 1 Excel surface chart created in a C# application.

The Excel surface chart has four subtypes:

· xlSurface: displays a standard surface chart.

· xlSurfaceWireFrame: displays a surface chart without colors.

· xlSurfaceTopView: displaysthe a surface chart viewed from above.

· xlSurfaceTopViewWireFrame: displays a surface chart without colors, viewed from above.

You can select a different subtype of surface chart from the above four options according to your application requirements.

I should point out here that Excel surface charts have some limits: they are not true X-Y-Z charts. X and Y are not treated as numeric data, and must consist of regularly defined, evenly spaced categories. Exactly one Z value is needed for each X-Y pair. Excel surface charts are drawn by connecting Z values at X-Y nodes with straight lines and planar sections. A saddle point is not accurately drawn if it occurs between the X-Y nodes. Color-filled surface charts in Excel do not allow transparency, meaning that you can not visualize the hidden parts of the surface.

This project is from the examples of Chapter 9 of my new book "Practical C# Charts and Graphics", where you can find more advanced chart and graphics programming for real-world .NET applications. For more information, please visit my website at www.authors.unicadpublish.com/~jack_xu

About the Author

Dr. Jack Xu has a Ph.D in theoretical physics. He has over 15 years programming experience in Basic, Fortran, C, C++, Matlab, and C#, specializing in numerical computation methods, algorithms, physical modeling, computer-aided design (CAD) development, graphics user interface, and 3D graphics. Currently, he is responsible for developing commercial CAD tools based on Microsoft .NET framework.

Please also read my other articles:

"Draw US Flag using C# and GDI+"

"Create Custom Color Maps in C#"

"Create a Custom Color Shading in C#"

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

--------------------------------------------------------------------------------------------

Form1.cs

using System;
using System.Windows.Forms;
using Excel;

namespace Example9_7
{
    public partial class Form1 : Form
    {
        private Excel.Application xla;

        public Form1()
        {
            InitializeComponent();
            xla = new Excel.Application();
        }

        private void btnPlot_Click(object sender, EventArgs e)
        {
            xla.Visible = true;
            Workbook wb = xla.Workbooks.Add(XlSheetType.xlWorksheet);
            Worksheet ws = (Worksheet)xla.ActiveSheet;

           // Now create the chart.
            ChartObjects chartObjs = (ChartObjects)ws.ChartObjects(Type.Missing);
            ChartObject chartObj = chartObjs.Add(100, 20, 300, 300);
            Chart xlChart = chartObj.Chart;

            int nRows = 25;
            int nColumns = 25;
            string upperLeftCell = "B3";
            int endRowNumber = System.Int32.Parse(upperLeftCell.Substring(1))
                + nRows - 1;
            char endColumnLetter = System.Convert.ToChar(
                Convert.ToInt32(upperLeftCell[0]) + nColumns - 1);
            string upperRightCell = System.String.Format("{0}{1}",
                endColumnLetter, System.Int32.Parse(upperLeftCell.Substring(1)));
            string lowerRightCell = System.String.Format("{0}{1}",
                endColumnLetter, endRowNumber);

            // Send single dimensional array to Excel:
            Range rg1 = ws.get_Range("B2", "Z2");
            double[] xarray = new double[nColumns];
            ws.Cells[1, 1] = "Data for surface chart";
            for (int i = 0; i < xarray.Length; i++)
            {
                xarray[i] = -3.0f + i * 0.25f;
                ws.Cells[i + 3, 1] = xarray[i];
                ws.Cells[2, 2 + i] = xarray[i];
            }

            Range rg = ws.get_Range(upperLeftCell, lowerRightCell);
            rg.Value2 = AddData(nRows,nColumns);

            Range chartRange = ws.get_Range("A2", lowerRightCell);
            xlChart.SetSourceData(chartRange, Type.Missing);
            xlChart.ChartType = XlChartType.xlSurface;

            // Customize axes:
            Axis xAxis = (Axis)xlChart.Axes(XlAxisType.xlCategory,
                XlAxisGroup.xlPrimary);
            xAxis.HasTitle = true;
            xAxis.AxisTitle.Text = "X Axis";

            Axis yAxis = (Axis)xlChart.Axes(XlAxisType.xlSeriesAxis,
                XlAxisGroup.xlPrimary);
            yAxis.HasTitle = true;
            yAxis.AxisTitle.Text = "Y Axis";

            Axis zAxis = (Axis)xlChart.Axes(XlAxisType.xlValue,
                XlAxisGroup.xlPrimary);
            zAxis.HasTitle = true;
            zAxis.AxisTitle.Text = "Z Axis";

            // Add title:
            xlChart.HasTitle = true;
            xlChart.ChartTitle.Text = "Peak Function";

            // Remove legend:
            xlChart.HasLegend = false;
           
            /* This following code is used to create Excel default color indices:
            for (int i = 0; i < 14; i++)
            {
                string cellString = "A" + (i + 1).ToString();
                ws.get_Range(cellString, cellString).Interior.ColorIndex = i + 1;
                ws.get_Range(cellString, cellString).Value2 = i + 1;
                cellString = "B" + (i + 1).ToString();
                ws.get_Range(cellString, cellString).Interior.ColorIndex = 14 + i + 1;
                ws.get_Range(cellString, cellString).Value2 = 14 + i + 1;
                cellString = "C" + (i + 1).ToString();
                ws.get_Range(cellString, cellString).Interior.ColorIndex = 2 * 14 + i + 1;
                ws.get_Range(cellString, cellString).Value2 = 2 * 14 + i + 1;
                cellString = "D" + (i + 1).ToString();
                ws.get_Range(cellString, cellString).Interior.ColorIndex = 3 * 14 + i + 1;
                ws.get_Range(cellString, cellString).Value2 = 3 * 14 + i + 1;
            }*/
        }

        private double[,] AddData(int nRows, int nColumns)
        {
            double[,] dataArray = new double[nRows, nColumns];
            double[] xarray = new double[nColumns];
            for (int i = 0; i < xarray.Length; i++)
            {
                xarray[i] = -3.0f + i * 0.25f;
            }
            double[] yarray = xarray;

            for (int i = 0; i < dataArray.GetLength(0); i++)
            {
                for (int j = 0; j < dataArray.GetLength(1); j++)
                {
                    dataArray[i, j] = 3 * Math.Pow((1 - xarray[i]), 2)
                        * Math.Exp(-xarray[i] * xarray[i] -
                        (yarray[j] + 1) * (yarray[j] + 1)) -
                        10 * (0.2 * xarray[i] - Math.Pow(xarray[i], 3) -
                        Math.Pow(yarray[j], 5)) *
                        Math.Exp(-xarray[i] * xarray[i] - yarray[j] * yarray[j])
                        - 1 / 3 * Math.Exp(-(xarray[i] + 1) * (xarray[i] + 1) -
                        yarray[j] * yarray[j]);
                }
            }
            return dataArray;
        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            xla.DisplayAlerts = false;
            if (xla != null)
            {
                xla.Quit();
                xla = null;
            }
            this.Close();
        }
    }
}

------------------------------------------------------------------------------------------------

Form1.Designer.cs

namespace Example9_7
{
    partial class Form1
    {
        /// <summary>
        /// Required designer variable.
        /// </summary>
        private System.ComponentModel.IContainer components = null;

        /// <summary>
        /// Clean up any resources being used.
        /// </summary>
        /// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
        protected override void Dispose(bool disposing)
        {
            if (disposing && (components != null))
            {
                components.Dispose();
            }
            base.Dispose(disposing);
        }

        #region Windows Form Designer generated code

        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InitializeComponent()
        {
            this.btnPlot = new System.Windows.Forms.Button();
            this.btnClose = new System.Windows.Forms.Button();
            this.SuspendLayout();
            //
            // btnPlot
            //
            this.btnPlot.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Right)));
            this.btnPlot.Location = new System.Drawing.Point(88, 72);
            this.btnPlot.Name = "btnPlot";
            this.btnPlot.Size = new System.Drawing.Size(112, 32);
            this.btnPlot.TabIndex = 0;
            this.btnPlot.Text = "Plot";
            this.btnPlot.UseVisualStyleBackColor = true;
            this.btnPlot.Click += new System.EventHandler(this.btnPlot_Click);
            //
            // btnClose
            //
            this.btnClose.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Right)));
            this.btnClose.Location = new System.Drawing.Point(88, 152);
            this.btnClose.Name = "btnClose";
            this.btnClose.Size = new System.Drawing.Size(112, 32);
            this.btnClose.TabIndex = 1;
            this.btnClose.Text = "Close";
            this.btnClose.UseVisualStyleBackColor = true;
            this.btnClose.Click += new System.EventHandler(this.btnClose_Click);
            //
            // Form1
            //
            this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
            this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
            this.ClientSize = new System.Drawing.Size(292, 266);
            this.Controls.Add(this.btnClose);
            this.Controls.Add(this.btnPlot);
            this.Name = "Form1";
            this.Text = "Form1";
            this.ResumeLayout(false);

        }

        #endregion

        private System.Windows.Forms.Button btnPlot;
        private System.Windows.Forms.Button btnClose;
    }
}