使用OleDb在C＃中将数据插入Excel 2007

时间：2022-09-27 13:20:31

I have used as a base for what I want to do this site: http://www.codeproject.com/script/Articles/ViewDownloads.aspx?aid=8500

我用它作为我想做这个网站的基础：http：//www.codeproject.com/script/Articles/ViewDownloads.aspx？aid = 8500

So basically, I want to use OleDb in my C# Windows form application to add some data to specific cells in my existing Excel spread sheet. All the examples I find want me to have some type of header cells. Like if my 'A1' cell had "Title" in it I could use:

基本上，我想在我的C＃Windows表单应用程序中使用OleDb将一些数据添加到现有Excel电子表格中的特定单元格中。我发现的所有示例都希望我有一些类型的标题单元格。就像我的'A1'单元格中有“标题”一样，我可以使用：

"INSERT INTO [SHEET1$] (Title) Values ('Book')"

The problem is that my Excel spread sheet does not have any header. What I need is to do:

问题是我的Excel电子表格没有任何标题。我需要做的是：

"INSERT INTO [SHEET1$] (A15) Values ('Book')".

Can someone help me figure out how to put data in specific cells around my spread sheet?

有人可以帮我弄清楚如何将数据放在我的电子表格的特定单元格中吗？

3 个解决方案

#1

6  

If you are still interested,

如果你还有兴趣，

There is no real way to specify a cell to write to using OleDb Insert Command, the OleDbCommand will automatically go to the next open row in the specified column. However you can use an Update Query such as:

没有真正的方法来指定要使用OleDb Insert Command写入的单元格，OleDbCommand将自动转到指定列中的下一个打开行。但是，您可以使用更新查询，例如：

sql = "Update [Sheet1$A1:A15] SET A15 = 'DesiredNumber'"; 
myCommand.CommandText = sql;
myCommand.ExecuteNonQuery();

This should work given you've defined:

这应该适用于您定义的：

System.Data.OleDb.OleDbConnection MyConnection;
System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();
string sql = null;
MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\\Example.xls';Extended Properties=Excel 8.0;");
MyConnection.Open();
myCommand.Connection = MyConnection;

#2

4  

See How To Use ADO.NET to Retrieve and Modify Records in an Excel Workbook With Visual Basic .NET at http://support.microsoft.com/kb/316934.

请参阅http://support.microsoft.com/kb/316934，了解如何使用ADO.NET在Excel工作簿中使用Visual Basic .NET检索和修改记录。

Table Naming Conventions

There are several ways you can reference a table (or range) in an Excel workbook:

有几种方法可以在Excel工作簿中引用表（或范围）：

  • Use the sheet name followed by a dollar sign (for example, [Sheet1$] or [My Worksheet$]). A workbook table that is referenced in this manner includes the whole used range of the worksheet.

    使用工作表名称后跟美元符号（例如，[Sheet1 $]或[My Worksheet $]）。以这种方式引用的工作簿表包括工作表的整个使用范围。

    Select * from [Sheet1$]

  • Use a range with a defined name (for example, [MyNamedRange]):

    使用具有已定义名称的范围（例如，[MyNamedRange]）：

    Select * from [MyNamedRange]

  • Use a range with a specific address (for example, [Sheet1$A1:B10]):

    使用具有特定地址的范围（例如，[Sheet1 $ A1：B10]）：

    Select * from [Sheet1$A1:B10]

Inserted from http://support.microsoft.com/kb/316934

插入自http://support.microsoft.com/kb/316934

#3

0  

I assume that you are using standard MS-Excel COM objects to interact with the excel file. You can try using system level ODBC drivers to interact with the excel file. While using this depending upon presence or absence of headers, you need to specify the same in the oledbConnection string and thereafter you can directly enter values to whichever cell you want.

我假设您使用标准的MS-Excel COM对象与excel文件进​​行交互。您可以尝试使用系统级ODBC驱动程序与excel文件进​​行交互。根据是否存在标题使用它时，您需要在oledbConnection字符串中指定相同的内容，然后您可以直接输入值到您想要的任何单元格。

#1

6  

If you are still interested,

如果你还有兴趣，

There is no real way to specify a cell to write to using OleDb Insert Command, the OleDbCommand will automatically go to the next open row in the specified column. However you can use an Update Query such as:

没有真正的方法来指定要使用OleDb Insert Command写入的单元格，OleDbCommand将自动转到指定列中的下一个打开行。但是，您可以使用更新查询，例如：

sql = "Update [Sheet1$A1:A15] SET A15 = 'DesiredNumber'"; 
myCommand.CommandText = sql;
myCommand.ExecuteNonQuery();

This should work given you've defined:

这应该适用于您定义的：

System.Data.OleDb.OleDbConnection MyConnection;
System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();
string sql = null;
MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\\Example.xls';Extended Properties=Excel 8.0;");
MyConnection.Open();
myCommand.Connection = MyConnection;

#2

4  

See How To Use ADO.NET to Retrieve and Modify Records in an Excel Workbook With Visual Basic .NET at http://support.microsoft.com/kb/316934.

请参阅http://support.microsoft.com/kb/316934，了解如何使用ADO.NET在Excel工作簿中使用Visual Basic .NET检索和修改记录。

Table Naming Conventions

There are several ways you can reference a table (or range) in an Excel workbook:

有几种方法可以在Excel工作簿中引用表（或范围）：

  • Use the sheet name followed by a dollar sign (for example, [Sheet1$] or [My Worksheet$]). A workbook table that is referenced in this manner includes the whole used range of the worksheet.

    使用工作表名称后跟美元符号（例如，[Sheet1 $]或[My Worksheet $]）。以这种方式引用的工作簿表包括工作表的整个使用范围。

    Select * from [Sheet1$]

  • Use a range with a defined name (for example, [MyNamedRange]):

    使用具有已定义名称的范围（例如，[MyNamedRange]）：

    Select * from [MyNamedRange]

  • Use a range with a specific address (for example, [Sheet1$A1:B10]):

    使用具有特定地址的范围（例如，[Sheet1 $ A1：B10]）：

    Select * from [Sheet1$A1:B10]

Inserted from http://support.microsoft.com/kb/316934

插入自http://support.microsoft.com/kb/316934

#3

0  

I assume that you are using standard MS-Excel COM objects to interact with the excel file. You can try using system level ODBC drivers to interact with the excel file. While using this depending upon presence or absence of headers, you need to specify the same in the oledbConnection string and thereafter you can directly enter values to whichever cell you want.

我假设您使用标准的MS-Excel COM对象与excel文件进​​行交互。您可以尝试使用系统级ODBC驱动程序与excel文件进​​行交互。根据是否存在标题使用它时，您需要在oledbConnection字符串中指定相同的内容，然后您可以直接输入值到您想要的任何单元格。

标签：

相关文章