public Column(IExtensions _excelExtensions, Type modelType, string modelPropertyName, FormatType format, int columnLetterAsInt, int?decimalPrecision = null)
{
_excelExtensions.GetExportModelPropertyNameAndDisplayName(modelType, modelPropertyName, out string textTitle);
ColumnLetter = _excelExtensions.GetColumnLetter(columnLetterAsInt);
ModelProperty = modelPropertyName;
HeaderTitle = textTitle;
Format = format;
DecimalPrecision = decimalPrecision;
}
/// <inheritdoc/>
public void FormatColumnRange(ExcelWorksheet itemcodeSheet, string startColumn, string endColumn, FormatType format, int?decimalPrecision = null)
{
if (_excelProvider.GetColumnNumber(startColumn) > _excelProvider.GetColumnNumber(endColumn))
{
throw new ArgumentOutOfRangeException(nameof(startColumn), $"{nameof(startColumn)} must be less than {nameof(endColumn)}");
}
string currenctColumn = startColumn;
do
{
FormatColumn(ref itemcodeSheet, currenctColumn, format, decimalPrecision);
//if there is only one col, finish after the styling
if (startColumn == endColumn)
{
break;
}
currenctColumn = _excelProvider.GetColumnLetter(_excelProvider.GetColumnNumber(currenctColumn) + 1);
}while (currenctColumn != endColumn);
}
/// <summary>
/// Parse through the sheet till the header row is found and assign column numbers to each of the col templates.
/// </summary>
/// <param name="columns"></param>
/// <param name="workSheet"></param>
/// <param name="headerRowId"></param>
/// <param name="RequiredFields"></param>
/// <param name="parseResults"></param>
private void FindColumnNamesAndCheckRequiredColumns(List <ImportColumnWithCellAddress> columns, ref ExcelWorksheet workSheet, ref int headerRowId)
{
foreach (ImportColumnWithCellAddress coltemplate in columns)
{
foreach (ExcelRangeBase firstRowCell in workSheet.Cells[headerRowId, workSheet.Dimension.Start.Column, headerRowId, workSheet.Dimension.End.Column])
{
if (coltemplate.ColumnHeaderOptions.Any(x => x.Equals(firstRowCell.Text, StringComparison.OrdinalIgnoreCase)))
{
coltemplate.ColumnNumber = firstRowCell.Start.Column;
continue;
}
}
if (coltemplate.IsRequired && coltemplate.ColumnNumber == 0)
{
ParseException parseException = new(workSheet.Name, coltemplate.Column)
{
ExceptionType = ParseExceptionType.RequiredColumnMissing,
Severity = ParseExceptionSeverity.Error,
};
_parseResults.Exceptions.Add(new KeyValuePair <int, ParseException>(headerRowId, parseException));
}
else if (coltemplate.ColumnNumber == 0 && coltemplate.IsRequired == false)
{
ParseException parseException = new(workSheet.Name, coltemplate.Column)
{
ExceptionType = ParseExceptionType.OptionalColumnMissing,
Severity = ParseExceptionSeverity.Warning,
};
_parseResults.Exceptions.Add(new KeyValuePair <int, ParseException>(headerRowId, parseException));
}
if (coltemplate.IsRequired == true)
{
_requiredFieldsColumnLocations.Add(_excelExtensions.GetColumnLetter(coltemplate.ColumnNumber));
}
}
}
private void CreateTestFile(ref ExcelWorksheet sheet, List <ParseTableTestBaseModel> models, int headerRow = 1, int startCol = 1)
{
int col = startCol;
sheet.Cells[$"{_excelExtensionsProvider.GetColumnLetter(col++)}{headerRow}"].Value = nameof(ParseTableTestBaseModel.RequiredText);
sheet.Cells[$"{_excelExtensionsProvider.GetColumnLetter(col++)}{headerRow}"].Value = nameof(ParseTableTestBaseModel.Date);
sheet.Cells[$"{_excelExtensionsProvider.GetColumnLetter(col++)}{headerRow}"].Value = nameof(ParseTableTestBaseModel.Decimal);
sheet.Cells[$"{_excelExtensionsProvider.GetColumnLetter(col++)}{headerRow}"].Value = nameof(ParseTableTestBaseModel.OptionalText);
int row = headerRow + 1;
col = startCol;
foreach (ParseTableTestBaseModel testModel in models)
{
sheet.Cells[$"{_excelExtensionsProvider.GetColumnLetter(col++)}{row}"].Value = testModel.RequiredText;
sheet.Cells[$"{_excelExtensionsProvider.GetColumnLetter(col++)}{row}"].Value = testModel.Date;
sheet.Cells[$"{_excelExtensionsProvider.GetColumnLetter(col++)}{row}"].Value = testModel.Decimal;
sheet.Cells[$"{_excelExtensionsProvider.GetColumnLetter(col++)}{row}"].Value = testModel.OptionalText;
//end of row
row++;
col = startCol;
}
}