What do I need to do to set up Visual Studio to be able to manipulate an Excel File?

Question

Let's say I want to find the value of a cell in an Excel file. In VBA, I would do this:

Dim varValue As Variant
Dim wb As Workbook
Dim ws As Worksheet
Set wb = ThisWorkbook
Set ws = wb.Worksheets("Sheet1")
varValue = ws.Range("A1").Value

How do I set up a Visual Studio project so I can access and modify an Excel file in C# rather than in VBA?

What references might I need to add?

Does the file need to be open in order to work with it?

Answer 1

You can use Excel automation or a third-party library.

To use Excel automation, you should install the Office PIAs and add a reference to Microsoft.Office.Interop.Excel.dll. You would then write code to open the file (using the ApplicationClass class) and manipulate it.

This approach would use the same object model that you're used to in VBA. However, since C# 3 does not support optional parameters or weak typing, it will be somewhat annoying. It would be easier to do it in VB .Net or C# 4 (currently in beta)

---

Excel automation is not suitable for code running in non-interactive context. There are a number of third-party libraries written entirely in .Net that can read and write Excel files.

In addition, if you only need to manipulate table-like data, you can use OleDb to run SQL statements against Excel files using this connection string.

You can use OleDbCommands to run SELECT * FROM [SheetName], assuming that the sheet is a table. You can also select from a named range. You can get the available tables by calling oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null) and looking at the TABLE_NAME column ion the returned DataTable.

Answer 2

You have to used Microsoft.Office.Interop.Excel. You'll need to use COM Interop. Here's a detailed tutorial about how it's done.

Answer 3

I wrote my own, fairly simple class to extract data from an Excel spreadsheet via C#. You need to include:

using Microsoft.Office.Interop;

Then you can do something like this to get started:

Excel.Application excel;
Excel.Workbook workbook;
Excel.Worksheet worksheet;
Excel.Sheets sheets;
Excel.Range range;

excel = new Microsoft.Office.Interop.Excel.Application();
workbook = excel.Workbooks.Open(workbookName, 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);
sheets = workbook.Worksheets;

...

Answer 4

You do not have to use interop.

You simply need a reference to...

System.Data.OleDb

Define a connection string as you would any other data source:

string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=YourPath;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";";

And add some code to query your database:

OleDbConnection objConnection = new OleDbConnection();
string strSQL = "SELECT * FROM [YourTable]";
objConnection = new OleDbConnection(connectionString);
objConnection.Open();
OleDbCommand cmd = new OleDbCommand(strSQL, objConnection);
DataTable dt = new DataTable();
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(dt);

...Then enumerate through your data rows.