c# excel文件导入到sqlserver中

来源：互联网发布：动态加载js文件并执行编辑：程序博客网时间：2024/06/06 04:55

.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"></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"></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'></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'></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'></mce:script>"); Response.Write(r2 + "条记录被导入"); } if (r1 > 0) { Response.Write("<mce:script language='javascript'></mce:script>"); Response.Write(r1 + "条记录被更新"); } } }}