将值插入excel [英] inserting value into excel

问题描述

我在刷卡时将数据插入excel，使用oledb连接

我的代码是：

< pre lang =xml>私有字符串GetConnectionString（）
 {
字典<字符串，字符串> props = new Dictionary< string，string>（）; 
 
 // XLSX  -  Excel 2007,2010,2012,2013 
 props [Provider] =Microsoft.ACE.OLEDB.12.0;; 
 props [扩展属性] =Excel 12.0 XML; 
道具[数据源] =C：\\MyExcel.xlsx; 
 
 
 StringBuilder sb = new StringBuilder（）; 
 
 foreach（KeyValuePair< string，string> prop in props）
 {
 sb.Append（prop.Key）; 
 sb.Append（'='）; 
 sb.Append（prop.Value）; 
 sb.Append（';'）; 
} 
 
返回sb.ToString（）; 
}< / pre> 

并在按钮点击中插入数据代码为：

  string  connectionString = GetConnectionString（）; 
 
 使用（OleDbConnection conn =  new  OleDbConnection（connectionString））
 {
 conn.Open（）; 
 OleDbCommand cmd =  new  OleDbCommand（）; 
 cmd.Connection = conn; 
 
  //   cmd.CommandText =CREATE TABLE [table1]（id INT）; ;  
  //   cmd.ExecuteNonQuery（）;  
 
 cmd.CommandText =   INSERT INTO [sheet1 $]（id）VALUES（ + newString +  ）;; 
 cmd.ExecuteNonQuery（）; 
 
 conn.Close（）; 
} 
 

这里newString变量包含数据字符串0008478660但在excel中它插入为8478660 。如何插入完整的字符串，使用零的

解决方案

（id）VALUES（ + newString + ）;;
cmd.ExecuteNonQuery（）;

conn.Close（）;
}

这里newString变量包含数据字符串0008478660但在excel中它插入为8478660。通过给出单引号插入完整的字符串，使用零

，否则将其视为数字。

 cmd。 CommandText =   INSERT INTO [sheet1 

（id）VALUES（' + newString + '）;;

Hi,

am inserting data into excel when the card is swiped,am using oledb connection
my code is :

<pre lang="xml">private string GetConnectionString()
       {
           Dictionary<string, string> props = new Dictionary<string, string>();

           // XLSX - Excel 2007, 2010, 2012, 2013
           props["Provider"] = "Microsoft.ACE.OLEDB.12.0;";
           props["Extended Properties"] = "Excel 12.0 XML";
           props["Data Source"] = "C:\\MyExcel.xlsx";

         
           StringBuilder sb = new StringBuilder();

           foreach (KeyValuePair<string, string> prop in props)
           {
               sb.Append(prop.Key);
               sb.Append('=');
               sb.Append(prop.Value);
               sb.Append(';');
           }

           return sb.ToString();
       }</pre>

and inserting data code in button click is:

string connectionString = GetConnectionString();

 using (OleDbConnection conn = new OleDbConnection(connectionString))
 {
     conn.Open();
     OleDbCommand cmd = new OleDbCommand();
     cmd.Connection = conn;

     //cmd.CommandText = "CREATE TABLE [table1] (id INT);";
     //cmd.ExecuteNonQuery();

     cmd.CommandText = "INSERT INTO [sheet1$](id) VALUES(" + newString + ");";
     cmd.ExecuteNonQuery();

     conn.Close();
 }

here newString variable contains data as string "0008478660" but in excel it's inserting as "8478660" . how insert complete string as it is, with zero's

解决方案

(id) VALUES(" + newString + ");"; cmd.ExecuteNonQuery(); conn.Close(); }

here newString variable contains data as string "0008478660" but in excel it's inserting as "8478660" . how insert complete string as it is, with zero's

by giving single quotes, otherwise it will consider as number.

cmd.CommandText = "INSERT INTO [sheet1

(id) VALUES('" + newString + "');";

这篇关于将值插入excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！