导入Excel（xlsx）到List

- 日理万妓 2021-12-11 03:57 380阅读 0赞

/// <summary>  
/// 导入Excel（xlsx）到List  
/// </summary>  
/// <typeparam name="T"></typeparam>  
/// <param name="stream"></param>  
/// <param name="sheetIndex">从零开始sheet索引</param>  
/// <param name="headerRowCount">表头行数，即从此开始读取数据</param>  
/// <returns></returns>  
public static List<T> ExcelToList<T>(MemoryStream stream,int sheetIndex, int headerRowCount)  
\{  
List<T> list = new List<T>();  
IWorkbook workbook = new XSSFWorkbook();  
try  
\{  
workbook = new XSSFWorkbook(stream);  
ISheet sheet = workbook.GetSheetAt(sheetIndex);  
IRow cellNum = sheet.GetRow(0);  
var propertys = typeof(T).GetProperties();  
string cellValue = null;  
int lastCellNum = cellNum.LastCellNum;

for (int i = headerRowCount; i <= sheet.LastRowNum; i++)  
\{  
IRow row = sheet.GetRow(i);  
var tInstance = System.Activator.CreateInstance<T>();  
for (int j = 0; j < lastCellNum; j++)  
\{  
cellValue = row.GetCell(j) != null ? row.GetCell(j).ToString() : null;

string propertyTypeFullName = (propertys\[j\].PropertyType).FullName;

if (IsNullableType(propertys\[j\].PropertyType))  
\{  
if (string.IsNullOrWhiteSpace(cellValue))  
\{  
propertys\[j\].SetValue(tInstance, null, null);  
continue;  
\}  
propertyTypeFullName = Nullable.GetUnderlyingType(propertys\[j\].PropertyType).FullName;  
\}

switch (propertyTypeFullName)  
\{  
case "System.String":  
propertys\[j\].SetValue(tInstance, cellValue, null);  
break;  
case "System.DateTime":  
DateTime dtParam = Convert.ToDateTime(cellValue, CultureInfo.InvariantCulture);  
propertys\[j\].SetValue(tInstance, dtParam, null);  
break;  
case "System.Boolean":  
bool blParam = Convert.ToBoolean(cellValue);  
propertys\[j\].SetValue(tInstance, blParam, null);  
break;  
case "System.Int16":  
short int16Param = Convert.ToInt16(cellValue);  
propertys\[j\].SetValue(tInstance, int16Param, null);  
break;  
case "System.Int32":  
int int32Param = Convert.ToInt32(cellValue);  
propertys\[j\].SetValue(tInstance, int32Param, null);  
break;  
case "System.Int64":  
long int64Param = Convert.ToInt64(cellValue);  
propertys\[j\].SetValue(tInstance, int64Param, null);  
break;  
case "System.Byte":  
byte btPrame = Convert.ToByte(cellValue);  
propertys\[j\].SetValue(tInstance, btPrame, null);  
break;  
case "System.Single":  
float sgParam = Convert.ToSingle(cellValue);  
propertys\[j\].SetValue(tInstance, sgParam, null);  
break;  
case "System.Double":  
double dbParam = Convert.ToDouble(cellValue);  
propertys\[j\].SetValue(tInstance, dbParam, null);  
break;  
default:  
propertys\[j\].SetValue(tInstance, null, null);  
break;  
\}  
\}

list.Add(tInstance);  
\}  
stream.Flush();  
stream.Close();  
workbook.Close();  
\}  
finally  
\{  
stream.Close();  
workbook.Close();  
\}  
return list;  
\}

转载于:https://www.cnblogs.com/starts/p/11155891.html