net core WebApi——使用NPOI导入导出操作

目录

• npoi

@

前言

时间过得好快，在之前升级到3.0之后，就感觉好久没再动过啥东西了，之前有问到swagger的中文汉化，虽说我觉得这种操作的意义不是太大，也是多少鼓捣了下，其实个人感觉就是元素内容替换，既然可以执行js了那不就是网页上随便搞了，所以就没往下再折腾，但是现在需要用到excel的操作了，那就不得不提起这个npoi了。

npoi

在之前.net framework的时候，工程需要用到excel的导入导出，当然用这个npoi是偶然，也就是找了下这个看着可以就用了，之后遇到的各种问题也都找资料解决，什么多行合并啊，打开2007版本错误啊之类的，但是不得不说，用着还挺好，所以既然net core需要了，那就看看呗，刚好也是支持的。

在util我们来引入这个类库npoi。

• 导入

在使用之前，我们先缕一下获取excel数据需要哪些准备操作。

1. 获取文件（这个就不多说）
2. 获取sheet信息（考虑有可能多sheet操作）
3. 根据sheet获取对应文件信息（多少行，当然有些还有合并）
4. 根据合并行来判断第一行是否为标题
5. 判断哪一行是列名（用于对应数据）
6. 遍历每一行并根据每一行的数据格式来获取（有可能是公式/日期/数字/普通文本等等）

ok，大致上清楚了之后，我们就一步步来看吧，这里我创建一个excelutil，来写第一个方法（这里只做说明展示吧）。

    public class excelutil
    {
        /// <summary>
        /// 读取excel多sheet数据
        /// </summary>
        /// <param name="filepath">文件路径</param>
        /// <param name="sheetname">sheet名</param>
        /// <returns></returns>
        public static dataset readexceltodataset(string filepath, string sheetname = null)
        {
            if (!file.exists(filepath))
            {
                logutil.debug($"未找到文件{filepath}");
                return null;
            }
            //获取文件信息
            filestream fs = new filestream(filepath, filemode.open, fileaccess.read);
            iworkbook workbook = workbookfactory.create(fs);
            //获取sheet信息
            isheet sheet = null;
            dataset ds = new dataset();
            if (!string.isnullorempty(sheetname))
            {
                sheet = workbook.getsheet(sheetname);
                if (sheet == null)
                {
                    logutil.debug($"{filepath}未找到sheet:{sheetname}");
                    return null;
                }
                datatable dt = readexcelfunc(workbook, sheet);
                ds.tables.add(dt);
            }
            else
            {
                //遍历获取所有数据
                int sheetcount = workbook.numberofsheets;
                for (int i = 0; i < sheetcount; i++) {
                    sheet = workbook.getsheetat(i);
                    if (sheet != null)
                    {
                        datatable dt = readexcelfunc(workbook, sheet);
                        ds.tables.add(dt);
                    }
                }
            }
            return ds;
        }

        /// <summary>
        /// 读取excel信息
        /// </summary>
        /// <param name="workbook">工作区</param>
        /// <param name="sheet">sheet</param>
        /// <returns></returns>
        private static datatable readexcelfunc(iworkbook workbook, isheet sheet)
        {
            datatable dt = new datatable();
            //获取列信息
            irow cells = sheet.getrow(sheet.firstrownum);
            int cellscount = cells.physicalnumberofcells;
            int emptycount = 0;
            int cellindex = sheet.firstrownum;
            list<string> listcolumns = new list<string>();
            bool isfindcolumn = false;
            while (!isfindcolumn)
            {
                emptycount = 0;
                listcolumns.clear();
                for (int i = 0; i < cellscount; i++)
                {
                    if (string.isnullorempty(cells.getcell(i).stringcellvalue))
                    {
                        emptycount++;
                    }
                    listcolumns.add(cells.getcell(i).stringcellvalue);
                }
                //这里根据逻辑需要，空列超过多少判断
                if (emptycount == 0)
                {
                    isfindcolumn = true;
                }
                cellindex++;
                cells = sheet.getrow(cellindex);
            }

            foreach (string columnname in listcolumns)
            {
                if (dt.columns.contains(columnname))
                {
                    //如果允许有重复列名，自己做处理
                    continue;
                }
                dt.columns.add(columnname, typeof(string));
            }
            //开始获取数据
            int rowscount = sheet.physicalnumberofrows;
            cellindex += 1;
            datarow dr = null;
            for (int i = cellindex; i < rowscount; i++) {
                cells = sheet.getrow(i);
                dr = dt.newrow();
                for (int j = 0; j < dt.columns.count; j++)
                {
                    //这里可以判断数据类型
                    switch (cells.getcell(j).celltype)
                    {
                        case celltype.string:
                            dr[j] = cells.getcell(j).stringcellvalue;
                            break;
                        case celltype.numeric:
                            dr[j] = cells.getcell(j).numericcellvalue.tostring();
                            break;
                        case celltype.unknown:
                            dr[j] = cells.getcell(j).stringcellvalue;
                            break;
                    }
                }
                dt.rows.add(dr);
            }
            return dt;
        }
    }

文件的导入操作就不再演示了，之前有文件上传的相关操作方法net core webapi——文件分片上传与跨域请求处理。

导入的处理这里也只是大致演示下，具体需要的东西包括情况可能会比较复杂，但是终归数据还是那些，只是操作方法不同罢了（别说什么骚操作）。

• 导出

相对于导入，导出的流程就比较简单了。

1. 获取数据信息（sql或文件）
2. 组成数据集合（list或datatable）
3. 创建sheet
4. 设置相关样式等等
5. 遍历赋值row
6. 导出文件流

了解完，我们就继续来搞吧。

        /// <summary>
        /// 导出excel文件
        /// </summary>
        /// <typeparam name="t">数据类型</typeparam>
        /// <param name="entities">数据实体</param>
        /// <param name="diccolumns">列对应关系,如name->姓名</param>
        /// <param name="title">标题</param>
        /// <returns></returns>
        public static byte[] exportexcel<t>(list<t> entities,dictionary<string,string> diccolumns, string title = null)
        {
            if (entities.count <= 0)
            {
                return null;
            }
            //hssfworkbook => xls
            //xssfworkbook => xlsx
            iworkbook workbook = new xssfworkbook();
            isheet sheet = workbook.createsheet("test");//名称自定义
            irow cellscolumn = null;
            irow cellsdata = null;
            //获取实体属性名
            propertyinfo[] properties = entities[0].gettype().getproperties();
            int cellsindex = 0;
            //标题
            if (!string.isnullorempty(title))
            {
                icellstyle style = workbook.createcellstyle();
                //边框  
                style.borderbottom = borderstyle.dotted;
                style.borderleft = borderstyle.hair;
                style.borderright = borderstyle.hair;
                style.bordertop = borderstyle.dotted;
                //水平对齐  
                style.alignment = horizontalalignment.left;

                //垂直对齐  
                style.verticalalignment = verticalalignment.center;

                //设置字体
                ifont font = workbook.createfont();
                font.fontheightinpoints = 10;
                font.fontname = "微软雅黑";
                style.setfont(font);

                irow cellstitle = sheet.createrow(0);
                cellstitle.createcell(0).setcellvalue(title);
                cellstitle.rowstyle = style;
                //合并单元格
                sheet.addmergedregion(new npoi.ss.util.cellrangeaddress(0, 1, 0, diccolumns.count - 1));
                cellsindex = 2;
            }
            //列名
            cellscolumn = sheet.createrow(cellsindex);
            int index = 0;
            dictionary<string, int> columns = new dictionary<string, int>();
            foreach (var item in diccolumns)
            {
                cellscolumn.createcell(index).setcellvalue(item.value);
                columns.add(item.value, index);
                index++;
            }
            cellsindex += 1;
            //数据
            foreach (var item in entities)
            {
                cellsdata = sheet.createrow(cellsindex);
                for (int i = 0; i < properties.length; i++)
                {
                    if (!diccolumns.containskey(properties[i].name)) continue;
                    //这里可以也根据数据类型做不同的赋值，也可以根据不同的格式参考上面的icellstyle设置不同的样式
                    object[] entityvalues = new object[properties.length];
                    entityvalues[i] = properties[i].getvalue(item);
                    //获取对应列下标
                    index = columns[diccolumns[properties[i].name]];
                    cellsdata.createcell(index).setcellvalue(entityvalues[i].tostring());
                }
                cellsindex++;
            }

            byte[] buffer = null;
            using (memorystream ms = new memorystream())
            {
                workbook.write(ms);
                buffer = ms.getbuffer();
                ms.close();
            }

            return buffer;
        }

测试

写完，免不了一通测试，这里不多说了，直接上图。

导入这里前面也说了没做界面上传什么的，就是一个文件路径，直接执行，excel原文件我也会同步上传到代码仓库。

导出的话，这里也是用swagger神器来测试。

数据库数据如下图。

带标题导出。

小结

最近真的是有点儿忙，一直在鼓捣opengl这类图形化的东西，各种矩阵转换模型转换，要么是用c++，qt写opengl，要么是用threejs搞opengl，唉，整的最近也只能是晚上回去摸索会儿net core，工作总是不那么尽如人意，但是身为程序猿的我们，不都是不断的摸索前进么？我们可以不会，但那不是我们不整的借口。