C# – Dynamic Types – Unmanaged Code – Excel Automation

C# is a strongly typed language. When building your project, the compiler will check if the types you are using are valid. If you enter something like

int i = “Hello there”;

your code will not compile. i should be an integer value, the compiler sees that and refuses to continue.

There are however situations where you do not want the compiler to do these checks. Reason is that you simply do not know the exact type. This is somewhat common when you work with COM objects.

In that case you will want to tell the compiler to just pass the given value (or invoke the given method), and leave it to the runtime to decide if it is ok or not. You achieve this by using the keword “dynamic”.

When automating Excel, you’ll work with a com object. The dynamic type is the Excel Worksheet. Let’s start with the code, and after that analyze what is happening.

To be able to run the code, you’ll need to add a Microsoft.Office.Interop.Excel reference to your project. You can find it under “COM”, simply search for “Excel”.

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


namespace AutoExcel
{
    class Program
    {
        static void Main(string[] args)
        {

            // Generate some testdata
            Dictionary<string,int[]> dict = new Dictionary<string,int[]>();
            for (int i = 1; i <=500; i+=1) {
                dict.Add("Number " + i.ToString(),new int[5] { 100+i, 200+i, 300+i, 400+i, 500+i });
            }

            // Create the Excel Sheet
            var excelApp = new Office.Excel.Application();
            excelApp.Visible = true;
            excelApp.Workbooks.Add();

            // dynamic: types will not be checked at compile time
            dynamic workSheet = excelApp.ActiveSheet;
            
            try
            {
                // Generate the header
                workSheet.Cells[1, "A"] = "MY AUTOMATED EXCELSHEET";
                workSheet.Cells[2, "A"] = "Demonstrating Excel Automation";
                workSheet.Cells[4, "A"] = "FIELD NAME";
                workSheet.Cells[4, "B"] = "VALUE #1";
                workSheet.Cells[4, "C"] = "VALUE #2";
                workSheet.Cells[4, "D"] = "VALUE #3";
                workSheet.Cells[4, "E"] = "VALUE #4";
                workSheet.Cells[4, "F"] = "VALUE #5";


                // generate the value fields
                int row = 5;

                foreach (KeyValuePair<string, int[]> val in dict)
                {
                    workSheet.Cells[row, "A"] = val.Key;
                    workSheet.Cells[row, "B"] = val.Value[0];
                    workSheet.Cells[row, "C"] = val.Value[1];
                    workSheet.Cells[row, "D"] = val.Value[2];
                    workSheet.Cells[row, "E"] = val.Value[3];
                    workSheet.Cells[row, "F"] = val.Value[4];

                    row += 1;
                }
                
                //  AutoSize the columns
                for (int i = 1; i <= 5; i++)
                {
                    workSheet.Columns[i].AutoFit();
                }

                // Some layout:

                // Font type
                workSheet.Range(workSheet.Cells(1, 1), workSheet.Cells(row, 6)).Font.Name = "Arial";
                workSheet.Range(workSheet.Cells(1, 1), workSheet.Cells(1, 1)).Font.Size = 16;
                workSheet.Range(workSheet.Cells(2, 1), workSheet.Cells(row, 6)).Font.Size = 8;
                // Bold text:
                workSheet.Rows(4).Font.Bold = true;


                // Freeze the header
                workSheet.Application.ActiveWindow.SplitRow = 4;
                workSheet.Application.ActiveWindow.FreezePanes = true;

                
                // Select the first cell
                workSheet.Cells(1, 1).Select();
                
            }
            catch (Exception ex)
            {
                Console.WriteLine("Excel reported the following Error:");
                Console.WriteLine(ex.Message);
            }
            finally
            {
                // Release the com object
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
            }
            Console.WriteLine("Ready.");
            Console.Read();
        }   
    }
}

Let’s start with line 6. I included the Microsoft.Office.Interop and named it “Office”. You do not have to do this, but I found that it makes my life easier.

Line 16-20. We are making a program that generates an Excelsheet. That makes more sense with some data to show. These lines simply generate some numbers.

line 23-25 initialise the Excelapplication, and..

line 28 declares a worksheet as dynamic. This means that whatever you tell the worksheet to do, the compiler accepts it.

Line 30 to 87 take care of filling the worksheet. Note that when you enter this code, Visual Studio will not warn you if you make any typo’s.

Before explaining the “finally” block, let’s do an experiment.

Run the program. It should show an Excelsheet with some mark-up.

Now add the following line of code at line 65.

workSheet.PlayTheGuitarAsIfYourLifeDependsOnIt();

You can be pretty sure this method is not known by the worksheet. However, you can still compile and run the program. Only at runtime, an exception is thrown.

Finally, the finally block.

Com objects are not managed. It is so called unmanaged code.

Normally, when you exit an application, references to all objects are cleaned-up automatically. That is .. all managed references. Com objects are not managed, and will not be cleaned up.

You can see this happen by commenting out line 91 and start the program, close it, and repeat that several times. Close all Excel sheets. Now look in your task manager. You’ll see that a lot of “Excels” are still running in the background. The only way to end those is by ending the processes using task manager.

What happened? In your code you create Excel Com objects. Those objects generated Excelsheets. You can close the Excelsheets, but not the com objects. When you close the program, the .NET Garbage Collector cleans-up all managed objects … and because the dynamic workSheet object is not managed, it is not cleaned up by the Garbage Collector.

Imagine what happens if you generate 200 Excelsheets automatically…

Fortunately there is a way to clean up those com objects, as demonstrated in line 91. Make sure that code is always executed. The finally-block is the best place to do that.

Leave a Reply