ASPNET 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


    }