asp.net中Excel导入sql server数据库

来源：互联网发布：电动机绕组数据技术编辑：程序博客网时间：2024/04/29 03:01

**.aspx页面：

代码：

------------------------------------------------------------------------------------------------------------

**.aspx.cs页面

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.IO;
using System.Diagnostics;
using System.Configuration;

namespace Web.Admin.StudentInfo
{
    public partial class importInfo : System.Web.UI.Page
    {
        string strConn = ConfigurationManager.ConnectionStrings["StuHelpConnectionString"].ConnectionString;
        //string strConn = "Data Source=ying;DataBase=StuHelp;Uid=sa;Pwd=123"; //链接SQL数据库
        protected void Page_Load(object sender, EventArgs e)
        {
            SqlConnection cn = new SqlConnection(strConn);
            cn.Open();
            //SqlDataAdapter sda = new SqlDataAdapter("select * from class", cn);
            //DataSet ds = new DataSet();
            //sda.Fill(ds);
            //this.GridView1.DataSource = ds.Tables[0].DefaultView;
            ////this.GridView1.DataKeyNames = new string[] { "id" };
            //this.GridView1.DataBind();
        }

        /// <summary>
        /// 查询EXCEL电子表格添加到DATASET
        /// </summary>

        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)//HasFile用来检查FileUpload是否有指定文件
            {
                Response.Write("<script>alert('请您选择Excel文件')</script> ");
                return;//当无文件时,返回
            }
            string IsXls = System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();//System.IO.Path.GetExtension获得文件的扩展名

            if (IsXls != ".xls")
            {
                Response.Write("<script>alert('只可以选择Excel文件')</script>");
                return;//当选择的不是Excel文件时,返回
            }
            SqlConnection cn = new SqlConnection(strConn);
            cn.Open();
            string filename = DateTime.Now.ToString("yyyymmddhhMMss") + FileUpload1.FileName;              //获取Execle文件名 DateTime日期函数

string savePath = Server.MapPath(("..\\StudentInfo\\getinfile\\") + filename);//Server.MapPath 获得虚拟服务器相对路径

FileUpload1.SaveAs(savePath); //SaveAs 将上传的文件内容保存在服务器上
//string savePath = @"D:\Users\Administrator.WIN-PFRMUGTM302\Desktop\新建 Microsoft Office Excel 工作表.xls";//Server.MapPath 获得虚拟服务器相对路径

            DataSet ds = ExecleDs(savePath, "StudentInfo");           //调用自定义方法
            File.Delete(savePath);
            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++)
                {
                   // int cardid = int.Parse(dr[i]["编号"].ToString());//日期 excel列名【名称不能变,否则就会出错】
                    string cardid = dr[i]["身份证号"].ToString();//编号列名以下类似
                    string stuname = dr[i]["姓名"].ToString();//编号列名以下类似
                    int depid = GetDepID(dr[i]["系部编号"].ToString());//日期 excel列
                    int proid = GetProID(dr[i]["专业编号"].ToString());//日期 excel列
                    int claid = GetClaID(dr[i]["班级编号"].ToString());//日期 excel列
                    DateTime stubirthday = DateTime.Parse(dr[i]["出生日期"].ToString());//日期 excel列
                    char stusex = char.Parse(dr[i]["性别"].ToString());//编号列名以下类似
                    string stunation = dr[i]["民族"].ToString();//编号列名以下类似
                    string stutype = dr[i]["考生类别"].ToString();//编号列名以下类似
                    string stunumber = dr[i]["考生号"].ToString();//编号列名以下类似
                    string stutestnum = dr[i]["学号"].ToString();//编号列名以下类似
                    string financeid = dr[i]["财务编号"].ToString();//编号列名以下类似
                    string politicsstatus = dr[i]["政治面貌"].ToString();//编号列名以下类似
                    string homeplace = dr[i]["家庭住址"].ToString();//编号列名以下类似
                    string scholldisplay = dr[i]["在校表现"].ToString();//编号列名以下类似

                    string sqlcheck = "select count(*) from studentinfo where CardID='" + cardid + "'"; //检查身份证号是否重复
                    SqlCommand sqlcmd = new SqlCommand(sqlcheck, cn);
                    int count = Convert.ToInt32(sqlcmd.ExecuteScalar());
                    if (count < 1)
                    {
                    string insertstr = "insert into StudentInfo(cardid,stuname,depid,proid,claid,stubirthday,stusex,stunation,stutype,stunumber,stutestnum,financeid,politicsstatus,homeplace,schooldisplay) values('" + cardid + "','" + stuname +"',"+depid+","+proid+","+claid+",'"+stubirthday+"','"+stusex+"','"+stunation+"','"+stutype+"','"+stunumber+"','"+stutestnum+"','"+financeid+"','"+politicsstatus+"','"+homeplace+"','"+scholldisplay+ "')";
                    //string insertstr = "insert into StudentInfo(cardid) values('" + cardid + "')";
                    SqlCommand cmd = new SqlCommand(insertstr, cn);
                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    //catch (MembershipCreateUserException ex)       //捕捉异常
                    catch (ExecutionEngineException ex)       //捕捉异常
                    {
                        Response.Write("<script>alert('导入内容:" + ex.Message + "')</script>");
                    }
                    }
                    else
                    {
                        Response.Write("<script>alert('有内容重复！请跳过继续导入！');</script> ");
                       // Response.Write("<script> if (confirm('错误：有记录重复。是否跳过？') == true) {alert('我是我是确定');}else {alert('我是取消');}</script> ");
                        continue;
                    }

                }
                Response.Write("<script>alert('Excle表导入成功!');location='list.aspx';</script>");
            }
        }

#region 把对应字段根据相关表改变类型值

        public int GetDepID(string name)//根据系部名称得到系部编号
        {
            DAL.StuDepartment dll = new DAL.StuDepartment();
            return dll.GetDepartmentID(name);
        }

        public int GetProID(string name)//根据专业名称得到专业编号
        {
            DAL.StuProfession dll = new DAL.StuProfession();
            return dll.GetProfessionID(name);
        }

        public int GetClaID(string name)//根据班级名称得到班级编号
        {
            DAL.StuClass dll = new DAL.StuClass();
            return dll.GetClassID(name);
        }

#endregion
}
}

如果有问题欢迎提问!