Opening and Navigating Excel with C#

• Download source files - 347 Kb

Introduction

This is a short introduction to opening an existing Microsoft Excel spreadsheet using Visual C# .NET. The computer must have Excel installed on the system for this code to run properly. The Excel assembly is used to open and manipulate Excel spreadsheets.

Code Explanation

First, the Excel assembly must be added to the project. To do this you must add a reference to the Excel 10 Object Library (Excel XP) by going to the Project -> Add Reference  menu item. Go to the COM tab of the dialog box that pops up and scroll down the Excel 10 Object Library. Double click on it and press OK. This adds the reference to your project. In the "using" section of your code, type

    using Excel; 

Once the assembly is added to the project, a new application needs to be created:

    Excel.Application excelApp = new Excel.ApplicationClass();

If you want to make Excel visible to the user you have to set the Visible property to true, the default is false.

    excelApp.Visable = true;

The code above opens the Excel application, in order to use the application, you have to open a workbook by creating a Workbook object. You can open a new blank workbook by using the following code:

    Excel.Workbook newWorkbook = 
        excelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);

The preceding code opens a blank workbook with one sheet. The .Add method takes a template object, the XlWBATemplate.xlWBATWorksheet object is a built in template for a blank workbook.

If you want to open an existing document for editing instead of creating a new one, you can use the following code to create a Workbook object:

    string workbookPath = "c:/SomeWorkBook.xls";
    Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath,
        0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "",
        true, false, 0, true, false, false);

The preceding code may need a little explanation. The workbookPath is of course the path to the existing spreadsheet that you want to open. The excelApp.Workbooks.open is a little less obvious. The following is the list of parameters that are passed to the function:

• WorkBooks.open(string Filename, object UpdateLinks, object ReadOnly, object Format, object Password, object WriteResPassword, object ReadOnlyRecommend, object Origin, object Delimiter, object Editable, object Notify, object Converter, object AddToMru, object Local, object CorruptLoad )

To view the documentation on this function, follow the link Microsoft’s website for further explanation of the function at:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaxl10/html/xlmthOpen.asp

Once the workbook is either created or opened, you must create a Sheets object that holds the Worksheets within the workbook. The following code will get all of the sheets in the workbook you previously opened.

    Excel.Sheets excelSheets = excelWorkbook.Worksheets;

Now that you have the collection of Worksheets, you must get an individual sheet edit data within.

    string currentSheet = "Sheet1";
    Excel.Worksheet excelWorksheet = 
        (Excel.Worksheet)excelSheets.get_Item(currentSheet);

In the preceding code, you have to type cast the excelSheets.get_Item(string) function because it returns an object. Now that you have the sheet you are ready to access individual cells with the following code:

    Excel.Range excelCell = 
        (Excel.Range)excelWorksheet.get_Range("A1", "A1");

The get_Range function must take two parameters. If the two parameters are equal, a single cell is selected; otherwise a range of cells will be selected. Again you have to type cast the return value of the function. Once you have a cell object, your can set its value using the .Value2 property, or use any of the other properties to manipulate ranges of cells.