private SheetNavigation SheetNavigation(ExcelWorksheet worksheet)
{
var navigationRowId = GetNavigationRowId();
var sheetNavigation = new SheetNavigation();
for (var columnId = 1; columnId <= worksheet.Dimension.Columns; ++columnId)
{
if (AllSheetNavigationFieldsSet(sheetNavigation))
{
return(sheetNavigation);
}
var value = worksheet.GetValue <string>(navigationRowId, columnId);
if (value == null)
{
continue;
}
if (value == SheetNavigationIdentifiers.GamybosPadalinys)
{
sheetNavigation.MakerColumn = columnId;
}
if (value.Contains(SheetNavigationIdentifiers.Kodas))
{
sheetNavigation.CodeColumn = columnId;
sheetNavigation = SheetNavigationWithDataAndHeaderIds(sheetNavigation, value);
}
if (value == SheetNavigationIdentifiers.PreparatoPavadinimas)
{
sheetNavigation.NameColumn = columnId;
}
}
return(null);
}
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 SheetNavigation SheetNavigationWithDataAndHeaderIds(SheetNavigation sheetNavigation, string excelValue)
{
var splitValues = excelValue.Split('-');
try
{
sheetNavigation.BlockHeaderRow = int.Parse(splitValues[1]);
sheetNavigation.DataStartRow = int.Parse(splitValues[2]);
return(sheetNavigation);
}
catch (Exception ex)
{
_logService.Error("Could not convert Kodas numeric values", ex);
throw;
}
}
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
});
}
}
private bool AllSheetNavigationFieldsSet(SheetNavigation sheetNavigation)
{
return(sheetNavigation.MakerColumn != 0 &&
sheetNavigation.NameColumn != 0 &&
sheetNavigation.CodeColumn != 0);
}
