public void ConvertToDataTable <T>(ExcelCompleteConvertToDataTableHandler handler, List <ExcelObjectMapping> mapping, int startRowNo = 1, string rowNoMapping = null) where T : class
{
DataTable dtOut = new DataTable();
T objSource = System.Activator.CreateInstance <T>();
//Generate DataTable Column
PropertyInfo[] pSourceInfo = objSource.GetType().GetProperties();
foreach (PropertyInfo pInfo in pSourceInfo)
{
string strPropertyType = string.Empty;
if (pInfo.PropertyType.FullName == objSource.GetType().ToString())
{
continue;
}
if (pInfo.PropertyType.IsGenericType && pInfo.PropertyType.Name.Contains("Nullable"))
{
Type tNullableType = Type.GetType(pInfo.PropertyType.FullName);
strPropertyType = tNullableType.GetGenericArguments()[0].FullName;
}
else if (!pInfo.PropertyType.IsGenericType)
{
strPropertyType = pInfo.PropertyType.FullName;
}
else
{
continue;
}
DataColumn col = new DataColumn(pInfo.Name, Type.GetType(strPropertyType));
dtOut.Columns.Add(col);
}
this.ReadRow(new ExcelRowReaderHandler((ExcelRowReader row) =>
{
Type objType = typeof(T);
DataRow dRow = dtOut.NewRow();
if (rowNoMapping != null)
{
System.Reflection.PropertyInfo prop = objType.GetProperty(rowNoMapping);
if (prop != null)
{
dRow[prop.Name] = (row.RowIndex - startRowNo) + 1;
}
}
while (row.ReadCell())
{
ExcelCellReader cell = row.GetCell();
ExcelObjectMapping map = mapping.Find(x => string.Format("{0}{1}", x.ColumnReference, row.RowIndex) == cell.CellReference);
if (map == null)
{
continue;
}
System.Reflection.PropertyInfo prop = objType.GetProperty(map.PropertyName);
if (prop == null)
{
continue;
}
string cellValue = cell.CellValue;
if (map.ExcelType == typeof(string))
{
dRow[prop.Name] = cellValue;
}
else if (cellValue != null)
{
if (map.ExcelType == typeof(float))
{
float f = 0;
if (float.TryParse(cellValue, out f))
{
dRow[prop.Name] = f;
}
}
else if (map.ExcelType == typeof(DateTime))
{
DateTime dt = DateTime.FromOADate(double.Parse(cellValue));
dRow[prop.Name] = dt;
}
}
}
if (row.RowIndex >= startRowNo)
{
bool allValueNull = true;
foreach (ExcelObjectMapping map in mapping)
{
System.Reflection.PropertyInfo prop = objType.GetProperty(map.PropertyName);
if (prop != null)
{
if (dRow[prop.Name] != DBNull.Value)
{
allValueNull = false;
break;
}
}
}
if (allValueNull)
{
return(false);
}
dtOut.Rows.Add(dRow);
dtOut.AcceptChanges();
}
return(true);
}));
handler(dtOut);
}
public void ConvertToList <T>(ExcelCompleteConvertToListHandler <T> handler, List <ExcelObjectMapping> mapping, int startRowNo = 1, string rowNoMapping = null) where T : class
{
int completeRange = 1000;
List <T> result = new List <T>();
this.ReadRow(new ExcelRowReaderHandler((ExcelRowReader row) =>
{
Type objType = typeof(T);
T obj = Activator.CreateInstance <T>();
if (rowNoMapping != null)
{
System.Reflection.PropertyInfo prop = objType.GetProperty(rowNoMapping);
if (prop != null)
{
prop.SetValue(obj, (row.RowIndex - startRowNo) + 1);
}
}
while (row.ReadCell())
{
ExcelCellReader cell = row.GetCell();
ExcelObjectMapping map = mapping.Find(x => string.Format("{0}{1}", x.ColumnReference, row.RowIndex) == cell.CellReference);
if (map == null)
{
continue;
}
System.Reflection.PropertyInfo prop = objType.GetProperty(map.PropertyName);
if (prop == null)
{
continue;
}
string cellValue = cell.CellValue;
if (map.ExcelType == typeof(string))
{
if (prop.PropertyType == typeof(DateTime) ||
prop.PropertyType == typeof(DateTime?))
{
CultureInfo info = new CultureInfo("en-US");
string format = "dd/MM/yyyy";
if (map.Format != null &&
map.Format is ExcelDateTimeFormat)
{
ExcelDateTimeFormat f = map.Format as ExcelDateTimeFormat;
if (f.Format != null)
{
format = f.Format;
}
if (f.Info != null)
{
info = new CultureInfo(f.Info);
}
}
DateTime date;
if (DateTime.TryParseExact(cellValue, format,
info, DateTimeStyles.None, out date))
{
prop.SetValue(obj, date, null);
}
}
else
{
prop.SetValue(obj, cellValue);
}
}
else if (cellValue != null)
{
if (map.ExcelType == typeof(DateTime))
{
DateTime dt = DateTime.FromOADate(double.Parse(cellValue));
prop.SetValue(obj, dt);
}
}
}
if (row.RowIndex >= startRowNo)
{
bool allValueNull = true;
foreach (ExcelObjectMapping map in mapping)
{
System.Reflection.PropertyInfo prop = objType.GetProperty(map.PropertyName);
if (prop != null)
{
if (prop.GetValue(obj) != null)
{
allValueNull = false;
break;
}
}
}
if (allValueNull)
{
return(false);
}
result.Add(obj);
if (result.Count == completeRange)
{
if (handler(result) == false)
{
return(false);
}
result = new List <T>();
}
}
return(true);
}));
if (result.Count > 0)
{
handler(result);
}
}
