C#_.NetCore_WebAPI项目_EXCEL数据导出（ExcelHelper_第二版_优化逻辑）

项目需要引用npoi的nuget包：dotnetcore.npoi-v1.2.2

 

本篇文章是对webapi项目使用npoi操作excel时的帮助类：excelhelper的改进优化做下记录：

备注：下面的帮助类代码使用的文件格式为：xlsx文件，xlsx相对xls的优缺点代码里有注释，推荐使用xlsx文件保存数据！

 

using microsoft.aspnetcore.mvc;
using microsoft.extensions.logging;
using microsoft.net.http.headers;
using npoi.ss.usermodel;
using npoi.xssf.usermodel;
using system;
using system.collections.generic;
using system.io;
using system.linq;
using system.reflection;

namespace paymentaccountapi.helper
{
    /// <summary>
    /// excel帮助类
    /// </summary>
    /// <typeparam name="t">泛型类</typeparam>
    /// <typeparam name="tcollection">泛型类集合</typeparam>
    public class excelhelp
    {
        private ilogger logger = null;

        public excelhelp(ilogger<excelhelp> logger)
        {
            this.logger = logger;
        }

        /// <summary>
        /// 将数据导出excel
        /// </summary>
        /// <param name="tlist">要导出的数据集</param>
        /// <param name="fieldnameandshownamedic">键值对集合（键：字段名，值：显示名称）</param>
        /// <param name="filedirectorypath">文件路径</param>
        /// <param name="excelname">文件名（必须是英文或数字）</param>
        /// <returns></returns>
        public iworkbook createorupdateworkbook<t>(list<t> tlist, dictionary<string, string> fieldnameandshownamedic, iworkbook workbook = null, string sheetname = "sheet1") where t : new()
        {
            //xls文件格式属于老版本文件，一个sheet最多保存65536行；而xlsx属于新版文件类型；
            //excel 07 - 2003一个工作表最多可有65536行，行用数字1—65536表示; 最多可有256列，列用英文字母a—z，aa—az，ba—bz，……，ia—iv表示；一个工作簿中最多含有255个工作表，默认情况下是三个工作表；
            //excel 2007及以后版本，一个工作表最多可有1048576行，16384列；
            if (workbook == null)
            {
                workbook = new xssfworkbook();
                //workbook = new hssfworkbook();
            }
            isheet worksheet = workbook.createsheet(sheetname);

            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);
            int cloumncount = columnnamelist.count;
            for (var i = 0; i < cloumncount; i++)
            {
                cell = row1.createcell(i);
                cell.setcellvalue(columnnamelist[i]);
                cell.cellstyle = cellheadstyle;
            }

            //根据反射创建其他行数据
            var raws = tlist.count;
            dictionary<string, propertyinfo> titlepropertydic = this.getindexpropertydic<t>(fieldnameandshownamedic);

            propertyinfo propertyinfo = null;
            t t = default(t);
            for (int i = 0; i < raws; i++)
            {
                if (i % 10000 == 0)
                {
                    this.logger.loginformation($"excel已创建{i + 1}条数据");
                }
                row1 = worksheet.createrow(i + 1);
                t = tlist[i];

                int cellindex = 0;
                foreach (var titlepropertyitem in titlepropertydic)
                {
                    propertyinfo = titlepropertyitem.value;
                    cell = row1.createcell(cellindex);

                    if (propertyinfo.propertytype == typeof(int)
                        || propertyinfo.propertytype == typeof(decimal)
                        || propertyinfo.propertytype == typeof(double))
                    {
                        cell.setcellvalue(convert.todouble(propertyinfo.getvalue(t) ?? 0));
                    }
                    else if (propertyinfo.propertytype == typeof(datetime))
                    {
                        cell.setcellvalue(convert.todatetime(propertyinfo.getvalue(t)?.tostring()).tostring("yyyy-mm-dd hh:mm:ss"));
                    }
                    else if (propertyinfo.propertytype == typeof(bool))
                    {
                        cell.setcellvalue(convert.toboolean(propertyinfo.getvalue(t).tostring()));
                    }
                    else
                    {
                        cell.setcellvalue(propertyinfo.getvalue(t)?.tostring() ?? "");
                    }
                    cellindex++;
                }

                //重要：设置行宽度自适应(大批量添加数据时，该行代码需要注释，否则会极大减缓excel添加行的速度！)
                //worksheet.autosizecolumn(i, true);
            }

            return workbook;
        }

        /// <summary>
        /// 保存workbook数据为文件
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="filedirectorypath"></param>
        /// <param name="filename"></param>
        public void saveworkbooktofile(iworkbook workbook, string filedirectorypath, string filename)
        {
            //xls文件格式属于老版本文件，一个sheet最多保存65536行；而xlsx属于新版文件类型；
            //excel 07 - 2003一个工作表最多可有65536行，行用数字1—65536表示; 最多可有256列，列用英文字母a—z，aa—az，ba—bz，……，ia—iv表示；一个工作簿中最多含有255个工作表，默认情况下是三个工作表；
            //excel 2007及以后版本，一个工作表最多可有1048576行，16384列；

            memorystream ms = new memorystream();
            //这句代码非常重要，如果不加，会报：打开的excel格式与扩展名指定的格式不一致
            ms.seek(0, seekorigin.begin);
            workbook.write(ms);
            byte[] mybytearray = ms.getbuffer();

            filedirectorypath = filedirectorypath.trimend('\\') + "\\";
            if (!directory.exists(filedirectorypath))
            {
                directory.createdirectory(filedirectorypath);
            }

            string filepath = filedirectorypath + filename;
            if (file.exists(filepath))
            {
                file.delete(filepath);
            }
            file.writeallbytes(filepath, mybytearray);
        }

        /// <summary>
        /// 保存workbook数据为下载文件
        /// </summary>
        public filecontentresult saveworkbooktodownloadfile(iworkbook workbook)
        {
            memorystream ms = new memorystream();
            //这句代码非常重要，如果不加，会报：打开的excel格式与扩展名指定的格式不一致
            ms.seek(0, seekorigin.begin);
            workbook.write(ms);
            byte[] mybytearray = ms.getbuffer();

            //对于.xls文件
            //application/vnd.ms-excel
            //用于.xlsx文件。
            //application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
            mediatypeheadervalue mediatype = new mediatypeheadervalue("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            mediatype.encoding = system.text.encoding.utf8;

            return new filecontentresult(mybytearray, mediatype.tostring());
        }


        /// <summary>
        /// 读取excel数据
        /// </summary>
        /// <param name="filepath"></param>
        /// <param name="fieldnameandshownamedic"></param>
        /// <returns></returns>
        public list<t> readdatalist<t>(string filepath, dictionary<string, string> fieldnameandshownamedic) where t : new()
        {
            list<t> tlist = null;
            t t = default(t);

            //标题属性字典列表
            dictionary<string, propertyinfo> titlepropertydic = this.getindexpropertydic<t>(fieldnameandshownamedic);
            //标题下标列表
            dictionary<string, int> titleindexdic = new dictionary<string, int>(0);

            propertyinfo propertyinfo = null;

            using (filestream filestream = new filestream(filepath, filemode.open, fileaccess.read))
            {
                iworkbook xssfworkbook = new xssfworkbook(filestream);
                var sheet = xssfworkbook.getsheetat(0);

                var rows = sheet.getrowenumerator();
                tlist = new list<t>(sheet.lastrownum + 1);
                //第一行数据为标题，
                if (rows.movenext())
                {
                    irow row = (xssfrow)rows.current;
                    icell cell = null;
                    string cellvalue = null;
                    for (int i = 0; i < row.cells.count; i++)
                    {
                        cell = row.cells[i];
                        cellvalue = cell.stringcellvalue;
                        if (titlepropertydic.containskey(cellvalue))
                        {
                            titleindexdic.add(cellvalue, i);
                        }
                    }
                }
                //从第2行数据开始获取
                while (rows.movenext())
                {
                    irow row = (xssfrow)rows.current;
                    t = new t();

                    foreach (var titleindexitem in titleindexdic)
                    {
                        var cell = row.getcell(titleindexitem.value);
                        if (cell != null)
                        {
                            propertyinfo = titlepropertydic[titleindexitem.key];
                            if (propertyinfo.propertytype == typeof(int))
                            {
                                propertyinfo.setvalue(t, convert.toint32(cell.numericcellvalue));
                            }
                            else if (propertyinfo.propertytype == typeof(decimal))
                            {
                                propertyinfo.setvalue(t, convert.todecimal(cell.numericcellvalue));
                            }
                            else if (propertyinfo.propertytype == typeof(double))
                            {
                                propertyinfo.setvalue(t, convert.todouble(cell.numericcellvalue));
                            }
                            else if (propertyinfo.propertytype == typeof(bool))
                            {
                                propertyinfo.setvalue(t, convert.toboolean(cell.stringcellvalue));
                            }
                            else if (propertyinfo.propertytype == typeof(datetime))
                            {
                                propertyinfo.setvalue(t, convert.todatetime(cell.stringcellvalue));
                            }
                            else
                            {
                                propertyinfo.setvalue(t, cell.stringcellvalue);
                            }

                        }
                    }
                    tlist.add(t);
                }
            }
            return tlist ?? new list<t>(0);
        }

        /// <summary>
        /// 根据属性名顺序获取对应的属性对象
        /// </summary>
        /// <param name="fieldnamelist"></param>
        /// <returns></returns>
        private dictionary<string, propertyinfo> getindexpropertydic<t>(dictionary<string, string> fieldnameandshownamedic)
        {
            dictionary<string, propertyinfo> titlepropertydic = new dictionary<string, propertyinfo>(fieldnameandshownamedic.count);

            list<propertyinfo> tpropertyinfolist = typeof(t).getproperties().tolist();
            propertyinfo propertyinfo = null;

            foreach (var item in fieldnameandshownamedic)
            {
                propertyinfo = tpropertyinfolist.find(m => m.name.equals(item.key, stringcomparison.ordinalignorecase));
                titlepropertydic.add(item.value, propertyinfo);
            }
            return titlepropertydic;
        }

    }
}