技术（2）页面导入、导出（一）Excel导入

引言：

               Excel的导入，自己接触过俩次，应该好好总结一下。

概述：

               向数据库导入数据也是系统开发必不可少的环节，传统的导入方式有俩种，一种是一条条复制、粘贴到数据库中，这个现在已经不会使用了，耗费时间、人力资源太大；一种是先把数据复制、粘贴到Excel，然后将数据库中的表导出为CSV文件，然后再将该文件修改为一个导入模板，将数据复制、粘贴到文件中，最后导入数据库；目前流行的代码导入方式：通过代码规则、table和Excel模板匹配，向数据库中导入数据。本篇博客重点介绍代码实现批量导入功能。

内容：

               小编以导入课表为例为大家讲述批量导入的过程。

          第一步：编写Controller层代码 ，这部分代码主要是实现将数据库数据与模板导入数据转换的过程，将模板输入的数据转换为数据库中相对应的字段值，通过table导入到数据库中。

using System;using System.Collections.Generic;using System.Linq;using System.Web;using System.Web.Mvc;using System.Text;using System.IO;using System.Data;using ITOO.Basic.Model;using ITOO.Basic.ViewModel;using ITOO.UIQueryProperties.ViewModel;using System.Web.Script.Serialization;using System.Collections;using ITOO.Library.Core.Common;using Newtonsoft.Json;using ITOO.Basic.IBLL;using ITOO.Library.Core.AOP;namespace ITOO.Basic.Client.Controllers{    public class ImportController : Controller    {                private IImportBll importBll = SpringHelper.GetObject<IImportBll>("ImportBll");        private IBuildingBll buildingBll = SpringHelper.GetObject<IBuildingBll>("BasicBuildingBLL");        private IRoomTypeBll roomTypeBll = SpringHelper.GetObject<IRoomTypeBll>             ("BasicRoomTypeBLL");        private IRoomBll roomBll = SpringHelper.GetObject<IRoomBll>("BasicRoomBLL");        private IImportBll ImportBll = SpringHelper.GetObject<IImportBll>("ImportBll");        private ICurriculumsBll CurriculemesBll = SpringHelper.GetObject<ICurriculumsBll>("CurriculumsBll");        private IBasicClassBll basicClassBll = SpringHelper.GetObject<IBasicClassBll>("BasicClassBll");        private ISchoolCalendaarBll schoolCalendarrbll = SpringHelper.GetObject<ISchoolCalendaarBll>("SchoolCalendaarBll");        private IBasicCourseBll basiccourseBll = SpringHelper.GetObject<IBasicCourseBll>("BasicCourseBll");        private ITeacherBll teacherBll = SpringHelper.GetObject<ITeacherBll>("TeacherBll");        private IDictionaryBll dictionaryBll = SpringHelper.GetObject<IDictionaryBll>("DictionaryBll");        private IBasicPeriodTimeBll periodTimeBll = SpringHelper.GetObject<IBasicPeriodTimeBll>("BasicPeriodTimeBll");        //转到视图        public ActionResult Index()        {            return View();        }        #region V5.0 下载导入课表的模板+ public FileResult GetExcelFlow()+李明 2016年3月27日19:11:17        /// <summary>        /// 导入上传文件        /// </summary>        /// <returns></returns>        public FileResult GetExcelFlow()        {            //模块名称需要和Upload下的Excel模块名称一致            string ExcelName = "课程表导入模板.xls";            //获取文件路径            string strpath = Server.MapPath("~\\ExcelFile\\DownloadFile") + "\\" + ExcelName;            return File(strpath, "application.vnd.ms-excel", ExcelName);        }        #endregion        #region  批量导入+ public ActionResult ImportFlowBatch()+冯坤贵+2016年3月26日        /// <summary>        /// 批量导入流程        /// </summary>        /// <returns></returns>        public ActionResult ImportFlowBatch()        {            #region  导入是Excel的限制            DataTable table = new DataTable();            HttpPostedFileBase file = Request.Files["files"];            string strFileName;            string strSavePath;            string ClientPath = AppDomain.CurrentDomain.BaseDirectory + "ExcelFile\\DownloadFile\\";            string strPaperId = "sheet1";            if (file == null || file.ContentLength <= 0)            {                ViewBag.error = "文件不能为空";                return View();            }            else            {                string strFilename = Path.GetFileName(file.FileName);                int intFilesize = file.ContentLength; //获取上传文件的大小（单位是字节）                string fileEx = System.IO.Path.GetExtension(strFilename); //获取上传文件的扩张名                string strNoFileName = System.IO.Path.GetFileNameWithoutExtension(strFilename);//获取上传文件的无扩张名                int Maxsize = 4000 * 1024;//定义上传文件的最大空间大小为4M                string FileType = ".xls,.xlsx"; //定义上传文件类型                strFileName = strNoFileName + DateTime.Now.ToString("yyyyMMddhhmmss") + fileEx;                if (!FileType.Contains(fileEx))                {                    ViewBag.error = "文件类型不对，只能导入xls和xlsx格式的文件";                }                if (intFilesize >= Maxsize)                {                    ViewBag.error = "上传文件超过4M，不能上传";                }                strSavePath = Path.Combine(ClientPath, strFileName);                file.SaveAs(strSavePath);            }            #endregion            table = importBll.ExcelToDataTable(strSavePath, strPaperId);            table.Columns.Add("curriculumID", typeof(string));//动态添加课程表ID列            table.Columns["学年学期"].ColumnName = "schoolCalendaarID";            table.Columns["班级"].ColumnName = "classID";            table.Columns["课程名称"].ColumnName = "courseID";            table.Columns["教师姓名"].ColumnName = "teacherID";            table.Columns["教室名称"].ColumnName = "roomID";            table.Columns["开始周"].ColumnName = "startWeek";            table.Columns["结束周"].ColumnName = "endWeek";            table.Columns["是否为奇数周"].ColumnName = "isOdd";            table.Columns["星期"].ColumnName = "weekID";            table.Columns["节次"].ColumnName = "cellTimeID";            int roomNum = table.Columns.Count;            //定义一个List用于接收每条需要查询的数据            List<BasicClassViewModel> entity = new List<BasicClassViewModel>();            BasicClassViewModel entityViewModel = new BasicClassViewModel();            //通过学年学期查询schoolCalendaarID            List<BasicSchoolCalendarViewModel> schoolList = new List<BasicSchoolCalendarViewModel>();            BasicSchoolCalendarViewModel schoolViewModel = new BasicSchoolCalendarViewModel();            //定义两个节次 ViewModel，待用。            //List<CurriculumViewModel> Curriculumlist = new List<CurriculumViewModel>();            //CurriculumViewModel Curriculum = new CurriculumViewModel();            //定义两个节次 ViewModel，待用。            List<BasicPeriodTimeViewModel> periodList = new List<BasicPeriodTimeViewModel>();            BasicPeriodTimeViewModel periodViewModel = new BasicPeriodTimeViewModel();            //定义两个ROOM viewModel ,待用。            List<BasicRoomViewModel> RoomList = new List<BasicRoomViewModel>();            BasicRoomViewModel Room = new BasicRoomViewModel();            //定义CourseViewModel            List<BasicCourseViewModel> CourseList = new List<BasicCourseViewModel>();            BasicCourseViewModel Course = new BasicCourseViewModel();            //定义TeacherViewModel            List<BasicTeacherViewModel> TeacherList = new List<BasicTeacherViewModel>();            BasicTeacherViewModel Teacher = new BasicTeacherViewModel();            //定义字典ViewModel            List<BasicDictionaryViewModel> WeekList = new List<BasicDictionaryViewModel>();            BasicDictionaryViewModel Week = new BasicDictionaryViewModel();            //****************以下是查询需要ID****************            for (int i = 0; i < table.Rows.Count; i++)            {                //动态添加curriculumIDGuid                Guid curriculumIDGuid = Guid.NewGuid();                table.Rows[i]["curriculumID"] = curriculumIDGuid.ToString();                #region 取出所有ID                //*************************                //1、通过班级名称查询classID                entityViewModel.ClassID = table.Rows[i]["classID"].ToString();                entity.Add(entityViewModel);                //2、通过学年学期查询schoolCalendaarID                schoolViewModel.SchoolCalendarID = table.Rows[i]["schoolCalendaarID"].ToString();                schoolList.Add(schoolViewModel);                //3、根据节次名称查询节次ID                periodViewModel.PeriodTimesName = table.Rows[i]["cellTimeID"].ToString();                periodList.Add(periodViewModel);                //4、根据房间名称查询房间ID                Room.RoomName = table.Rows[i]["roomID"].ToString();                RoomList.Add(Room);                //5、通过课程名称和课程代码查课程guid                Course.CourseName = table.Rows[i]["courseID"].ToString();                Course.CourseCode = table.Rows[i]["课程代码"].ToString();                CourseList.Add(Course);                //6、通过教师名称和教师编码查询教师guid                Teacher.Name = table.Rows[i]["teacherID"].ToString();                Teacher.EmployeeNo = table.Rows[i]["教工号"].ToString();                TeacherList.Add(Teacher);                //7、通过星期几查到星期对应的guid                Week.Content = table.Rows[i]["weekID"].ToString();                WeekList.Add(Week);                #endregion            }            //查询出对应的ID            entity = basicClassBll.QueryClassByClassName(entity);            schoolList = schoolCalendarrbll.QuerySchoolCalendarIDBySchoolYearAndTeam(schoolList);            TeacherList = teacherBll.QueryTeacherIdByNameAndCode(TeacherList);            CourseList = basiccourseBll.QueryCourseIdByNameAndCode(CourseList);            WeekList = dictionaryBll.QueryWeekIdByWeekName(WeekList);            periodList = periodTimeBll.QueryPeriodIDByPeriodName(periodList);            RoomList = roomBll.QueryRoomIDByRoomName(RoomList);            int count = table.Rows.Count;            for (int j = 0; j < count; j++)            {                table.Rows[j]["cellTimeID"] = periodList[0].PeriodTimeID;                table.Rows[j]["roomID"] = RoomList[0].RoomID;                table.Rows[j]["CourseID"] = CourseList[0].CourseID;                table.Rows[j]["classID"] = entity[0].ClassID;                table.Rows[j]["schoolCalendaarID"] = schoolList[0].SchoolCalendarID;                table.Rows[j]["teacherID"] = TeacherList[0].TeacherID;                table.Rows[j]["weekID"] = WeekList[j].DictionaryID;                //移除没用项                table.Columns.Remove(table.Columns["课程代码"].ColumnName);                table.Columns.Remove(table.Columns["教工号"].ColumnName);            }            //去重算法            for (int i = 0; i < count; i++)            {                for (int j = 1; j < count; j++)                {                    if (table.Rows[i]["cellTimeID"] == table.Rows[j]["cellTimeID"])                    {                        table.Rows[j].Delete();                        count--;                    }                    else                    {                        //连接数据库                        MySqlHelper mysqlHelper = new MySqlHelper("server=192.168.22.238;userid=root;password=123;database=basicentity;Character Set=utf8;");                        //批量导入数据                        mysqlHelper.BulkInsert(table);                                           }                }            }                        return Content("<script >alert('导入成功，您太棒了！' );window.location.href = '/Import/index';</script >", "text/html");        #endregion        }    }}

          第二步：html层代码：这部分代码主要是页面显示一个导入按钮，单击按钮后出现一个form界面，选择相应的文件进行导入。                                    

<span style="font-size:18px;">@*@{    ViewBag.Title = "Index";}*@<h2>导入课表</h2><script src="../../Scripts/Import/Import.js"></script><input id="btnImport" type="button" value="导入" onclick="showImportFrm()" /><div id="importDiv" style="display: none">        <form action="/Import/ImportFlowBatch" method="post" enctype="multipart/form-data">            <div style="margin-bottom: 20px">                <span class="tip">提示：导入前请先下载EXCEL模板 </span><a class="btn-lit" href="../../ExcelFile/DownloadFile/课程表导入模板.xls">                    <span>点击下载EXCEL模板</span></a>            </div>            <div style="margin-bottom: 20px">                <input id="FileUpload" type="file" name="files" data-options="prompt:'Choose a file...'" />            </div>        </form>    </div></span>

              第三步：Import.js代码  ：主要实现界面动态提示的功能            

//显示导入Excel表格function showImportFrm() {    $("#importDiv").css("display", "block");    $("#importDiv").dialog({        width: 600,        height: 250,        modal: true,        title: "批量导入",        collapsible: true,        minimizable: true,        maximizable: true,        resizable: true,        buttons: [{            id: 'btnAdd',            text: '添加',            iconCls: 'icon-add',            handler: function () {                //让表单提交                doImport();            }        }, {            id: 'btnCancelAdd',            text: '取消',            iconCls: 'icon-cancel',            handler: function () {                $("#importDiv").dialog("close");            }        }]    });}//确定导入Excelfunction doImport() {    var file = ($("#FileUpload").val());    if (file == "") {        $.messager.alert('系统提示', '请选择将要上传的文件!');        return;    } else {        var stuff = file.match(/^(.*)(\.)(.{1,8})$/)[3];        if (stuff != 'xls') {            $.messager.alert('警告', '文件类型不正确，请选择.xls文件!', 'warning');            return;        } else {            $("#importDiv form").submit();            //$.messager.alert('提示', '信息导入成功, ');            $.ajax({                type: "POST",                //data: "file=" + file,                url: "/Import/ImportFlowBatch/",                success: function (result) {                    $.messager.alert('系统提示', '信息导入成功,');                }            });        }    }}

          关于controller层那些数据查询的B层代码没有写在博客里面，这部分代码比较简单，只是简单的查询。通过这三步就能实现简单的导入功能了。       

总结：

             这种导入的方法虽然简单，但是存在明显的问题，Excel模板中导入的数据，如果出现重复数据，不能实现去重，但是能保证导入数据与数据库中的数据不重复。之前在做新生系统的时候，调试过新生信息导入的功能，那里通过配置文件匹配信息，感觉性能以及去重效果更好，但是还没有研究，等小编研究了再与大家分享。