.aspx 文件 <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="WebApplication1._Default" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"> <title>无标题页</title> </head> <body> <form id="form1" runat="server"> <div style="text-align: center" mce_style="text-align: center"> <table style="width: 395px; height: 84px" border="1"> <tr> <td style="width:380px; height: 23px;" align="center"> 批量导入excel数据</td> </tr> <tr> <td style="width: 380px; height: 26px;"> <asp:FileUpload ID="FileUpload1" runat="server" Width="380px" /></td> </tr> <tr> <td style="width:380px; height: 28px;" align="center"> <asp:Button ID="Button1" runat="server" Text="导入" OnClick="Button1_Click" Width="55px" /></td> </tr> </table> <asp:Label ID="Label1" runat="server" Width="466px"></asp:Label></div> </form> </body> cs 文件 using System; using System.Data; using System.Data.SqlClient; using System.Data.OleDb; 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.IO; namespace WebApplication1 { public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } public DataSet ReadExcel(string strPath)//从EXCEL文件中取数据到数据集DataSet中 { //查询语句 string strExcel = "select * from [sheet1$]"; OleDbDataAdapter objCommand = null; DataSet objDs = null; //连接 string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strPath + ";" + "Extended Properties=Excel 8.0;"; try { //创建连接对象 OleDbConnection objConn = new OleDbConnection(strConn); //打开连接 objConn.Open(); //创建读取对象 objCommand = new OleDbDataAdapter(strExcel, strConn); //创建数据集 objDs = new DataSet(); //添充数据集 objCommand.Fill(objDs, "table1"); objConn.Close(); } catch (Exception objex) { //清空数据集合 objDs = null; //写异常 Response.Write("出错,请检查数据!" + objex.Message); } return objDs; } protected void Button1_Click(object sender, EventArgs e) { string Mapname = ""; if (FileUpload1.HasFile == false) { Response.Write("<mce:script type="text/javascript"><!-- alert('请您选择Excel文件') // --></mce:script> "); return;//当无文件时,返回 } string IsXls = System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();//文件名变为字符串变为小写,判断上传文件的类型system.IO.path.getextention() if (IsXls != ".xls") { Response.Write("<mce:script type="text/javascript"><!-- alert('只可以选择Excel文件') // --></mce:script>"); return;//当选择的不是Excel文件时,返回 } string UpFilePath = "exceldata"; string[] up = FileUpload1.PostedFile.FileName.Split(new char[] { '//' }); string p = MapPath("") + "//" + UpFilePath + "//" + up[up.Length - 1]; if (File.Exists(p)) { Response.Write("<mce:script language='javascript'><!-- alert('该文件名已经存在!'); // --></mce:script>"); } else { int FileSize = FileUpload1.PostedFile.ContentLength; byte[] arrFile = new byte[FileSize]; if (FileSize > 0) { string[] Date = DateTime.Now.ToString().Split(' '); string[] name1 = Date[0].Split('-'); string[] name2 = Date[1].Split(':'); for (int i = 0; i < 3; i++) { Mapname += name1[i] + name2[i]; } string FileName; string dType; string FilePath; Mapname = Mapname + up[up.Length-1]; FileName = up[up.Length-1]; string[] getdoctype = FileName.Split('.'); dType = getdoctype[1].ToString(); FilePath = "//" + UpFilePath + "//" + Mapname; Stream StreamReader = FileUpload1.PostedFile.InputStream; StreamReader.Read(arrFile, 0, FileSize); this.FileUpload1.PostedFile.SaveAs(MapPath("") + "//" + UpFilePath + "//" + Mapname); } else { Response.Write("<mce:script language='javascript'><!-- alert('批量导入失败!'); // --></mce:script>"); return; } } string strpath; strpath = MapPath("") + "//" + UpFilePath + "//" + Mapname; DataSet data = ReadExcel(strpath); DataTable dt = data.Tables["table1"]; int r1 = 0; int r2 = 0; for (int i = 0; i < dt.Rows.Count; i++) { string ResponseSQL = ""; string ch = "'" + dt.Rows[i][0].ToString() + "'"; string fzb = "'" + dt.Rows[i][1].ToString() + "'"; string caf = "'" + dt.Rows[i][2].ToString() + "'"; string mgf = "'" + dt.Rows[i][3].ToString() + "'"; //如何判断记录是否已经存在,如果存在则更新数据 ResponseSQL = string.Format("select * from person where id={0}", ch);//sql查询 判断有无重复记录,重复记录则更新! string connString; connString = "server=.;initial catalog=test;user id=sa;password="; SqlConnection conn = new SqlConnection(connString); conn.Open(); SqlCommand cmd = new SqlCommand(ResponseSQL, conn); cmd.CommandType = CommandType.Text; cmd.CommandText = ResponseSQL; SqlDataReader itemexist = cmd.ExecuteReader();//executenoquery()返回受影响的行数 if (itemexist.HasRows) { itemexist.Close(); string ResponseSQL2 = string.Format("update person set id={0},name={1},age={2} where id={0}", ch,fzb, caf, mgf);//sql语句插入 SqlCommand cmd2 = new SqlCommand(ResponseSQL2, conn); r1 = cmd2.ExecuteNonQuery() + r1; cmd.Dispose(); cmd2.Dispose(); } else { itemexist.Close(); string ResponseSQL1 = string.Format("insert into person(id,name,age,sex) values({0},{1},{2},{3})", ch, fzb, caf, mgf);//sql语句插入 SqlCommand cmd1 = new SqlCommand(ResponseSQL1, conn); cmd1.CommandType = CommandType.Text; // Response.Write(ResponseSQL1); cmd1.CommandText = ResponseSQL1; r2 = cmd1.ExecuteNonQuery() + r2; // Response.Write(r2); cmd.Dispose(); cmd1.Dispose(); conn.Close(); } } if (r2 > 0) { Response.Write("<mce:script language='javascript'><!-- alert('Excel文件导入数据库成功!'); // --></mce:script>"); Response.Write(r2 + "条记录被导入"); } if (r1 > 0) { Response.Write("<mce:script language='javascript'><!-- alert('Excel文件更新数据库成功!'); // --></mce:script>"); Response.Write(r1 + "条记录被更新"); } } } }