市委组织部考核项目——将Excel中的每一行数据拆成多条并依次保存到数据库中

       

        这几天一直在倒腾Excel导入这一块，开始还挺顺利的，使用老办法给用户制定法一个模板，制定好表头，让用户依次添加数据，我们需要做的事就是将此Excel转换成DataTable，这样就很容易写到数据库中了。到后边可就没这么顺利了，表头固定不了，需要动态下载表头，本想只要将表头下载下来，然后再照原方法去做就好了，后边才知道往数据库中录数据的时候也不像之前那样了，将excel中的每一行数据都作为一条数据保存到数据库中，现在需要将每一条数据拆开成多条数据保存到数据库中年，且拆开的条数也不固定。开始想着挺难得，觉得做判断的时候也不好做，错误列表也不好固定，保存数据的时候又这么多条件......，不想动手， 想不出来该怎么下手，到最后实在拖不下去的时候才塌下心来仔细的想了想逻辑，结果没费多长时间也研究出来了，做出来才发现其实也只是用了个嵌套循环就出来了，比以往费事的地方也就多了个获取excel表头内容，根据它去查询，判断等。

       下面我把整个过程写在这，分享一下我成功的喜悦。

       这是下载下来的excel模板：

                            

       根据这个模板，我需要将一行 拆成5条数据保存到数据库中，也就是说"干部名称，干部职位，单位名称，单位类型，较差"为一条记录，"干部名称，干部职位，单位名称，单位类型，良好"为一条记录，以此类推。

js代码：

<form id="form1" runat="server" style="width: 100%">        <div class="container">            <asp:TextBox ID="TxtYear" name="HidYear" runat="server" hidden="1"></asp:TextBox>            <asp:TextBox ID="TxtTarget" name="HidTarget" runat="server" hidden="1"></asp:TextBox><%--指标名称--%>            <asp:TextBox ID="TxtTargetId" name="HidTargetId" runat="server" hidden="1"></asp:TextBox>            <asp:TextBox ID="TxtUnitName" name="HidUnitName" runat="server" hidden="1"></asp:TextBox><%--所属单位名称--%>            <asp:TextBox ID="TxtUnitNameId" name="HidUnitNameId" runat="server" hidden="1"></asp:TextBox>            <asp:TextBox ID="TxtUnitType" name="HidUnitType" runat="server" hidden="1"></asp:TextBox><%--所属单位类别--%>            <asp:TextBox ID="TxtUnitTypeId" name="HidUnitTypeId" runat="server" hidden="1"></asp:TextBox>            <div class="cnt form">                <fieldset style="width: 1020px; height: 45px;">                    <table class="data-form" cellspacing="0" cellpadding="0">                        <tr>                            <td>                                <span class="tip">提示：导入数据前请选择指标下载相应的EXCEL模板</span>                                <a id="downloadExcel" class="btn-lit"><span>点下载EXCEL模板</span></a>                                <br />                                <br />                            </td>                        </tr>                        <tr>                            <td>                                <asp:FileUpload ID="fupImport" runat="server" /><%--上传文件地址--%>                                                                 <asp:LinkButton ID="btnImport" CssClass="btn-lit" runat="server" OnClick="btnImport_Click"><span>批量导入</span></asp:LinkButton>                            </td>                        </tr>                    </table>                </fieldset>            </div>        </div>    </form>

后台代码：

using System;using System.Collections.Generic;using System.Linq;using System.Web;using System.Web.UI;using System.Web.UI.WebControls;using EvaluationSystem.BLL;using System.Data;using System.Text;using EvaluationSystem.Model;namespace EvaluationSystem.Web.Assessment.InputComputer{    public partial class CadresQuality : System.Web.UI.Page    {        //年份        string strYear = "";        //考核指标        string strTargetName = "";        //考核指标id        string strTargetId = "";        //所属单位名称        string strUnitName = "";        //所属单位id        string strUnitNameId = "";        //处级干部名称        string strCadres = "";        //处级干部id        string strCadresId = "";        //单位类型名称        string strUnitType = "";        //单位类型id        string strUnitTypeId = "";        //处级干部职位        string strPosition = "";        //票数        string strVotes = "";        //级别id        string strLevelId = "";        //列名        string strheadfield = "";        //级别        string strLevel = "";        //错误列        string strVoteField = "";        /// <summary>        /// 向错误列表中添加当前行        /// </summary>        /// <param name="dtErrorRow">代表错误列表的datatable</param>        private void AddErrorRow(DataTable dtErrorRow, string reason)        {            //新建数据行            DataRow drAddErrorRow = dtErrorRow.NewRow();            //填充数据行            drAddErrorRow["干部名称"] = "\'" + strCadres;            drAddErrorRow["干部职位"] = "\'" + strVoteField;            drAddErrorRow["单位名称"] = "\'" + strUnitName;            drAddErrorRow["票数"] = "\'" + strVoteField;            drAddErrorRow["错误原因"] = reason;            dtErrorRow.Rows.Add(drAddErrorRow);        }        protected void btnImport_Click(object sender, EventArgs e)        {            try            {                //考核时间                strYear = TxtYear.Text;                //考核指标名称                strTargetName = TxtTarget.Text;                //考核指标id                strTargetId = TxtTargetId.Text;                //处级干部单位定性指标类                CharacterizationTargetBLL targetBll = new CharacterizationTargetBLL();                //处级干部定性指标级别类                CharacterizationLevelBLL levelBll = new CharacterizationLevelBLL();                //处级干部单位基础信息类                CadresBaseInfoBLL cadresBasicBll = new CadresBaseInfoBLL();                //处级干部定性指标信息录入类                CadresCharacterizationVotesBLL votesBll = new CadresCharacterizationVotesBLL();                //BLL层把Excel转化为datatable类                CreateExcelDataBLL createExcelData = new CreateExcelDataBLL();                //将datatable类转换成excel                DataTableToExcel dataTableToExcel = new DataTableToExcel();                //获取上传文件地址                string url = fupImport.PostedFile.FileName.ToString();                if (url == "")                {                    //数据源为空，弹出提示：请选择Excel文件！                    Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('请选择Excel文件！');</script>");                    return;                }                //如果文件扩展名不是xls则提示文件格式不正确                if (url.Substring(url.LastIndexOf("."), url.Length - url.LastIndexOf(".")) != ".xls")                {                    Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('文件类型不正确，请选择扩展名为.xls的文件！');</script>");                    return;                }                string urlLocation = url.Substring(url.LastIndexOf("\\") + 1);//获取文件名                DataTable dtCadresData;                //在系统中建文件夹up,并将excel文件另存                this.fupImport.SaveAs(Server.MapPath("~\\UploadFile") + "\\" + urlLocation);//记录文件名到服务器相对应的文件夹中                //获得文件路径                string strpath = Server.MapPath("~\\UploadFile") + "\\" + urlLocation;                //把上传的Excel转换为datatable                dtCadresData = createExcelData.CreateExcelDataSource(strpath);                /*-------------------------------------判断数据源是否合法--------------------------------------*/                //判断时间是否为服务器的时间                string strServerTime = votesBll.GetYear();                if (strYear != strServerTime)                {                    Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('时间已过期,不能录入数据!');</script>");                    //退出方法                    return;                }                //判断dtCadresData中是否包含全部要求的字段                for (int i = 0; i < dtCadresData.Columns.Count; i++)                {                    //只要有一个字段不被包含，则提示"数据源缺少必要的字段"，并退出循环和整个方法                    if (!dtCadresData.Columns.Contains(dtCadresData.Columns[i].ToString().Trim()))                    {                        Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('数据源缺少必要的字段,请检查Excel数据源！');</script>");                        //退出方法                        return;                    }                }                //判断数据源中是否有数据                if (dtCadresData.Rows.Count == 0)                {                    Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('Excel文件中没有任何数据，请填充数据！');</script>");                    //退出方法                    return;                }                //判断是否有相同的行                DataView dvCadresData = new DataView(dtCadresData);                if (dvCadresData.Count != dvCadresData.ToTable(true, "单位名称", "干部名称", "干部职位","单位类别").Rows.Count)                {                    Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('" + "第" + (dvCadresData.ToTable(true, "单位名称", "干部名称", "干部职位","单位类别").Rows.Count + 2) + "条数据已存在，请删除此条数据！" + "');</script>");                    //退出方法                    return;                }                DataSet dsCadresData = new DataSet("ds_CadresData"); //创建一个名为ds_CadresData的DataSet                //手动创建的新数据表-处级干部定性信息录入表                DataTable dtAddCadresData = new DataTable("dt_AddCadresData"); //创建一个名为dt_AddCadresData的DataTalbe                //为dt_AddCadresData表内建立Column（表头）,添加数据列:                dtAddCadresData.Columns.Add(new DataColumn("Targetid", typeof(string)));//指标id                dtAddCadresData.Columns.Add(new DataColumn("CadresID", typeof(string)));//考核对象id                dtAddCadresData.Columns.Add(new DataColumn("Name", typeof(string)));//所属单位名称                dtAddCadresData.Columns.Add(new DataColumn("CharacterizationLevelId", typeof(string)));//级别                dtAddCadresData.Columns.Add(new DataColumn("other2", typeof(string)));//级别id                dtAddCadresData.Columns.Add(new DataColumn("other3", typeof(string)));//所属单位类别id                dtAddCadresData.Columns.Add(new DataColumn("YearTime", typeof(string)));                dtAddCadresData.Columns.Add(new DataColumn("Votes", 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)));                //遍历整个dt ,将为空的单元格写入默认数据“0”；                for (int i = 0; i < dtCadresData.Rows.Count; i++)                {                    for (int j = 4; j < dtCadresData.Columns.Count; j++)                    {                        if (dtCadresData.Rows[i][j].ToString().Trim() == "" || dtCadresData.Rows[i][j].ToString().Trim() == null)                        {                            dtCadresData.Rows[i][j] = "0";                        }                    }                }                //从上传的Excel转换为的datatable表中取出数据，放入处级干部定性指标信息录入表中                for (int intRow = 0; intRow < dtCadresData.Rows.Count; intRow++)                {                    //处级干部名称                    strCadres = dtCadresData.Rows[intRow][0].ToString();                    //干部职位                    strPosition = dtCadresData.Rows[intRow][1].ToString();                    //单位名称                    strUnitName = dtCadresData.Rows[intRow][2].ToString();                    //单位类别                    strUnitType = dtCadresData.Rows[intRow][3].ToString();                    //根据单位类别查找类别id                    //根据处级干部名称和职位还有所属单位和checked=yes去T_CadresBaseInfo表中查找处级干部单位id                    StringBuilder strWhere = new StringBuilder();                    strWhere.Append("other1='单位类别' and Name='" + strUnitType + "'");                    DataSet ds = cadresBasicBll.GetUnitTypeId(strWhere.ToString());                    //级别id                    strUnitTypeId = ds.Tables[0].Rows[0][0].ToString().Trim();                    //根据处级干部名称和职位还有所属单位和checked=yes去T_CadresBaseInfo表中查找处级干部单位id                    StringBuilder strWhere1 = new StringBuilder();                    strWhere1.Append("CadresName='" + strCadres + "'and Checked='Yes'and Position='" + strPosition + "'and Unit='" + strUnitName + "'");                    //处级干部单位id                    strCadresId = cadresBasicBll.GetIdByName(strWhere1.ToString());                    //判断处级干部单位id知否存在，如果不存在说明此处级干部单位今年不是考核对象                    if (strCadresId == "" || strCadresId == null)                    {                        // 向错误列表中添加当前行                        AddErrorRow(dtErrorRow, "此处级干部今年未被列入考核对象，请核对信息！");                        // 跳出当前循环                        continue;                    }                    for (int intColumns = 4; intColumns < dtCadresData.Columns.Count; intColumns++)                    {                        //票数                        strVotes = dtCadresData.Rows[intRow][intColumns].ToString();                        //级别                                   strLevel = dtCadresData.Columns[intColumns].ColumnName;                        //根基时间,级别,指标id去级别表中查找级别id                        StringBuilder strWhere2 = new StringBuilder();                        strWhere2.Append("TargetId='" + strTargetId + "'and Timestamp='" + strYear + "'and LevelId='" + strLevel + "'");                        DataSet ds2 = levelBll.GetLevel(strWhere2.ToString());                        //级别id                        strLevelId = ds2.Tables[0].Rows[0][0].ToString().Trim();                        //处级干部单位定性指标录入实体                        CadresCharacterizationVotesEntity votesEntity = new CadresCharacterizationVotesEntity();                        //将判断条件传到实体中                        votesEntity.YearTime = strYear;//考核时间                        votesEntity.Targetid = strTargetId;//指标id                        votesEntity.CharacterizationLevelId = strLevel;//级别                        votesEntity.other2 = strLevelId;//级别id                        votesEntity.CadresID= strCadresId;//处级干部id                        votesEntity.Name = strUnitName;//所属单位名称                        votesEntity.other3 = strUnitTypeId;//所属单位类别id                        votesEntity.Votes = strVotes;//票数                        //如果指定的记录已存在则将当前行添加到错误列表，并跳出当前循环                        if (votesBll.ExistProperty(votesEntity) == true)                        {                            // 向错误列表中添加当前行                            strVoteField = strLevel + ":" + strVotes;//票数                            AddErrorRow(dtErrorRow, "指定的数据已存在！");                            // 跳出当前循环                            continue;                        }                        //添加处级干部原始数据表的新行                        DataRow drAddCadresData = dtAddCadresData.NewRow();//注意这边创建dt的新行的方法。指定类型是DataRow而不是TableRow，然后不用new直接的用创建的DataTable下面的NewRow方法。                        //虚拟学生对应的表                        drAddCadresData["Targetid"] = strTargetId;                        drAddCadresData["CadresID"] = strCadresId;                        drAddCadresData["YearTime"] = strYear;                        drAddCadresData["CharacterizationLevelId"] = strLevel;                        drAddCadresData["other2"] = strLevelId;                        drAddCadresData["other3"] = strUnitTypeId;                        drAddCadresData["Name"] = strUnitName;                        drAddCadresData["Votes"] = strVotes;                        dtAddCadresData.Rows.Add(drAddCadresData);  //将一整条数据写入表中                    }                }                //将表加入DataSet中                dsCadresData.Tables.Add(dtAddCadresData);                string tablename;                tablename = "T_CadresCharacterizationVotes";                Boolean flagImportRecord = votesBll.ImportRecord(dsCadresData, tablename);                if (true == flagImportRecord && dtErrorRow.Rows.Count == 0)                {                    //导入成功，弹出提示                    Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('导入成功！');</script>");                }                else if (flagImportRecord == false)                {                    //导入失败，弹出提示                    Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('导入失败');</script>");                }                else if (true == flagImportRecord && dtErrorRow.Rows.Count != 0)                {                    //只有部分数据导入成功，弹出提示                    Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('只有部分导入成功，有错误的部分已导出为EXCEL，请查看新导出的EXCEL；新导出的Excel修改错误后可作为数据源重新导入');</script>");                    //将错误数据导出到Excel并打开显示                    dataTableToExcel.ToExcel(dtErrorRow);                }            }            catch (Exception ex)            {                MessageBox.Show(this.Page, ex.Message);            }        }    }}

B层：这里只介绍excel转换成datatable那一部分代码，其它查询的的方法就不一一介绍了

/// <summary>        /// 传入excel路径，转换为datatable        /// </summary>        /// <param name="url"></param>        /// <returns></returns>        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 dt;            }            catch (Exception ex)            {                throw ex;            }            finally            {                oleAdapter.Dispose();                oleConn.Close();                oleConn.Dispose();                //删除上传的Excel文件（因为该文件的存在会占用多余的网站空间）                if (File.Exists(url))                {                    File.Delete(url);                }            }        }

        /// <summary>        /// excel导入开发区单位原始数据录入表        /// 2-25日陈金荣添加        /// </summary>        /// <param name="ds"></param>        /// <param name="tablename"></param>        /// <returns></returns>        public bool ImportRecord(DataSet ds, string tablename)        {            bool bloimport;            bloimport = dal.ImportRecordAll(ds);            return bloimport;        }

D层：这里只介绍Excel导入这一部分

 /// <summary>        ///excel导入开发区单位原始数据        ///2-25陈金荣添加        /// </summary>        /// <returns></returns>        public bool ImportRecordAll(DataSet ds)        {            TestHelper testhelp = new TestHelper(); ;            bool test;            test = testhelp.InsertTable(ds.Tables[0], "T_CadresCharacterizationVotes", ds.Tables[0].Columns);            return test;        }

Help中：

 /// <summary>批量导入DataTable        /// 批量导入DataTable        /// </summary>        /// <param name="dt">DataTable数据表</param>        /// <param name="tableName">表名</param>        /// <param name="dtColum">数据列集合</param>        /// <return>Boolean值:true成功，false失败</return>        public Boolean InsertTable(DataTable dt, string tableName, DataColumnCollection dtColum)        {            //打开数据库            GetConn();            try            {                //声明SqlBulkCopy ,using释放非托管资源                using (SqlBulkCopy sqlBC = new SqlBulkCopy(sqlConn))                {                    //一次批量的插入的数据量                    sqlBC.BatchSize = 1000;                    //超时之前操作完成所允许的秒数，如果超时则事务不会提交 ，数据将回滚，所有已复制的行都会从目标表中移除                    sqlBC.BulkCopyTimeout = 60;                    //设置要批量写入的表                    sqlBC.DestinationTableName = tableName;                    //自定义的datatable和数据库的字段进行对应                                        for (int i = 0; i < dtColum.Count; i++)                    {                        sqlBC.ColumnMappings.Add(dtColum[i].ColumnName.ToString(), dtColum[i].ColumnName.ToString());                    }                    //批量写入                    sqlBC.WriteToServer(dt);                }                return true;            }            catch            {                return false;            }            finally            {                //关闭数据库                sqlConn.Close();            }        }

       整个实现过程就是这样，没有想象的那么费劲，现在唯一担心的事就怕数据量大的时候时间长的出乎意料，这还须进行测试。

      通过这一阶段的学习，我想说，认认真真的想，也就那样；踏踏实实的做，会成功的！加油！