private ParseException LogCellExceptionParseException(string worksheetName, ImportColumnTemplate column, string cellAddress)
{
ParseException parseException = new(worksheetName, column)
{
ColumnLetter = GetColumnLetter(cellAddress),
Row = GetRowNumber(cellAddress),
ExceptionType = ParseExceptionType.UnexpectedDataType,
};
return(parseException);
}
/// <summary>
/// Try's to set the excel value to the model property
/// </summary>
/// <param name="workSheet"></param>
/// <param name="rowNumber"></param>
/// <param name="coltemplate"></param>
/// <param name="cell"></param>
/// <param name="value"></param>
private void SetValue(ExcelWorksheet workSheet, int rowNumber, ImportColumnTemplate coltemplate, ExcelRange cell, object value)
{
try
{
// get info about property
PropertyInfo modelPropertyInfo = _model.GetType().GetProperty(coltemplate.Column.ModelProperty);
// set value of property
modelPropertyInfo.SetValue(_model, value, null);
}
catch (Exception ex)
{
_singleRowErrors.Add(_excelExtensions.LogDeveloperException(workSheet.Name, coltemplate, cell.Address, rowNumber, ex.Message));
}
}
/// <summary>
/// Try to parse the cell by the columns format type.
/// </summary>
/// <param name="workSheet"></param>
/// <param name="rowNumber"></param>
/// <param name="coltemplate"></param>
/// <param name="cell"></param>
/// <remarks>Will throw <see cref="NullReferenceException"/> errors. disable them when debugging through this dll file. </remarks>
private void ParseCell(ExcelWorksheet workSheet, int rowNumber, ImportColumnTemplate coltemplate, ExcelRange cell)
{
switch (coltemplate.Column.Format)
{
case FormatType.Bool:
{
try
{
//try to parse
bool value = _parser.ParseBool(cell);
SetValue(workSheet, rowNumber, coltemplate, cell, value);
}
catch (NullReferenceException)
{
_singleRowErrors.Add(_excelExtensions.LogNullReferenceException(workSheet.Name, coltemplate, cell.Address, rowNumber));
}
catch (Exception)
{
_singleRowErrors.Add(_excelExtensions.LogCellException(workSheet.Name, coltemplate, cell.Address, rowNumber));
}
break;
}
case FormatType.Currency:
{
try
{
decimal?value = _parser.ParseCurrency(cell);
SetValue(workSheet, rowNumber, coltemplate, cell, value);
}
catch (NullReferenceException)
{
_singleRowErrors.Add(_excelExtensions.LogNullReferenceException(workSheet.Name, coltemplate, cell.Address, rowNumber));
}
catch (Exception)
{
_singleRowErrors.Add(_excelExtensions.LogCellException(workSheet.Name, coltemplate, cell.Address, rowNumber));
}
break;
}
case FormatType.Date:
{
try
{
DateTime?date = _parser.ParseDate(cell);
SetValue(workSheet, rowNumber, coltemplate, cell, date);
}
catch (NullReferenceException)
{
_singleRowErrors.Add(_excelExtensions.LogNullReferenceException(workSheet.Name, coltemplate, cell.Address, rowNumber));
}
catch (Exception)
{
_singleRowErrors.Add(_excelExtensions.LogCellException(workSheet.Name, coltemplate, cell.Address, rowNumber));
}
break;
}
case FormatType.Duration:
{
try
{
TimeSpan?duration = _parser.ParseTimeSpan(cell);
SetValue(workSheet, rowNumber, coltemplate, cell, duration);
}
catch (NullReferenceException)
{
_singleRowErrors.Add(_excelExtensions.LogNullReferenceException(workSheet.Name, coltemplate, cell.Address, rowNumber));
}
catch (Exception)
{
_singleRowErrors.Add(_excelExtensions.LogCellException(workSheet.Name, coltemplate, cell.Address, rowNumber));
}
break;
}
case FormatType.Percent:
{
try
{
decimal?value = _parser.ParsePercent(cell);
SetValue(workSheet, rowNumber, coltemplate, cell, value);
}
catch (NullReferenceException)
{
_singleRowErrors.Add(_excelExtensions.LogNullReferenceException(workSheet.Name, coltemplate, cell.Address, rowNumber));
}
catch (Exception)
{
_singleRowErrors.Add(_excelExtensions.LogCellException(workSheet.Name, coltemplate, cell.Address, rowNumber));
}
break;
}
case FormatType.Double:
{
try
{
double?value = _parser.ParseDouble(cell);
SetValue(workSheet, rowNumber, coltemplate, cell, value);
}
catch (NullReferenceException)
{
_singleRowErrors.Add(_excelExtensions.LogNullReferenceException(workSheet.Name, coltemplate, cell.Address, rowNumber));
}
catch (Exception)
{
_singleRowErrors.Add(_excelExtensions.LogCellException(workSheet.Name, coltemplate, cell.Address, rowNumber));
}
break;
}
case FormatType.Decimal:
{
try
{
decimal?value = _parser.ParseDecimal(cell);
SetValue(workSheet, rowNumber, coltemplate, cell, value);
}
catch (NullReferenceException)
{
_singleRowErrors.Add(_excelExtensions.LogNullReferenceException(workSheet.Name, coltemplate, cell.Address, rowNumber));
}
catch (Exception)
{
_singleRowErrors.Add(_excelExtensions.LogCellException(workSheet.Name, coltemplate, cell.Address, rowNumber));
}
break;
}
case FormatType.DecimalList:
{
try
{
decimal?val = null;
try
{
val = _parser.ParseDecimal(cell);
}
catch (NullReferenceException)
{
//Fine if this is null. Lets keep going
}
// get info about property
PropertyInfo modelPropertyInfo = _model.GetType().GetProperty(coltemplate.Column.ModelProperty);
var list = modelPropertyInfo.GetValue(_model);
Dictionary <string, decimal?> decimalList;
if (list == null) //this is the first item in the list so we need to init the list.
{
decimalList = new Dictionary <string, decimal?>();
}
else
{
decimalList = (Dictionary <string, decimal?>)list;
}
decimalList.Add(coltemplate.ColumnHeaderOptions[0], val);
modelPropertyInfo.SetValue(_model, decimalList, null);
}
catch (NullReferenceException)
{
_singleRowErrors.Add(_excelExtensions.LogNullReferenceException(workSheet.Name, coltemplate, cell.Address, rowNumber));
}
catch (Exception)
{
_singleRowErrors.Add(_excelExtensions.LogCellException(workSheet.Name, coltemplate, cell.Address, rowNumber));
}
break;
}
case FormatType.Int:
{
try
{
int?value = _parser.ParseInt(cell);
SetValue(workSheet, rowNumber, coltemplate, cell, value);
}
catch (NullReferenceException)
{
_singleRowErrors.Add(_excelExtensions.LogNullReferenceException(workSheet.Name, coltemplate, cell.Address, rowNumber));
}
catch (Exception)
{
_singleRowErrors.Add(_excelExtensions.LogCellException(workSheet.Name, coltemplate, cell.Address, rowNumber));
}
break;
}
case FormatType.StringList:
{
try
{
_parser.CheckIfCellIsNullOrEmpty(cell, "a string list");
string cellVal = cell.Value.ToString().Trim();
if (string.IsNullOrEmpty(cellVal) && !string.IsNullOrEmpty(cell.Formula))
{
//We have a formula which is setting the field to blank instead of null
throw new NullReferenceException();
}
// get info about property
PropertyInfo modelPropertyInfo = _model.GetType().GetProperty(coltemplate.Column.ModelProperty);
object list = modelPropertyInfo.GetValue(_model);
Dictionary <string, string> stringlist;
if (list == null) //this is the first item in the list so we need to init the list.
{
stringlist = new Dictionary <string, string>();
}
else
{
stringlist = (Dictionary <string, string>)list;
}
stringlist.Add(coltemplate.ColumnHeaderOptions[0], cellVal);
modelPropertyInfo.SetValue(_model, stringlist, null);
}
catch (NullReferenceException)
{
_singleRowErrors.Add(_excelExtensions.LogNullReferenceException(workSheet.Name, coltemplate, cell.Address, rowNumber));
}
catch (Exception)
{
_singleRowErrors.Add(_excelExtensions.LogCellException(workSheet.Name, coltemplate, cell.Address, rowNumber));
}
break;
}
case FormatType.String:
//uses default
default:
{
try
{
string value = _parser.ParseString(cell);
SetValue(workSheet, rowNumber, coltemplate, cell, value);
}
catch (NullReferenceException)
{
_singleRowErrors.Add(_excelExtensions.LogNullReferenceException(workSheet.Name, coltemplate, cell.Address, rowNumber));
}
catch (Exception)
{
_singleRowErrors.Add(_excelExtensions.LogCellException(workSheet.Name, coltemplate, cell.Address, rowNumber));
}
break;
}
}
}
public KeyValuePair <int, ParseException> LogCellException(string worksheetName, ImportColumnTemplate displayName, string cellAddress, int rowNumber)
{
ParseException parseException = LogCellExceptionParseException(worksheetName, displayName, cellAddress);
return(new KeyValuePair <int, ParseException>(rowNumber, parseException));
}
public KeyValuePair <string, ParseException> LogCellException(string worksheetName, ImportColumnTemplate displayName, string cellAddress, string modelPropertyName)
{
ParseException parseException = LogCellExceptionParseException(worksheetName, displayName, cellAddress);
return(new KeyValuePair <string, ParseException>(modelPropertyName, parseException));
}
private ParseException LogNullReferenceExceptionParseException(string worksheetName, ImportColumnTemplate column, string cellAddress)
{
ParseException parseException = new(worksheetName, column)
{
ColumnLetter = GetColumnLetter(cellAddress),
Row = GetRowNumber(cellAddress),
ExceptionType = ParseExceptionType.MissingData,
};
return(parseException);
}
private ParseException LogDeveloperExceptionParseException(string worksheetName, ImportColumnTemplate importColumn, string cellAddress, string exeptionMessage)
{
//ParseException parseException = new(worksheetName, displayName, cellAddress, null, "An error occurred when trying to set the property info. The error is: " + message)
ParseException parseException = new(worksheetName, importColumn.Column)
{
ColumnLetter = GetColumnLetter(cellAddress),
Message = $"An error occurred when trying to set the property info. The error is: {exeptionMessage}",
Row = GetRowNumber(cellAddress),
ExceptionType = ParseExceptionType.Generic,
Severity = ParseExceptionSeverity.Error,
};
return(parseException);
}
public KeyValuePair <int, ParseException> LogDeveloperException(string worksheetName, ImportColumnTemplate importColumn, string cellAddress, int rowNumber, string message)
{
ParseException parseException = LogDeveloperExceptionParseException(worksheetName, importColumn, cellAddress, message);
return(new KeyValuePair <int, ParseException>(rowNumber, parseException));
}
public KeyValuePair <string, ParseException> LogDeveloperException(string worksheetName, ImportColumnTemplate importColumn, string cellAddress, string message, string modelPropertyName)
{
ParseException parseException = LogDeveloperExceptionParseException(worksheetName, importColumn, cellAddress, message);
return(new KeyValuePair <string, ParseException>(modelPropertyName, parseException));
}