asp.net如何把excel导入到sql 谁知道,最好是实例,测试过的更好,谢谢分享; 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 http://www.92dotnet.com/showtopic-428.aspxusing System;using System.Data;using System.Configuration;using System.Collections;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 System.Data.SqlClient;using System.Data.OleDb;using System.IO;public partial class Excel : System.Web.UI.Page{ protected void Page_Load(object sender, EventArgs e) { } /// <summary> /// 把Excel中的信息导入到数据库的表中 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void ButASPNET_Click(object sender, EventArgs e) { if (this.ExcelFile.PostedFile != null) { SqlConnection conn = new SqlConnection("server=.;database=ExcelDB;uid=sa;pwd=123456"); conn.Open(); try { string fileurl = typename(ExcelFile);//调用typename方法取得excel文件路径 DataSet ds = new DataSet();//取得数据集 ds = xsldata(fileurl); int errorcount = 0;//记录错误信息条数 int insertcount = 0;//记录插入成功条数 int updatecount = 0;//记录更新信息条数 for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { //string StuID = ds.Tables[0].Rows[i][0].ToString(); string StuName = ds.Tables[0].Rows[i][1].ToString(); string StuSex = ds.Tables[0].Rows[i][2].ToString(); string StuAge = ds.Tables[0].Rows[i][3].ToString(); string StuAddress = ds.Tables[0].Rows[i][4].ToString(); if (StuName != "" && StuSex != "" && StuAge != "" && StuAddress != "") { SqlCommand selectcmd = new SqlCommand("select count(*) from StudentInfo where StuName='" + StuName + "'", conn); int count = Convert.ToInt32(selectcmd.ExecuteScalar()); if (count > 0) { SqlCommand selectcmd2 = new SqlCommand("select count(*) from StudentInfo where StuName='" + StuName + "'", conn); int count2 = Convert.ToInt32(selectcmd2.ExecuteScalar()); if (count2 > 0) { SqlCommand updatecmd = new SqlCommand("update StudentInfo set StuSex='" + StuSex + "',StuAge='" + StuAge + "',StuAddress='" + StuAddress + "' where stuid='" + StuID + "' and StuName='" + StuName + "'", conn); updatecmd.ExecuteNonQuery(); updatecount++; } else { SqlCommand insertcmd = new SqlCommand("insert into StudentInfo values('" + StuName + "','" + StuSex + "','" + StuAge + "','" + StuAddress + "')", conn); insertcmd.ExecuteNonQuery(); insertcount++; } } else { Response.Write("<script language='javascript'>alert('数据信息有错误,导入失败,请检查!!!');</script>"); break; } } else { errorcount++; } } Response.Write("<script language='javascript'>alert('" + insertcount + "条数据导入成功!" + updatecount + "条数据更新成功!" + errorcount + "条数据部分信息为空没有导入!');</script>"); } catch { Response.Write("<script language='javascript'>alert('导入失败!!!');</script>"); } finally { conn.Close(); } } } /// <summary> /// 把EXCEL文件上传到服务器并返回文件路径 /// </summary> /// <param name="fileloads"></param> /// <returns></returns> private String typename(FileUpload fileloads) { string fullfilename = this.ExcelFile.PostedFile.FileName; string filename = fullfilename.Substring(fullfilename.LastIndexOf("\\") + 1); string type = fullfilename.Substring(fullfilename.LastIndexOf(".") + 1); string murl = ""; if (type == "xls") { /* * 以下的代码用于保存上传文件的路径的: * this.ExcelFile.PostedFile.SaveAs(Server.MapPath("ExcelFiles") + "\\" + filename); * murl = (Server.MapPath("ExcelFiles") + "\\" + filename).ToString(); */ murl = fullfilename; } else { Response.Write("<script language='javascript'>alert('导入文件格式不对!');</script>"); } return murl; } /// <summary> /// 把EXCLE数据读入dataset返回l数据集 /// </summary> /// <param name="filepath"></param> /// <returns></returns> private DataSet xsldata(string filepath) { string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;IMEX=1'"; System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(strCon); string filename = filepath.Substring(filepath.LastIndexOf("\\") + 1); string SheetName = filename.Substring(0, filename.LastIndexOf(".")); string strCom = "SELECT * FROM [Sheet1$]"; Conn.Open(); System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, Conn); DataSet ds = new DataSet(); myCommand.Fill(ds, "[SheetName$]"); Conn.Close(); return ds; }}参考:这里 参考:http://www.92dotnet.com/showtopic-428.aspx支持Excel 2007版本string strCon = null;if (Path.GetExtension(filepath) == ".xlsx"){ strCon = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\"";} if (Path.GetExtension(filepath) == ".xls"){ strCon = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";} 1、在typename方法里把if (type == "xls")改为if (type == "xls"||type == "xlsx"))2、在xsldata方法里把string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;IMEX=1'";换成我刚传上的代码即可。这样导入的时候2003和2007两个版本都可支持了!!! http://blog.csdn.net/chenjianyong94/archive/2009/09/21/4576133.aspx chart控件画饼图 数据库被插入恶意代码 《醒目》Visual Studio 2008 测试功能谁人知?谁人晓? .net2003能安装Ajax吗?那位大姐大哥能给个网址! 高人帮忙看下,菜鸟急呼! 有关DATALIST问题 关于网页间传输数据的若干问题 关于asp.net的数据库连接问题 有经验的请进,数据库设计中冗余字段的问题 为什么我不能使用dataset.Tables[0].Rows[0].ItemArray[2]得到数据啦?记得以前可以使用ItemArray[]的! 简单问题。。 安装程序无法复制一个或多个文件,特定错误代码是0x5
using System;
using System.Data;
using System.Configuration;
using System.Collections;
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 System.Data.SqlClient;
using System.Data.OleDb;
using System.IO;
public partial class Excel : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{ } /// <summary>
/// 把Excel中的信息导入到数据库的表中
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void ButASPNET_Click(object sender, EventArgs e)
{
if (this.ExcelFile.PostedFile != null)
{
SqlConnection conn = new SqlConnection("server=.;database=ExcelDB;uid=sa;pwd=123456");
conn.Open();
try
{
string fileurl = typename(ExcelFile);//调用typename方法取得excel文件路径
DataSet ds = new DataSet();//取得数据集
ds = xsldata(fileurl);
int errorcount = 0;//记录错误信息条数
int insertcount = 0;//记录插入成功条数
int updatecount = 0;//记录更新信息条数
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
//string StuID = ds.Tables[0].Rows[i][0].ToString();
string StuName = ds.Tables[0].Rows[i][1].ToString();
string StuSex = ds.Tables[0].Rows[i][2].ToString();
string StuAge = ds.Tables[0].Rows[i][3].ToString();
string StuAddress = ds.Tables[0].Rows[i][4].ToString();
if (StuName != "" && StuSex != "" && StuAge != "" && StuAddress != "")
{
SqlCommand selectcmd = new SqlCommand("select count(*) from StudentInfo where StuName='" + StuName + "'", conn);
int count = Convert.ToInt32(selectcmd.ExecuteScalar());
if (count > 0)
{
SqlCommand selectcmd2 = new SqlCommand("select count(*) from StudentInfo where StuName='" + StuName + "'", conn);
int count2 = Convert.ToInt32(selectcmd2.ExecuteScalar());
if (count2 > 0)
{
SqlCommand updatecmd = new SqlCommand("update StudentInfo set StuSex='" + StuSex + "',StuAge='" + StuAge + "',StuAddress='" + StuAddress + "' where stuid='" + StuID + "' and StuName='" + StuName + "'", conn);
updatecmd.ExecuteNonQuery();
updatecount++;
}
else
{ SqlCommand insertcmd = new SqlCommand("insert into StudentInfo values('" + StuName + "','" + StuSex + "','" + StuAge + "','" + StuAddress + "')", conn);
insertcmd.ExecuteNonQuery();
insertcount++;
}
}
else
{
Response.Write("<script language='javascript'>alert('数据信息有错误,导入失败,请检查!!!');</script>");
break; }
}
else
{
errorcount++;
}
}
Response.Write("<script language='javascript'>alert('" + insertcount + "条数据导入成功!" + updatecount + "条数据更新成功!" + errorcount + "条数据部分信息为空没有导入!');</script>");
}
catch
{
Response.Write("<script language='javascript'>alert('导入失败!!!');</script>");
}
finally
{
conn.Close();
} } }
/// <summary>
/// 把EXCEL文件上传到服务器并返回文件路径
/// </summary>
/// <param name="fileloads"></param>
/// <returns></returns>
private String typename(FileUpload fileloads)
{
string fullfilename = this.ExcelFile.PostedFile.FileName;
string filename = fullfilename.Substring(fullfilename.LastIndexOf("\\") + 1);
string type = fullfilename.Substring(fullfilename.LastIndexOf(".") + 1);
string murl = "";
if (type == "xls")
{
/*
* 以下的代码用于保存上传文件的路径的:
* this.ExcelFile.PostedFile.SaveAs(Server.MapPath("ExcelFiles") + "\\" + filename);
* murl = (Server.MapPath("ExcelFiles") + "\\" + filename).ToString();
*/
murl = fullfilename;
}
else
{
Response.Write("<script language='javascript'>alert('导入文件格式不对!');</script>"); }
return murl;
}
/// <summary>
/// 把EXCLE数据读入dataset返回l数据集
/// </summary>
/// <param name="filepath"></param>
/// <returns></returns>
private DataSet xsldata(string filepath)
{
string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;IMEX=1'";
System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(strCon);
string filename = filepath.Substring(filepath.LastIndexOf("\\") + 1);
string SheetName = filename.Substring(0, filename.LastIndexOf("."));
string strCom = "SELECT * FROM [Sheet1$]";
Conn.Open();
System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, Conn);
DataSet ds = new DataSet();
myCommand.Fill(ds, "[SheetName$]");
Conn.Close();
return ds;
}}
参考:这里
string strCon = null;
if (Path.GetExtension(filepath) == ".xlsx")
{
strCon = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\"";
}
if (Path.GetExtension(filepath) == ".xls")
{
strCon = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";} 1、在typename方法里把if (type == "xls")改为if (type == "xls"||type == "xlsx"))
2、在xsldata方法里把string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;IMEX=1'";换成我刚传上的代码即可。这样导入的时候2003和2007两个版本都可支持了!!!