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

最新推荐文章于 2020-11-08 21:14:40 发布

IT四王子

最新推荐文章于 2020-11-08 21:14:40 发布

阅读量1.5k

点赞数

分类专栏：技术研究所

本文链接：https://blog.csdn.net/liming363873117/article/details/51646988

版权

技术研究所专栏收录该内容

7 篇文章 0 订阅

订阅专栏

引言：

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="导入" οnclick="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");
            }
        }]
    });

}
//确定导入Excel
function 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层代码没有写在博客里面，这部分代码比较简单，只是简单的查询。通过这三步就能实现简单的导入功能了。