Excel导入-----导出(包含所选和全部)操作
在做系统的时候，很多时候信息量太大，这时候就需要进行Excel表格信息的导入和导出，今天就来给大家说一下我使用Excel表格信息导入和导出的心得。
1：首先需要在前端显示界面View视图中添加导入Excel和导出Excel按钮：
<div class="btn-group"> <button type="button" class="btn btn-success"> <i class="fa fa-download"></i> @T("导出") </button> <button type="button" class="btn btn-success dropdown-toggle" data-toggle="dropdown" aria-expanded="false"> <span class="caret"></span> <span class="sr-only"> </span> </button> @* 导出全部的form提交 *@ <ul class="dropdown-menu" role="menu"> <li class="divider"></li> <form action="/控制器/ExportExcelAll" method="post"> <li> <button type="submit" name="exportexcel-all"> <i class="fa fa-file-excel-o"></i> @T("导出到Excel(全部)") </button> </li> </form> <li> <button type="button" id="exportexcel-selected"> <i class="fa fa-file-excel-o"></i> @T("导出到Excel(所选)") </button> </li> </ul> </div> <button type="button" name="importexcel" class="btn bg-olive" data-toggle="modal" data-target="#importexcel-window"> <i class="fa fa-upload"></i> @T("导入") </button>
这里注意，导出（所选）Excel是通过获取当下的表单的方式来导出数据的，导出是通过提交form表单实现的（原因为通过submit没有响应）
2：添加点击事件后弹出来的操作界面（importexcel-window）：通过 data 属性：在按钮上设置属性 data-toggle="modal"，同时设置 data-target="#identifier" 或 href="#identifier" 来指定要切换的特定的模态框（带有 id="identifier"）。通过 JavaScript：使用这种技术，您可以通过简单的一行 JavaScript 来调用带有 id="identifier" 的模态框：
$('#identifier').modal(options)
@*import GradeMessage form*@ <div id="importexcel-window" class="modal fade" tabindex="-1" role="dialog" aria-labelledby="importexcel-window-title"> <div class="modal-dialog"> <div class="modal-content"> <div class="modal-header"> <button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">×</span></button> <h4 class="modal-title" id="importexcel-window-title">@T("Admin.Common.ImportFromExcel")</h4> </div> @using (Html.BeginForm("ImportExcel", "GradeMessage", FormMethod.Post, new { enctype = "multipart/form-data" })) { <div class="form-horizontal"> <div class="modal-body"> @Html.AntiForgeryToken() <div class="form-group"> <div class="col-md-2"> <div class="label-wrapper"> <label class="control-label"> @T("Admin.Common.ExcelFile") </label> </div> </div> <div class="col-md-10"> <input type="file" id="importexcelfile" name="importexcelfile" class="form-control" /> </div> </div> </div> <div class="modal-footer"> <button type="submit" class="btn btn-primary"> @T("Admin.Common.ImportFromExcel") </button> </div> </div> } </div> </div> </div>
3.在控制器端添加导入Excel和导出Excel方法：
（1）导入Excel：
public ActionResult ImportExcel() { try { var file = Request.Files["importexcelfile"]; if (file != null && file.ContentLength > 0) { _importManager.ImportGradeMessageFromXlsx(file.InputStream); } else { ErrorNotification(_localizationService.GetResource("Admin.Common.UploadFile")); return RedirectToAction("List"); } SuccessNotification(_localizationService.GetResource("导入成功")); return RedirectToAction("List"); } catch (Exception exc) { ErrorNotification(exc); return RedirectToAction("List"); } }
这里面
_importManager.ImportGradeMessageFromXlsx(file.InputStream)
中_importManager是实例化接口并调用接口中的方法ImportFamiliesFromXlsx（）
添加成员变量_importManager：private readonly IImportManager _importManager;
接口IImportManager ：
public partial interface IImportManager { /// <summary> /// Import products from XLSX file /// </summary> /// <param name="stream">Stream</param> void ImportGradeMessageFromXlsx(Stream stream); }
接口方法的实现：
public void ImportGradeMessageFromXlsx(Stream stream) { { using (var xlPackage = new ExcelPackage(stream)) { //得到第一个表的工作簿 var worksheet = xlPackage.Workbook.Worksheets.FirstOrDefault(); if (worksheet == null) throw new NopException("No worksheet found"); //列的属性 var properties = new[] { "参赛者姓名", }; int iRow = 2;//行 while (true) { bool allColumnsAreEmpty = true; if (worksheet.Cells[iRow, 2].Value != null && !String.IsNullOrEmpty(worksheet.Cells[iRow, 2].Value.ToString())) { allColumnsAreEmpty = false; } if (allColumnsAreEmpty) break; string CompetitorName = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "参赛者姓名")].Value); string EventName = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "赛事信息")].Value);//外键对应的名称 var gradeMessage = _gradeMessageService.GetAllGradeMessages().FirstOrDefault(f => f.CompetitorDocumentNumber == CompetitorDocumentNumber);//通过证件好查询所用的信息 bool newGradeMessage = false; if (gradeMessage == null) { gradeMessage = new GradeMessage(); newGradeMessage = true; } gradeMessage.CompetitorName = CompetitorName;//此处在进行相应的属性值最好与之前设置列属性的顺序一致，并采用相同的英文名称 gradeMessage.GradeReleaseTime = DateTime.ParseExact(GradeReleaseTime, "yyyy-MM-dd", System.Globalization.CultureInfo.CurrentCulture);//将string转成DateTime gradeMessage.GradeJudge = GradeJudge; gradeMessage.EventId = _eventSystemMessageService.GetAllEventSystemMessages().FirstOrDefault(x => x.EventName == EventName).Id;//通过外键表的名称并查询处外键Id if (newGradeMessage) { _gradeMessageService.InsertGradeMessage(gradeMessage); } else { _gradeMessageService.UpdateGradeMessage(gradeMessage); } //next gradeMessage iRow++; } } } }
这样就可以通过Excel表格数据进行数据的导入了
注意：对于”可为空“的导入时需要判断（if-else）否则会出现”未将对象设置到对象实例“错误：例如
if (_groupRegistratorMessageService.GetAllGroupRegistratorMessages().FirstOrDefault(x => x.GroupName == GroupName)!=null) { registratorMessage.GroupId = _groupRegistratorMessageService.GetAllGroupRegistratorMessages().FirstOrDefault(x => x.GroupName == GroupName).Id; } else { registratorMessage.GroupId = null; }
（2）将数据导出到Excel表格：
进行导出时与进行导入时的步骤大庭相径,不同的是一些接口的调用
(2.1)导出全部
[HttpPost, ActionName("List")] [FormValueRequired("exportexcel-all")] public ActionResult ExportExcelAll() { var gradeMessages = _gradeMessageService.GetAllGradeMessages().ToList(); try { var bytes = _exportManager.ExportGradeMessagesToXlsx(gradeMessages); return File(bytes, MimeTypes.TextXlsx, "gradeMessages.xlsx"); } catch (Exception exc) { ErrorNotification(exc); return RedirectToAction("List"); } }
同样的这里面的
_exportManager.ExportGradeMessagesToXlsx(gradeMessages)
是实现导出Excel方法的接口并调用方法ExportFamiliesToXlsx()
添加成员变量_exportManager：private readonly IExportManager _exportManager;
IExportManager 接口：
public partial interface IExportManager { /// <summary> /// Export GradeMessage list to XLSX /// </summary> /// <param name="stream">Stream</param> /// <param name="families">Customers</param> byte[] ExportGradeMessagesToXlsx(IEnumerable<GradeMessage> GradeMessages);;//这里的GradeMessage是对应数据库的类
}
IExportManager 接口实现：
public byte[] ExportGradeMessagesToXlsx(IEnumerable<GradeMessage> GradeMessages) { var properties = new[] { new PropertyByName<GradeMessage>("参赛者姓名", p => p.CompetitorName),
new PropertyByName<GradeMessage>("赛事信息", p=>GetGradeNameById(p.EventId)),//注意这里是不允许多层查询的只好通过自定义方法来传参查询 }; return ExportToXlsx(properties, GradeMessages); } /// <summary> /// 获取赛事系统名称（通过id找到名称） /// </summary> /// <param name="id"></param> /// <returns></returns> private string GetGradeNameById(int id) { return _eventSystemMessageService.GetEventSystemMessageById(id).EventName; }
这样就可以完成功能了。
（2.2）导出（所选）
控制器中的方法：
[HttpPost] public ActionResult ExportExcelSelected(string selectedIds) { var gradeMessages = new List<GradeMessage>(); if (selectedIds != null) { var ids = selectedIds .Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries) .Select(x => Convert.ToInt32(x)) .ToArray(); gradeMessages.AddRange(_gradeMessageService.GetGradeMessagesByIdCollection(ids)); } try { byte[] bytes = _exportManager.ExportGradeMessagesToXlsx(gradeMessages); return File(bytes, MimeTypes.TextXlsx, "gradeMessages.xlsx"); } catch (Exception exc) { ErrorNotification(exc); return RedirectToAction("List"); } }
注意，这里是导出接口和实现方法和导出全部的一样。
视图端通过点击事件请求数据
@using (Html.BeginForm("ExportExcelSelected", "GradeMessage", FormMethod.Post, new { id = "export-excel-selected-form" })) { @Html.AntiForgeryToken() <input type="hidden" id="selectedIds" name="selectedIds" value="" /> } <script type="text/javascript"> $(document).ready(function () { $('#exportexcel-selected').click(function (e) { e.preventDefault(); var ids = selectedIds.join(","); $('#export-excel-selected-form #selectedIds').val(ids); $('#export-excel-selected-form').submit(); return false; }); }); </script>
这样就完成导入导出来。
As you wish.
