According to Microsoft, at Microsoft Support, “automation a process that permits applications that are written in languages such as Visual C# .NET to programmatically control other applications. Automation to Excel permits you to perform actions using C#, such as creating a new workbook and adding data to the workbook.” You can use C# to manipulate MS Excel.

In that article Microsoft Support goes on to say: “Excel exposes this programmatic functionality through an object model. The object model is a collection of classes and methods that serve as counterparts to the logical components of Excel. For example, there is an Application object, a Workbook object, and a Worksheet object, each of which contains the functionality of those pieces of Excel. To access the object model from Visual C# .NET, you can set a project reference to the type library.”

An Example: C# to Excel

So we have C# output to Excel. Here is what the code below produces. After you run the program in Visual Studio and click the Button, be patient as it will take several seconds for Excel to appear. It’s worth the wait.

Create an Automation Client for Microsoft Excel

Below are the steps from the website. I have updated and modified things slightly to work with a WPF program using Visual Studio 2017. I needed to give the button a name in the XAML code by adding this code: x:Name=”Button”. Also the code for the event is different. If you follow the instructions below it should work out fine.

1. Start Microsoft Visual Studio .NET.
2. On the File menu, click New, and then click Project. Select Windows Application from the Visual C# Project types. Form1 is created by default.
3. Add a reference to the Microsoft Excel Object Library. To do this, follow these steps:
1. On the Project menu, click Add Reference.
2. On the COM tab, locate Microsoft Excel Object Library, and click Select.(version 1.9 as of Sept. 2018)
3. Click OK in the Add References dialog box to accept your selections. If you are prompted to generate wrappers for the libraries that you selected, click Yes.
4. On the View menu, select Toolbox to display the toolbox, and then add a button to Form1.
5. Double-click Button1. The code window for the form appears.
6. In the code window, change it so you have the code below for the click event.
7. Scroll to the top of the code window. Add the following line to the end of the list of using directives, shown below in the second listing.

using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;

The version here is s shortened simplified version. For the complete code listing please visit the website.

private void Button_Click(object sender, RoutedEventArgs e)
        {
            Excel.Application oXL;
            Excel._Workbook oWB;
            Excel._Worksheet oSheet;
            Excel.Range oRng;

            try
            {
                //Start Excel and get Application object.
                oXL = new Excel.Application();
                oXL.Visible = true;

                //Get a new workbook.
                oWB = (Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));
                oSheet = (Excel._Worksheet)oWB.ActiveSheet;

                //Add table headers going cell by cell.
                oSheet.Cells[1, 1] = "First Name";
                oSheet.Cells[1, 2] = "Last Name";
                oSheet.Cells[1, 3] = "Full Name";
                oSheet.Cells[1, 4] = "Salary";

                //Format A1:D1 as bold, vertical alignment = center.
                oSheet.get_Range("A1", "D1").Font.Bold = true;
                oSheet.get_Range("A1", "D1").VerticalAlignment =
                Excel.XlVAlign.xlVAlignCenter;

                // Create an array to multiple values at once.
                string[,] saNames = new string[5, 2];

                saNames[0, 0] = "John";
                saNames[0, 1] = "Smith";
                saNames[1, 0] = "Tom";
                saNames[1, 1] = "Brown";
                saNames[2, 0] = "Sue";
                saNames[2, 1] = "Thomas";
                saNames[3, 0] = "Jane";
                saNames[3, 1] = "Jones";
                saNames[4, 0] = "Adam";
                saNames[4, 1] = "Johnson";

                //Fill A2:B6 with an array of values (First and Last Names).
                oSheet.get_Range("A2", "B6").Value2 = saNames;

                //Fill C2:C6 with a relative formula (=A2 & " " & B2).
                oRng = oSheet.get_Range("C2", "C6");
                oRng.Formula = "=A2 & \" \" & B2";

                //Fill D2:D6 with a formula(=RAND()*100000) and apply format.
                oRng = oSheet.get_Range("D2", "D6");
                oRng.Formula = "=RAND()*100000";
                oRng.NumberFormat = "$0.00";

                //AutoFit columns A:D.
                oRng = oSheet.get_Range("A1", "D1");
                oRng.EntireColumn.AutoFit();

                //Manipulate a variable number of columns for Quarterly Sales Data.
                //DisplayQuarterlySales(oSheet);

                //Make sure Excel is visible and give the user control
                //of Microsoft Excel's lifetime.
                oXL.Visible = true;
                oXL.UserControl = true;
            }
            catch (Exception theException)
            {
                String errorMessage;
                errorMessage = "Error: ";
                errorMessage = String.Concat(errorMessage, theException.Message);
                errorMessage = String.Concat(errorMessage, " Line: ");
                errorMessage = String.Concat(errorMessage, theException.Source);

                MessageBox.Show(errorMessage, "Error");
            }
        }

In the main listing I commented out this: DisplayQuarterlySales(oSheet); because I did’t want to cover too much information in this introductory post. That code calls another method. The other method takes the worksheet as a parameter like this

private void DisplayQuarterlySales(Excel._Worksheet oWS)
{