private ISheet AddValue(ISheet sheet, ISheetDataWrapper sheetDetailDataWrapper, Type type)
{
IList <PropertyInfo> checkPropertyInfos = ExcelModelsPropertyManage.CreatePropertyInfos(type);
Int32 cellCount = 0;
foreach (var item in sheetDetailDataWrapper.Datas)
{
if (item == null)
{
StartRow++;
continue;
}
IRow newRow = sheet.CreateRow(StartRow);
buildContext.Row = newRow;
foreach (PropertyInfo property in checkPropertyInfos)
{
Object obj = property.GetValue(item, null);
Type t = property.PropertyType;
ICell cell;
if (t.IsGenericType && t.GetGenericTypeDefinition() == typeof(IEnumerable <>))
{
var ssd = ((IEnumerable)obj).Cast <IExtendedBase>();
if (ssd == null)
{
continue;
}
foreach (var v in sheetDetailDataWrapper.Titles)
{
IExtendedBase sv = ssd.Where(s => s.TypeId == v.TypeId).SingleOrDefault();
SetCell(newRow, cellCount++, sv.TypeValue);
}
continue;
}
SetCell(newRow, cellCount++, obj);
}
StartRow++;
cellCount = 0;
}
return(sheet);
}
/// <summary>
/// 导入
/// </summary>
/// <typeparam name="T">具体对象</typeparam>
/// <param name="fs"></param>
/// <param name="fileName"></param>
/// <param name="isFirstRowColumn"></param>
/// <returns></returns>
public static IEnumerable <T> ExcelToDataTable <T>(Stream fs, bool isFirstRowColumn = false) where T : new()
{
List <T> ts = new List <T>();
Type type = typeof(T);
IList <PropertyInfo> checkPropertyInfos = ExcelModelsPropertyManage.CreatePropertyInfos(type);
try
{
IWorkbook workbook = WorkbookFactory.Create(fs);
fs.Dispose();
ISheet sheet = workbook.GetSheetAt(0);
if (sheet != null)
{
IRow firstRow = sheet.GetRow(0);
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
Int32 startRow = isFirstRowColumn ? 1 : 0;
int rowCount = sheet.LastRowNum; //行数
int length = checkPropertyInfos.Count;
length = length > cellCount + 1 ? cellCount + 1 : length;
Boolean haveValue = false;
for (int i = startRow; i <= rowCount; ++i)
{
IRow row = sheet.GetRow(i);
if (row == null)
{
continue; //没有数据的行默认是null
}
T t = new T();
for (int f = 0; f < length; f++)
{
ICell cell = row.GetCell(f);
if (cell == null || String.IsNullOrEmpty(cell.ToString()))
{
continue;
}
object b = cell.ToString();
if (cell.CellType == CellType.Numeric)
{
//NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型
if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型
{
b = cell.DateCellValue;
}
else
{
b = cell.NumericCellValue;
}
}
PropertyInfo pinfo = checkPropertyInfos[f];
if (pinfo.PropertyType.Name != b.GetType().Name) //类型不一样的时候，强转
{
b = System.ComponentModel.TypeDescriptor.GetConverter(pinfo.PropertyType).ConvertFrom(b.ToString());
}
type.GetProperty(pinfo.Name).SetValue(t, b, null);
if (!haveValue)
{
haveValue = true;
}
}
if (haveValue)
{
ts.Add(t); haveValue = false;
}
}
}
return(ts);
}
catch (Exception ex)
{
return(null);
}
}
private ISheet SetTitle(ISheet sheet, ISheetDataWrapper sheetDetailDataWrapper, Type type)
{
IRow titleRow = null;
ICell titleCell = null;
if (!String.IsNullOrEmpty(sheetDetailDataWrapper.DataName))
{
titleRow = sheet.CreateRow(StartRow);
buildContext.Row = titleRow;
StartRow++;
titleCell = SetCell(titleRow, 0, sheetDetailDataWrapper.DataName);
if (OnHeadCellSetAfter != null)
{
OnHeadCellSetAfter(buildContext);
}
}
IRow row = sheet.CreateRow(StartRow);
buildContext.Row = row;
IList <PropertyInfo> checkPropertyInfos = ExcelModelsPropertyManage.CreatePropertyInfos(type);
int i = 0;
foreach (PropertyInfo property in checkPropertyInfos)
{
DisplayNameAttribute dn = property.GetCustomAttributes(typeof(DisplayNameAttribute), false).SingleOrDefault() as DisplayNameAttribute;
if (dn != null)
{
SetCell(row, i++, dn.DisplayName);
continue;
}
Type t = property.PropertyType;
if (t.IsGenericType)
{
if (sheetDetailDataWrapper.Titles == null || sheetDetailDataWrapper.Titles.Count() == 0)
{
continue;
}
foreach (var item in sheetDetailDataWrapper.Titles)
{
SetCell(row, i++, item.TypeName);
}
}
}
if (titleCell != null && i > 0)
{
titleCell.MergeTo(titleRow.CreateCell(i - 1));
titleCell.CellStyle = this.CenterStyle;
}
StartRow++;
return(sheet);
}
private ISheet AddValue(ISheet sheet, ISheetDataWrapper sheetDetailDataWrapper, Type type)
{
IList <PropertyInfoDetail> checkPropertyInfos = ExcelModelsPropertyManage.CreatePropertyInfos(type);
Int32 cellCount = 0;
foreach (var item in sheetDetailDataWrapper.Datas)
{
if (item == null)
{
_startRow++;
continue;
}
IRow newRow = sheet.CreateRow(_startRow);
_buildContext.Row = newRow;
foreach (PropertyInfoDetail propertyInfoDeatil in checkPropertyInfos)
{
Object obj = propertyInfoDeatil.PropertyInfoV.GetValue(item, null);
Type t = propertyInfoDeatil.PropertyInfoV.PropertyType;
if (t.IsGenericType && t.GetGenericTypeDefinition() == typeof(IEnumerable <>))
{
var ssd = ((IEnumerable)obj).Cast <IExtendedBase>();
if (ssd == null)
{
continue;
}
foreach (var title in sheetDetailDataWrapper.Titles)
{
IExtendedBase sv = ssd.SingleOrDefault(s => s.TypeId == title.TypeId);
Object val = null;
if (sv == null || sv.TypeValue == null)
{
val = ExcelModelsPropertyManage.GetExtendedDefaultValue(title.GetType());
}
else
{
val = sv.TypeValue;
}
SetCell(newRow, cellCount++, val);
}
continue;
}
SetCell(newRow, cellCount++, obj == null && propertyInfoDeatil.DefaultVale != null ? propertyInfoDeatil.DefaultVale : obj);
}
_startRow++;
cellCount = 0;
}
return(sheet);
}