c# List、Excel文件流互转

c# List、Excel文件流互转

为了优化文件流与LIst要通过介质Datatable的资源浪费问题，这里采用了特性标注的优雅解决方案，对文件与List转换的高效且优雅的处理，方便大家使用

一、自定义文件流

使用自定义的流对象重写系统的流对象，为了解决后面的流被意外关闭问题

public class NPOIMemoryStream : MemoryStream
{
    /// <summary>
    /// 获取流是否关闭
    /// </summary>
    public bool IsColse
    {
        get;
        private set;
    }
    public NPOIMemoryStream(bool colse = false)
    {
        IsColse = colse;
    }
    public override void Close()
    {
        if (IsColse)
        {
            base.Close();
        }
    }
}

二、List转流

/// <summary>
/// list转流
/// </summary>
/// <param name="list">集合</param>
/// <param name="isXlsx">true:xlsx false:xls</param>
/// <param name="sheetName">页码名称</param>
/// <returns></returns>
public static NPOIMemoryStream ConvertToExcel<T>(this List<T> list, bool isXlsx = true,string sheetName = "SheetOne")
{
    IWorkbook workbook = null;

    if (!isXlsx)
    workbook = new HSSFWorkbook();
    else
    workbook = new XSSFWorkbook();

    ISheet defaultSheet = workbook.CreateSheet(sheetName);
    //获取传递的泛型类型
    Type type = typeof(T);

    PropertyInfo[] properties = type.GetProperties();
    //定义列头
    IRow header = defaultSheet.CreateRow(0);
    //设置一下表头样式,将表头设置为加粗字体
    ICellStyle style = workbook.CreateCellStyle();
    var Font = workbook.CreateFont();
    Font.IsBold = true;
    style.SetFont(Font);

    Dictionary<string, string> maps = new Dictionary<string, string>();
    foreach (PropertyInfo property in properties)
    {
    object[] objs = property.GetCustomAttributes(typeof(DescriptionAttribute), true);
    if (objs != null && objs.Length > 0)
    {
    var description = ((DescriptionAttribute)objs[0]).Description;
    maps.Add(property.Name, description);
    }
    }
    //渲染列头
    int i = 0;
    foreach (PropertyInfo pro in properties)
    {
    if (maps.ContainsKey(pro.Name))
    {
    header.CreateCell(i).SetCellValue(maps[pro.Name]);
    header.GetCell(i).CellStyle = style;
    i++;
    }
    }
    //渲染数据
    int RowNumber = 1;  //控制行号增加的变量
    list.ForEach(item => {
    IRow row = defaultSheet.CreateRow(RowNumber); //创建一行写一行的数据
    PropertyInfo[] tempProperties = properties;

    int ColumnNumber = 0;   //控制列增加的变量
    foreach (PropertyInfo pro in properties)
    {
    if(maps.ContainsKey(pro.Name))
    {
    if (pro.GetValue(item) == null)   //在这里进行属性判空
    {
    row.CreateCell(ColumnNumber).SetCellValue("");
    }
    else if (pro.PropertyType.Name.StartsWith("List"))
    {
    var value = JsonConvert.SerializeObject(pro.GetValue(item));
    row.CreateCell(ColumnNumber).SetCellValue(value);
    }
    else
    {
    //打印基础类型数据
    row.CreateCell(ColumnNumber).SetCellValue(Convert.ToString(pro.GetValue(item)));
    }
    ColumnNumber++;
    }
    }
    RowNumber++;
    });

    NPOIMemoryStream ms = new NPOIMemoryStream();
    workbook.Write(ms);
    ms.Flush();
    ms.Seek(0, SeekOrigin.Begin);
    return ms;
}

三、流转List

/// <summary>
        /// 流转list
        /// </summary>
        /// <param name="file">文件流</param>
        /// <param name="sheetName">指定读取excel工作薄sheet的名称</param>
        /// <returns></returns>
        public static List<T> ConvertToList<T>(this FileStream file, string sheetName = null)
        {
            //获取文件后缀名
            string type = Path.GetExtension(file.Name);
            //判断是否导入合法文件
            if (!extName.Contains(type))
            {
                return null;
            }
            //转成为文件流
            MemoryStream ms = new MemoryStream();
            file.CopyTo(ms);
            ms.Seek(0, SeekOrigin.Begin);
            //实例化T数组
            List<T> list = new List<T>();
            //获取数据
            list = ConvertToList<T>(ms, sheetName);
            return list;
        }
        /// <summary>
        /// Excel流转list
        /// </summary>
        /// <param name="fileStream">文件流</param>
        /// <param name="sheetName">指定读取excel工作薄sheet的名称</param>
        /// <returns></returns>
        public static List<T> ConvertToList<T>(this Stream fileStream, string sheetName = null)
        {
            //创建Excel数据结构
            IWorkbook workbook = WorkbookFactory.Create(fileStream);
            //如果有指定工作表名称
            ISheet sheet = null;
            if (!string.IsNullOrEmpty(sheetName))
            {
                sheet = workbook.GetSheet(sheetName);
                //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
                if (sheet == null)
                {
                    sheet = workbook.GetSheetAt(0);
                }
            }
            else
            {
                //如果没有指定的sheetName，则尝试获取第一个sheet
                sheet = workbook.GetSheetAt(0);
            }
            //实例化T数组
            List<T> list = new List<T>();
            if (sheet != null)
            {
                //一行最后一个cell的编号 即总的列数
                IRow cellNum = sheet.GetRow(0);
                int num = cellNum.LastCellNum;
                //获取泛型对象T的所有注解
                PropertyInfo[] peroperties = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
                //缓存字段对应描述，提升性能
                Dictionary<string, string> maps = new Dictionary<string, string>();
                foreach (PropertyInfo property in peroperties)
                {
                    object[] objs = property.GetCustomAttributes(typeof(DescriptionAttribute), true);
                    if (objs != null && objs.Length > 0)
                    {
                        var description = ((DescriptionAttribute)objs[0]).Description;
                        maps.Add(description, property.Name);
                    }
                }
                //每行转换为单个T对象
                for (int i = 1; i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    var obj = Activator.CreateInstance<T>();
                    for (int j = 0; j < num; j++)
                    {
                        //行名称
                        var colName = cellNum.GetCell(j) + "";
                        string propName = "";
                        //去找注解对应的对象字段名
                        foreach (PropertyInfo property in peroperties)
                        {
                            if (maps.ContainsKey(colName) && property.Name == maps[colName])
                            {
                                propName = property.Name;
                                //没有数据的单元格都默认是null
                                ICell cell = row.GetCell(j);
                                if (cell != null)
                                {
                                    var value = row.GetCell(j).ToString();
                                    ConvertByPropTypeName(property, obj, value);
                                }
                            }

                        }
                    }
                    list.Add(obj);
                }
            }
            return list;
        }
/// <summary>
        /// 根据字段类型名称进行不同的值转换
        /// </summary>
        /// <typeparam name="T">泛型类</typeparam>
        /// <param name="prop">字段信息</param>
        /// <param name="item">实体</param>
        /// <param name="rowValue">文件中的表值</param>
        public static void ConvertByPropTypeName<T>(PropertyInfo prop, T item, string rowValue)
        {
            if (prop.PropertyType.Name.Equals("DateTime"))
            {
                prop.SetValue(item, Convert.ToDateTime(rowValue));
            }
            else if (prop.PropertyType.Name.Equals("Int32"))
            {
                prop.SetValue(item, Convert.ToInt32(rowValue));
            }
            else if (prop.PropertyType.Name.Equals("Decimal"))
            {
                prop.SetValue(item, Convert.ToDecimal(rowValue));
            }
            else if (prop.PropertyType.Name.Equals("Double"))
            {
                prop.SetValue(item, Convert.ToDouble(rowValue));
            }
            else
            {
                prop.SetValue(item, rowValue); //设置值
            }
        }

四、使用方式

//只需要对对应实体加入特性就可以
class StudentDto
{
    [Description("名字")]
    public string Name { get; set; }
    [Description("性别")]
    public string Sex { get; set; }
}

static void Main(string[] args)
{
    var list =  ConvertToExcel<StudentDto>(流);
}