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

ASPNET npoi帮助类

程序员文章站 2022-03-13 13:34:28
nuget添加npoi ......

nuget添加npoi

    /// <summary>
    /// npoi帮助类
    /// </summary>
    public static class npoihelper
    {
        /// <summary>
        /// 根据文件路径,获取表格集合
        /// </summary>
        /// <param name="filepath"></param>
        /// <returns></returns>
        public static list<datatable> getdatatablelist(string filepath)
        {
            var list = new concurrentbag<datatable>();

            using (var stream = new filestream(filepath, filemode.open, fileaccess.read))
            {
                var isexcel2007 = filepath.isexcel2007();
                var workbook = stream.getworkbook(isexcel2007);
                var sheetindexlist = new list<int>();
                for (int i = 0; i < workbook.numberofsheets; i++) sheetindexlist.add(i);
                parallel.foreach(sheetindexlist, new paralleloptions
                {
                    maxdegreeofparallelism = 3
                }, (source, state, index) =>
                {
                    try
                    {
                        if (!workbook.issheethidden(source))
                            list.add(getdatatabletoy(workbook, source));
                    }
                    catch (npoi.poifs.filesystem.officexmlfileexception nopiex)
                    {
                        console.writeline($"sheetindex:{index}\t\texception:{nopiex.message}");
                    }
                    catch (exception e)
                    {
                        console.writeline(e);
                    }
                });
            }

            return list.tolist();
        }

        /// <summary>
        /// 根据sheet索引,把数据转换为datatable,以y轴为准
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="sheetindex">sheet索引</param>
        /// <param name="validrowindex"></param>
        /// <returns></returns>
        public static datatable getdatatabletoy(iworkbook workbook, int sheetindex, int validrowindex = 0)
        {
            var sheet = workbook.getsheetat(sheetindex);
            var table = new datatable(sheet.sheetname);

            //  设置最大列,默认为1
            var maxcolumnnum = 1;
            //  不是有效列集合,连续超过三行不读取后续所有列
            var novalidcolumnlist = new list<int>();
            //  列:按照列把数据填充到datatable中,防止无限列出现
            for (var columnindex = 0; columnindex < maxcolumnnum; columnindex++)
            {
                var column = new datacolumn();
                table.columns.add(column);
                novalidcolumnlist.add(columnindex);
                //  列中所有数据都是null为true
                var isallempty = true;
                //  行
                for (var rowindex = 0; rowindex < sheet.lastrownum; rowindex++)
                {
                    if (columnindex == 0) table.rows.add(table.newrow());
                    var itemrow = sheet.getrow(rowindex);
                    if (itemrow == null) continue;
                    maxcolumnnum = maxcolumnnum < itemrow.lastcellnum ? itemrow.lastcellnum : maxcolumnnum;
                    //  把格式转换为utf-8
                    var itemcellvalue = itemrow.getvalue(columnindex).formatutf8string();
                    if (!itemcellvalue.isnullorwhitespace()) isallempty = false;
                    table.rows[rowindex][columnindex] = itemcellvalue;
                }

                //  当前列有值
                if (!isallempty)
                    novalidcolumnlist.clear();
                //  连续空白列超过三行 或 有空白行且当前行为最后一行
                else if (novalidcolumnlist.count > 3 || (novalidcolumnlist.count > 0 && columnindex == maxcolumnnum - 1))
                {
                    for (var i = novalidcolumnlist.count - 1; i >= 0; i--)
                        table.columns.removeat(novalidcolumnlist[i]);
                    break;
                }
            }
            // 得到一个sheet中有多少个合并单元格
            int sheetmergecount = sheet.nummergedregions;
            for (var i = 0; i < sheetmergecount; i++)
            {
                // 获取合并后的单元格
                var range = sheet.getmergedregion(i);
                sheet.ismergedregion(range);
                var cellvalue = string.empty;
                for (var mrowindex = range.firstrow; mrowindex <= range.lastrow; mrowindex++)
                {
                    for (var mcolumnindex = range.firstcolumn; mcolumnindex <= range.lastcolumn; mcolumnindex++)
                    {
                        var itemcellvalue = table.rows[range.firstrow][range.firstcolumn].formatutf8string();
                        if (!itemcellvalue.isnullorwhitespace())
                            cellvalue = itemcellvalue;
                        table.rows[mrowindex][mcolumnindex] = cellvalue;
                    }
                }
            }

            return table;
        }

        #region 公共方法

        /// <summary>
        /// 判断excel是否是2007版本:.xls
        /// </summary>
        /// <param name="filepath"></param>
        /// <returns></returns>
        public static bool isexcel2007(this string filepath)
        {
            return path.getextension(filepath)?.tolower() == ".xls";
        }

        /// <summary>
        /// 根据版本创建iworkbook对象
        /// </summary>
        /// <param name="stream"></param>
        /// <param name="isexcel2007"></param>
        /// <returns></returns>
        public static iworkbook getworkbook(this stream stream, bool isexcel2007)
        {
            return isexcel2007 ? (iworkbook)new hssfworkbook(stream) : new xssfworkbook(stream);
        }
        /// <summary>
        /// 获取xssfrow的值(全部统一转成字符串)
        /// </summary>
        /// <param name="row"></param>
        /// <param name="index"></param>
        /// <returns></returns>
        public static string getvalue(this irow row, int index)
        {
            var rowcell = row.getcell(index);
            return getvaluebycellstyle(rowcell, rowcell?.celltype);
        }

        /// <summary>
        /// 根据单元格的类型获取单元格的值
        /// </summary>
        /// <param name="rowcell"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public static string getvaluebycellstyle(icell rowcell, celltype? type)
        {
            string value = string.empty;
            switch (type)
            {
                case celltype.string:
                    value = rowcell.stringcellvalue;
                    break;
                case celltype.numeric:
                    if (dateutil.iscellinternaldateformatted(rowcell))
                    {
                        value = datetime.fromoadate(rowcell.numericcellvalue).tostring();
                    }
                    else if (dateutil.iscelldateformatted(rowcell))
                    {
                        value = datetime.fromoadate(rowcell.numericcellvalue).tostring();
                    }
                    //有些情况,时间搓?数字格式化显示为时间,不属于上面两种时间格式
                    else if (rowcell.cellstyle.getdataformatstring() == null)
                    {
                        value = datetime.fromoadate(rowcell.numericcellvalue).tostring();
                    }
                    else if (rowcell.cellstyle.getdataformatstring().contains("$"))
                    {
                        value = "$" + rowcell.numericcellvalue.tostring();
                    }
                    else if (rowcell.cellstyle.getdataformatstring().contains("¥"))
                    {
                        value = "¥" + rowcell.numericcellvalue.tostring();
                    }
                    else if (rowcell.cellstyle.getdataformatstring().contains("¥"))
                    {
                        value = "¥" + rowcell.numericcellvalue.tostring();
                    }
                    else if (rowcell.cellstyle.getdataformatstring().contains("€"))
                    {
                        value = "€" + rowcell.numericcellvalue.tostring();
                    }
                    else
                    {
                        value = rowcell.numericcellvalue.tostring();
                    }
                    break;
                case celltype.boolean:
                    value = rowcell.booleancellvalue.tostring();
                    break;
                case celltype.error:
                    value = erroreval.gettext(rowcell.errorcellvalue);
                    break;
                case celltype.formula:
                    //  todo: 是否存在 嵌套 公式类型
                    value = getvaluebycellstyle(rowcell, rowcell?.cachedformularesulttype);
                    break;
            }
            return value;
        }

        #endregion


    }