码农公社 210.net.cn 210是何含义？10月24日是程序员节，1024 = 210、210既 210 之意。
C#_.NetFramework_Web项目_EXCEL数据导入、导出。C#_.NetFramework_Web项目会涉及到对excel的操作，给大家分享导入导出的C#代码。
导入
需要引用NPOI的Nuget包：NPOI-v2.4.1
/// <summary>
/// EXCEL帮助类
/// </summary>
/// <typeparam name="T">泛型类</typeparam>
/// <typeparam name="TCollection">泛型类集合</typeparam>
public class ExcelHelp<T, TCollection> where T : new() where TCollection : List<T>, new()
{
//http请求Request对象
public static HttpRequest baseRequest = HttpContext.Current.Request;
//http请求Response对象
public static HttpResponse baseResponse = HttpContext.Current.Response;
/// <summary>
/// 将数据导出EXCEL
/// </summary>
/// <param name="columnNameAndShowNameDic">列名+显示名</param>
/// <param name="tColl">数据集（tColl里的类属性名必须和字典中的列名一致）</param>
public static void ExportExcelData(Dictionary<string, string> columnNameAndShowNameDic, TCollection tColl)
{
IWorkbook workbook = new HSSFWorkbook();
ISheet worksheet = workbook.CreateSheet("sheet1");
List<string> columnNameList = columnNameAndShowNameDic.Keys.ToList();
List<string> showNameList = columnNameAndShowNameDic.Values.ToList();
//设置首列显示
IRow row1 = worksheet.GetRow(0);
ICell cell = null;
for (var i = 0; i < columnNameList.Count; i++)
{
cell = row1.CreateCell(i);
cell.SetCellValue(columnNameList[i]);
}
Dictionary<int, PropertyInfo> indexPropertyDic = GetIndexPropertyDic(columnNameList);
for (int i = 0; i < tColl.Count; i++)
{
row1 = worksheet.GetRow(i + 1);
for (int j = 0; j < indexPropertyDic.Count; j++)
{
cell = row1.CreateCell(i);
cell.SetCellValue(indexPropertyDic[j].GetValue(tColl[i]).ToString());
}
}
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
byte[] buffer = ms.GetBuffer();
baseResponse.Clear();
baseResponse.Buffer = true;
baseResponse.ContentEncoding = System.Text.Encoding.UTF8;
//baseResponse.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
baseResponse.ContentType = "application/vnd.ms-excel";
//设置导出文件名
baseResponse.AddHeader("content-disposition", "attachment; filename=" + "MaintainReport" + ".xlsx");
baseResponse.AddHeader("Content-Length", buffer.Length.ToString());
baseResponse.BinaryWrite(buffer);
baseResponse.Flush();
baseResponse.End();
}
/// <summary>
/// 根据属性名顺序获取对应的属性对象
/// </summary>
/// <param name="fieldNameList"></param>
/// <returns></returns>
private static Dictionary<int, PropertyInfo> GetIndexPropertyDic(List<string> fieldNameList)
{
Dictionary<int, PropertyInfo> indexPropertyDic = new Dictionary<int, PropertyInfo>(fieldNameList.Count);
List<PropertyInfo> tPropertyInfoList = typeof(T).GetProperties().ToList();
PropertyInfo propertyInfo = null;
for (int i = 0; i < fieldNameList.Count; i++)
{
propertyInfo = tPropertyInfoList.Find(m => m.Name.Equals(fieldNameList[i], StringComparison.OrdinalIgnoreCase));
indexPropertyDic.Add(i, propertyInfo);
}
return indexPropertyDic;
}
}
导出
项目需要引用NPOI的Nuget包：DotNetCore.NPOI-v1.2.2
A-前端触发下载Excel的方法有三种：
1-JS-Url跳转请求-后台需要返回文件流数据：
window.Location.href = "/ajax/toolhelper.js?action=reBuyExport&beginTime=" + beginTime + "&endTime=" + endTime;
2-使用form+iframe请求文件流-后台需要返回文件流数据：
<form target="downLoadIframe" method="post" action="/ajax/toolhelper.js?action=reBuyExport">
<div class="form-group">
<label for="datetime">beginTime:</label>
<input type="date" class="form-control" name="beginTime" placeholder="Enter beginTime" />
</div>
<div class="form-group">
<label for="datetime">endTime:</label>
<input type="date" class="form-control" name="endTime" placeholder="Enter endTime">
</div>
<button type="submit" class="btn btn-primary" id="btnExport">导出Excel</button>
</form>
<iframe id="downLoadIframe" name="downLoadIframe" style="display:none;"></iframe>
3-JS-Fetch请求使用Blob保存二进制文件流数据，通过A标签下载流文件-后台需要返回文件流数据：
领导推荐这种方法，经过检验的，可以应对大文件下载的超时问题
fetch(url).then(function (res) {
res.blob().then(function (blob) {
var a = document.createElement('a');
var url = window.URL.createObjectURL(blob);
a.href = url;
a.download = fileName;
a.click();
window.URL.revokeObjectURL(url);
});
});
B-后台返回流数据：
Core下的Excel帮助类
Core的中间件请求方法：
TBDataHelper为提前注入的数据库帮助类，需要改成自己的数据请求类；
自定义的导出文件名，不能输入中文，暂时还没有找到解决办法；
BaseMiddleware为基类，切记基类中只能存常态化的数据，如：下一中间件，配置，缓存。不能存放Request，Response等！！！