C#_.NetCore_Web项目_EXCEL数据导出
<p><strong>项目须要引用NPOI的Nuget包：DotNetCore.NPOI-v1.2.2</strong></p><p> </p>
<p><strong>A-前端触发下载Excel的方法有三种：</strong></p>
<p> <strong>1-JS-Url跳转请求-背景须要返回文件流数据：</strong></p>
window.Location.href = "/ajax/toolhelper.js?action=reBuyExport&beginTime=" + beginTime + "&endTime=" + endTime;
<p> <strong>2-使用form+iframe请求文件流-背景须要返回文件流数据：</strong></p>
<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>
<p> <strong>3-JS-Fetch请求使用Blob生存二进制文件流数据，通过A标签下载流文件-背景须要返回文件流数据：</strong></p>
<p> 向导推荐这种方法，颠末查验的，可以应对大文件下载的超时标题</p>
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);
});
});
<p> </p>
<p><strong>B-背景返回流数据：</strong></p>
<p> </p>
<p><strong>Core下的Excel资助类</strong></p>
<div align="center"></div><div align="center"></div>
/// <summary>
/// EXCEL资助类
/// </summary>
/// <typeparam name="T">泛型类</typeparam>
/// <typeparam name="TCollection">泛型类聚集</typeparam>
public class ExcelHelp<T, TCollection> where TCollection : List<T> where T : new()
{
public static ExcelHelp<T, TCollection> INSTANCE = new ExcelHelp<T, TCollection>();
//获取httpResponse对象原位置，放在这里不知道会报错：服务器无法在发送 HTTP 标头之后追加标头
//大概是这里拿到的httpResponse对象不是最新请求的对象导致的，将其放到方法内部即可
//HttpResponse baseResponse = HttpContext.Current.Response;
/// <summary>
/// 将数据导出EXCEL
/// </summary>
/// <param name="tList">要导出的数据集</param>
/// <param name="fieldNameAndShowNameDic">键值对聚集（键：字段名，值：表现名称）</param>
/// <param name="httpResponse">相应</param>
/// <param name="excelName">文件名（必须是英文或数字）</param>
/// <returns></returns>
public async Task ExportExcelData(TCollection tList, Dictionary<string, string> fieldNameAndShowNameDic, HttpResponse httpResponse, string excelName = "exportResult")
{
IWorkbook workbook = new HSSFWorkbook();
ISheet worksheet = workbook.CreateSheet("sheet1");
List<string> columnNameList = fieldNameAndShowNameDic.Values.ToList();
//设置首列表现
IRow row1 = worksheet.CreateRow(0);
ICell cell = null;
ICellStyle cellHeadStyle = workbook.CreateCellStyle();
//设置首行字体加粗
IFont font = workbook.CreateFont();
font.Boldweight = short.MaxValue;
cellHeadStyle.SetFont(font);
for (var i = 0; i < columnNameList.Count; i++)
{
cell = row1.CreateCell(i);
cell.SetCellValue(columnNameList);
cell.CellStyle = cellHeadStyle;
}
//根据反射创建其他行数据
var raws = tList.Count;
Dictionary<int, PropertyInfo> indexPropertyDic = this.GetIndexPropertyDic(fieldNameAndShowNameDic.Keys.ToList());
for (int i = 0; i < raws; i++)
{
row1 = worksheet.CreateRow(i + 1);
for (int j = 0; j < fieldNameAndShowNameDic.Count; j++)
{
cell = row1.CreateCell(j);
if (indexPropertyDic.PropertyType == typeof(int)
|| indexPropertyDic.PropertyType == typeof(decimal)
|| indexPropertyDic.PropertyType == typeof(double))
{
cell.SetCellValue(Convert.ToDouble(indexPropertyDic.GetValue(tList)));
}
else if (indexPropertyDic.PropertyType == typeof(DateTime))
{
cell.SetCellValue(Convert.ToDateTime(indexPropertyDic.GetValue(tList).ToString()));
}
else if (indexPropertyDic.PropertyType == typeof(bool))
{
cell.SetCellValue(Convert.ToBoolean(indexPropertyDic.GetValue(tList).ToString()));
}
else
{
cell.SetCellValue(indexPropertyDic.GetValue(tList).ToString());
}
}
//设置行宽度自适应
worksheet.AutoSizeColumn(i, true);
}
MediaTypeHeaderValue mediaType = new MediaTypeHeaderValue("application/vnd.ms-excel");
mediaType.Encoding = System.Text.Encoding.UTF8;
httpResponse.ContentType = mediaType.ToString();
//设置导出文件名
httpResponse.Headers.Add("content-disposition", $"attachment;filename={excelName}.xls");
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
//这句代码非常告急，假如不加，会报：打开的EXCEL格式与扩展名指定的格式不划一
ms.Seek(0, SeekOrigin.Begin);
byte[] myByteArray = ms.GetBuffer();
httpResponse.Headers.Add("Content-Length", myByteArray.Length.ToString());
await httpResponse.Body.WriteAsync(myByteArray, 0, myByteArray.Length);
}
/// <summary>
/// 根据属性名顺序获取对应的属性对象
/// </summary>
/// <param name="fieldNameList"></param>
/// <returns></returns>
private 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, StringComparison.OrdinalIgnoreCase));
indexPropertyDic.Add(i, propertyInfo);
}
return indexPropertyDic;
}
}
View Code
<p> </p>
<p><strong>Core的中心件请求方法：</strong></p>
<p>TBDataHelper为提前注入的数据库资助类，须要改成本身的数据请求类；</p>
<p>自界说的导出文件名，不能输入中文，临时还没有找到解决办法；</p>
<p>BaseMiddleware为基类，牢记基类中只能存常态化的数据，如：下一中心件，设置，缓存。不能存放Request，Response等！！！</p>
<div align="center"></div><div align="center"></div>
public class ToolHelperMiddleware : BaseMiddleware
{
public TBDataHelper TBDataHelper { get; set; }
public ToolHelperMiddleware(RequestDelegate next, ConfigurationManager configurationManager, IMemoryCache memoryCache, TBDataHelper tBDataHelper) : base(next, configurationManager, memoryCache)
{
this.TBDataHelper = tBDataHelper;
}
public async Task Invoke(HttpContext httpContext)
{
var query = httpContext.Request.Query;
var queryAction = query["action"];
switch (queryAction)
{
case "reBuyExport":
await this.ReBuyExport(httpContext);
break;
}
}
/// <summary>
/// 复购数据导出
/// </summary>
/// <param name="httpContext"></param>
/// <returns></returns>
private async Task ReBuyExport(HttpContext httpContext)
{
var request = httpContext.Request;
var response = httpContext.Response;
var requestForm = request.Form;
try
{
DateTime beginTime = Convert.ToDateTime(requestForm["beginTime"]);
DateTime endTime = Convert.ToDateTime(requestForm["endTime"]);
List<RebuyModel> rebuyModelList = this.TBDataHelper.SelectReBuyList(beginTime, endTime);
Dictionary<string, string> fieldNameAndShowNameDic = new Dictionary<string, string>(0);
fieldNameAndShowNameDic.Add("UserID", "用户ID");
fieldNameAndShowNameDic.Add("PayCount", "付出数");
fieldNameAndShowNameDic.Add("BeforeBuyCount", beginTime.ToString("MM/dd") + "之前付出数");
string fileName = $"{beginTime.ToString("MMdd")}_{endTime.ToString("MMdd")}ReBuyExport_{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}";
await ExcelHelp<RebuyModel, List<RebuyModel>>.INSTANCE.ExportExcelData(rebuyModelList, fieldNameAndShowNameDic, response, fileName);
}
catch (Exception e)
{
throw e;
}
}
}
View Code
<div align="center"></div><div align="center"></div>
/// <summary>
/// 中心件基类
/// </summary>
public abstract class BaseMiddleware
{
/// <summary>
/// 等同于ASP.NET内里的WebCache(HttpRuntime.Cache)
/// </summary>
protected IMemoryCache MemoryCache { get; set; }
/// <summary>
/// 获取设置文件内里的设置内容
/// </summary>
protected ConfigurationManager ConfigurationManager { get; set; }
/// <summary>
/// 下一个中心件
/// </summary>
protected RequestDelegate Next { get; set; }
public BaseMiddleware(RequestDelegate next, params object[] @params)
{
this.Next = next;
foreach (var item in @params)
{
if (item is IMemoryCache)
{
this.MemoryCache = (IMemoryCache)item;
}
else if (item is ConfigurationManager)
{
this.ConfigurationManager = (ConfigurationManager)item;
}
}
}
}
View Code
<p> </p>
页:
[1]