/// <summary>
/// загрузить из таблички Excel в DTO с записью в лог
/// </summary>
protected List <T> LoadFromExcel <T>(FileParam excelFileParam, string logScopeName, int cellCount, Func <ExcelRangeBase[], T> createElem)
{
logger.LogTrace(logScopeName);
using (logger.BeginScope(logScopeName))
{
if ((excelFileParam == null) || (string.IsNullOrWhiteSpace(excelFileParam.fileName)))
{
throw new ArgumentException("No filename given");
}
logger.LogTrace(string.Format("opening '{0}'", excelFileParam.fileName));
FileInfo excelFile = new FileInfo(excelFileParam.fileName);
if (!excelFile.Exists)
{
throw new ArgumentException("File not found");
}
using (ExcelPackage package = new ExcelPackage(excelFile))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
int rowCount = worksheet.Dimension.Rows;
if (rowCount < 2)
{
throw new ApplicationException("Too little rows. Need at least two: header + data");
}
List <T> result = new List <T>();
//skipping first line - that is header
//numeration base is 1, not 0
for (int i = 2; i <= rowCount; i++)
{
ExcelRange row = worksheet.Cells[string.Format("{0}:{0}", i)];
// see if all cells of this row are empty
bool allEmpty = row.All(c => string.IsNullOrWhiteSpace(c.Text));
if (allEmpty)
{
continue; // skip this row
}
//check that we have correct number of non-empty first cells
bool hasEnoughNotEmpty = (row.Take(cellCount).Count(cell => !string.IsNullOrWhiteSpace(cell.Text)) == cellCount);
if (!hasEnoughNotEmpty)
{
throw new ArgumentException(
string.Format("file '{0}' row {1}, need at least {2} non-empty starting columns",
excelFileParam.fileName, i, cellCount));
}
T newElem = createElem(row.Take(cellCount).ToArray());
result.Add(newElem);
logger.LogTrace(string.Format("row {0}. Created element: {1}", i, newElem.ToString()));
}
logger.LogDebug(string.Format("{0} elements found", result.Count));
return(result);
}
}
}