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

Opening and Navigating Excel with C#

Rate me:
Please Sign up or sign in to vote.
4.57/5 (139 votes)
17 Apr 2012CPOL3 min read 2M   28.5K   246   291
Introduction to manipulating Excel with C#.

Introduction 

Note: This article has been updated to interact with Office 2007. I left the old .NET 1.0 solution for Office XP available for download for reference (in case you are using an old version of Office).

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 Microsoft.Office.Interop.Excel library by going to the Project -> Add Reference  menu item. Go to the .NET tab of the dialog box that pops up and scroll down the Microsoft.Office.Interop.Excel list item. Double click on it and press OK. This adds the reference to your project. In the "using" section of your code, type

C#
using Excel = Microsoft.Office.Interop.Excel; 

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

C#
Excel.Application excelApp = new Excel.Application();

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

C#
excelApp.Visible = 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:

C#
Excel.Workbook newWorkbook = excelApp.Workbooks.Add();

The preceding code opens a blank workbook with one sheet. The .Add method optionally takes a template object.  If the parameter is left blank, the default template will be used (like when you open Excel manually).

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

C#
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, and if using .NET 4.0 and the Office 2007 library (Microsoft.Office.Interop.Excel library), it is the only parameter required. The rest of the excelApp.Workbooks.open parameters are 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 to Microsoft’s website for further explanation of the function at: http://msdn.microsoft.com/en-us/library/bb179167%28v=office.12%29.aspx

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.

C#
Excel.Sheets excelSheets = excelWorkbook.Worksheets;

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

C#
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) method because it returns an object. Now that you have the sheet you are ready to access individual cells with the following code:

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
AnswerRe: How do I get the default worksheet? Pin
Narsters11-Jun-07 15:22
Narsters11-Jun-07 15:22 
GeneralRe: How do I get the default worksheet? Pin
Komputec12-Jun-07 10:09
Komputec12-Jun-07 10:09 
Generalproblem wih excel reference Pin
channaca26-May-07 20:00
channaca26-May-07 20:00 
GeneralRe: problem wih excel reference Pin
Narsters7-May-07 3:33
Narsters7-May-07 3:33 
GeneralRe: problem wih excel reference Pin
DarrollWalsh16-May-07 9:41
DarrollWalsh16-May-07 9:41 
QuestionRe: problem wih excel reference Pin
Mike Donner27-Jun-07 10:15
Mike Donner27-Jun-07 10:15 
GeneralCopy rows/columns from one excel file to another excel file [modified] Pin
mujahidcodeproject22-Feb-07 0:09
mujahidcodeproject22-Feb-07 0:09 
GeneralSelecting specific cells and writing them to a database [modified] Pin
admol15-Feb-07 0:45
admol15-Feb-07 0:45 
Hi there,

I am creating a web site using C#. I have used your code successfully and I am able to read in the value of the cell A1 and display it in a label. However I want to read in multiple specific columns from my excel document and write them to a SQL database.

How do I pull only certain values from my excel document and in turn write them to a database?

I basically want to add a new record to my database with data from lets say the 1st, 5th and 10th columns in a row. I then want to add another record with the same columns from the next row maybe through some sort of a loop.


Any help would be greatly appreciate!!

Thanks,

Adrian.


-- modified at 7:40 Thursday 15th February, 2007
AnswerRe: Selecting specific cells and writing them to a database Pin
Narsters15-Feb-07 4:15
Narsters15-Feb-07 4:15 
GeneralRe: Selecting specific cells and writing them to a database Pin
mujahidcodeproject22-Feb-07 0:07
mujahidcodeproject22-Feb-07 0:07 
QuestionRe: Selecting specific cells and writing them to a database Pin
lildiapaz24-Jul-07 10:50
lildiapaz24-Jul-07 10:50 
AnswerRe: Selecting specific cells and writing them to a database Pin
Narsters24-Jul-07 11:11
Narsters24-Jul-07 11:11 
QuestionQueryInterface failed Pin
steve_dee9-Jan-07 1:04
steve_dee9-Jan-07 1:04 
AnswerRe: QueryInterface failed Pin
Narsters9-Jan-07 2:33
Narsters9-Jan-07 2:33 
GeneralRe: QueryInterface failed Pin
steve_dee9-Jan-07 2:34
steve_dee9-Jan-07 2:34 
AnswerRe: QueryInterface failed Pin
Narsters9-Jan-07 3:03
Narsters9-Jan-07 3:03 
GeneralRe: QueryInterface failed Pin
steve_dee9-Jan-07 3:55
steve_dee9-Jan-07 3:55 
GeneralRe: QueryInterface failed Pin
racasens7-Mar-07 2:07
racasens7-Mar-07 2:07 
AnswerRe: QueryInterface failed Pin
crystallized28-Jun-07 7:08
crystallized28-Jun-07 7:08 
Generalprinting excel table Pin
rawbone226-Dec-06 10:45
rawbone226-Dec-06 10:45 
GeneralRe: printing excel table Pin
Narsters6-Dec-06 11:44
Narsters6-Dec-06 11:44 
Questionhow to copy a sheet to another new workbook? Pin
fivehun19-Nov-06 17:48
fivehun19-Nov-06 17:48 
AnswerRe: how to copy a sheet to another new workbook? Pin
Narsters20-Nov-06 4:39
Narsters20-Nov-06 4:39 
GeneralRe: how to copy a sheet to another new workbook? Pin
jerry_pendergraft3-Apr-07 12:34
jerry_pendergraft3-Apr-07 12:34 
AnswerRe: how to copy a sheet to another new workbook? Pin
Narsters3-Apr-07 15:11
Narsters3-Apr-07 15:11 

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.