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,多次调用以上方法即可。
这些代码只是初步取出了数据,并没有进行数据为空等的校验,需要根据业务需要进行修改。