将excel中的sheet1导入到sqlserver中

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

原文地址：C#将Excel数据表导入SQL数据库的两种方法作者：windream

方式一:

实现在c#中可高效的将excel数据导入到sqlserver数据库中,很多人通过循环来拼接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.Windows.Forms;
using System.Data.OleDb;

namespace ExcelToSQL
{
    public
partial class Form1 : Form
    {

public Form1()

{

InitializeComponent();

}

private void button1_Click(object sender, EventArgs e)

{

//测试，将excel中的student导入到sqlserver的db_test中，如果sql中的数据表不存在则创建

string connString = "server = (local); uid = sa; pwd = sa; database
= db_test";

System.Windows.Forms.OpenFileDialog fd = new
OpenFileDialog();

if (fd.ShowDialog() ==
DialogResult.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.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文件的第一行为列标题,从第二行开始全部都是数据记录

string strSql = string.Format("if not exists(select * from
sysobjects where name = '{0}') create table {0}(",
sheetName);
//以sheetName为表名

foreach (System.Data.DataColumn c in ds.Tables[0].Columns)

{

strSql += string.Format("[{0}] varchar(255),",
c.ColumnName);

}

strSql = strSql.Trim(',') +
")";


using (System.Data.SqlClient.SqlConnection 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文件的第二行数据开始，不管数据表的结构是什么样的，反正就是第
一列的数据会插入到数据表的第一列字段中，第二列的数据插入到数据表的第二列字段中，以此类推，它本身不会去判断要插入的数据是对应数据表中哪一个字段的

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.NotifyAfter =
100;//进度提示的行数

bcp.DestinationTableName =
sheetName;//目标表

bcp.WriteToServer(ds.Tables[0]);

}

}

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.Update();

}

}
}

方式二：

先将Excel文件转换成DataTable，然后再循环将记录插入到数据库表中，这种方式可以任由程序员来选择将哪列数据导入到数据表的哪个字段中

namespace ExcelToSQL
{
    public
partial class Form2 : Form
    {

public Form2()

{

InitializeComponent();

}

DataTable dt = new DataTable();

string connString = "server = (local); uid = sa; pwd = sa; database
= db_test";

SqlConnection conn;

private void button1_Click(object sender, EventArgs e)

{

System.Windows.Forms.OpenFileDialog fd = new
OpenFileDialog();

if (fd.ShowDialog() == DialogResult.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 [student$]", strConn);

DataSet ds = new DataSet();

try

{

da.Fill(ds);

dt = ds.Tables[0];

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 dr = null;

for (int i = 0; i < dt.Rows.Count; i++)

{

dr = dt.Rows[i];

insertToSql(dr);

}

conn.Close();

MessageBox.Show("导入成功！");

}

else

{

MessageBox.Show("没有数据！");

}

}

private void insertToSql(DataRow dr)

{

//excel表中的列名和数据库中的列名一定要对应

string name = dr["StudentName"].ToString();

string sex = dr["Sex"].ToString();

string no = dr["StudentIDNO"].ToString();

string major = dr["Major"].ToString();

string sql = "insert into student values('" + name + "','" + sex +
"','" + no + "','" + major
+"')";

SqlCommand cmd = new SqlCommand(sql,
conn);

cmd.ExecuteNonQuery();

}
}
}

原文地址：将excel中的sheet1导入到sqlserver中作者：笑笑

using System; using System.Data; using System.Windows.Forms; using System.Data.OleDb; namespace WindowsApplication2 { public partial class Form1 : Form { public Form1() { InitializeComponent(); } 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(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;"; 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); //如果目标表不存在则创建 string strSql = string.Format("if object_id('{0}') is null create table {0}(", sheetName); foreach (System.Data.DataColumn c in ds.Tables[0].Columns) { strSql += string.Format("[{0}] varchar(255),", c.ColumnName); } strSql = strSql.Trim(',') + ")"; using (System.Data.SqlClient.SqlConnection sqlconn = new System.Data.SqlClient.SqlConnection(connectionString)) { sqlconn.Open(); System.Data.SqlClient.SqlCommand command = sqlconn.CreateCommand(); command.CommandText = strSql; command.ExecuteNonQuery(); sqlconn.Close(); } //用bcp导入数据 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.NotifyAfter = 100;//进度提示的行数
bcp.DestinationTableName = sheetName;//目标表
bcp.WriteToServer(ds.Tables[0]);

sbc.DestinationTableName = "mobile_black "; //服务器上目标表的名称

sbc.BulkCopyTimeout = 180; //设置允许超时时间为100秒

sbc.ColumnMappings.Add("mobile", "mobile"); //本地表和目标表列名

sbc.ColumnMappings.Add("ts_id", "ts_id"); //如果表有多个字段,下面继续添加

sbc.WriteToServer(dt);//插入数据

result = true;

}
}
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.Update();
}
}
}

主要代码：

System.Data.SqlClient.SqlBulkCopy类，让大数量插入到MSSQL数据库中可以很快搞定。

项目中一个表100W条数据，普通SQL插入语句，花了10多分钟。

使用System.Data.SqlClient.SqlBulkCopy插入，只用了几秒钟。

下面是主要的代码：

//省略连接字符串
SqlConnection conn = new
SqlConnection(".....");
conn.Open();
//初始化类
using
(System.Data.SqlClient.SqlBulkCopy sqlBC =
new
System.Data.SqlClient.SqlBulkCopy(conn))
{
//获取需要导入的数据表
DataTable dt = GetDataTable();
//每10W条数据一个事物
sqlBC.BatchSize = 100000;
//超时时间
sqlBC.BulkCopyTimeout = 60;
//表名Users
sqlBC.DestinationTableName =
"dbo.Users";
//字段对应，分表为原数据表字段名，和导入数据库的字段名
sqlBC.ColumnMappings.Add("Access_ID",
"MSSQL_ID");
sqlBC.ColumnMappings.Add("Access_Name",
"MSSQL_Name");
//sqlBC.ColumnMappings.Add("Access_...",
"MSSQL_...");
//sqlBC.ColumnMappings.Add("Access_...",
"MSSQL_...");
//导入到数据库
sqlBC.WriteToServer(dt);