第11章 初识SqlSugarCore之NPOI Excel导入

1 重构LogicRender\ Index.cshtml

1.1 添加HTML定义

                        <button type="button" name="importexcel" class="btn mx-auto bg-info text-white " data-bs-toggle="modal" data-bs-target="#importexcel-window">

                            <i class="fas fa-upload">i>

                            Excel数据导入

                        button>

 <div class="modal fade" id="importexcel-window" tabindex="-1" aria-labelledby="exampleModalLabel" aria-hidden="true">

    <div class="modal-dialog">

        <div class="modal-content">

            <div class="modal-header">

                <h5 class="modal-title" id="exampleModalLabel">从Excel文件导入数据到数据库h5>

                <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close">button>

            div>

                <form asp-controller="LogicRender" asp-action="ImportExcelToExam" method="post" enctype="multipart/form-data">

                <div class="form-horizontal">

                    <div class="modal-body">

                        <div class="form-group row">

                            <div class="col-md-3">

                                <div class="label-wrapper">

                                    <label class="col-form-label">

                                        Excel文件

                                    label>

                                div>

                            div>

                            <div class="col-md-9">

                                  <input type="file" id="importExcelFile" name="importExcelFile" class="form-control" />

                            div>

                        div>

                    div>

                    <div class="modal-footer">

                        <button type="submit" class="btn btn-primary">

                            导 入

                        button>

                    div>

                div>

            form>

        div>

    div>

div>

2 重构LogicRenderController

    /// name="importExcelFile">表单文件接口实例，把将要执行导入操作Excel文件的相关信息数据存储到该接口实例中。

        ///

        /// 【导入Excel文件到考试成绩表】

        ///

        /// 摘要：

        ///     把作Excel文件中所有考试成绩，持久化保存到数据库的考试成绩表中。

        ///

        ///

        /// 返回：

        ///    当前页。

        ///

        ///

        [HttpPost]

        public virtual async Task ImportExcelToExam(IFormFile importExcelFile)

        {

            IWorkbook _workbook = null;

            try

            {

                if (importExcelFile != null && importExcelFile.Length > 0)

                {

                    Stream _stream = importExcelFile.OpenReadStream();

                    //如果是“.xlsx”编码格式Excel文件。

                    if (importExcelFile.FileName.IndexOf(".xlsx", StringComparison.Ordinal) > 0)

                    {

                       _workbook = new XSSFWorkbook(_stream);

                    }

                    //如果是“.xls”编码格式Excel文件。

                    else if (importExcelFile.FileName.IndexOf(".xls", StringComparison.Ordinal) > 0)

                    {

                        _workbook = new HSSFWorkbook(_stream);

                    }

                    // 从Excel文件的第1个(“考试成绩”)表中读取数据。

                    ISheet _sheet = _workbook.GetSheetAt(0);

                    if (_sheet == null)

                        throw new Exception($"{importExcelFile.FileName}:表中没有任何的数据。");

                    // 获取第1个(“考试成绩”)表的最后一行的索引值（NPOI中间件是从“0”开始计算行数的，即Excel文件的第“1”行，是NPOI中间件的“0”行，所以该索引值=Excel文件行-1）

                    int _rowCount = _sheet.LastRowNum;

                    List _exportExamList = new List();

                    for (int i = 1; i <= _rowCount; i++)

                    {

                        IRow _row = _sheet.GetRow(i);

                        Exam _exam = new Exam();

                        string _code = _row.GetCell(1).ToString();

                        _exam.StudentId = _context.SugarScope.Queryable().Where(student => student.Code == _code).Single().Id;

                        _exam.Name = _row.GetCell(2).ToString();

                        //注意：使用NPOI中间件获取Excel文件指定单元格中数据时，必须把该单元格中数据转换为“string=ToString()”，才能进行强制类型转换，否则会在强制转换时再现异常。

                        _exam.CourseId = Convert.ToInt32(_row.GetCell(3).ToString());

                        if (_row.GetCell(4) != null)

                        {

                            //注意：使用NPOI中间件获取Excel文件指定单元格中数据时，必须把该单元格中数据转换为“string=ToString()”，才能进行强制类型转换，否则会在强制转换时再现异常。

                            _exam.Score = Convert.ToInt32(_row.GetCell(4).ToString());

                        }

                        else

                        {

                            _exam.Comment = _row.GetCell(5).ToString();

                        }

                        _exportExamList.Add(_exam);

                    }

                    await _context.SugarScope.Insertable(_exportExamList).ExecuteReturnEntityAsync();

                }

               if(_workbook!=null)

                    _workbook.Close();

                return RedirectToAction("Index");

            }

            catch (Exception exception)

            {

                if (_workbook != null)

                    _workbook.Close();

                Console.Write(exception.Message);

                return RedirectToAction("Index");

            }

        }

3 纠错

    在上1章中本人说NPOI中间件不支持持久化“.xlsx”编码格式的Excel文件是错误的，NPOI中间件支持写“.xlsx”编码格式Excel文件，但是必须通过“ XSSFWorkbook _excelBook = new XSSFWorkbook();”，来构建NPOI中间件实例，

    “ XSSFWorkbook”实例，即支持持久化“.xlsx”编码格式的Excel文件；也支持久化“.xls”编码格式的Excel文件，但“ HSSFWorkbook”实例，却只支持持久化“.xls”编码格式的Excel文件。

例如:

///

        /// 【导出所选考试成绩到Excel文件】

        ///

        /// 摘要：

        ///     把所有考试成绩，持久化保存到指定Excel文件中。

        /// 注意：

        ///     该方法通过“ControllerBase.File”直接把内存中指定Excel文件持久化保存到客户端的“下载”目录(文件夹)中； 服务器端不在持久化保存该Excel文件。

        ///

        ///

        /// 返回：

        ///    把内存中指定Excel文件持久化保存到客户端的“下载”目录(文件夹)中后，打开。

        ///

        ///

        public async Task ExportAllExamToExcel()

        {

            //获取“考试成绩”的所有实例。

            List _exportExamList = await _context.SugarScope.Queryable()

                .Includes(score => score.CourseSingleton)//1层级联

                .Includes(

                    exam => exam.StudentSingleton,

                    student => student.SpecialtySingleton)//2层级联

                 .Includes(

                    exam => exam.StudentSingleton,

                    student => student.CategorySingleton)//2层级联

                .Includes(

                    exam => exam.StudentSingleton,

                    student => student.CategorySingleton,

                    category => category.GradeSingleton)//3层级联

                .OrderBy(exam => exam.Id)

                .ToListAsync();

            //通过NPOI中间件，创建Excel文件的对象。

            XSSFWorkbook _excelBook = new XSSFWorkbook();

            //在当前创建Excel文件，添加一个工作表：“考试成绩”

            ISheet _examSheet = _excelBook.CreateSheet("考试成绩");

            //结“考试成绩”工作表添加第一行的头部标题

            var row1 = _examSheet.CreateRow(0);

            row1.CreateCell(0).SetCellValue("编号");

            row1.CreateCell(1).SetCellValue("姓名");

            row1.CreateCell(2).SetCellValue("学号");

            row1.CreateCell(3).SetCellValue("专业");

            row1.CreateCell(4).SetCellValue("年级");

            row1.CreateCell(5).SetCellValue("班级");

            row1.CreateCell(6).SetCellValue("试卷名称");

            row1.CreateCell(7).SetCellValue("课程");

            row1.CreateCell(8).SetCellValue("成绩");

            //将数据依次写入 “考试成绩”工作表各个行

            for (var i = 0; i < _exportExamList.Count; i++)

            {

                var rowtemp = _examSheet.CreateRow(i + 1);

                rowtemp.CreateCell(0).SetCellValue(_exportExamList[i].Id.ToString());

                rowtemp.CreateCell(1).SetCellValue(_exportExamList[i].StudentSingleton.Name);

                rowtemp.CreateCell(2).SetCellValue(_exportExamList[i].StudentSingleton.Code);

                rowtemp.CreateCell(3).SetCellValue(_exportExamList[i].StudentSingleton.SpecialtySingleton.Name);

                rowtemp.CreateCell(4).SetCellValue(_exportExamList[i].StudentSingleton.CategorySingleton.GradeSingleton.Name);

                rowtemp.CreateCell(5).SetCellValue(_exportExamList[i].StudentSingleton.CategorySingleton.Name);

                rowtemp.CreateCell(6).SetCellValue(_exportExamList[i].Name);

                rowtemp.CreateCell(7).SetCellValue(_exportExamList[i].CourseSingleton.Name);

                if (_exportExamList[i].Score != null)

                {

                    rowtemp.CreateCell(8).SetCellValue(_exportExamList[i].Score.ToString());

                }

                else

                {

                    rowtemp.CreateCell(8).SetCellValue(_exportExamList[i].Comment);

                }

            }

            //当前被持久化保存的Excel文件的文件名。

            //string _excelFileName = "所有成绩(" + DateTime.Now.ToString("HHmmssfff") + ").xls";

            string _excelFileName = "所有成绩(" + DateTime.Now.ToString("HHmmssfff") + ").xlsx";

            MemoryStream _memoryStream = new MemoryStream();

            try

            {

                _excelBook.Write(_memoryStream);

                byte[] data = _memoryStream.ToArray();

                //通过“ControllerBase.File”直接把内存中指定Excel文件持久化保存到客户端的“下载”目录(文件夹)中； 服务器端不在持久化保存该Excel文件。

                return File(data, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", _excelFileName);

            }

            catch (Exception exception)

            {

                if (_excelBook != null)

                    _excelBook.Close();

                if (_memoryStream != null)

                {

                    _memoryStream.Close();

                    _memoryStream.Dispose();

                }

                Console.Write(exception.Message);

                return RedirectToAction("Index");

            }

        }

修改推荐优先级：NPOI>ClosedXML>EPPlus

对以上功能更为具体实现和注释见：22-10-21-11_SqlSugarAcquaintance(初识SqlSugarCore之NPOI Excel导入)。