把excel的数据导入数据库

后台代码：
protected void putIn_Click(object sender, EventArgs e)
{
string file = "";
file = this.File1.Value;
this.ImportExcel(file);
this.load(this.txt_page.Value);
}

private void ImportExcel(string file)
{
File1.PostedFile.SaveAs(Server.MapPath(file.Substring(https://www.360docs.net/doc/9f10194673.html,stIndexOf("\\") + 1)));//SaveAs 将上传的文件内容保存在服务器上
string path = File1.Value.Substring(https://www.360docs.net/doc/9f10194673.html,stIndexOf("."));
string IsXls = System.IO.Path.GetExtension(path).ToString().ToLower();//System.IO.Path.GetExtension获得文件的扩展名

string strConn = "";
if (IsXls == ".xls")
{
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(file.Substring(https://www.360docs.net/doc/9f10194673.html,stIndexOf("\\") + 1)) + ";Extended Properties=Excel 8.0;";//execl 2003
}
else if (IsXls == ".xlsx")
{
strConn = "Provider= Microsoft.Ace.OleDB.12.0;Data Source=" + Server.MapPath(file.Substring(https://www.360docs.net/doc/9f10194673.html,stIndexOf("\\") + 1)) + ";Extended Properties=Excel 12.0;";//execl 2007以上（需要装个AccessDatabaseEngine引擎）
}
DataSet ds = new DataSet();
using (System.Data.OleDb.OleDbConnection cn1 = new OleDbConnection(strConn))
{
using (OleDbDataAdapter dr = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn))
{
dr.Fill(ds);
}
}
//插入到数据库

SqlConnection cn = new BSqlDataProvider().GetSqlConnection();
cn.Open();
//if (File1.HasFile == false)//HasFile用来检查FileUpload是否有指定文件
//{
// Response.Write(" ");
// return;//当无文件时,返回
//}
string path = File1.Value.Substring(https://www.360docs.net/doc/9f10194673.html,stIndexOf("."));
string IsXls = System.IO.Path.GetExtension(path).ToString().ToLower();//System.IO.Path.GetExtension获得文件的扩展名
if (IsXls != ".xls")
{
Response.Write("");
//return;//当选择的不是Excel文件时,返回
}
//string filename = FileUpload1.FileName; //获取Execle文件名 DateTime日期函数
string savePath = Server.MapPath(file.Substring(https://www.360docs.net/doc/9f10194673.html,stIndexOf("\\") + 1));//Server.MapPath 获得虚拟服务器相对路径
File1.PostedFile.SaveAs(savePath); //SaveAs 将上传的文件内容保存在服务器上
//DataSet ds1 = ExcelSqlConnection(savePath, fileName); //调用自定义方法
DataRow[] dr1 = ds.Tables[0].Select(); //定义一个DataRow数组
int rowsnum = ds.Tables[0].Rows.Count;
if (rowsnum == 0)
{
Response.Write(""); //当Excel表为空时,对用户进行提示
}
else
{
for (int i = 0; i < dr1.Length; i++)
{
string companyname = dr1[i]["公司名称"].ToString();//日期 excel列名【名称不能变,否则就会出错】
string address = dr1[i]["公司地址"].ToString();
string phone = dr1[i]["联系人电话1"].ToString();
string linkname = dr1[i]["联系人"].ToString();
string phone2 = dr1[i]["联系人电话2"].ToString();
string createdtime = dr1[i]["加入时间"].ToString();
string insertstr = "insert into salse_company_new values('" + companyname + "','" + address + "','" + phone + "','" + linkname + "','" + phone2 + "','" + createdtime + "','','',0)";
//System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.AppSettings ["ConnectionString"]);
//con.Open();
SqlCommand cmd = new SqlCommand(insertstr, cn);
try
{
cmd.ExecuteNonQuery();
}
catch (MembershipCreateUserException ex) //捕捉异常
{
Response.Write("");
}

}
Response.Write("");
}

cn.Close();
}


GetSqlConnection方法：
public System.Data.SqlClient.SqlConnection GetSqlConnection()
{
string conStr = System.Configuration.ConfigurationManager.AppSettings["ConnectionString"];
System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(conStr);
return con;
}


页面代码：