C# 将sheet中数据转为list

   
public IList<T> ExportToList<T>(ISheet sheet, string[] fields) where T : class,new()
        {
            IList<T> list = new List<T>();

            //遍历每一行数据
            for (int i = sheet.FirstRowNum + 1, len = sheet.LastRowNum + 1; i < len; i++)
            {
                T t = new T();
                IRow row = sheet.GetRow(i);

                for (int j = 0, len2 = fields.Length; j < len2; j++)
                {
                    Type propertyType = typeof(T).GetProperty(fields[j]).PropertyType; //获取当前属性的类型
                    ICell cell = row.GetCell(j);
                    object cellValue = null;

                    if (cell == null)
                    {
                        continue;
                    }

                    if(propertyType == typeof(string) | cell.CellType == CellType.Blank)
                    {
                        cell.SetCellType(CellType.String);
                        cellValue = cell.StringCellValue;
                    }
                    if (propertyType == typeof(int) && cell.CellType != CellType.Blank)
                    {
                        cell.SetCellType(CellType.Numeric);
                        cellValue = Convert.ToInt32(cell.NumericCellValue); //Double转int
                    }
                    if (propertyType == typeof(bool))
                    {
                        cell.SetCellType(CellType.Boolean);
                        cellValue = cell.BooleanCellValue;
                    }

                    typeof(T).GetProperty(fields[j]).SetValue(t, cellValue, null);
                }
                list.Add(t);
            }
            return list;
        }

调用如下：

static void Main(string[] args)
        {

            string _fromfile = @"D:\code\csharp\person.xlsx";
            string _tofile = @"D:\test.xlsx";
            
            IWorkbook book = null;
            try
            {
                book = new XSSFWorkbook(_fromfile);
            }
            catch (IOException ex)
            {
                Console.WriteLine(ex.Message);
            }
            catch (Exception ex)
            {
                book = new HSSFWorkbook(File.OpenRead(_fromfile));
            }

            ISheet sheet = book.GetSheet("person");
            

            ExcelHelper helper = new ExcelHelper(_fromfile);
          
            string[] properties = new string[] { "name", "age", "sex", "id", "height", "weight", "country", "hometown", "phone" };
            foreach (var p in helper.ExportToList<Person>(sheet, properties))
            {
                Console.WriteLine("   " + p.name.GetType() + "   " + p.phone  + "  " + p.sex  + "   "  + p.weight.GetType());
            }
            Console.Read(); 
}
            

 说明：以上代码可以实现从Excel中读取数据，并将每个sheet里的对象集合放在一个list中。对于一个Excel，多次调用以上方法即可。

这些代码只是初步取出了数据，并没有进行数据为空等的校验，需要根据业务需要进行修改。

转载于:https://www.cnblogs.com/tomspapaya/p/3643682.html