如何设置Visual Studio以便能够操作Excel文件？

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

假设我想在Excel文件中找到单元格的值。在VBA中,我会这样做:

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?

如何设置Visual Studio项目以便我可以在C#中而不是在VBA中访问和修改Excel文件?

What references might I need to add?

我可能需要添加哪些参考?

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

该文件是否需要打开才能使用它?

4 个解决方案

#1

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

我编写了自己的,相当简单的类,通过C#从Excel电子表格中提取数据。你需要包括:

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

#2

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

您可以使用Excel自动化或第三方库。

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.

要使用Excel自动化,您应该安装Office PIA并添加对Microsoft.Office.Interop.Excel.dll的引用。然后,您将编写代码以打开文件(使用ApplicationClass类)并对其进行操作。

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)

此方法将使用您在VBA中使用的相同对象模型。但是,由于C#3不支持可选参数或弱类型,因此会有些烦人。在VB .Net或C#4(目前处于测试阶段)这样做会更容易

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.

Excel自动化不适用于在非交互式上下文中运行的代码。有许多完全用.Net编写的第三方库可以读写Excel文件。

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.

此外,如果您只需要操作类似表的数据,则可以使用OleDb使用此连接字符串对Excel文件运行SQL语句。

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.

您可以使用OleDbCommands运行SELECT * FROM [SheetName],假设工作表是一个表。您也可以从命名范围中进行选择。您可以通过调用oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,null)并查看返回的DataTable中的TABLE_NAME列来获取可用表。

#3

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.

...然后枚举您的数据行。

#4

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.

您必须使用Microsoft.Office.Interop.Excel。您需要使用COM Interop。这是一个关于它是如何完成的详细教程。

#1