Creating Excel Charts in C#

Introduction

Microsoft Office Excel Object Library, commonly known as Microsoft Office Interop Object, can help developers generating and exporting report from Microsoft Excel. In this tutorial, I will explain with code snippets how to plot a graph from the data in Excel sheet, and export a chart from Excel sheet through C# and save it locally. This tutorial will be very useful for developers working on Desktop and Web applications using Visual Studio .NET where we need to do lot of report generation from Microsoft Excel.

Microsoft Office Interop Object Library

There are a number of free third-party libraries available for handling Microsoft Excel files in C#. But, in this tutorial we will use the COM library because it’s already available in your system if Microsoft Office is installed.

To use the features of a Microsoft Office application from your .NET application, a developer must use the primary interop assembly (PIA). The PIA enables managed code to interact with a Microsoft Office application’s COM-based object model. During Visual Studio installation, the PIAs are automatically installed in a local file system. When you create a new Office project, Visual Studio adds references to the PIAs that are required to build the project.

For Excel to be used in C# applications, we need to resolve the issue of interoperation between C# and Excel. An Excel application can be regarded as a COM server, so the basis of interoperation between C# and Excel applications is COM Interop.

Data Preparation in an Excel Sheet

In my example, I have downloaded employee salary master data from the Exceldatapro Web site. The Salary Sheet is a document that includes complete details of the amount payable to each employee for work done during a particular period of time. It includes details such as basic pay, allowances, deductions, overtime, and so forth of employees. The salary sheet, or Employee sheet, is the Payroll or Human Resource document used to calculate salaries of employees in any company.

Figure 1 shows the data source used for preparing the chart data.

Figure 1: Employee Salary Data for Cart Generation

Create the Chart from an Excel Data Sheet

Step 1

First, Create a new C# Console Project. Open the Solution Explorer pane, right-click References, and click Add Reference. This will open a new window, “Add Reference.” Click the COM tab and select “Microsoft Excel Object Library” (see Figures 2-4).

Figure 2: Add a new reference in a Visual Studio project

Figure 3: COM object reference selection window

Figure 4: Microsoft Office Excel Interop COM library added

Step 2

Next, you need to add the reference in the code to use all the methods of Excel object library. Refer to the following line of code.

using Excel = Microsoft.Office.Interop.Excel;

Step 3

Instantiate an Excel application, worksheet, and workbook objects.

Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

Step 4

Set all worksheet columns.

xlWorkSheet.Cells[1, 1] = "SL";
xlWorkSheet.Cells[1, 2] = "Name";
xlWorkSheet.Cells[1, 3] = "CTC";
xlWorkSheet.Cells[1, 4] = "DA";
xlWorkSheet.Cells[1, 5] = "HRA";
xlWorkSheet.Cells[1, 6] = "Conveyance";
xlWorkSheet.Cells[1, 7] = "Medical Expenses";
xlWorkSheet.Cells[1, 8] = "Special";
xlWorkSheet.Cells[1, 9] = "Bonus";
xlWorkSheet.Cells[1, 10] = "TA";
xlWorkSheet.Cells[1, 11] = "TOTAL";
xlWorkSheet.Cells[1, 11] = "Contribution to PF";
xlWorkSheet.Cells[1, 12] = "Profession Tax";
xlWorkSheet.Cells[1, 13] = "TDS";
xlWorkSheet.Cells[1, 14] = "Salary Advance";
xlWorkSheet.Cells[1, 15] = "TOTAL";
xlWorkSheet.Cells[1, 16] = "NET PAY";

Step 5

To open the Excel file, you can use the following code:

Excel.Application xlApp1 = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp1.Workbooks.Open
   (@"C:\Sample\Employee Data.xlsx");
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;

Step 6

Create the Excel Chart and export it in BMP format.

Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet
   .ChartObjects(Type.Missing);
Excel.ChartObject myChart =
   (Excel.ChartObject)xlCharts.Add(10, 80, 300, 250);
Excel.Chart chartPage = myChart.Chart;
chartRange = xlWorkSheet.get_Range("A1", "R22");
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
// Export chart as picture file
chartPage.Export(@"C:\Sample\EmployeeExportData.bmp", "BMP",
   misValue);
xlWorkBook.SaveAs("EmployeeExportData.xls",
   Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue,
   misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive,
   misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();

Finally, the following code snippet shows the entire program.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Excel = Microsoft.Office.Interop.Excel;


namespace CreateExcelChart
{
   class ExcelChart
   {
      static void Main(string[] args)
      {
         Excel.Application xlApp;
         Excel.Workbook xlWorkBook;
         Excel.Worksheet xlWorkSheet;
         object misValue = System.Reflection.Missing.Value;

         xlApp = new Excel.Application();
         xlWorkBook = xlApp.Workbooks.Add(misValue);
         xlWorkSheet = (Excel.Worksheet)xlWorkBook
            .Worksheets.get_Item(1);

         // Add data columns
         xlWorkSheet.Cells[1, 1] = "SL";
         xlWorkSheet.Cells[1, 2] = "Name";
         xlWorkSheet.Cells[1, 3] = "CTC";
         xlWorkSheet.Cells[1, 4] = "DA";
         xlWorkSheet.Cells[1, 5] = "HRA";
         xlWorkSheet.Cells[1, 6] = "Conveyance";
         xlWorkSheet.Cells[1, 7] = "Medical Expenses";
         xlWorkSheet.Cells[1, 8] = "Special";
         xlWorkSheet.Cells[1, 9] = "Bonus";
         xlWorkSheet.Cells[1, 10] = "TA";
         xlWorkSheet.Cells[1, 11] = "TOTAL";
         xlWorkSheet.Cells[1, 11] = "Contribution to PF";
         xlWorkSheet.Cells[1, 12] = "Profession Tax";
         xlWorkSheet.Cells[1, 13] = "TDS";
         xlWorkSheet.Cells[1, 14] = "Salary Advance";
         xlWorkSheet.Cells[1, 15] = "TOTAL";
         xlWorkSheet.Cells[1, 16] = "NET PAY";


         Excel.Application xlApp1 = new Excel.Application();
         Excel.Workbook xlWorkbook = xlApp1.Workbooks.Open
            (@"C:\Sample\Employee Data.xlsx");
         Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
         Excel.Range xlRange = xlWorksheet.UsedRange;

         int rowCount = xlRange.Rows.Count;
         int colCount = xlRange.Columns.Count;

         for (int i = 1; i <= rowCount; i++)
         {
            for (int j = 1; j <= colCount; j++)
            {
               Console.WriteLine(xlRange.Cells[i, j]
                  .Value2.ToString());
               xlWorkSheet.Cells[i, j] = xlRange.Cells[i, j]
                  .Value2.ToString();

            }
         }

         Console.ReadLine();

         Excel.Range chartRange;

         Excel.ChartObjects xlCharts = (Excel.ChartObjects)
            xlWorkSheet.ChartObjects(Type.Missing);
         Excel.ChartObject myChart = (Excel.ChartObject)
            xlCharts.Add(10, 80, 300, 250);
         Excel.Chart chartPage = myChart.Chart;

         chartRange = xlWorkSheet.get_Range("A1", "R22");
         chartPage.SetSourceData(chartRange, misValue);
         chartPage.ChartType = Excel.XlChartType.xlColumnClustered;

         // Export chart as picture file
         chartPage.Export(@"C:\Sample\EmployeeExportData.bmp",
            "BMP", misValue);

         xlWorkBook.SaveAs("EmployeeExportData.xls",
            Excel.XlFileFormat.xlWorkbookNormal, misValue,
            misValue, misValue, misValue,
            Excel.XlSaveAsAccessMode.xlExclusive, misValue,
            misValue, misValue, misValue, misValue);
         xlWorkBook.Close(true, misValue, misValue);
         xlApp.Quit();

         DeallocateObject(xlWorkSheet);
         DeallocateObject(xlWorkBook);
         DeallocateObject(xlApp);
         DeallocateObject(xlApp1);

      }
      private static void DeallocateObject(object obj)
      {
         try
         {
            System.Runtime.InteropServices.Marshal
               .ReleaseComObject(obj);
            obj = null;
         }
         catch (Exception ex)
         {
            obj = null;
            Console.WriteLine("Exception Occurred while releasing
               object " + ex.ToString());
         }
         finally
         {
            GC.Collect();
         }
      }
   }
}

Figure 5 shows the Excel Chart generated programmatically.

Figure 5: Employee Salary Data exported chart

Conclusion

I hope the preceding tutorial taught you about reading data from Microsoft Excel and exporting a chart using C#. That’s all for today. Happy reading!