ASP.NET中,如何实现把EXCEL的表导入到MYSQL中.代码实现 通过点击一个按钮,把相应路径的excel文件导入到mysql指定的数据表中。请问代码怎么写? 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 这是我的一个上传到mssql的代码,你借鉴先看看吧if (FileUpload1.HasFile == false || FileUpload7.HasFile == false)//HasFile用来检查FileUpload是否有指定文件 { Page.ClientScript.RegisterStartupScript(this.GetType(), "message", "alert('请选择EXCEL文件!')", true); return;//当无文件时,返回 } string IsXls = System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();//System.IO.Path.GetExtension获得文件的扩展名 string IsXls2 = System.IO.Path.GetExtension(FileUpload7.FileName).ToString().ToLower();//System.IO.Path.GetExtension获得文件的扩展名 if (IsXls != ".xls" || IsXls2 != ".xls") { Page.ClientScript.RegisterStartupScript(this.GetType(), "message", "alert('只可选择EXCEL文件!')", true); return;//当选择的不是Excel文件时,返回 } a.open(); string filename = FileUpload1.FileName; //获取Execle文件名 DateTime日期函数 string savePath = Server.MapPath(("~\\upfiles\\") + filename);//Server.MapPath 获得虚拟服务器相对路径 FileUpload1.SaveAs(savePath); //SaveAs 将上传的文件内容保存在服务器上 DataSet ds = ExecleDs(savePath, filename); //调用自定义方法 DataRow[] dr = ds.Tables[0].Select(); //定义一个DataRow数组 int rowsnum = ds.Tables[0].Rows.Count; if (rowsnum == 0) { Response.Write("<script>alert('Excel表为空表,无数据!')</script>"); //当Excel表为空时,对用户进行提示 } else { for (int i = 0; i < dr.Length; i++) { string Word_Level = dr[i]["Word_Level"].ToString();//日期 excel列名【名称不能变,否则就会出错】 string Word_Grade = dr[i]["Word_Grade"].ToString();//编号 列名 以下类似 string Field_One = dr[i]["Field_One"].ToString(); string Field_Two = dr[i]["Field_Two"].ToString(); string Field_Three = dr[i]["Field_Three"].ToString(); string Field_Four = dr[i]["Field_Four"].ToString(); string Field_Five = dr[i]["Field_Five"].ToString(); string Field_Six = dr[i]["Field_Six"].ToString(); string Field_Seven = dr[i]["Field_Seven"].ToString(); string Field_Eight = dr[i]["Field_Eight"].ToString(); string insertstr = "insert into Five_Word_Field_Data (Word_Level,Word_Grade,Field_One,Field_Two,Field_Three,Field_Four,Field_Five,Field_Six,Field_Seven,Field_Eight) values ('" + Word_Level + "','" + Word_Grade + "','" + Field_One + "','" + Field_Two + "','" + Field_Three + "','" + Field_Four + "','" + Field_Five + "','" + Field_Six + "','" + Field_Seven + "','" + Field_Eight + "')"; //SqlCommand cmd = new SqlCommand(insertstr); try { a.execsql(insertstr); //cmd.ExecuteNonQuery(); } catch (MembershipCreateUserException ex) //捕捉异常 { Response.Write("<script>alert('导入内容:" + ex.Message + "')</script>"); } } Page.ClientScript.RegisterStartupScript(this.GetType(), "message", "alert('导入成功!')", true); } a.close(); public DataSet ExecleDs(string filenameurl, string table) { string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filenameurl + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'"; OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); DataSet ds = new DataSet(); OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$]", conn); odda.Fill(ds, table); return ds; conn.Close(); } 可以用NPOI将Excel数据导入到datatable然后datatable写入数据库,无论什么数据库 网上有相关代码思路就是把EXCEL数据导入DataSet然后遍历他用事务插入数据库。 “点击按钮,选择Execl文件,读取Execl文件,然后放到DataTable里,然后,循环添加,就完了阿.”可我不知道该怎么写代码啊! public DataSet ExecleDs(string filenameurl, string table) { string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filenameurl + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'"; OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); DataSet ds = new DataSet(); OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$]", conn); odda.Fill(ds, table); conn.Close(); return ds; } protected void Button1_Click(object sender, EventArgs e)//上传按钮 { if (FileUpload1.HasFile == false || FileUpload2.HasFile == false)//HasFile用来检查FileUpload是否有指定文件 { Page.ClientScript.RegisterStartupScript(this.GetType(), "message", "alert('请选择EXCEL文件!')", true); return;//当无文件时,返回 } string IsXls = System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();//System.IO.Path.GetExtension获得文件的扩展名 string IsXls2 = System.IO.Path.GetExtension(FileUpload2.FileName).ToString().ToLower();//System.IO.Path.GetExtension获得文件的扩展名 if (IsXls != ".xls" || IsXls2 != ".xls") { Page.ClientScript.RegisterStartupScript(this.GetType(), "message", "alert('只可选择EXCEL文件!')", true); return;//当选择的不是Excel文件时,返回 } a.open(); string filename = FileUpload1.FileName; //获取Execle文件名 DateTime日期函数 string savePath = Server.MapPath(("~\\upfiles\\") + filename);//Server.MapPath 获得虚拟服务器相对路径 FileUpload1.SaveAs(savePath); //SaveAs 将上传的文件内容保存在服务器上 DataSet ds = ExecleDs(savePath, filename); //调用自定义方法 DataRow[] dr = ds.Tables[0].Select(); //定义一个DataRow数组 int rowsnum = ds.Tables[0].Rows.Count; if (rowsnum == 0) { Response.Write("<script>alert('Excel表为空表,无数据!')</script>"); //当Excel表为空时,对用户进行提示 } else { string Word_Level = DropDownList1.SelectedValue; string Word_Grade = TextBox1.Text; for (int i = 0; i < dr.Length; i++) { //string Word_Level = dr[i]["Word_Level"].ToString();//日期 excel列名【名称不能变,否则就会出错】 //string Word_Grade = dr[i]["Word_Grade"].ToString();//编号 列名 以下类似 string Field_One = dr[i]["Field_One"].ToString(); string Field_Two = dr[i]["Field_Two"].ToString(); string Field_Three = dr[i]["Field_Three"].ToString(); string Field_Four = dr[i]["Field_Four"].ToString(); string Field_Five = dr[i]["Field_Five"].ToString(); string Field_Six = dr[i]["Field_Six"].ToString(); string Field_Seven = dr[i]["Field_Seven"].ToString(); string Field_Eight = dr[i]["Field_Eight"].ToString(); string insertstr = "insert into Five_Word_Field_Data (Word_Level,Word_Grade,Field_One,Field_Two,Field_Three,Field_Four,Field_Five,Field_Six,Field_Seven,Field_Eight) values ('" + Word_Level + "','" + Word_Grade + "','" + Field_One + "','" + Field_Two + "','" + Field_Three + "','" + Field_Four + "','" + Field_Five + "','" + Field_Six + "','" + Field_Seven + "','" + Field_Eight + "')"; //SqlCommand cmd = new SqlCommand(insertstr); try { a.execsql(insertstr); //cmd.ExecuteNonQuery(); } catch (MembershipCreateUserException ex) //捕捉异常 { Response.Write("<script>alert('导入内容:" + ex.Message + "')</script>"); } } Page.ClientScript.RegisterStartupScript(this.GetType(), "message", "alert('导入成功!')", true); } a.close(); //上传内容 a.open(); string filename2 = FileUpload2.FileName; //获取Execle文件名 DateTime日期函数 string savePath2 = Server.MapPath(("~\\upfiles\\") + filename2);//Server.MapPath 获得虚拟服务器相对路径 FileUpload2.SaveAs(savePath2); //SaveAs 将上传的文件内容保存在服务器上 DataSet ds2 = ExecleDs(savePath2, filename2); //调用自定义方法 DataRow[] dr2 = ds2.Tables[0].Select(); //定义一个DataRow数组 int rowsnum2 = ds2.Tables[0].Rows.Count; if (rowsnum2 == 0) { Response.Write("<script>alert('Excel表为空表,无数据!')</script>"); //当Excel表为空时,对用户进行提示 } else { string Word_Name = TextBox1.Text; for (int i = 0; i < dr2.Length; i++) { //string Word_Name = dr2[i]["Word_Name"].ToString();//日期 excel列名【名称不能变,否则就会出错】 string Field_One_Word = dr2[i]["Field_One_Word"].ToString();//编号 列名 以下类似 string Field_Two_Word = dr2[i]["Field_Two_Word"].ToString(); string Field_Three_Word = dr2[i]["Field_Three_Word"].ToString(); string Field_Four_Word = dr2[i]["Field_Four_Word"].ToString(); string Field_Five_Word = dr2[i]["Field_Five_Word"].ToString(); string Field_Six_Word = dr2[i]["Field_Six_Word"].ToString(); string Field_Seven_Word = dr2[i]["Field_Seven_Word"].ToString(); string Field_Eight_Word = dr2[i]["Field_Eight_Word"].ToString(); string Field_One_If = dr2[i]["Field_One_If"].ToString(); string Field_Two_If = dr2[i]["Field_Two_If"].ToString(); string Field_Three_If = dr2[i]["Field_Three_If"].ToString(); string Field_Four_If = dr2[i]["Field_Four_If"].ToString(); string Field_Five_If = dr2[i]["Field_Five_If"].ToString(); string Field_Six_If = dr2[i]["Field_Six_If"].ToString(); string Field_Seven_If = dr2[i]["Field_Seven_If"].ToString(); string Field_Eight_If = dr2[i]["Field_Eight_If"].ToString(); string insertstr = "insert into Five_Word_Data (Word_Name,Field_One_Word,Field_Two_Word,Field_Three_Word,Field_Four_Word,Field_Five_Word,Field_Six_Word,Field_Seven_Word,Field_Eight_Word,Field_One_If,Field_Two_If,Field_Three_If,Field_Four_If,Field_Five_If,Field_Six_If,Field_Seven_If,Field_Eight_If) values ('" + Word_Name + "','" + Field_One_Word + "','" + Field_Two_Word + "','" + Field_Three_Word + "','" + Field_Four_Word + "','" + Field_Five_Word + "','" + Field_Six_Word + "','" + Field_Seven_Word + "','" + Field_Eight_Word + "','" + Field_One_If + "','" + Field_Two_If + "','" + Field_Three_If + "','" + Field_Four_If + "','" + Field_Five_If + "','" + Field_Six_If + "','" + Field_Seven_If + "','" + Field_Eight_If + "')"; //SqlCommand cmd = new SqlCommand(insertstr); try { a.execsql(insertstr); //cmd.ExecuteNonQuery(); } catch (MembershipCreateUserException ex) //捕捉异常 { Response.Write("<script>alert('导入内容:" + ex.Message + "')</script>"); } } Page.ClientScript.RegisterStartupScript(this.GetType(), "message", "alert('导入成功!')", true); } a.close(); } 谢谢generhappy了啊,现在我用类似的代码成功导入数据库了,可excel导入后,那个文件就不能正常打开了,说是在被另一个进程使用,是因为那儿没有关闭吗? 导入都是差不多的,和数据库没关系你看下你电脑的进程管理器,里面是不是有个excel进程 现在是把自己的电脑作既作为客户端,也作为服务器时,可以正常导入;但发布在我们部门的服务器上时,不能正常导入。我们的服务器是server2003,64位的。据说是把配置管理器中的any CPU 改为X86,可在我的VS2005中没有配置管理器啊,找了好久! 请问下,有木有.net语言实现这个功能的代码,有的话希望粘贴出来,谢谢了 using System;using System.Data;using System.Configuration;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using MySql.Data.MySqlClient;using System.Data.OleDb;public partial class exceltomysql1 : System.Web.UI.Page{ // MySqlConnection sqlcon; // MySqlCommand sqlcom; // string strConn = "server=localhost;user id=root;password=;database=chenzhen;"; protected void Page_Load(object sender, EventArgs e) { } protected void Button1_Click(object sender, EventArgs e) { MySqlConnection conn = new MySqlConnection("server=localhost;user id=root;password=;database=chenzhen;"); if (FileUpload1.HasFile == false)//HasFile用来检查FileUpload是否有指定文件 { Page.ClientScript.RegisterStartupScript(this.GetType(), "message", "alert('请选择EXCEL文件!')", true); return;//当无文件时,返回 } string IsXls = System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();//System.IO.Path.GetExtension获得文件的扩展名 if (IsXls != ".xlsx") { Page.ClientScript.RegisterStartupScript(this.GetType(), "message", "alert('只可选择EXCEL文件!')", true); return;//当选择的不是Excel文件时,返回 } conn.Open(); string filename = FileUpload1.FileName; //获取Execle文件名 DateTime日期函数 // string savePath = Server.MapPath(("~\\upfiles\\") + filename);//Server.MapPath 获得虚拟服务器相对路径 string savePath = Server.MapPath(("~\\") + filename);//Server.MapPath 获得虚拟服务器相对路径 FileUpload1.SaveAs(savePath); //SaveAs 将上传的文件内容保存在服务器上 DataSet ds = ExecleDs(savePath, filename); //调用自定义方法 DataRow[] dr = ds.Tables[0].Select(); //定义一个DataRow数组 int rowsnum = ds.Tables[0].Rows.Count; if (rowsnum == 0) { Response.Write("<script>alert('Excel表为空表,无数据!')</script>"); //当Excel表为空时,对用户进行提示 } else { for (int i = 0; i < dr.Length; i++) { string id = dr[i]["ID"].ToString(); string Word_Level = dr[i]["province"].ToString();//日期 excel列名【名称不能变,否则就会出错】 string Word_Grade = dr[i]["city"].ToString();//编号 列名 以下类似 if (Word_Level=="**") { break; } else { string insertstr = "insert into tb_city(province,city) values('" + Word_Level + "','" + Word_Grade + "')"; MySqlCommand cmd = new MySqlCommand(insertstr, conn); try { cmd.ExecuteNonQuery(); } catch (MembershipCreateUserException ex) //捕捉异常 { Response.Write("<script>alert('导入内容:" + ex.Message + "')</script>"); } } // conn.Close(); } } conn.Close(); Response.Write("<script>alert('Excle表导入成功!');</script>"); } public DataSet ExecleDs(string filenameurl, string table) { string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "Data Source=" + filenameurl + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'"; // string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "Data Source=" + filenameurl + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'"; OleDbConnection conn1 = new OleDbConnection(strConn); // conn.Close(); conn1.Open(); DataSet ds = new DataSet(); OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$]", conn1); odda.Fill(ds, table); return ds; conn1.Close(); }} JS代码混淆保护问题 Url传递参数时,把参数编码 求一日历控件要最完美的!~~~~ 在 VS2005 中怎么取出我设置的DataKeys值! 字符串问题? 请我我要定时清除数据库数据怎么办? SVN能否在外网使用?详细请见全文,,,谢谢 请问如何设置使输出的csv文件的编码为GB2312 DataGrid1.DataSource=ds;DataGrid1.datamember="testIn"和 ds.Tables["testIn"].DefaultView;有什么区别 运行网站提示应用程序中服务器错误, “缺少根元素” 救命呀,我的ASP.NET出大错了!! .net中的内置身份验证问题
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "message", "alert('请选择EXCEL文件!')", true);
return;//当无文件时,返回
}
string IsXls = System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();//System.IO.Path.GetExtension获得文件的扩展名
string IsXls2 = System.IO.Path.GetExtension(FileUpload7.FileName).ToString().ToLower();//System.IO.Path.GetExtension获得文件的扩展名
if (IsXls != ".xls" || IsXls2 != ".xls")
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "message", "alert('只可选择EXCEL文件!')", true);
return;//当选择的不是Excel文件时,返回
}
a.open();
string filename = FileUpload1.FileName; //获取Execle文件名 DateTime日期函数
string savePath = Server.MapPath(("~\\upfiles\\") + filename);//Server.MapPath 获得虚拟服务器相对路径
FileUpload1.SaveAs(savePath); //SaveAs 将上传的文件内容保存在服务器上
DataSet ds = ExecleDs(savePath, filename); //调用自定义方法
DataRow[] dr = ds.Tables[0].Select(); //定义一个DataRow数组
int rowsnum = ds.Tables[0].Rows.Count;
if (rowsnum == 0)
{
Response.Write("<script>alert('Excel表为空表,无数据!')</script>"); //当Excel表为空时,对用户进行提示
}
else
{
for (int i = 0; i < dr.Length; i++)
{
string Word_Level = dr[i]["Word_Level"].ToString();//日期 excel列名【名称不能变,否则就会出错】
string Word_Grade = dr[i]["Word_Grade"].ToString();//编号 列名 以下类似
string Field_One = dr[i]["Field_One"].ToString();
string Field_Two = dr[i]["Field_Two"].ToString();
string Field_Three = dr[i]["Field_Three"].ToString();
string Field_Four = dr[i]["Field_Four"].ToString();
string Field_Five = dr[i]["Field_Five"].ToString();
string Field_Six = dr[i]["Field_Six"].ToString();
string Field_Seven = dr[i]["Field_Seven"].ToString();
string Field_Eight = dr[i]["Field_Eight"].ToString(); string insertstr = "insert into Five_Word_Field_Data (Word_Level,Word_Grade,Field_One,Field_Two,Field_Three,Field_Four,Field_Five,Field_Six,Field_Seven,Field_Eight) values ('" + Word_Level + "','" + Word_Grade + "','" + Field_One + "','" + Field_Two + "','" + Field_Three + "','" + Field_Four + "','" + Field_Five + "','" + Field_Six + "','" + Field_Seven + "','" + Field_Eight + "')"; //SqlCommand cmd = new SqlCommand(insertstr);
try
{
a.execsql(insertstr);
//cmd.ExecuteNonQuery();
}
catch (MembershipCreateUserException ex) //捕捉异常
{
Response.Write("<script>alert('导入内容:" + ex.Message + "')</script>");
}
}
Page.ClientScript.RegisterStartupScript(this.GetType(), "message", "alert('导入成功!')", true);
}
a.close();
public DataSet ExecleDs(string filenameurl, string table)
{
string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filenameurl + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
DataSet ds = new DataSet();
OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$]", conn);
odda.Fill(ds, table);
return ds;
conn.Close();
}
然后datatable写入数据库,无论什么数据库
可我不知道该怎么写代码啊!
{
string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filenameurl + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
DataSet ds = new DataSet();
OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$]", conn);
odda.Fill(ds, table);
conn.Close();
return ds; }
protected void Button1_Click(object sender, EventArgs e)//上传按钮
{
if (FileUpload1.HasFile == false || FileUpload2.HasFile == false)//HasFile用来检查FileUpload是否有指定文件
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "message", "alert('请选择EXCEL文件!')", true);
return;//当无文件时,返回
}
string IsXls = System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();//System.IO.Path.GetExtension获得文件的扩展名
string IsXls2 = System.IO.Path.GetExtension(FileUpload2.FileName).ToString().ToLower();//System.IO.Path.GetExtension获得文件的扩展名
if (IsXls != ".xls" || IsXls2 != ".xls")
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "message", "alert('只可选择EXCEL文件!')", true);
return;//当选择的不是Excel文件时,返回
}
a.open();
string filename = FileUpload1.FileName; //获取Execle文件名 DateTime日期函数
string savePath = Server.MapPath(("~\\upfiles\\") + filename);//Server.MapPath 获得虚拟服务器相对路径
FileUpload1.SaveAs(savePath); //SaveAs 将上传的文件内容保存在服务器上
DataSet ds = ExecleDs(savePath, filename); //调用自定义方法
DataRow[] dr = ds.Tables[0].Select(); //定义一个DataRow数组
int rowsnum = ds.Tables[0].Rows.Count;
if (rowsnum == 0)
{
Response.Write("<script>alert('Excel表为空表,无数据!')</script>"); //当Excel表为空时,对用户进行提示
}
else
{
string Word_Level = DropDownList1.SelectedValue;
string Word_Grade = TextBox1.Text;
for (int i = 0; i < dr.Length; i++)
{
//string Word_Level = dr[i]["Word_Level"].ToString();//日期 excel列名【名称不能变,否则就会出错】
//string Word_Grade = dr[i]["Word_Grade"].ToString();//编号 列名 以下类似
string Field_One = dr[i]["Field_One"].ToString();
string Field_Two = dr[i]["Field_Two"].ToString();
string Field_Three = dr[i]["Field_Three"].ToString();
string Field_Four = dr[i]["Field_Four"].ToString();
string Field_Five = dr[i]["Field_Five"].ToString();
string Field_Six = dr[i]["Field_Six"].ToString();
string Field_Seven = dr[i]["Field_Seven"].ToString();
string Field_Eight = dr[i]["Field_Eight"].ToString(); string insertstr = "insert into Five_Word_Field_Data (Word_Level,Word_Grade,Field_One,Field_Two,Field_Three,Field_Four,Field_Five,Field_Six,Field_Seven,Field_Eight) values ('" + Word_Level + "','" + Word_Grade + "','" + Field_One + "','" + Field_Two + "','" + Field_Three + "','" + Field_Four + "','" + Field_Five + "','" + Field_Six + "','" + Field_Seven + "','" + Field_Eight + "')"; //SqlCommand cmd = new SqlCommand(insertstr);
try
{
a.execsql(insertstr);
//cmd.ExecuteNonQuery();
}
catch (MembershipCreateUserException ex) //捕捉异常
{
Response.Write("<script>alert('导入内容:" + ex.Message + "')</script>");
}
}
Page.ClientScript.RegisterStartupScript(this.GetType(), "message", "alert('导入成功!')", true);
}
a.close(); //上传内容
a.open();
string filename2 = FileUpload2.FileName; //获取Execle文件名 DateTime日期函数
string savePath2 = Server.MapPath(("~\\upfiles\\") + filename2);//Server.MapPath 获得虚拟服务器相对路径
FileUpload2.SaveAs(savePath2); //SaveAs 将上传的文件内容保存在服务器上
DataSet ds2 = ExecleDs(savePath2, filename2); //调用自定义方法
DataRow[] dr2 = ds2.Tables[0].Select(); //定义一个DataRow数组
int rowsnum2 = ds2.Tables[0].Rows.Count;
if (rowsnum2 == 0)
{
Response.Write("<script>alert('Excel表为空表,无数据!')</script>"); //当Excel表为空时,对用户进行提示
}
else
{
string Word_Name = TextBox1.Text;
for (int i = 0; i < dr2.Length; i++)
{
//string Word_Name = dr2[i]["Word_Name"].ToString();//日期 excel列名【名称不能变,否则就会出错】
string Field_One_Word = dr2[i]["Field_One_Word"].ToString();//编号 列名 以下类似
string Field_Two_Word = dr2[i]["Field_Two_Word"].ToString();
string Field_Three_Word = dr2[i]["Field_Three_Word"].ToString();
string Field_Four_Word = dr2[i]["Field_Four_Word"].ToString();
string Field_Five_Word = dr2[i]["Field_Five_Word"].ToString();
string Field_Six_Word = dr2[i]["Field_Six_Word"].ToString();
string Field_Seven_Word = dr2[i]["Field_Seven_Word"].ToString();
string Field_Eight_Word = dr2[i]["Field_Eight_Word"].ToString();
string Field_One_If = dr2[i]["Field_One_If"].ToString();
string Field_Two_If = dr2[i]["Field_Two_If"].ToString();
string Field_Three_If = dr2[i]["Field_Three_If"].ToString();
string Field_Four_If = dr2[i]["Field_Four_If"].ToString();
string Field_Five_If = dr2[i]["Field_Five_If"].ToString();
string Field_Six_If = dr2[i]["Field_Six_If"].ToString();
string Field_Seven_If = dr2[i]["Field_Seven_If"].ToString();
string Field_Eight_If = dr2[i]["Field_Eight_If"].ToString(); string insertstr = "insert into Five_Word_Data (Word_Name,Field_One_Word,Field_Two_Word,Field_Three_Word,Field_Four_Word,Field_Five_Word,Field_Six_Word,Field_Seven_Word,Field_Eight_Word,Field_One_If,Field_Two_If,Field_Three_If,Field_Four_If,Field_Five_If,Field_Six_If,Field_Seven_If,Field_Eight_If) values ('" + Word_Name + "','" + Field_One_Word + "','" + Field_Two_Word + "','" + Field_Three_Word + "','" + Field_Four_Word + "','" + Field_Five_Word + "','" + Field_Six_Word + "','" + Field_Seven_Word + "','" + Field_Eight_Word + "','" + Field_One_If + "','" + Field_Two_If + "','" + Field_Three_If + "','" + Field_Four_If + "','" + Field_Five_If + "','" + Field_Six_If + "','" + Field_Seven_If + "','" + Field_Eight_If + "')"; //SqlCommand cmd = new SqlCommand(insertstr);
try
{
a.execsql(insertstr);
//cmd.ExecuteNonQuery();
}
catch (MembershipCreateUserException ex) //捕捉异常
{
Response.Write("<script>alert('导入内容:" + ex.Message + "')</script>");
}
}
Page.ClientScript.RegisterStartupScript(this.GetType(), "message", "alert('导入成功!')", true);
}
a.close();
}
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using MySql.Data.MySqlClient;
using System.Data.OleDb;
public partial class exceltomysql1 : System.Web.UI.Page
{
// MySqlConnection sqlcon;
// MySqlCommand sqlcom;
// string strConn = "server=localhost;user id=root;password=;database=chenzhen;"; protected void Page_Load(object sender, EventArgs e)
{ }
protected void Button1_Click(object sender, EventArgs e)
{
MySqlConnection conn = new MySqlConnection("server=localhost;user id=root;password=;database=chenzhen;");
if (FileUpload1.HasFile == false)//HasFile用来检查FileUpload是否有指定文件
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "message", "alert('请选择EXCEL文件!')", true);
return;//当无文件时,返回
}
string IsXls = System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();//System.IO.Path.GetExtension获得文件的扩展名 if (IsXls != ".xlsx")
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "message", "alert('只可选择EXCEL文件!')", true);
return;//当选择的不是Excel文件时,返回
}
conn.Open();
string filename = FileUpload1.FileName; //获取Execle文件名 DateTime日期函数
// string savePath = Server.MapPath(("~\\upfiles\\") + filename);//Server.MapPath 获得虚拟服务器相对路径
string savePath = Server.MapPath(("~\\") + filename);//Server.MapPath 获得虚拟服务器相对路径
FileUpload1.SaveAs(savePath); //SaveAs 将上传的文件内容保存在服务器上
DataSet ds = ExecleDs(savePath, filename); //调用自定义方法
DataRow[] dr = ds.Tables[0].Select(); //定义一个DataRow数组
int rowsnum = ds.Tables[0].Rows.Count;
if (rowsnum == 0)
{
Response.Write("<script>alert('Excel表为空表,无数据!')</script>"); //当Excel表为空时,对用户进行提示
}
else
{
for (int i = 0; i < dr.Length; i++)
{
string id = dr[i]["ID"].ToString();
string Word_Level = dr[i]["province"].ToString();//日期 excel列名【名称不能变,否则就会出错】
string Word_Grade = dr[i]["city"].ToString();//编号 列名 以下类似
if (Word_Level=="**")
{
break;
}
else
{
string insertstr = "insert into tb_city(province,city) values('" + Word_Level + "','" + Word_Grade + "')";
MySqlCommand cmd = new MySqlCommand(insertstr, conn);
try
{
cmd.ExecuteNonQuery();
}
catch (MembershipCreateUserException ex) //捕捉异常
{
Response.Write("<script>alert('导入内容:" + ex.Message + "')</script>");
}
}
// conn.Close();
}
} conn.Close();
Response.Write("<script>alert('Excle表导入成功!');</script>");
}
public DataSet ExecleDs(string filenameurl, string table)
{ string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "Data Source=" + filenameurl + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";
// string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "Data Source=" + filenameurl + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
OleDbConnection conn1 = new OleDbConnection(strConn);
// conn.Close();
conn1.Open();
DataSet ds = new DataSet();
OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$]", conn1);
odda.Fill(ds, table);
return ds;
conn1.Close();
}
}