Automating MS Excel Using Visual Studio .NET

• Download source - 2.15 Kb

Introduction

I have written this article to address requests I have been getting through e-mail on how to automate MS Excel. Most people who are looking at this article might also be interested at the following articles for automating MS Word documents. Here is the link for Automating MS Word using C#, and here is the link for Automating MS Word using C++.

Back to the main subject, there are many spreadsheets in the business world, and more and more of them are being created and sometimes used in ways that simply Excel was not designed for, for instance storing sensitive and crucial data for laboratories and etc…

To start with, this article will not get into the very advanced automations available in Excel, but it will give a framework that hopefully can be used by others to improve on and make it more expansible. The framework will allow you to create an Excel object and control some of the basic functionalities such as getting worksheet information and extracting data from the worksheet given a range.

The program that I had to develop has a larger scope, I will just be concentrating on the Excel portion. But there are a few neat classes which I developed for file system navigation using threads. If there is enough request for such an article, or if I get a chance to do it, I will go ahead and post it. In the meantime I hope that the following article benefits you.

Background

Having enough understanding of OOP and familiarity with the C# language.

Using the code

I will provide the Excel wrapper class that can be used in your project. The code will be discussed below. I will not get too much into the Excel object model, first because it is a huge task, and second because there is already documentation done by Microsoft. Before we start, here is a quick start for beginners who need to know how to setup an Office Automation project:

Create a new project, for simplicity, create a Windows application, go ahead and right click on References in the Solution Explorer, and select Add Reference… When the Add Reference window comes up, select the COM tab. This will list all Component names which are available on your machine. Since we are going to use MS Excel, you will scroll down until you find: Microsoft Excel 11.0 Object Library.

Note: Yours might be a different version depending on the version of Office installed on your machine. This is for MS Excel 2003.

using System;
using System.IO;
using System.Collections;
using System.Threading;
using Office = Microsoft.Office.Core;
using Excel = Microsoft.Office.Interop.Excel;
using System.Diagnostics;

namespace ATPMain
{
    /// <summary>
    /// Project:    Code Project Demo
    /// Author:        Vahe Karamian
    /// Date:        03/01/2005
    /// Version:    1.0
    /// </summary>
    public class VkExcel
    {
        private Excel.Application    excelApp = null;
        private Excel.Workbook        excelWorkbook = null;
        private Excel.Sheets            excelSheets = null;
        private Excel.Worksheet        excelWorksheet = null;
        
        ...

using Office = Microsoft.Office.Core;
using Excel = Microsoft.Office.Interop.Excel;

You will need to include these two so you can use the Excel object in your code. So we need to have an Excel.Application object, Excel.Workbook object, Excel.Sheets object, and Excel.Worksheet object. These object will be used to control and extract data from Excel. So we declare the following variables to represent the mentioned objects: excelApp, excelWorkbook, excelSheets, and excelWorksheet.

    ....

    private static object vk_missing    = System.Reflection.Missing.Value;

    private static object vk_visible    = true;
    private static object vk_false      = false;
    private static object vk_true       = true;

    private bool vk_app_visible = false;

    private object    vk_filename;

#region OPEN WORKBOOK VARIABLES
    private object vk_update_links                  = 0;
    private object vk_read_only                     = vk_true;
    private object vk_format                        = 1;
    private object vk_password                      = vk_missing;
    private object vk_write_res_password            = vk_missing;
    private object vk_ignore_read_only_recommend    = vk_true;
    private object vk_origin                        = vk_missing;
    private object vk_delimiter                     = vk_missing;
    private object vk_editable                      = vk_false;
    private object vk_notify                        = vk_false;
    private object vk_converter                     = vk_missing;
    private object vk_add_to_mru                    = vk_false;
    private object vk_local                         = vk_false;
    private object vk_corrupt_load                  = vk_false;
#endregion

#region CLOSE WORKBOOK VARIABLES
    private object vk_save_changes       = vk_false;
    private object vk_route_workbook     = vk_false;
#endregion

    /// <summary>
    /// Vahe Karamian - 03/04/2005 - Excel Object Constructor.
    /// </summary>
    public VkExcel()
    {
        this.startExcel();
    }

    /// <summary>
    /// Vahe Karamian - 03/04/2005 - Excel Object Constructor
    /// visible is a parameter, either TRUE or FALSE, of type object.
    /// </summary>
    /// Visible parameter, true for visible, false for non-visible
    public VkExcel(bool visible)
    {
        this.vk_app_visible = visible;
        this.startExcel();
    }
    ...

In the above block, we have predefined some constants that will be used to open a given Excel file. To find out more about what each parameter represents or does, you should look into the documentation that comes with Excel.

We have two constructors: VkExcel() which by default will start Excel hidden, and the other VkExcel(bool visible) which gives you the option to specify if you would like to see the Excel application or not.

    ...
    /// <summary>
    /// Vahe Karamian - 03/04/2005 - Start Excel Application
    /// </summary>
#region START EXCEL
    private void startExcel()
    {
        if( this.excelApp == null )
        {
            this.excelApp = new Excel.ApplicationClass();
        }

        // Make Excel Visible
        this.excelApp.Visible = this.vk_app_visible;
    }
#endregion

    /// <summary>
    /// Vahe Karamian - 03/23/2005 - Kill the current Excel Process
    /// </summary>
#region STOP EXCEL
    public void stopExcel()
    {
        if( this.excelApp != null )
        {
            Process[] pProcess; 
            pProcess = System.Diagnostics.Process.GetProcessesByName("Excel");
            pProcess[0].Kill();
        }
    }
#endregion
    ...

The above code starts and stops the Excel Application. startExcel() checks to see if the excelApp object is initialized or not, if it is then just make sure its visibility is set to the visible property. If not, it goes ahead and initializes the object for us. stopExcel() also checks to see if the object is currently in use, and if it is then it will go ahead and kill the process.

Note: pProcess[0].Kill() will make sure that Excel is gone for good! Some people that do Excel automation always complain that after they quit the application, Excel disappears but the Excel process is still in the task monitor, this code will take care of that for you!

    ...
    /// <summary>
    /// Vahe Karamian - 03/09/2005 - Open File function for Excel 2003
    /// The following function will take in a filename, and a password
    /// associated, if needed, to open the file.
    /// </summary>
#region OPEN FILE FOR EXCEL
    public string OpenFile(string fileName, string password)
    {
        vk_filename = fileName;

        if( password.Length > 0 )
        {
            vk_password = password;
        }

        try
        {
            // Open a workbook in Excel
            this.excelWorkbook = this.excelApp.Workbooks.Open(
                fileName, vk_update_links, vk_read_only, 
                vk_format, vk_password,
                vk_write_res_password, 
                vk_ignore_read_only_recommend, vk_origin,
                vk_delimiter, vk_editable, vk_notify, 
                vk_converter, vk_add_to_mru,
                vk_local, vk_corrupt_load);
        }
        catch(Exception e)
        {
            this.CloseFile();
            return e.Message;
        }
        return "OK";
    }
#endregion

    public void CloseFile()
    {
        excelWorkbook.Close( vk_save_changes, 
            vk_filename, vk_route_workbook );
    }
    ...

Alright, so the above code allows you to open Excel files. OpenFile(string fileName, string password) takes two parameters, the filename, or FULLNAME which is the path + filename, and a password parameter, which is used for protected sheets. Notice that the open function takes a bunch of parameters, which we have defined in the class. CloseFile() will goes ahead and closes the file.

Note: The code provided is for MS Excel 2003. For earlier versions, the parameters are a little different, you will need to check the documentation. If you need help on that send me an e-mail and I will try to help you out.

    ...
    /// <summary>
    /// Vahe Karamian - 03/20/2005 - Get Excel Sheets
    /// Get the collection of sheets in the workbook
    /// </summary>
#region GET EXCEL SHEETS
    public void GetExcelSheets()
    {
        if( this.excelWorkbook != null )
        {
            excelSheets = excelWorkbook.Worksheets;
        }
    }
#endregion

    /// <summary>
    /// Vahe Karamian - 03/21/2005 - Find Excel ATP Worksheet
    /// Search for ATP worksheet, if found return TRUE
    /// </summary>
    /// <returns>bool</returns>
#region FIND EXCEL ATP WORKSHEET
    public bool FindExcelATPWorksheet(string worksheetName)
    {
        bool ATP_SHEET_FOUND = false;

        if( this.excelSheets != null )
        {
            // Step thru the worksheet collection and see if ATP sheet is
            // available. If found return true;
            for( int i=1; i<=this.excelSheets.Count; i++ )
            {
                this.excelWorksheet = 
                   (Excel.Worksheet)excelSheets.get_Item((object)i);
                if( this.excelWorksheet.Name.Equals(worksheetName) )
                {
                    this.excelWorksheet.Activate();
                    ATP_SHEET_FOUND = true;
                    return ATP_SHEET_FOUND;
                }
            }
        }
        return ATP_SHEET_FOUND;
    }
#endregion
    ...

The above code demonstrates how to get all worksheets belonging to a workbook and getting a specific worksheet to extract data from. GetExcelSheets() gets all the sheets. FindExcelATPWorkSheet(string worksheetName) searches for worksheets with the name worksheetName.

    ...
    /// <summary>
    /// Vahe Karamian - 03/22/2005 - Get Range from Worksheet
    /// Return content of range from the selected range
    /// </summary>
    /// Range parameter: Example, GetRange("A1:D10")
#region GET RANGE
    public string[] GetRange(string range)
    {
        Excel.Range workingRangeCells = 
          excelWorksheet.get_Range(range,Type.Missing);
        //workingRangeCells.Select();
        System.Array array = (System.Array)workingRangeCells.Cells.Value2;
        string[] arrayS = this.ConvertToStringArray(array);

        return arrayS;
    }
#endregion
    ...

GetRange(string range) is the function that actually retrieves the data from the Excel sheet and we convert the returned values into a string[]. This is done by the next function call: this.ConvertToStringArray(array). Then the string[] is passed back to the caller who can consume it in any way they want.

    ...
    /// <summary>
    /// Vahe Karamian - 03/22/2005 - Convert To String Array
    /// Convert System.Array into string[]
    /// </summary>
    /// Values from range object
    /// <returns>String[]</returns>
#region CONVERT TO STRING ARRAY
    private string[] ConvertToStringArray(System.Array values)
    {
        string[] newArray = new string[values.Length];

        int index = 0;
        for ( int i = values.GetLowerBound(0); 
              i <= values.GetUpperBound(0); i++ )
        {
            for ( int j = values.GetLowerBound(1); 
                      j <= values.GetUpperBound(1); j++ )
            {
                if(values.GetValue(i,j)==null)
                {
                    newArray[index]="";
                }
                else
                {
                    newArray[index]=(string)values.GetValue(i,j).ToString();
                }
                index++;
            }
        }
        return newArray;
    }
#endregion
}

And the final code portion: ConvertToStringArray(System.Array values) will take the array passed from GetRange(...) to put it into a string array and pass it back.

We have reached the end of our object. As you can see, it is a very simple object with very minimum functionality, but it is a good starting point for anyone who needs to get started quickly, and you can very easily expand it into a more complex object.

I have not included a demo project. The reason is that it is extremely easy to use the object. You will just need to follow these steps to initialize and use the VkExcel object.

1. Create an object of type VkExcel: VkExcel excel = new VkExcel(false);. Remember that VkExcel(...) takes a parameter for visibility of the application.
2. Open an Excel file: string status = excel.OpenFile( filename, password );, pass in the filename or fullname if you are using a OpenFileDialog. If the file is not password protected, set password to null.
3. Check to see if the file was opened successfully: if( status.equal("OK")) ...
4. Retrieve Excel sheets: excel.GetExcelSheets(); will get all sheets internally in the object.
5. Search for specific sheet: excel.FindExcelWorksheet(worksheetName); will look for any sheet in a given file. Pass in the worksheetName as parameter.
6. Retrieve data given a range: string[] A4D4 = excel.GetRange("A4:D4"); this will return values in the range in a string[].

That's all there is to it!