1、方式一:实现在 c#中可高效的将 excel 数据导入到 sqlserver 数据库中,很多人通过循环来拼接 sql,这样做不但容易出错而且效率低下,最好的办法是使用 bcp,也就是System.Data.SqlClient.SqlBulkCopy 类来实现。using System; using System.Data; using System.Windows.Forms; using System.Data.OleDb; namespace WindowsApplication2 public partial class Form1 : Form public Form1() Initi
2、alizeComponent(); private void button1_Click(object sender, EventArgs e) /测试,将 excel 中的 sheet1 导入到 sqlserver 中 string connString = “server=localhost;uid=sa;pwd=sqlgis;database=master“; System.Windows.Forms.OpenFileDialog fd = new OpenFileDialog(); if (fd.ShowDialog() = DialogResult.OK) TransferData(
3、fd.FileName, “sheet1“, connString); public void TransferData(string excelFile, string sheetName, string connectionString) DataSet ds = new DataSet(); try /获取全部数据 string strConn = “Provider=Microsoft.Jet.OLEDB.4.0;“ + “Data Source=“ + excelFile + “;“ + “Extended Properties=Excel 8.0;“; OleDbConnectio
4、n conn = new OleDbConnection(strConn); conn.Open(); string strExcel = “; OleDbDataAdapter myCommand = null; strExcel = string.Format(“select * from 0$“, sheetName); myCommand = new OleDbDataAdapter(strExcel, strConn); myCommand.Fill(ds, sheetName); /如果目标表不存在则创建 excel 文件的第一行为列标题,从第二行开始全部都是数据记录string
5、strSql = string.Format(“if object_id(0) is null create table 0(“, sheetName); /以 sheetName 为表名foreach (System.Data.DataColumn c in ds.Tables0.Columns) strSql += string.Format(“0 varchar(255),“, c.ColumnName); strSql = strSql.Trim(,) + “)“; using (System.Data.SqlClient.SqlConnection sqlconn = new Sys
6、tem.Data.SqlClient.SqlConnection(connectionString) sqlconn.Open(); System.Data.SqlClient.SqlCommand command = sqlconn.CreateCommand(); command.CommandText = strSql; command.ExecuteNonQuery(); sqlconn.Close(); /用 bcp 导入数据 /excel 文件中列的顺序必须和数据表的列顺序一致,因为数据导入时,是从 excel 文件的第二行数据开始,不管数据表的结构是什么样的,反正就是第一列的数据
7、会插入到数据表的第一列字段中,第二列的数据插入到数据表的第二列字段中,以此类推,它本身不会去判断要插入的数据是对应数据表中哪一个字段的using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(connectionString) bcp.SqlRowsCopied += new System.Data.SqlClient.SqlRowsCopiedEventHandler(bcp_SqlRowsCopied); bcp.BatchSize = 100;/每次传输的行数 bcp.Noti
8、fyAfter = 100;/进度提示的行数 bcp.DestinationTableName = sheetName;/目标表 bcp.WriteToServer(ds.Tables0); catch (Exception ex) System.Windows.Forms.MessageBox.Show(ex.Message); /进度显示 void bcp_SqlRowsCopied(object sender, System.Data.SqlClient.SqlRowsCopiedEventArgs e) this.Text = e.RowsCopied.ToString(); this
9、.Update(); C# Excel 导入数据库(二)方式二:本文先将 Excel 文件转换成 DataTable,然后再循环将记录插入到数据库表中,这种方式可以任由程序员来选择将哪列数据导入到数据表的哪个字段中DataTable table =new DataTable();/数据绑定到 datagridview1private void button1_Click(object sender, EventArgs e) System.Windows.Forms.OpenFileDialog fd = new OpenFileDialog();if (fd.ShowDialog() = D
10、ialogResult.OK)string fileName = fd.FileName;bind(fileName);private void bind(string fileName) string strConn = “Provider=Microsoft.Jet.OLEDB.4.0;“ +“Data Source=“ + fileName + “;“ +“Extended Properties=Excel 8.0;HDR=Yes;IMEX=1“;OleDbDataAdapter da = new OleDbDataAdapter(“SELECT * FROM Sheet1$“, str
11、Conn);DataSet ds = new DataSet();tryda.Fill(ds);dt = ds.Tables0;this.dataGridView1.DataSource = dt; catch (Exception err)MessageBox.Show(“操作失败!“+err.ToString();/将 Datagridview1 的记录插入到数据库 private void button2_Click(object sender, EventArgs e) if (dataGridView1.Rows.Count 0)DataRow dr = null;for (int
12、i = 0; i dt.Rows.Count; i+) dr = dt.Rowsi;insertToSql(dr);MessageBox.Show(“导入成功!“);else MessageBox.Show(“没有数据!“);private void insertToSql(DataRow dr) string name=dr“姓名“.ToString();string age = dr“年龄“.ToString();string sql = “insert into sheet1 values(“+name+“,“+age+“)“;SqlConnection conn = new SqlConnection();SqlCommand cmd = new SqlCommand(sql,conn);conn.Open();cmd.ExecuteNonQuery();conn.Close();