欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-06-10 16:05:58
项目需要引用NPOI的Nuget包:DotNetCore.NPOI-v1.2.2 本篇文章是对WebAPI项目使用NPOI操作Excel时的帮助类:ExcelHelper的改进优化做下记录: 备注:下面的帮助类代码使用的文件格式为:xlsx文件,xlsx相对xls的优缺点代码里有注释,推荐使用xls ......

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

    }
}