C#将Excel数据表导入SQL数据库的两种方法（高效率）.docx

1、C#将Excel数据表导SQL数据库的两种法（效率）法:使bcp，也就是System.Data.SqlClient.SqlBulkCopy 类来实现。using System;using System.Collections.Generic; using System.ComponentModel; using System.Data;using System.Drawing; using System.Linq; using System.Text;using System.Threading.Tasks; using System.Windows.Forms;using System.Da

2、ta.OleDb;namespace Test1public partial class Form4 : Formpublic Form4()InitializeComponent();private void button1_Click(object sender, EventArgs e)/connStr = Server=USER-20180315FD;initial catalog=AliDataTech;user id=sa;password=wxd123456;Connect Timeout=5;/测试，将excel中的student导到sqlserver的db_test中，如果s

3、ql中的数据表不存在则创建/string connString = server = (local); uid = sa; pwd = wxd123456; database = db_test;string connString = Server=USER-20180315FD;initial catalog=db_test;user id=sa;password=wxd123456;Connect Timeout=5; System.Windows.Forms.OpenFileDialog fd = new OpenFileDialog();if (fd.ShowDialog() = Di

4、alogResult.OK)TransferData(fd.FileName, student, 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.

5、0; OleDbConnection 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件的第为列标题,从第开始全部都是数据记录stri

6、ng strSql = string.Format(if not exists(select * from sysobjects where name = 0) 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.SqlConnec

7、tion sqlconn = new System.Data.SqlClient.SqlConnection(connectionString)sqlconn.Open();System.Data.SqlClient.SqlCommand command = sqlconn.CreateCommand(); command.CommandText = strSql;command.ExecuteNonQuery(); sqlconn.Close();/bcp导数据/excel件中列的顺序必须和数据表的列顺序致，因为数据导时，是从excel件的第数据开始，不管数据表的结构是什么样的，反正就usi

8、ng (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(connectionString)bcp.SqlRowsCopied += new System.Data.SqlClient.SqlRowsCopiedEventHandler(bcp_SqlRowsCopied); bcp.BatchSize = 1;/每次传输的数bcp.NotifyAfter = 1;/进度提的数bcp.DestinationTableName = sheetName;/标表bcp.WriteToServer

9、(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(); textBox1 .Text = e.RowsCopied.ToString(); textBox1.Update();this.Update();private void progr

10、essBar1_Click(object sender, EventArgs e)法：先将Excel件转换成DataTable，然后再循环将记录插到数据库表中，这种式可以任由程序员来选择将哪列数据导到数据表的哪个 字段中using System;using System.Collections.Generic; using System.ComponentModel; using System.Data;using System.Drawing; using System.Linq; using System.Text;using System.Threading.Tasks; using S

11、ystem.Windows.Forms;using System.Data.OleDb; using System.Data.SqlClient;namespace Test1public partial class Form5 : Formpublic Form5()InitializeComponent();DataTable dt = new DataTable();/string connString = Server=USER-20180315FD;initial catalog=db_test;user id=sa;password=wxd123456;Connect Timeou

12、t=5; string connString = Server=USER-20180315FD;initial catalog=db_test;user id=sa;password=wxd123456;Connect Timeout=5;SqlConnection conn;private void button1_Click(object sender, EventArgs e)System.Windows.Forms.OpenFileDialog fd = new OpenFileDialog(); if (fd.ShowDialog() = DialogResult.OK)string

13、 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 student$, strConn); DataSet ds = new Dat

14、aSet();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)conn = new SqlConnection(connString); conn.Open();if (dataGridView1.Rows.Count 0)DataRow

15、dr = null;for (int i = 0; i dt.Rows.Count; i+)dr = dt.Rowsi; insertToSql(dr);conn.Close(); MessageBox.Show(导成功！);elseMessageBox.Show(没有数据！);private void insertToSql(DataRow dr)/excel表中的列名和数据库中的列名定要对应string name = drStudentName.ToString(); string sex = drSex.ToString();string no = drStudentIDNO.ToStr

16、ing(); string major = drMajor.ToString();string sql = insert into student values( + name + , + sex + , + no + , + major + ); SqlCommand cmd = new SqlCommand(sql, conn);cmd.ExecuteNonQuery();.9 Form 5l = 1 回 恤button!butt o泣宝贝名称搜索类型流里来谏展现里一曰期但很扫1广8D划Y计名V28月芽儿粗踉短2019/1/12标注冒18DY V28匕CI？ I C I C( |俨.，- - -.，, .,.,，一.,乒.-、,.Ill|,T_ , 47计算机站内搜索月芽儿2018秋批里祈款10 182019/1/12499移动站内搜索月芽儿兔毛毛批里祈款10 182019/1/1223计算机站内搜索月芽儿兔毛毛批里祈款10 182019/1/128899移动站内定向投放月芽儿粗踉短标注冒18DY V282019/1/12377移动站内搜索月芽儿粗踉短标注冒18DY V282019/1/124161计算机站内定向投放月芽儿粗踉短4计算机站内搜索