在做系统的时候，很多时候信息量太大，这时候就需要进行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 > )
{
_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 = ;//行
while (true)
{
bool allColumnsAreEmpty = true;
if (worksheet.Cells[iRow, ].Value != null && !String.IsNullOrEmpty(worksheet.Cells[iRow, ].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>
这样就完成导入导出来。