private Tuple <T, IEnumerable <CellDataEntity> > ProcessTransverseData <T>(ISheet sheet, IEnumerable <CellMarkEntity> cellMarks, IEnumerable <CellMergeEntity> cellMerges)
where T : class, new()
{
T entity = new T();
List <CellDataEntity> cellDatas = new List <CellDataEntity>();
CellDataEntity cellData = null;
foreach (var item in cellMarks)
{
cellData = ProcessCell(sheet, item, cellMerges);
Type originalType = item.PropertyInfo.PropertyType.GetGenericArguments()[0];
if (originalType == typeof(int))
{
item.PropertyInfo.SetValue(entity, int.Parse(cellData.CellValue));
}
else if (originalType == typeof(double))
{
item.PropertyInfo.SetValue(entity, double.Parse(cellData.CellValue));
}
else if (originalType == typeof(DateTime))
{
item.PropertyInfo.SetValue(entity, DateTime.Parse(cellData.CellValue));
}
else if (originalType == typeof(bool))
{
item.PropertyInfo.SetValue(entity, Boolean.Parse(cellData.CellValue));
}
else
{
item.PropertyInfo.SetValue(entity, cellData.CellValue);
}
cellDatas.Add(cellData);
}
return(new Tuple <T, IEnumerable <CellDataEntity> >(entity, cellDatas));
}
private Tuple <IEnumerable <T>, IEnumerable <CellDataEntity> > ProcessVerticalData <T>(ISheet sheet, IEnumerable <CellMarkEntity> cellMarks, IEnumerable <CellMergeEntity> cellMerges)
where T : class, new()
{
int beginRow = cellMarks.Min(t => t.RowIndex) + 1;
List <T> list = new List <T>();
List <CellDataEntity> cellDatas = new List <CellDataEntity>();
CellDataEntity cellData = null;
T entity = null;
IRow row = null;
cellMarks = cellMarks.OrderBy(t => t.CellIndex);
for (var rowIndex = beginRow; rowIndex < sheet.PhysicalNumberOfRows; rowIndex++)
{
entity = new T();
row = sheet.GetRow(rowIndex);
foreach (var item in cellMarks)
{
cellData = ProcessCell(sheet, item, cellMerges, row);
if (cellData == null)
{
continue;
}
Type originalType = item.PropertyInfo.PropertyType.GetGenericArguments()[0];
if (originalType == typeof(int))
{
int intVal = 0;
cellData.ConvertSuccess = int.TryParse(cellData.CellValue, out intVal);
item.PropertyInfo.SetValue(entity, intVal);
}
else if (originalType == typeof(double))
{
double dbVal = 0;
cellData.ConvertSuccess = double.TryParse(cellData.CellValue, out dbVal);
item.PropertyInfo.SetValue(entity, dbVal);
}
else if (originalType == typeof(DateTime))
{
DateTime dtVal = DateTime.Now;
cellData.ConvertSuccess = DateTime.TryParse(cellData.CellValue, out dtVal);
item.PropertyInfo.SetValue(entity, dtVal);
}
else if (originalType == typeof(bool))
{
bool blVal = true;
cellData.ConvertSuccess = bool.TryParse(cellData.CellValue, out blVal);
item.PropertyInfo.SetValue(entity, blVal);
}
else
{
item.PropertyInfo.SetValue(entity, cellData.CellValue);
}
cellDatas.Add(cellData);
}
list.Add(entity);
}
return(new Tuple <IEnumerable <T>, IEnumerable <CellDataEntity> >(list.AsEnumerable(), cellDatas.AsEnumerable()));
}
private CellDataEntity ProcessCell(ISheet sheet, CellMarkEntity item, IEnumerable <CellMergeEntity> cellMerges, IRow row = null)
{
CellDataEntity cellData = new CellDataEntity();
cellData.TitleName = item.TitleName;
cellData.ColumnName = item.ColumnName;
cellData.ColIndex = item.CellIndex;
ICell cell = null;
int rowIndex = 0;
if (row == null)
{
rowIndex = item.RowIndex;
cell = sheet.GetRow(item.RowIndex).GetCell(item.CellIndex + 1);
}
else
{
rowIndex = row.RowNum;
cell = row.GetCell(item.CellIndex);
}
if (cell == null)
{
return(null);
}
cellData.RowIndex = rowIndex;
if (cell.IsMergedCell == false)
{
cellData.CellPostion = string.Concat(ExportExcelUtil.IndexToColName(item.CellIndex), rowIndex + 1);
Type originalType = item.PropertyInfo.PropertyType.GetGenericArguments()[0];
string cellValue = "";
switch (cell.CellType)
{
case CellType.Numeric:
if (HSSFDateUtil.IsCellDateFormatted(cell))
{
cellValue = cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss.fff");
}
else
{
cellValue = cell.NumericCellValue.ToString();
}
break;
case CellType.String:
cellValue = cell.StringCellValue;
break;
case CellType.Boolean:
cellValue = cell.BooleanCellValue.ToString();
break;
case CellType.Formula:
if (originalType == typeof(DateTime))
{
cellValue = cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss.fff");
}
else
{
cell.SetCellType(CellType.String);
cellValue = cell.ToString();
}
break;
}
cellData.IsEmpty = string.IsNullOrEmpty(cellValue);
cellData.CellValue = cellValue;
//if (originalType == typeof(DateTime))
//{
// cellData.CellValue = cell.DateCellValue.ToString();
//}
//else
//{
// if (cell.CellType == CellType.Formula)
// cell.SetCellType(CellType.String);
// cellData.CellValue = cell.ToString();
//}
}
else
{
CellDimension dimension = ProcessCellDimension(cell, cellMerges);
var merges = cellMerges.Where(t => t.FirstRow == dimension.FirstRowIndex && t.FirstColumn == dimension.FirstColIndex);
cellData.CellPostion = string.Concat(ExportExcelUtil.IndexToColName(dimension.FirstColIndex), dimension.FirstRowIndex + 1);
if (merges != null && merges.Any())
{
string cellValue = merges.FirstOrDefault().DataCell.ToString();
cellData.CellValue = cellValue;
}
}
cellData.Length = StringUtil.StrLength(cellData.CellValue);
return(cellData);
}
