private bool AllBlockDataColumnsUnset(BlockDataColumns blockDataColumns)
{
return(blockDataColumns.AmountFirstHalf == 0 &&
blockDataColumns.AmountSecondHalf == 0 &&
blockDataColumns.Date == 0 &&
blockDataColumns.Comments == 0);
}
private bool AllBlockDataColumnsSet(BlockDataColumns blockDataColumns)
{
return(blockDataColumns.AmountFirstHalf != 0 &&
blockDataColumns.AmountSecondHalf != 0 &&
blockDataColumns.Date != 0 &&
blockDataColumns.Comments != 0);
}
private string GetBlockHeader(ExcelWorksheet sheet, SheetNavigation value, BlockDataColumns blockData)
{
var amountFirstHalfValue = sheet.GetValue <string>(value.BlockHeaderRow, blockData.AmountFirstHalf);
var amountSecondHalfValue = sheet.GetValue <string>(value.BlockHeaderRow, blockData.AmountSecondHalf);
var dateValue = sheet.GetValue <string>(value.BlockHeaderRow, blockData.Date);
var commentsValue = sheet.GetValue <string>(value.BlockHeaderRow, blockData.Comments);
return(amountFirstHalfValue ??
amountSecondHalfValue ??
dateValue ??
commentsValue ?? throw new Exception($"Could not find block header for {sheet.Name}. AmountFirstHalf column ID: {blockData.AmountFirstHalf}"));
}
private IEnumerable <BlockDataColumns> GetBlocksToRead(KeyValuePair <string, SheetNavigation> sheetNavigation)
{
var workbook = _excelPackage.Workbook.Worksheets.First(sh => sh.Name == sheetNavigation.Key);
var navigationRowId = GetNavigationRowId();
var blockStartColumn = GetBlockStartColumn(workbook, navigationRowId);
var blockDataColumns = new BlockDataColumns();
for (int i = blockStartColumn; i <= workbook.Dimension.Columns; ++i)
{
var value = workbook.GetValue <string>(navigationRowId, i);
if (value == null)
{
continue;
}
if (AllBlockDataColumnsSet(blockDataColumns))
{
yield return(blockDataColumns);
blockDataColumns = new BlockDataColumns();
}
//if END was found but blockDataColumns contains some valid values - row is malformed
if (value.ToLower() == ExcelDataBlockColumnNaming.EndLowerCase)
{
if (AllBlockDataColumnsUnset(blockDataColumns))
{
yield break;
}
throw new Exception($"Malformed row that identifies which blocks to read for {workbook.Name}." +
$" Found END token before all header columns had their column identifiers");
}
if (value == ExcelDataBlockColumnNaming.AmountFirstHalf)
{
blockDataColumns.AmountFirstHalf = i;
}
else if (value == ExcelDataBlockColumnNaming.AmountSecondHalf)
{
blockDataColumns.AmountSecondHalf = i;
}
else if (value == ExcelDataBlockColumnNaming.Date)
{
blockDataColumns.Date = i;
}
else if (value == ExcelDataBlockColumnNaming.Comments)
{
blockDataColumns.Comments = i;
}
}
throw new Exception($"Malformed row that identifies which blocks to read for {workbook.Name} - did not find END token");
}
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
});
}
}
