public static Task <List <List <T> > > GetGroupedDTOs <T>(MemoryStream stream, string fileName, ImportConfig config, IExcelConfigurationRepo excelConfig) where T : Output, new()
{
return(Task.Run(() =>
{
stream.Position = 0;
IWorkbook wbk;
if (fileName.EndsWith(".xls", StringComparison.InvariantCultureIgnoreCase))
{
wbk = new HSSFWorkbook(stream); //This will read 2007 Excel format
}
else if (fileName.EndsWith(".xlsx", StringComparison.InvariantCultureIgnoreCase))
{
wbk = new XSSFWorkbook(stream);
}
else
{
throw new Exception("This format is not supported");
}
var sheet = wbk.GetSheetAt(0); //get first sheet from workbook
IRow headerRow = sheet.GetRow(0); //Get Header Row
var columnNames = Utils.GetColumnNames(headerRow);
excelConfig.Save(1, typeof(T).Name, fileName, columnNames);
var result = new List <List <T> >();
var miniResult = new List <T>();
var firstRow = sheet.FirstRowNum;
var lastRow = sheet.LastRowNum;
for (int i = (firstRow + 1); i <= lastRow; i++) //Read Excel File
{
var row = sheet.GetRow(i);
try
{
if (miniResult.Count > 0 && (row == null || row.All(x => x.CellType == CellType.Blank)))
{
result.Add(miniResult);
miniResult = new List <T>();
}
else
{
if (row != null && row.Any(x => x.CellType != CellType.Blank))
{
miniResult.Add(GetDTO <T>(row, columnNames.ToList(), i));
}
}
}
catch (Exception ex)
{
System.Diagnostics.Debugger.Break();
}
}
if (miniResult.Count > 0)
{
result.Add(miniResult);
}
return result;
}));
}
public static Task <ConcurrentBag <T> > GetDTOs <T>(MemoryStream stream, string fileName, ImportConfig config, IExcelConfigurationRepo excelConfig) where T : Output, new()
{
return(Task.Run(() =>
{
var result = new ConcurrentBag <T>();
stream.Position = 0;
IWorkbook wbk;
if (fileName.EndsWith(".xls", StringComparison.InvariantCultureIgnoreCase))
{
wbk = new HSSFWorkbook(stream); //This will read 2007 Excel format
}
else if (fileName.EndsWith(".xlsx", StringComparison.InvariantCultureIgnoreCase))
{
wbk = new XSSFWorkbook(stream);
}
else
{
throw new Exception("This format is not supported");
}
var sheet = wbk.GetSheetAt(0); //get first sheet from workbook
IRow headerRow = sheet.GetRow(0); //Get Header Row
var columnNames = Utils.GetColumnNames(headerRow);
excelConfig.Save(1, typeof(T).Name, fileName, columnNames);
var firstRow = sheet.FirstRowNum;
var lastRow = sheet.LastRowNum;
for (int i = (firstRow + 1); i <= lastRow; i++) //Read Excel File
{
var row = sheet.GetRow(i);
if (row == null && config.IgnoreNullRows)
{
continue;
}
try
{
if (row.All(x => x == null || string.IsNullOrEmpty(x.ToString())))
{
continue;
}
}
catch
{
System.Diagnostics.Debugger.Break();
}
result.Add(GetDTO <T>(row, columnNames.ToList(), i));
}
return result;
}));
}