private ExcelBlockData GetBlockData(BlockDataColumns blockDataColumns, KeyValuePair <string, SheetNavigation> sheetNavigation)
{
var sheet = _excelPackage.Workbook.Worksheets.First(sh => sh.Name == sheetNavigation.Key);
var blockHeader = GetBlockHeader(sheet, sheetNavigation.Value, blockDataColumns);
_logService.Information($"Parsing block header for '{blockHeader}'");
var parasedBlockHeader = ExcelParser.ParsedBlockHeader(blockHeader);
return(new ExcelBlockData
{
SheetName = sheetNavigation.Key,
Date = parasedBlockHeader,
ProductData = GetProductDataForBlock(sheet, sheetNavigation.Value, blockDataColumns, parasedBlockHeader)
});
}
private IEnumerable <ExcelProductData> GetProductDataForBlock(ExcelWorksheet sheet,
SheetNavigation sheetNavigation, BlockDataColumns blockDataColumns, DateTime blockDateHeader)
{
_logService.Information($"Getting product data for {blockDateHeader:yyyy-MM-dd}");
for (var i = sheetNavigation.DataStartRow; i < sheet.Dimension.Rows; ++i)
{
var maker = sheet.GetValue <string>(i, sheetNavigation.MakerColumn);
var code = sheet.GetValue <string>(i, sheetNavigation.CodeColumn);
var name = sheet.GetValue <string>(i, sheetNavigation.NameColumn);
if (EndOfData(new List <string> {
maker, code, name
}))
{
yield break;
}
if (code is null)
{
continue;
}
var amountFirstHalfCell = sheet.Cells[i, blockDataColumns.AmountFirstHalf];
var amountSecondHalfCell = sheet.Cells[i, blockDataColumns.AmountSecondHalf];
var dateCell = sheet.Cells[i, blockDataColumns.Date];
var commentsCell = sheet.Cells[i, blockDataColumns.Comments];
var details = commentsCell.GetValue <string>() ?? string.Empty;
var dataCells = new List <ExcelRange> {
amountFirstHalfCell, amountSecondHalfCell, dateCell, commentsCell
};
var allCellsEmpty = AllCellsEmpty(dataCells);
var cellComments = GetCellComments(dataCells);
var cellBackgroundColors = GetCellBackgroundColors(dataCells);
var cellsHaveShapes = CellsHaveShapes(dataCells, sheet.Drawings);
if (IsSkipableRow(allCellsEmpty, cellComments, cellBackgroundColors))
{
continue;
}
//we care if maker or name is null ONLY if we DON'T skip the row
if (maker is null)
{
throw new NoNullAllowedException($"'Gamybos padalinys' cannot be empty. {sheet.Name};{blockDateHeader:yyyy-MM-dd};{code}");
}
if (name is null)
{
throw new NoNullAllowedException($"'Pavadinimas' cannot be empty. {sheet.Name};{blockDateHeader:yyyy-MM-dd};{code}");
}
var amountFirstHalfText = amountFirstHalfCell.GetValue <string>();
var amountSecondHalfText = amountSecondHalfCell.GetValue <string>();
var amountFirstHalf = 0;
var amountSecondHalf = 0;
try
{
amountFirstHalf = ExcelParser.GetAmountIntValue(amountFirstHalfText);
amountSecondHalf = ExcelParser.GetAmountIntValue(amountSecondHalfText);
}
catch (Exception ex)
{
throw new FormatException($"Failed to parse amount for:{Environment.NewLine} {sheet.Name} {blockDateHeader:yyyy-MM-dd} {code} {name} {ex.Message}", ex);
}
yield return(new ExcelProductData
{
Maker = maker.Trim(),
Code = code.Trim(),
Name = name.Trim(),
AmountFirstHalf = amountFirstHalf,
AmountSecondHalf = amountSecondHalf,
Date = SetProductDate(amountFirstHalfCell, amountSecondHalfCell, blockDateHeader),
Details = details.Trim(),
OrderNumber = ExcelParser.ExtractOrderNumber(details.Trim()),
Comments = cellComments,
CellBackgroundColors = cellBackgroundColors,
HasShapes = cellsHaveShapes
});
}
}