当我使用Windows窗体在Excel中插入值时。 [英] When I insert the values in excel using windows forms.

问题描述

private void btn_save_Click(object sender, EventArgs e)
     {
         DataTable sheet1 = new DataTable("StockTaking");
         OleDbConnectionStringBuilder csbuilder = new OleDbConnectionStringBuilder();
         csbuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
         csbuilder.DataSource = "D:\\Anwesh\\New Microsoft Excel Worksheet.xls";
         csbuilder.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES");
         string selectSql = @"SELECT * FROM [New Microsoft Excel Worksheet$]";
         using (OleDbConnection con = new OleDbConnection(csbuilder.ConnectionString))
         using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, con))
         {
             con.Open();
             adapter.Fill(sheet1);
         }

         //Insert
         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.ACE.OLEDB.12.0;Data Source='C:\\Users\\557546\\Downloads\\New Microsoft Excel Worksheet.xls';Extended Properties=Excel 8.0;");
         MyConnection.Open();
         myCommand.Connection = MyConnection;
         myCommand.Parameters.AddWithValue("@col1", txt_warehousNo.Text);
         myCommand.Parameters.AddWithValue("@col2", txt_barcd.Text);
         myCommand.Parameters.AddWithValue("@col3", txt_batchnum.Text);
         myCommand.Parameters.AddWithValue("@col4", txt_itmcode.Text);
         myCommand.Parameters.AddWithValue("@col5", txt_qty.Text);
         sql = "Insert into [New Microsoft Excel Worksheet$] (WareHouse No,Barcode,BatchNumber,ItemCode,Quantity) values(@col1,@col2,@col3,@col4,@col5)";
         myCommand.CommandText = sql;
         myCommand.ExecuteNonQuery();
         MyConnection.Close();
     }

我的尝试：



Microsoft Office Access数据库引擎找不到对象新建Microsoft Excel工作表$。确保对象存在，并正确拼写其名称和路径名。

What I have tried:

The Microsoft Office Access database engine could not find the object 'New Microsoft Excel Worksheet$'. Make sure the object exists and that you spell its name and the path name correctly.

推荐答案

;
using （OleDbConnection con = new OleDbConnection（csbuilder.ConnectionString））
using using （OleDbDataAdapter adapter = new OleDbDataAdapter（selectSql，con））
{
con.Open（）;
adapter .Fill（sheet1）;
}

// 插入
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.ACE.OLEDB.12.0 ; Data Source ='C：\\Users\\557546 \\Downloads \\ New Microsoft Excel Worksheet.xls';扩展属性= Excel 8.0;）;
MyConnection.Open（）;
myCommand.Connection = MyConnection;
myCommand.Parameters.AddWithValue（ @ col1，txt_warehousNo.Text）;
myCommand.Parameters.AddWithValue（ @ col2，txt_barcd.Text）;
myCommand.Parameters.AddWithValue（ @ col3，txt_batchnum.Text）;
myCommand.Parameters.AddWithValue（ @ col4，txt_itmcode.Text）;
myCommand.Parameters.AddWithValue（ @ col5，txt_qty.Text）;
sql = 插入[新Microsoft Excel工作表

"; using (OleDbConnection con = new OleDbConnection(csbuilder.ConnectionString)) using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, con)) { con.Open(); adapter.Fill(sheet1); } //Insert 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.ACE.OLEDB.12.0;Data Source='C:\\Users\\557546\\Downloads\\New Microsoft Excel Worksheet.xls';Extended Properties=Excel 8.0;"); MyConnection.Open(); myCommand.Connection = MyConnection; myCommand.Parameters.AddWithValue("@col1", txt_warehousNo.Text); myCommand.Parameters.AddWithValue("@col2", txt_barcd.Text); myCommand.Parameters.AddWithValue("@col3", txt_batchnum.Text); myCommand.Parameters.AddWithValue("@col4", txt_itmcode.Text); myCommand.Parameters.AddWithValue("@col5", txt_qty.Text); sql = "Insert into [New Microsoft Excel Worksheet

（ WareHouse No，Barcode，BatchNumber，ItemCode，Quantity）值（@ col1，@ col2，@ col3，@ col4，@ col5）;
myCommand.CommandText = sql;
myCommand.ExecuteNonQuery（）;
MyConnection.Close（）;
}

(WareHouse No,Barcode,BatchNumber,ItemCode,Quantity) values(@col1,@col2,@col3,@col4,@col5)"; myCommand.CommandText = sql; myCommand.ExecuteNonQuery(); MyConnection.Close(); }

我的尝试：



Microsoft Office Access数据库引擎找不到对象新建Microsoft Excel工作表

What I have tried:

The Microsoft Office Access database engine could not find the object 'New Microsoft Excel Worksheet

。确保对象存在，并正确拼写其名称和路径名。

'. Make sure the object exists and that you spell its name and the path name correctly.

这篇关于当我使用Windows窗体在Excel中插入值时。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！