NPOI 常用方法封装

NOPI使用常用方法封装

1、将ISheet转为List

/// <summary>
/// 工作表转List
/// </summary>
/// <typeparam name="T">需要转换的泛型类</typeparam>
/// <param name="sheet">工作表</param>
/// <param name="rowStartIndex">起始索引</param>
/// <returns></returns>
public static List<T> SheetToList<T>(ISheet sheet, int rowStartIndex = 1) where T : class, new()
{
    var list = new List<T>();
    var workType = sheet.Workbook.GetType().Name;
    var rows = sheet.GetRowEnumerator();
    if (rows == null) return null;
    IRow row = null;
    while (rows.MoveNext())
    {
        switch (workType)
        {
            case "XSSFWorkbook":
                row = (XSSFRow)rows.Current;
                break;
            case "HSSFWorkbook":
                row = (XSSFRow)rows.Current;
                break;
        }
        if (row == null || row.RowNum < rowStartIndex) continue;
        var t = Activator.CreateInstance(typeof(T)) as T;
        var props = t.GetType().GetProperties().ToArray();
        for (var i = 0; i < row.LastCellNum; i++)
        {
            var cell = row.GetCell(i);
            if (cell == null) continue;
            var type = props[i].PropertyType;
            var value = GetCellValue(type, cell.ToString());
            props[i].SetValue(t, value);
        }
        list.Add(t);
    }
    return list;
}


 /// <summary>
 /// 获取单元格的值
 /// </summary>
 /// <param name="valueType"></param>
 /// <param name="value"></param>
 /// <returns></returns>
 private static object GetCellValue(Type valueType, string value)
 {
     if (valueType == typeof(int))
     {
         return int.Parse(value);
     }
     else if (valueType == typeof(float))
     {
         return float.Parse(value);
     }
     else if (valueType == typeof(DateTime))
     {
         return DateTime.Parse(value);
     }
     else if (valueType == typeof(decimal))
     {
         return decimal.Parse(value);
     }
     else
     {
         return value;
     }
 }

// 对应扩展
/// <summary>
///  将数据表转为泛型对象
/// </summary>
/// <typeparam name="T">与工作表列对应数量的泛型实体类</typeparam>
/// <param name="sheet">休要转换工作表</param>
/// <param name="rowIndex">工作表中有效数据的起始索引</param>
/// <returns></returns>
public static List<T> TryTransToList<T>(this ISheet sheet, int rowIndex = 0) where T : class,new()
{
    try
    {
        return ExcelHelper.ExcelHelper.SheetToList<T>(sheet, rowIndex);
    }
    catch (Exception e)
    {
        throw new ArgumentNullException(paramName: nameof(TryTransToList), "数据转换异常，请检查源文件");
    }
}

说明：目前只支持的泛型类是根据工作表的实际模板生成，对复杂的表头还需扩展处理

2、IWorkbook 转 byte[]

/// <summary>
/// 将表格<seealso cref="IWorkbook"/>转为<seealso cref="Byte[]"/>流
/// </summary>
/// <param name="workbook"></param>
/// <returns></returns>
public static Byte[] ConvertToBytes(this IWorkbook workbook)
{
    try
    {
        var stream = new MemoryStream();
        workbook.Write(stream);
        return stream.ToArray();
    }
    catch (Exception ex)
    {
        return new byte[0]{};
    }
}

3、给ISheet新增一列

/// <summary>
/// 给指定工作表添加信息列
/// </summary>
/// <param name="sheet">信息表</param>
/// <param name="infos">错误信息</param>
/// <param name="colTitle">列标题</param>
/// <param name="titleRowIndex">列标题行起始索引</param>
/// <param name="backgroundColor">标记行的背景色</param>
/// <param name="foregroundColor">标记行的前景色</param>
/// <returns></returns>
public static Stream AddInfoColumn(
    ISheet sheet,
    List<WrongInfo> infos,
    string colTitle,
    int titleRowIndex=0,
    short backgroundColor = HSSFColor.Red.Index,
    short foregroundColor = HSSFColor.White.Index
    )
{
    if (sheet == null) return null;
    try
    {
        // 获取工作表得最后一列
        var firstRow = sheet.GetRow(titleRowIndex);
        var newColNum = firstRow.LastCellNum + 1;
        firstRow.CreateCell(firstRow.LastCellNum + 1).SetCellValue(colTitle); // 设置标题

        // 根据错误信息中行索引进行添加数据
        infos?.ForEach(a =>
        {
            var row = sheet.GetRow(a.RowIndex);
            row?.CreateCell(newColNum).SetCellValue(a.WrongMsg);
            //  改变这一行的文字颜色及背景色
            SetCellStyle(row, backgroundColor, foregroundColor);
        });
        return sheet.ConvertToStream();
    }
    catch (Exception e)
    {
        return null;
    }
}


/// <summary>
/// 设置单元格样式
/// </summary>
/// <param name="row"></param>
/// <param name="backgroundColor"></param>
/// <param name="foregroundColor"></param>
private static void SetCellStyle(
    IRow row,
    short backgroundColor=HSSFColor.Red.Index,
    short foregroundColor=HSSFColor.White.Index)
{
    if (row == null) return;
    foreach (var cell in row.Cells)
    {
        cell.CellStyle.FillBackgroundColor = backgroundColor;
        cell.CellStyle.FillForegroundColor = foregroundColor;
    }
}

4、通过Stream获取WorkBook

 /// <summary>
 /// 获取工作表空间
 /// </summary>
 /// <param name="stream">文件流</param>
 /// <param name="fileName">文件名称</param>
 /// <returns></returns>
 public static IWorkbook GetWorkbookByStream(Stream stream,string fileName)
 {
     // 判断是不是文件
     if (fileName.EndsWith(".xlsx"))
     {
         return new XSSFWorkbook(stream);
     }else if (fileName.EndsWith(".xls"))
     {
         return new HSSFWorkbook(stream);
     }
     else
     {
         return null;
     }
 }

5、给Row 中的Cell添加前景色和背景色

private static void SetCellStyle(
    IRow row)
{
    short background;
    short foreground;
    var workType = row.GetWorkbookType();
    switch (workType)
    {
        case ExcelFileType.Xlsx:
            background = new XSSFColor(Color.Red).Index;
            foreground = new XSSFColor(Color.Black).Index;
            break;
        case ExcelFileType.Xls:
            background = HSSFColor.Red.Index;
            foreground = HSSFColor.Black.Index;
            break;
        default:
            throw new ArgumentOutOfRangeException();
    }

    if (row == null) return;
    foreach (var cell in row.Cells)
    {
        cell.CellStyle.FillBackgroundColor = background;
        cell.CellStyle.FillForegroundColor = foreground;
    }
}

6、获取workbook的类型（2003/2007）

 /// <summary>
 /// 获取工作表的了类型
 /// </summary>
 /// <param name="sheet"></param>
 /// <returns></returns>
 public static ExcelFileType GetWorkbookType(this ISheet sheet)
 {
     var workType = sheet.Workbook.GetType().Name;
     switch (workType)
     {
         case "XSSFWorkbook":
             return ExcelFileType.Xlsx;
         case "HSSFWorkbook":
             return ExcelFileType.Xls;
         default: return default;
     }
 }

7、读取 File文件流扩展及校验

public static Stream ReadFileStream(this FileUploadDto fileDto) => fileDto.File?.OpenReadStream();

8、文件转换为IWorkbook 对象扩展

 /// <summary>
 /// 将文件dto 转换成工作表
 /// <see cref="ReadFileStream"/> or <seealso cref="CheckNull"/>
 /// </summary>
 /// <param name="fileDto">上传的文件</param>
 /// <returns></returns>
 public static IWorkbook ConvertToWorkbook(FileUploadDto fileDto)
 {
     try
     {
         var stream = fileDto.ReadFileStream().CheckNull();
         var fileName = fileDto.File.FileName;
         return ExcelHelper.ExcelHelper.GetWorkbookByStream(stream, fileName);
     }
     catch (Exception e)
     {
         throw new ArgumentNullException(paramName: nameof(ConvertToWorkbook), "文件流为空");
     }
 }

9、CheckNull 扩展集

/// <summary>
/// 检查转换后集合是否为空
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="list"></param>
/// <returns></returns>
public static List<T> CheckNull<T>(this List<T> list)=>
    list ?? throw new ArgumentNullException(paramName: nameof(CheckNull), "未将对象实例化");

/// <summary>
/// 检查文件流是否为空
/// </summary>
/// <param name="stream"></param>
/// <returns></returns>
public static Stream CheckNull(this Stream stream) =>
    stream ?? throw new ArgumentNullException(paramName: nameof(stream),"文件流为空");