C#_.NetCore_Web项目_EXCEL数据导出

项目需要引用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;
                }
            }
        }

    }