写入到空白Excel工作表使用ADO.NET [英] Writing to a blank excel sheet with ADO.NET

问题描述

我想使用ADO.NET连接并写入到Excel文件。我已创建了默认的Excel工作表的空白文件（我自己也尝试用自定义表。）

由于某种原因，我无法将数据写入一整行的表。如果我创建一个新的工作表，它工作正常，但后来我有太多的表，我无法删除的任何纸张。

有什么特别的你需要做的，写一个数据行到一个空白页？

我尝试做的：

 路径=包括我的文件的路径。

CONNSTRING =的String.Format（供应商= Microsoft.Jet.OLEDB.4.0;数据源= {0};扩展属性= \的Excel 8.0; HDR = NO; \使用Server.Mappath（路径））;

dbCmd.CommandText =更新[工作表Sheet1 $]设置F1 ='COL1'，F2 ='col2的'，F3 ='COL3'，F4 ='COL4';
dbCmd.ExecuteNonQuery（）;
 

解决方案

下面是创建品牌的新S preadsheet，创建一个表（Sheet1中），然后插入行成的一个例子。大多数这个例子中是基于从大卫·海登（博客条目这个任务伟大的博客文章，顺便说一句！）。

此外，你应该看看这个 Microsoft知识库文章读取/写入到Excel ADO.NET - 它确实进入了很多细节

  //其中大部分code是从大卫海登的博客：
    // http://www.davidhayden.com/blog/dave/archive/2006/05/26/2973.aspx
    静态无效的主要（字串[] args）
    {
        字符串的connectionString = @供应商= Microsoft.Jet.OLEDB.4.0;数据源= C：\ TEMP \ TestSO1.xls;扩展属性=的Excel 8.0; HDR = NO;;
        DbProviderFactory厂=
          DbProviderFactories.GetFactory（System.Data.OleDb）;

        使用（的DbConnection连接= factory.CreateConnection（））
        {
            connection.ConnectionString =的connectionString;
            使用（的DbCommand命令= connection.CreateCommand（））
            {
                connection.Open（）; //打开连接

                //使用$符号表名称后表示，这是
                //现有的表，而不是实际创建它。这基本上定义
                //的元数据的INSERT语句将随之而来。
                //如果$符号被删除，那么新的工作表中创建一个名为工作表Sheet1。
                command.CommandText =CREATE TABLE [工作表Sheet1 $]（F1号，F2 CHAR（255），F3 CHAR（128））;
                command.ExecuteNonQuery（）;

                //现在值插入到现有的表......没有新的表被添加。
                command.CommandText =INSERT INTO [表Sheet 1 $]（F1，F2，F3）VALUES（4，\坦帕\\佛罗里达\）;
                command.ExecuteNonQuery（）;

                //插入另一行的表...
                command.CommandText =INSERT INTO [表Sheet 1 $]（F1，F2，F3）VALUES（5，\匹兹堡\\宾夕法尼亚\）;
                command.ExecuteNonQuery（）;
            }
        }
    }
 

我发现的唯一的问题是，即使在连接字符串各国不使用标题，你还是要定义的列名的表，和ADO.NET插入一行，当你创建一个具有行头名的表。我似乎无法找到身边，除了去后我都插入和取出的第一行的方式。不是很优雅。

希望这有助于！让我知道如果您有其他问题。

I am trying to use ADO.NET to connect to and write to an excel file. I have created a blank file with the default excel sheets (I have also tried with a custom sheet.)

For some reason I am unable to write a full row of data to the sheet. If I create a new sheet it works fine, however then I have too many sheets and I am unable to delete any sheets.

Is there something special you need to do to write a row of data to a blank sheet?

I try to do:

path= the path including my file. 

connString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=NO;\"", Server.MapPath(path));

dbCmd.CommandText = "Update  [Sheet1$] Set F1 = 'Col1', F2 = 'Col2', F3 = 'Col3', F4 = 'Col4'";
dbCmd.ExecuteNonQuery(); 

解决方案

Here's an example of creating a brand new spreadsheet, creating a sheet (Sheet1) and then inserting a row into that. Most of this example was based on a blog entry from David Hayden (great blog entry for this task, btw!!).

Also, you should check out this Microsoft KB article for reading/writing to Excel from ADO.NET -- it really goes into a lot of detail.

    //Most of this code was from David Hayden's blog:
    // http://www.davidhayden.com/blog/dave/archive/2006/05/26/2973.aspx
    static void Main(string[] args)
    {
        string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\TestSO1.xls;Extended Properties=""Excel 8.0;HDR=NO;""";
        DbProviderFactory factory =
          DbProviderFactories.GetFactory("System.Data.OleDb");

        using (DbConnection connection = factory.CreateConnection())
        {
            connection.ConnectionString = connectionString;
            using (DbCommand command = connection.CreateCommand())
            {
                connection.Open();  //open the connection

                //use the '$' notation after the sheet name to indicate that this is
                // an existing sheet and not to actually create it.  This basically defines
                // the metadata for the insert statements that will follow.
                // If the '$' notation is removed, then a new sheet is created named 'Sheet1'.
                command.CommandText = "CREATE TABLE [Sheet1$] (F1 number, F2 char(255), F3 char(128))";
                command.ExecuteNonQuery();

                //now we insert the values into the existing sheet...no new sheet is added.
                command.CommandText = "INSERT INTO [Sheet1$] (F1, F2, F3) VALUES(4,\"Tampa\",\"Florida\")";
                command.ExecuteNonQuery();

                //insert another row into the sheet...
                command.CommandText = "INSERT INTO [Sheet1$] (F1, F2, F3) VALUES(5,\"Pittsburgh\",\"Pennsylvania\")";
                command.ExecuteNonQuery();
            }
        }
    }

The only problem I found is that even though the connection string states not to use headers, you still have to define column names for your sheet, and ADO.NET inserts a row when you create the sheet that has the row header names. I can't seem to find a way around that besides going in after I insert everything and removing the first row. Not very elegant.

Hope this helps!! Let me know if you have other questions.

这篇关于写入到空白Excel工作表使用ADO.NET的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！