Click here to Skip to main content
15,867,756 members
Articles / Programming Languages / C#
Article

Automating MS Excel Using Visual Studio .NET

Rate me:
Please Sign up or sign in to vote.
4.46/5 (65 votes)
1 May 20056 min read 773.1K   5.4K   209   155
This code will demonstrate how to automate and get content from a MS Excel document.

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.

C#
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;
        
        ...
C#
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.

C#
    ....

    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!

C#
    ...
    /// <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.

C#
    ...
    /// <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.

C#
    ...
    /// <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!

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


Written By
Software Developer Noorcon Inc.
United States United States
Published Books:

Introduction to Game Programing: Using C# and Unity 3D designed and developed to help individuals that are interested in the field of computer science and game programming. It is intended to illustrate the concepts and fundamentals of computer programming. It uses the design and development of simple games to illustrate and apply the concepts.

Book Preview:
Preview all chapters

Available from:
Amazon.com
Barnes and Noble Book Store
Amazon Kindle (eBook)
iTunes - iBook (eBook)

Vahé Karamian
www.noorcon.com
www.facebook.com/NoorconInc

Comments and Discussions

 
QuestionThank you Pin
Member 354006820-Dec-17 2:27
Member 354006820-Dec-17 2:27 
AnswerRe: Thank you Pin
Vahe Karamian2-Jan-18 14:36
Vahe Karamian2-Jan-18 14:36 
QuestionSee also this: Pin
dietmar paul schoder29-Jul-14 5:25
professionaldietmar paul schoder29-Jul-14 5:25 
GeneralRe: See also this: Pin
Vahe Karamian6-Mar-15 10:22
Vahe Karamian6-Mar-15 10:22 
Questionwhen I am using excel.GetRange("A4:L4"); am getting arrayOutOfIndexBound exception. Pin
Member 107340879-Apr-14 20:32
Member 107340879-Apr-14 20:32 
GeneralMy vote of 5 Pin
McDelfino28-Apr-12 22:07
McDelfino28-Apr-12 22:07 
GeneralMy vote of 5 Pin
Zakir 216-Jul-11 5:32
Zakir 216-Jul-11 5:32 
Its awsome.
i want to ask some on this Smile | :)
QuestionHow to open and read data from excel Pin
LuckyByChance3-Nov-10 16:38
LuckyByChance3-Nov-10 16:38 
AnswerRe: How to open and read data from excel Pin
MC234493216-Oct-11 15:27
MC234493216-Oct-11 15:27 
AnswerRe: How to open and read data from excel Pin
JamesHoward9723-Nov-11 22:55
JamesHoward9723-Nov-11 22:55 
GeneralLicense Pin
codeservant28-Aug-10 1:12
codeservant28-Aug-10 1:12 
GeneralVisual Studio 2008 Excel Workbook/Template Pin
russellfamily8@gmail.com25-Mar-10 17:45
russellfamily8@gmail.com25-Mar-10 17:45 
Generalsetting the password 2 null!!! Pin
The Tornado5-Nov-09 0:16
The Tornado5-Nov-09 0:16 
GeneralRe: setting the password 2 null!!! Pin
CSciBorg14-Sep-10 7:55
CSciBorg14-Sep-10 7:55 
GeneralErrors with c# Pin
Zackimuc25-Sep-09 3:54
Zackimuc25-Sep-09 3:54 
GeneralAdd reference in VS 2005 Pin
Rajeevlochan25-May-09 1:38
Rajeevlochan25-May-09 1:38 
Questionhow to resolve this error? seeking urgent help!!!!!!! Pin
arpitasegal420-Apr-09 23:09
arpitasegal420-Apr-09 23:09 
QuestionHow to modify color of a part of the text in Excel using c# Pin
gigisullivan25-Mar-09 1:04
gigisullivan25-Mar-09 1:04 
AnswerRe: How to modify color of a part of the text in Excel using c# Pin
gigisullivan25-Mar-09 4:43
gigisullivan25-Mar-09 4:43 
Questionusing excel 2000 with C# Pin
mgruby24-Nov-08 2:55
mgruby24-Nov-08 2:55 
QuestionExcel File Query Pin
nehamayank4-Nov-08 19:48
nehamayank4-Nov-08 19:48 
AnswerRe: Excel File Query Pin
Vahe Karamian14-Nov-08 7:20
Vahe Karamian14-Nov-08 7:20 
QuestionVKexcel Pin
jhyn2-Oct-08 15:51
jhyn2-Oct-08 15:51 
AnswerRe: VKexcel Pin
Vahe Karamian14-Nov-08 7:18
Vahe Karamian14-Nov-08 7:18 
GeneralExcel 2007 - open, unprotect, save worksheets as csv files, and close Pin
Sara Rodriguez17-Sep-08 10:35
Sara Rodriguez17-Sep-08 10:35 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.