使用Oledb将数据写入Excel [英] Write Data to Excel using Oledb

问题描述

没有表结构或任何东西，它是一个用户文档。

当我尝试，我一直有一个OleDbException

• INSERT查询回复：
  


• 更新查询回复：
  没有给出一个或多个必需参数的值。

我的代码：

  using（OleDbConnection connection = new OleDbConnection Provider = Microsoft.ACE.OLEDB.12.0; Data Source =+ @C：\Users\ [...] \Classeur.xls+;扩展属性= \Excel 8.0; HDR = NO; IMEX = 1; READONLY = FALSE\））
 {
 connection.Open（）; 
 OleDbCommand commande = new OleDbCommand（
INSERT INTO [Feuil1 $]（F1，F2，F3）VALUES（'A3'，'B3'，'C3'）;，connection）; 
 commande.ExecuteNonQuery（）; 
 
 connection.Close（）; 
 connection.Dispose（）; 
} 
  

新测试（没有成功！）：

  using（OleDbConnection connection = new OleDbConnection（@Provider = Microsoft.Jet.OLEDB.4.0; Data Source =+ @C：\Users\ [...] \Classeur.xls+;扩展属性= \Excel 8.0; HDR = NO; IMEX = 1; READONLY = FALSE\））
 {
 string column =A; 
 string row =1; 
 string worksheetName =Feuil1; 
 string data =TEST; 
 connection.Open（）; 
 string commandString = String.Format（UPDATE [{0} $ {1} {2}：{1} {2}] SET F1 ='{3}'，worksheetName，column，row，data） ; 
 OleDbCommand commande = new OleDbCommand（
 commandString，connection）; 
 connection.Close（）; 
 connection.Dispose（）; 
} 
  

解决方案

我终于找到了！
简单的问题IMEX（这么多小时丢失了！）

所以如果有任何人有同样的问题：

  //用于读取数据
扩展属性= \Excel 8.0; HDR = NO; IMEX = 1; READONLY = FALSE\
 
 //用于写入数据
扩展属性= \Excel 8.0; HDR = NO; IMEX = 3; READONLY = FALSE\
  

Is it possible to write data using Oledb into a common excel ?

There are no table structure or anything, it's a user document.

When I tried, i had always an OleDbException

• "INSERT" query reply : Operation must use an application that can be updated.
• "UPDATE" query reply : No value given for one or more required parameters.

My code:

  using (OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @"C:\Users\[...]\Classeur.xls" + ";Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1;READONLY=FALSE\""))
        {
            connection.Open();
            OleDbCommand commande = new OleDbCommand(
              "INSERT INTO [Feuil1$](F1,F2,F3) VALUES ('A3','B3','C3');", connection);
            commande.ExecuteNonQuery();

            connection.Close();
            connection.Dispose();
        }

New test (without sucess !) :

       using (OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @"C:\Users\[...]\Classeur.xls" + ";Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1;READONLY=FALSE\""))
        {
            string column = "A";
            string row = "1";
            string worksheetName = "Feuil1";
            string data = "TEST";
            connection.Open();
            string commandString = String.Format("UPDATE [{0}${1}{2}:{1}{2}] SET F1='{3}'", worksheetName, column, row, data);
            OleDbCommand commande = new OleDbCommand(
                commandString, connection);
            connection.Close();
            connection.Dispose();
        }

解决方案

I finally found ! Simple question of IMEX ( So many hours lost for that !)

So if anyone have the same issue :

 //for reading data 
 Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1;READONLY=FALSE\"

 //for writing data 
 Extended Properties=\"Excel 8.0;HDR=NO;IMEX=3;READONLY=FALSE\"

这篇关于使用Oledb将数据写入Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！