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

using Excel = Microsoft.Office.Interop.Excel;

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

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.

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:

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:

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:

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.

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:

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.

Our task is , we need to run the excel macro through programatically.
In local environment it was working fine, when we follow the below Steps:-
1) Microsoft.Office.Interop.Excel.Application appExcel = new Microsoft.Office.Interop.Excel.Application();
2) Microsoft.Office.Interop.Excel.Workbook workBook = appExcel.Workbooks.Open("FilePath",
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
3) appExcel.Run("Macro name", "$W$5", 1, 0, "$Q$11:$Q$112", Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing,Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing,Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Note:-
On localhost everything is working fine and the macro is getting executed.
But the functionality is not working as expected when hosted in IIS(version 7.5).
For this to work the changes which we have done are:-
a) Control Panel -> Administrative Tools -> Component Services
Computers -> My Computer -> DCOM Config -> Microsoft Excel Application -> Properties
i) Here in the security tab we have Customized all the permissions by adding user(IUSR) and giving him all
access permissions.
After doing these changes we are able to create instance of Interop.Excel.
But we could not open the workbook.
ii) In Identity tab of Microsoft Excel Application peroperties, by default"The launching user"is selected.
After changing the value to "The interactive user" we are able to open the workbook.
But we could not run the macro.
iii) C:\Windows\System32\config\systemprofile
In this location we have created Desktop folder. For this folder properties, in the security tab we have created user(IUSR) and given full control.
Now we are able to run the macro and getting the desired output even when running the application on IIS hosted site.
Issue:-
After making all the changes when we have moved the changes to production server, there we could not find Microsoft Excel Application in DCOM Config.
Microsoft Office is not completely installed in production server. Only Excel software was installed on the server.
We are getting null reference exception at below line of code.
Microsoft.Office.Interop.Excel.Workbook workBook = appExcel.Workbooks.Open("FilePath",
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
Our production server is running on windows 2008 R2.
Could any one please suggest the right way to resolve this issue.

HI,Any body knows how to bold a part of a excel cell content with c# coding? eg: abcdefgh if so plz send a email to supuna2u@gmail.com or post the answer here. This is a big help! I have googled but could not came up with a solution

I am not sure where you got this error, but it is most likely due to the fact you are using Excel 2007. The best advice I can give you is to look at examples for VBA and remember that none of the parameters in the c# methods are optional.

HII have an excel file to be read in C#. I used the ODBC to read the data from the excel and it works fine. But one of its sheets has embeded objects e.g Textbox in it. I have to read data from that text box. So how can i read this text box data from C#.