使用SqlBulkCopy类实现导入excel表格-白红宇

使用SqlBulkCopy类实现导入excel表格

阅读量：7219 次

发布时间：2019-06-29

本文共 15443 字，大约阅读时间需要 51 分钟。

前言：

介绍了SqlBulkCopy类批量操作数据库的相关操作，最后提到了可以使用这个类实现excel文件导入数据库，接下来我做简单介绍。

首先说一下思路：

把excel中的数据读出来并放入到DataTable中。

使用SqlBulkCopy类的方法批量导入数据库。

虽然只用简单的两部。但是我们需要考虑的问题还是用很多的，其中很重要的一点就是判断需要导入即excel中的数据是否合法，包括：是不是为空，是不是太长，是不是有重复，导入的字段是否对应。看起来挺吓人的，其实思路还是最重要的。只要敢想什么都能实现。

下面我简单的写一个小例子来说明一下。

多余的不说，直接到点击导入按钮：

protected void btnImportTeacher_Click(object sender, EventArgs e)        {            //成员业务逻辑层            MemberInfoBLL  memberInfo = new MemberInfoBLL() ;            //BLL层把Excel转化为datatable类            CreateExcelDataBLL  createExcelData = new CreateExcelDataBLL();            //将错误信息导出到Excel                       DataTableToExcel dataTableToExcel = new DataTableToExcel();            /*-------------------------------------准备Excel文件--------------------------------------*/            //获取上传文件地址            string url = fupImportTeacher.PostedFile.FileName.ToString();            if (url == "")            {                //数据源为空，弹出提示：请选择Excel文件！                Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "");                return;            }            string urlLocation = url.Substring(url.LastIndexOf("\\") + 1);//获取文件名            DataTable dtAllMember;            //在系统中建文件夹up,并将excel文件另存            this.fupImportTeacher.SaveAs(Server.MapPath("~\\UploadFile") + "\\" + urlLocation);//记录文件名到服务器相对应的文件夹中            //获得文件路径            string strpath = Server.MapPath("~\\UploadFile") + "\\" + urlLocation;            //把上传的Excel转换为datatable            dtAllMember = createExcelData.CreateExcelDataSource(strpath);

以上代码涉及到的类包括CreateExcelDataBLL 、DataTableToExcel

/******************************************************************************* *文    件：CreateExcelDataBLL.cs *作    者：韩义 *所属小组：图书馆维修管理系统 *文件说明：基础系统-把excel转化为datatable *创建日期：2013年1月23日9:43:16 *修改作者： *修改日期： *修改描述： *版 本 号：V1.0 *版本号变更记录：     ********************************************************************************/using System;using System.Collections.Generic;using System.Linq;using System.Text;//引用各命名空间using System.Data;using System.Data.OleDb;using System.Data.SqlClient;using System.IO;namespace BLL{    public class CreateExcelDataBLL    {        //构造方法        public CreateExcelDataBLL()        {                   }        ///         /// 传入excel路径，转换为datatable        /// 
        ///         /// 
            public DataTable CreateExcelDataSource(string url)        {            //定义一个DataTable数据表            DataTable dt = null;            //获得excel数据            string connetionStr = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + url + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";            //从Excel表的Sheet1单元格获取数据            string strSql = "select * from [Sheet1$]";            OleDbConnection oleConn = new OleDbConnection(connetionStr);            OleDbDataAdapter oleAdapter = new OleDbDataAdapter(strSql, connetionStr);            try            {                //把Excel数据填充给DataTable                dt = new DataTable();                oleAdapter.Fill(dt);                //返回数据表                return removeEmpty(dt);            }            catch (Exception ex)            {                throw ex;            }            finally            {                oleAdapter.Dispose();                oleConn.Close();                oleConn.Dispose();                //删除上传的Excel文件（因为该文件的存在会占用多余的网站空间）                if (File.Exists(url))                {                    File.Delete(url);                }            }        }        ///         /// 循环去除datatable中的空行        /// 
        ///         protected DataTable removeEmpty(DataTable dt)        {            List
    
      removelist = new List
     
      ();            for (int i = 0; i < dt.Rows.Count; i++)            {                bool rowdataisnull = true;                for (int j = 0; j < dt.Columns.Count; j++)                {                    if (!string.IsNullOrEmpty(dt.Rows[i][j].ToString().Trim()))                    {                        rowdataisnull = false;                    }                }                if (rowdataisnull)                {                    removelist.Add(dt.Rows[i]);                }            }            for (int i = 0; i < removelist.Count; i++)            {                dt.Rows.Remove(removelist[i]);            }            return dt;         }    }}

/******************************************************************************* *文    件：DataTableToExcel.cs *作    者：韩义 *所属小组：图书馆维修管理系统 *文件说明：基础系统-把datatable转化为excel *创建日期：2013年1月23日10:43:16 *修改作者： *修改日期： *修改描述： *版 本 号：V1.0 *版本号变更记录：     ********************************************************************************/using System;using System.Collections.Generic;using System.Linq;using System.Text;//导入命名空间using System.Data;using System.Web;using System.Web.UI;using System.Web.UI.WebControls;using System.IO;namespace BLL{    public class DataTableToExcel    {        ///         /// 导出Excel        /// 
        ///         ///         public void ToExcel(DataTable dt)        {            DataGrid dgExcel = new DataGrid();            dgExcel.DataSource = dt;            dgExcel.DataBind();            HttpContext.Current.Response.Charset = "GB2312";            string fileName = HttpUtility.UrlEncode(Guid.NewGuid().ToString(), System.Text.Encoding.UTF8);            string str = "attachment;filename=" + fileName + ".xls";            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;            HttpContext.Current.Response.ContentType = "application/ms-excel";            HttpContext.Current.Response.AppendHeader("content-disposition", str);            StringWriter sw = new StringWriter();            HtmlTextWriter htmTextWriter = new HtmlTextWriter(sw);            dgExcel.RenderControl(htmTextWriter);            HttpContext.Current.Response.Write("
    ");            string style = "";//防止导出excel时将以0开头的全数字数据的0去掉            HttpContext.Current.Response.Write(style);            HttpContext.Current.Response.Write("");            HttpContext.Current.Response.Write(sw);            HttpContext.Current.Response.Write("");            HttpContext.Current.Response.End();        }    }}

两个类的作用我想通过上面的解释大家都应该明白他们的作用了。

验证excel中数据的有效性：

下面介绍如何验证excel中数据的有效性。

/*-------------------------------------判断数据源是否合法--------------------------------------*/            //定义要求的字段数据            string[] headfields = { "成员姓名","学号","身份等级","小组名称","密码","性别","年龄","手机号","QQ号","邮箱","备注"};            //判断dtAllTeacher中是否包含全部要求的字段            for (int i = 0; i < headfields.Length; i++)            {                //只要有一个字段不被包含，则提示"数据源缺少必要的字段"，并退出循环和整个方法                if (!dtAllMember.Columns.Contains(headfields[i]))                {                    Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "");                    //退出方法                    return;                }            }            //判断数据源中是否有数据            if (dtAllMember.Rows.Count == 0)            {                Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "");                //退出方法                return;            }            //判断是否有相同学号的行            DataView dvTeacher = new DataView(dtAllMember);            if (dvTeacher.Count != dvTeacher.ToTable(true, "学号").Rows.Count)            {                Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "");                //退出方法                return;            }            DataSet dsMember = new DataSet("dt_Member"); //创建一个名为dt_Member的DataSet            //手动创建的新数据表-学生数据表            DataTable dtAddMember = new DataTable("dt_AddMember"); //创建一个名为dt_AddMember的DataTalbe            //为dt_AddMember表内建立Column（表头）,添加数据列:            dtAddMember.Columns.Add(new DataColumn("memberName", typeof(string)));            dtAddMember.Columns.Add(new DataColumn("memberStudentID", typeof(string)));            dtAddMember.Columns.Add(new DataColumn("level", typeof(string)));            dtAddMember.Columns.Add(new DataColumn("groupID", typeof(string)));            dtAddMember.Columns.Add(new DataColumn("memberPW", typeof(string)));            dtAddMember.Columns.Add(new DataColumn("sex", typeof(string)));            dtAddMember.Columns.Add(new DataColumn("age", typeof(string)));            dtAddMember.Columns.Add(new DataColumn("phone", typeof(string)));            dtAddMember.Columns.Add(new DataColumn("qq", typeof(string)));            dtAddMember.Columns.Add(new DataColumn("email", typeof(string)));            dtAddMember.Columns.Add(new DataColumn("explian", typeof(string)));                                 //新建数据表用于存放错误数据            DataTable dtErrorRow = new DataTable();            //为dtErrorRow创建列            dtErrorRow.Columns.Add(new DataColumn("成员姓名", typeof(string)));            dtErrorRow.Columns.Add(new DataColumn("学号", typeof(string)));            dtErrorRow.Columns.Add(new DataColumn("身份等级", typeof(string)));            dtErrorRow.Columns.Add(new DataColumn("小组名称", typeof(string)));            dtErrorRow.Columns.Add(new DataColumn("密码", typeof(string)));            dtErrorRow.Columns.Add(new DataColumn("性别", typeof(string)));            dtErrorRow.Columns.Add(new DataColumn("年龄", typeof(string)));            dtErrorRow.Columns.Add(new DataColumn("手机号", typeof(string)));            dtErrorRow.Columns.Add(new DataColumn("QQ号", typeof(string)));            dtErrorRow.Columns.Add(new DataColumn("邮箱", typeof(string)));            dtErrorRow.Columns.Add(new DataColumn("备注", typeof(string)));                        dtErrorRow.Columns.Add(new DataColumn("错误原因", typeof(string)));            //从上传的Excel转换为的datatable表中取出数据，放入成员信息。            for (int intRow = 0; intRow < dtAllMember.Rows.Count; intRow++)            {                                        //成员姓名                strMemberName = dtAllMember.Rows[intRow]["成员姓名"].ToString();                //成员姓名                memberStudentID = dtAllMember.Rows[intRow]["学号"].ToString();                strLevel = dtAllMember.Rows[intRow]["身份等级"].ToString();                strGroupName = dtAllMember.Rows[intRow]["小组名称"].ToString();                                strmemberPW = dtAllMember.Rows[intRow]["密码"].ToString();                strSex = dtAllMember.Rows[intRow]["性别"].ToString();                strAge = dtAllMember.Rows[intRow]["年龄"].ToString();                strPhone = dtAllMember.Rows[intRow]["手机号"].ToString();                strQQ = dtAllMember.Rows[intRow]["QQ号"].ToString();                                strEmail = dtAllMember.Rows[intRow]["邮箱"].ToString();                strExplian=dtAllMember.Rows[intRow]["备注"].ToString();                                             //根据小组名称判断，小组是否存在                Boolean  bFlag = false;                //判断输入的组名是否存在                bFlag = new BLL.GroupInfoBLL().ExistsByName(strGroupName);                                               // ID                if (bFlag ==true )                {                    //小组ID为--韩义                    strGroupID = new BLL.GroupInfoBLL().GetModelByGroupName(strGroupName).groupID;                }                //否则将当前行添加到错误列表并跳出当前循环                else                {                                        //向错误列表中添加当前行                    AddErrorRow(dtErrorRow, "指定的组名称不存在");                    //跳出当前循环                    continue;                }                                             //判断成员ID是否存在                bFlag = false;//定义标识变量                //判断学号是否存在                bFlag = new BLL.MemberInfoBLL().Exists(memberStudentID);                //如果指定的成员代码已存在则将当前行添加到错误列表，并跳出当前循环                if (bFlag==true )                {                    //向错误列表中添加当前行                    AddErrorRow(dtErrorRow, "指定的成员已存在");                    //跳出当前循环                    continue;                }                //判断性别类型是否正确                if (strSex != "男" && strSex != "女")                {                    //向错误列表中添加当前行                    AddErrorRow(dtErrorRow, "成员性别错误");                    //跳出当前循环                    continue;                }                //判断身份等级是否正确                if (strLevel !="组员")                {                    //向错误列表中添加当前行                    AddErrorRow(dtErrorRow, "导入成员身份级别只能为“组员”，组长请在系统中指定");                    //跳出当前循环                    continue;                }                //判断是否包含密码信息                if (strmemberPW =="")                {                    //向错误列表中添加当前行                    AddErrorRow(dtErrorRow, "成员密码不能为空");                    //跳出当前循环                    continue;                }                //判断是否包含电话信息                if (strPhone =="")                {                    //向错误列表中添加当前行                    AddErrorRow(dtErrorRow, "成员手机号不能为空");                    //跳出当前循环                    continue;                }                //判断是否包含邮箱信息                if (strEmail =="")                {                     //向错误列表中添加当前行                    AddErrorRow(dtErrorRow, "成员邮箱不能为空");                    //跳出当前循环                    continue;                }                //判断是否包含学号信息                if (memberStudentID == "")                {                    //向错误列表中添加当前行                    AddErrorRow(dtErrorRow, "成员学号不能为空");                    //跳出当前循环                    continue;                }                //判断是否包含姓名信息                if (strMemberName  == "")                {                    //向错误列表中添加当前行                    AddErrorRow(dtErrorRow, "成员姓名不能为空");                    //跳出当前循环                    continue;                }                //添加成员信息表的新行                DataRow drAddMember = dtAddMember.NewRow();//注意这边创建dt的新行的方法。指定类型是DataRow而不是TableRow，然后不用new直接的用创建的DataTable下面的NewRow方法。                //学生信息表对应的各列值                drAddMember["memberName"] = strMemberName;                drAddMember["memberStudentID"] = memberStudentID;                drAddMember["level"] = strLevel;                drAddMember["groupID"] = strGroupID;                drAddMember["memberPW"] = strmemberPW;                drAddMember["sex"] = strSex;                drAddMember["age"] = strAge;                drAddMember["phone"] = strPhone;                drAddMember["qq"] = strQQ;                drAddMember["email"] = strEmail;                drAddMember["explian"] = strExplian;                               dtAddMember.Rows.Add(drAddMember);  //将一整条数据写入表中                         }            //将表加入DataSet中:成员信息            dsMember.Tables.Add(dtAddMember);            //将DataSet中数据表导入数据库            Boolean flagImportMember = memberInfo.ImportMember(dsMember);            //判断成员是否导入成功            if (true == flagImportMember && dtErrorRow.Rows.Count == 0)            {                //导入成功，弹出提示                Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "");            }            else if (false == flagImportMember)            {                //导入失败，弹出提示                Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "");            }            else if (true == flagImportMember && dtErrorRow.Rows.Count != 0)            {                //只有部分数据导入成功，弹出提示                Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "");                //将错误数据导出到Excel并打开显示                dataTableToExcel.ToExcel(dtErrorRow);            }        }

说明：以上代码中建立了两个表一个用于导入：字段改为与数据库对应，另一个表用于导出：字段改为汉字说明。其中memberInfo.ImportMember方法就是实现数据库的批量导入，这在上一篇博客中已经有介绍了这里就不再累述了。

总结：

原来感觉挺难的东西，现在都已经完整的总结了出来。体会挺深的，什么东西首先不能被吓住，平常心态对待每一个新知识，总会找到熟悉的身影，总有那么个头绪让你一点一点屡清楚了。

转载地址：http://oixym.baihongyu.com/

你可能感兴趣的文章