public IList<T> ExportToList<T>(ISheet sheet, string[] fields) where T : class,new()
{
IList<T> list = new List<T>(); //遍历每一行数据
for (int i = sheet.FirstRowNum + , len = sheet.LastRowNum + ; i < len; i++)
{
T t = new T();
IRow row = sheet.GetRow(i); for (int j = , 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，多次调用以上方法即可。
这些代码只是初步取出了数据，并没有进行数据为空等的校验，需要根据业务需要进行修改。