项目需要引用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帮助类
/// <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[i]);
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[j].propertytype == typeof(int)
|| indexpropertydic[j].propertytype == typeof(decimal)
|| indexpropertydic[j].propertytype == typeof(double))
{
cell.setcellvalue(convert.todouble(indexpropertydic[j].getvalue(tlist[i])));
}
else if (indexpropertydic[j].propertytype == typeof(datetime))
{
cell.setcellvalue(convert.todatetime(indexpropertydic[j].getvalue(tlist[i]).tostring()));
}
else if (indexpropertydic[j].propertytype == typeof(bool))
{
cell.setcellvalue(convert.toboolean(indexpropertydic[j].getvalue(tlist[i]).tostring()));
}
else
{
cell.setcellvalue(indexpropertydic[j].getvalue(tlist[i]).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[i], stringcomparison.ordinalignorecase));
indexpropertydic.add(i, propertyinfo);
}
return indexpropertydic;
}
}
core的中间件请求方法：
tbdatahelper为提前注入的数据库帮助类，需要改成自己的数据请求类；
自定义的导出文件名，不能输入中文，暂时还没有找到解决办法；
basemiddleware为基类，切记基类中只能存常态化的数据，如：下一中间件，配置，缓存。不能存放request，response等！！！
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;
}
}
}
/// <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;
}
}
}
}
黄山市民网：https://www.huangshanshimin.com/
