Excel数据导入sql数据库方法

Excel 导入数据到sql server 2010中代码实现：

using System;

using System.Collections.Generic;

using https://www.360docs.net/doc/6810739077.html,ponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Data.SqlClient;

using System.Data.OleDb;

using System.Collections;

using System.IO;

public partial class pageitems_excelin : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

}

protected void Button1_Click(object sender, EventArgs e)

{

if (FileUpload1.HasFile == false)

{

Page.RegisterStartupScript(""," ");

return;//当无文件时,返回

}

string IsXls = System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();

if (IsXls != ".xlsx" && IsXls != ".xls")

{

Page.RegisterStartupScript("","");

return;//当选择的不是Excel文件时,返回

}

SqlConnection cn = SqlHelp.GetCon();

cn.Open();

string strpath = FileUpload1.PostedFile.FileName.ToString(); //获取Execle文件路径

string filename = FileUpload1.FileName; //获取Execle文件名

DataSet ds = ExecleDs(strpath, filename);

DataRow[] dr = ds.Tables[0].Select(); //定义一个DataRow数组

int rowsnum = ds.Tables[0].Rows.Count;

if (rowsnum == 0)

{

Page.RegisterStartupScript("",""); //当Excel表为空时,对用户进行提示

}

else

{

for (int i = 0; i < dr.Length; i++)

{

string sqlcheck = "select count(*) from name where name='" + dr[i]["name"].ToString() + "'"; //检查用户是否存在

int num = Convert.ToInt32(SqlHelp.ExObject(sqlcheck));

if (num == 0)

{

string insertstr = "insert into name(name,sex,age) values('" + dr[i]["name"].ToString() + "','" + dr[i]["sex"].ToString() + "','" + dr[i]["age"].ToString() + "')"; SqlCommand cmd = new SqlCommand(insertstr, cn);

try

{

cmd.ExecuteNonQuery();

}

catch (Exception ex) //捕捉异常

{

Page.RegisterStartupScript("","");

}

}

else

{

Page.RegisterStartupScript("",""); //若存在,将已存在用户信息打出,并提示此用户无法插入

continue;

}

}

Page.RegisterStartupScript("","");

}

cn.Close();

}

public DataSet ExecleDs(string filenameurl,string table)

{

//string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" +filenameurl+ ";Extended Properties='Excel 4.0; HDR=YES; IMEX=1'";

string strConn = "Provider=Microsoft.ACE.OleDb.12.0;" + "data source=" + filenameurl + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";

OleDbConnection conn = new OleDbConnection(strConn);

OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$]",conn);

DataSet ds = new DataSet();

odda.Fill(ds,table);

return ds;

}

}

前台：

批量导入

注意几点：

1、需要下载：AccessDatabaseEngine.exe 网上有！

2、Sqlhelp类网上有，搜索可得到！

3、数据库建立有name，sex，age

4、excel可以是xls，和xlsx后缀名；