fastNpoi 1.0.13

fastNpoi

介绍

对npoi.core进行二次封装,目的是调用更加方便

安装教程

1.nuget 控制台输入

Install-Package fastNpoi -Version 1.0.1

2.nuget 管理程序 搜索fastNpoi

使用说明

1. 依赖注入

serices.AddScoped(typeof(IFastNpoi<>), typeof(FastNpoi<>));

2. 非依赖注入

new FastNpoi<T>()

3. 实体特性声明

public class TestModel
    {
        [Npoi(headerName:"主键",mergeCell:false,position:2)]
        public int Id { get; set; }
        [Npoi(headerName: "名称",position:1)]
        public string? Name { get; set; }
        [Npoi(headerName: "随机数")]
        public int RandomNum { get; set; }
        [Npoi(headerName: "创建日期")]
        public DateTime CreateTime { get; set; } = DateTime.Now;
    }

headerName:对应的表头名称,mergeCell:本条数据和下一条数据的值相同，合并单元格，默认false，position:表头位置，默认999，数值越大越靠后，OperationType：表头类型分为导入、导出以及导入导出,width:单元格长度

4. 代码声明表头和实体的对应关系

var easyNpoi = serviceProvider.GetService<IFastNpoi<TestModel>>();
easyNpoi.InitWorkbook(@"C:\Users\53205\Desktop\test\20220708124735.xlsx").GetSheet()
.HeaderMapper("id",t=>t.Id)//通过表达式声明属性
.HeaderMapper("id","Id");//通过属性的字符串声明

用代码声明的表头映射优先级大于特性标注

5. 简单的导入

var easyNpoi = serviceProvider.GetService<IFastNpoi<TestModel>>();
var list1 = easyNpoi.InitWorkbook(@"C:\Users\53205\Desktop\test\20220708124735.xlsx").GetSheet().ToList();

6. 简单的导出

var list=new List<TestModel>(1000);
for(int i=0;i<1000; i++)
{
    TestModel item = new TestModel()
    {
        Id = i,
        Name = $"创建用的名称{i}",
        RandomNum = new Random().Next()
    };
    list.Add(item);
}
var easyNpoi = serviceProvider.GetService<IFastNpoi<TestModel>>();
easyNpoi.CreateWorkbook().CreateSheet()
    .SetDataList(list)
    .ToFile(@"C:\Users\53205\Desktop\test");

7.设置表头样式

easyNpoi.CreateWorkbook().CreateSheet().SetHeaderStyle(workbook =>
{
    var style= workbook.CreateCellStyle();
    var font = workbook.CreateFont();
    font.FontName = "宋体";
    font.FontHeight = 15;
    font.FontHeightInPoints = 12;
    font.Color = NPOI.HSSF.Util.HSSFColor.Blue.Index;
    style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Red.Index;
    style.FillPattern = FillPattern.SolidForeground;
    style.SetFont(font);
    return style;
});

8. 设置数据样式

easyNpoi.CreateWorkbook().CreateSheet().SetDataStyle(workbook =>
{
    var style= workbook.CreateCellStyle();
    var font = workbook.CreateFont();
    font.FontName = "宋体";
    font.FontHeight = 15;
    font.FontHeightInPoints = 12;
    font.Color = NPOI.HSSF.Util.HSSFColor.Blue.Index;
    style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Red.Index;
    style.FillPattern = FillPattern.SolidForeground;
    style.SetFont(font);
    return style;
});

9.不生成表头，用于读取模板内表头使用并根据模板表头进行赋值

easyNpoi.CreateWorkbook().CreateSheet().DisableHeader();//不生成表头 内部逻辑是先将表头写入 然后读取表头的信息按映射名赋值

10.数据修改 一般用于实体内存在字典映射项

easyNpoi.CreateWorkbook().CreateSheet().DataMapper(t=>t.Id=0)//相当于foreach

11.各种漂移量设置

easyNpoi.CreateWorkbook().CreateSheet()
.SetHeaderIndex(0)//表头所在行的索引 默认为0
.SetColumnStartIndex(0)//第一列所在的索引 默认为0
.SetHeaderSkipNumber(1)//表头和数据列之间的间隔 默认为1
.SetRowSkipNumber(1)//每一行之间的间隔 默认为1
.SetColumnSkipNumber(1)//每一列之间建的间隔 默认为1

12.手动合并单元格(原生api,做了简单封装)

easyNpoi.CreateWorkbook().CreateSheet().MergeCell(int startRowIndex,int endRowIndex,int startColumnIndex,int endColumnIndex);

13.生成标题列

easyNpoi.CreateWorkbook().CreateSheet()
    .SetHeaderIndex(1)//表头向下偏移1行
    .SetDataList(list)
    .SheetMapper(sheet =>
        {
            //对sheet进行追加操作,在第一行第一列加入标题数据，如果还需要别的样式,在这个action里面调用原生Api操作即可
            var row = sheet.CreateRow(0);
            var cell = row.CreateCell(0);
            cell.SetCellValue("测试的标题");
        })
    .MergeCell(0, 0, 0, 4)//第一行的4个列合并成一个单元格
    .ToFile(@"C:\Users\53205\Desktop\test");

14.对单元格添加批注

easyNpoi.CreateWorkbook().CreateSheet().
    .SetCommentVisable(true)//生成的批注默认显示
    .CommentCell(new List<CommentModel>()
    {
        // 声明批注的具体位置
        new CommentModel(){RowIndex=4,ColumnIndex=0,Message="测试一个批注"},
        new CommentModel(){RowIndex=4,ColumnIndex=0,Message="测试一个批注2"},
    })
    .ToFile(@"C:\Users\53205\Desktop\test");

15.取值和赋值实现类替换（默认实现使用反射实现,可自行替换成emit或者表达式树取值赋值提高速度） 首先实现接口

public interface IDataOperation
    {
        object GetValue<T>(T data, PropertyInfo property) where T : class, new();
        void SetValue<T>(T data, PropertyInfo property, object value) where T : class, new();
    }

然后调用

easyNpoi.CreateWorkbook().CreateSheet().
    SetDataOperation(你的实现类)；

16.动态解析json对象,并将其解析成为列 首先实现接口

easyNpoi.CreateWorkbook().CreateSheet()
    .SetJsonMapping(t=>t.JsonStr,new Dictionary<string, string>()
    {
        {"json的姓名","name" },//key为列名,value为json数据内的key
        {"json的value","value" }
    })

接口列表

/// <summary>
        /// 根据文件路径加载一个workbook
        /// </summary>
        /// <param name="path"></param>
        /// <returns></returns>
        IFastNpoi<T> InitWorkbook(string path);
        /// <summary>
        /// 根据文件流加载一个workbook
        /// </summary>
        /// <param name="stream"></param>
        /// <returns></returns>
        IFastNpoi<T> InitWorkbook(Stream stream);
        /// <summary>
        /// 创建一个workbook
        /// </summary>
        /// <returns></returns>
        IFastNpoi<T> CreateWorkbook();
        /// <summary>
        /// 创建一个sheet页
        /// </summary>
        /// <param name="name">sheet页名称</param>
        /// <returns></returns>
        IFastNpoi<T> CreateSheet(string name);
        /// <summary>
        /// 创建一个sheet
        /// </summary>
        /// <returns></returns>
        IFastNpoi<T> CreateSheet();
        /// <summary>
        /// 产生一个完全一样的实现类对象
        /// </summary>
        /// <returns></returns>
        IFastNpoi<T> Clone();
        /// <summary>
        /// 根据索引读取sheet页
        /// </summary>
        /// <param name="index"></param>
        /// <returns></returns>
        IFastNpoi<T> GetSheet(int index = 0);
        /// <summary>
        /// 根据sheet页名称读取sheet页
        /// </summary>
        /// <param name="name">sheet名</param>
        /// <returns></returns>
        IFastNpoi<T> GetSheet(string name);
        /// <summary>
        /// 数据赋值实现类替换
        /// </summary>
        /// <param name="operation">赋值取值实现类</param>
        /// <returns></returns>
        IFastNpoi<T> SetDataOperation(IDataOperation operation);
        /// <summary>
        /// 设置表头索引位置 默认为0
        /// </summary>
        /// <param name="headerIndex"></param>
        /// <returns></returns>
        IFastNpoi<T> SetHeaderIndex(int headerIndex);
        /// <summary>
        /// 设置列起始位置 默认为0
        /// </summary>
        /// <param name="columnStartIndex"></param>
        /// <returns></returns>
        IFastNpoi<T> SetColumnStartIndex(int columnStartIndex);
        /// <summary>
        /// 
        /// </summary>
        /// <param name="offset"></param>
        /// <returns></returns>
        IFastNpoi<T> SetRowSkipNumber(int offset);
        IFastNpoi<T> SetColumnSkipNumber(int offset);
        /// <summary>
        /// 设置表头和数据之间的间隔行数 默认为1
        /// </summary>
        /// <param name="offset"></param>
        /// <returns></returns>
        IFastNpoi<T> SetHeaderSkipNumber(int offset);
        /// <summary>
        /// 设置表头的样式
        /// </summary>
        /// <param name="styleFunc"></param>
        /// <returns></returns>
        IFastNpoi<T> SetHeaderStyle(Func<IWorkbook,ICellStyle> styleFunc);
        /// <summary>
        /// 设置数据的样式
        /// </summary>
        /// <param name="styleFunc"></param>
        /// <returns></returns>
        IFastNpoi<T> SetDataStyle(Func<IWorkbook,ICellStyle> styleFunc);
        /// <summary>
        /// 设置添加批注的单元格样式
        /// </summary>
        /// <param name="styleFunc"></param>
        /// <returns></returns>
        IFastNpoi<T> SetCommentCellStyle(Func<IWorkbook, ICellStyle> styleFunc);
        /// <summary>
        /// 设置批注的默认可见性
        /// </summary>
        /// <param name="visable"></param>
        /// <returns></returns>
        IFastNpoi<T> SetCommentVisable(bool visable);
        /// <summary>
        /// 设置实体list
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        IFastNpoi<T> SetDataList(List<T> list);
        /// <summary>
        /// 设置表头映射规则(此规则优先级高于实体上的特性)
        /// </summary>
        /// <param name="columnName">表头名</param>
        /// <param name="columnExpression">映射属性表达式</param>
        /// <param name="width">表格宽度</param>
        /// <param name="operationType">表头类型(导入,导出，导入和导出)</param>
        /// <param name="position">生成表头位置</param>
        /// <param name="mergeCell">是否合并单元格</param>
        /// <returns></returns>
        IFastNpoi<T> HeaderMapper(string columnName,Expression<Func<T,object>> columnExpression, int width=0,OperationType operationType= OperationType.Import_Export, int position=999,bool mergeCell=false);
        /// <summary>
        /// 设置表头映射规则(此规则优先级高于实体上的特性)
        /// </summary>
        /// <param name="columnName">表头名</param>
        /// <param name="propertyName">映射的属性名称</param>
        /// <param name="width">表格宽度</param>
        /// <param name="operationType">表头类型(导入,导出，导入和导出)</param>
        /// <param name="position">生成表头位置</param>
        /// <param name="mergeCell">是否合并单元格</param>
        /// <returns></returns>
        IFastNpoi<T> HeaderMapper(string columnName, string propertyName, int width = 0, OperationType operationType = OperationType.Import_Export, int position = 999,bool mergeCell=false);
        /// <summary>
        /// 数据操作
        /// </summary>
        /// <param name="action"></param>
        /// <returns></returns>
        IFastNpoi<T> DataMapper(Action<T> action);
        /// <summary>
        /// 表格合并
        /// </summary>
        /// <param name="startRowIndex">起始行序数</param>
        /// <param name="endRowIndex">结束行序数</param>
        /// <param name="startColumnIndex">起始列序数</param>
        /// <param name="endColumnIndex">结束列序数</param>
        /// <returns></returns>
        IFastNpoi<T> MergeCell(int startRowIndex,int endRowIndex,int startColumnIndex,int endColumnIndex);
        /// <summary>
        /// 使用CommentModel模型对指定cell添加批注
        /// </summary>
        /// <param name="commentModels"></param>
        /// <returns></returns>
        IFastNpoi<T> CommentCell(List<CommentModel> commentModels);
        IFastNpoi<T> CommentCell(Func<Dictionary<string, int>, List<CommentModel>> func);
        /// <summary>
        /// 对sheet进行追加操作
        /// </summary>
        /// <param name="action"></param>
        /// <returns></returns>
        IFastNpoi<T> SheetMapper(Action<ISheet> action);
        /// <summary>
        /// 使特性中的position属性失效
        /// </summary>
        /// <returns></returns>
        IFastNpoi<T> DisableOrderby();
        /// <summary>
        /// 不生成header(一般用于模板写入，表头使用模板的)
        /// </summary>
        /// <returns></returns>
         IFastNpoi<T> DisableHeader();
        /// <summary>
        /// 将workbook转化成为xlsx文件
        /// </summary>
        /// <param name="dirPath">生成地址的文件夹路径</param>
        /// <returns>文件名称</returns>
         string ToFile(string dirPath);
        /// <summary>
        /// 将workbook转化为byte数组
        /// </summary>
        /// <returns></returns>
         byte[] ToByte();
        /// <summary>
        /// 获取excel内的表头数据 key为表头名称 value为对应的列序数
        /// </summary>
        /// <returns></returns>
         Dictionary<string, int> GetHeader();
        /// <summary>
        /// 将workbook的数据转化为实体list
        /// </summary>
        /// <returns></returns>
         List<T> ToList();