写入到空白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屋!