public static Movimento TryParse2013(IXLRangeRow row)
{
try
{
var data = (DateTime)row.Cell(1).Value;
var categoria = (string)row.Cell(2).Value;
var descrizione = (string)row.Cell(3).Value;
var spesa = Convert.ToDecimal(row.Cell(4).Value);
return new Movimento
{
Data = data,
Categoria = categoria,
Descrizione = descrizione,
Spesa = spesa,
};
}
catch
{
return null;
}
}
/// <summary>
/// RenderRow
/// </summary>
/// <param name="iXLRangeRow"></param>
/// <param name="builder"></param>
/// <param name="columnsCount"></param>
static void RenderRow(IXLRangeRow iXLRangeRow, ref StringBuilder builder, int columnsCount)
{
for (int iRow = 1; iRow <= columnsCount; iRow++)
{
String columnValue = iXLRangeRow.Cell(iRow).GetString();
builder.Append("\"" + columnValue + "\"").Append(",");
}
}
/// <summary>
/// IsBlankRow
/// </summary>
/// <param name="iXLRangeRow"></param>
/// <param name="columnsCount"></param>
/// <returns></returns>
static bool IsBlankRow(IXLRangeRow iXLRangeRow, int columnsCount)
{
bool isEmpty = true;
for (int jRow = 1; jRow <= columnsCount; jRow++)
{
if (iXLRangeRow.Cell(jRow).IsEmpty() == false)
{
isEmpty = false;
break;
}
}
return isEmpty;
}
public void Add(IXLRangeRow range)
{
_ranges.Add((XLRangeRow)range);
}
public static List <string> UpdateContentFromExcelRow <T>(T toUpdate, ExcelHeaderRow headerInfo,
IXLRangeRow toProcess)
{
// ReSharper disable StringLiteralTypo
var skipColumns = new List <string>
{
"contentid",
"id",
"contentversion",
"lastupdatedon",
"originalfilename",
"pointdetail"
};
// ReSharper restore StringLiteralTypo
var properties = typeof(T).GetProperties().ToList();
var propertyNames = properties.Select(x => x.Name.ToLower()).ToList();
var columnNames = headerInfo.Columns.Where(x => !string.IsNullOrWhiteSpace(x.ColumnHeader))
.Select(x => x.ColumnHeader.TrimNullToEmpty().ToLower()).ToList();
var namesToProcess = propertyNames.Intersect(columnNames).Where(x => !skipColumns.Any(x.StartsWith))
.ToList();
var propertiesToUpdate = properties.Where(x => namesToProcess.Contains(x.Name.ToLower())).ToList();
var returnString = new List <string>();
foreach (var loopProperties in propertiesToUpdate)
{
if (loopProperties.PropertyType == typeof(string))
{
var excelResult = GetStringFromExcelRow(headerInfo, toProcess, loopProperties.Name);
if (excelResult.ValueParsed == null || !excelResult.ValueParsed.Value)
{
returnString.Add($"Row {toProcess.RowNumber()} - could not process {loopProperties.Name}");
}
loopProperties.SetValue(toUpdate, excelResult.ParsedValue.TrimNullToEmpty());
}
else if (loopProperties.PropertyType == typeof(Guid?))
{
var excelResult = GetGuidFromExcelRow(headerInfo, toProcess, loopProperties.Name);
if (excelResult.ValueParsed == null || !excelResult.ValueParsed.Value)
{
returnString.Add($"Row {toProcess.RowNumber()} - could not process {loopProperties.Name}");
}
loopProperties.SetValue(toUpdate, excelResult.ParsedValue);
}
else if (loopProperties.PropertyType == typeof(Guid))
{
var excelResult = GetGuidFromExcelRow(headerInfo, toProcess, loopProperties.Name);
if (excelResult.ValueParsed == null || !excelResult.ValueParsed.Value ||
excelResult.ParsedValue == null)
{
returnString.Add($"Row {toProcess.RowNumber()} - could not process {loopProperties.Name}");
}
loopProperties.SetValue(toUpdate, excelResult.ParsedValue);
}
else if (loopProperties.PropertyType == typeof(DateTime?))
{
var excelResult = GetDateTimeFromExcelRow(headerInfo, toProcess, loopProperties.Name);
if (excelResult.ValueParsed == null || !excelResult.ValueParsed.Value)
{
returnString.Add($"Row {toProcess.RowNumber()} - could not process {loopProperties.Name}");
}
loopProperties.SetValue(toUpdate, excelResult.ParsedValue);
}
else if (loopProperties.PropertyType == typeof(DateTime))
{
var excelResult = GetDateTimeFromExcelRow(headerInfo, toProcess, loopProperties.Name);
if (excelResult.ValueParsed == null || !excelResult.ValueParsed.Value ||
excelResult.ParsedValue == null)
{
returnString.Add($"Row {toProcess.RowNumber()} - could not process {loopProperties.Name}");
}
loopProperties.SetValue(toUpdate, excelResult.ParsedValue);
}
else if (loopProperties.PropertyType == typeof(double?))
{
var excelResult = GetDoubleFromExcelRow(headerInfo, toProcess, loopProperties.Name);
if (excelResult.ValueParsed == null || !excelResult.ValueParsed.Value)
{
returnString.Add($"Row {toProcess.RowNumber()} - could not process {loopProperties.Name}");
}
loopProperties.SetValue(toUpdate, excelResult.ParsedValue);
}
else if (loopProperties.PropertyType == typeof(double))
{
var excelResult = GetDoubleFromExcelRow(headerInfo, toProcess, loopProperties.Name);
if (excelResult.ValueParsed == null || !excelResult.ValueParsed.Value ||
excelResult.ParsedValue == null)
{
returnString.Add($"Row {toProcess.RowNumber()} - could not process {loopProperties.Name}");
}
loopProperties.SetValue(toUpdate, excelResult.ParsedValue);
}
else if (loopProperties.PropertyType == typeof(int?))
{
var excelResult = GetIntFromExcelRow(headerInfo, toProcess, loopProperties.Name);
if (excelResult.ValueParsed == null || !excelResult.ValueParsed.Value)
{
returnString.Add($"Row {toProcess.RowNumber()} - could not process {loopProperties.Name}");
}
loopProperties.SetValue(toUpdate, excelResult.ParsedValue);
}
else if (loopProperties.PropertyType == typeof(int))
{
var excelResult = GetIntFromExcelRow(headerInfo, toProcess, loopProperties.Name);
if (excelResult.ValueParsed == null || !excelResult.ValueParsed.Value ||
excelResult.ParsedValue == null)
{
returnString.Add($"Row {toProcess.RowNumber()} - could not process {loopProperties.Name}");
}
loopProperties.SetValue(toUpdate, excelResult.ParsedValue);
}
else if (loopProperties.PropertyType == typeof(bool?))
{
var excelResult = GetBoolFromExcelRow(headerInfo, toProcess, loopProperties.Name);
if (excelResult.ValueParsed == null || !excelResult.ValueParsed.Value)
{
returnString.Add($"Row {toProcess.RowNumber()} - could not process {loopProperties.Name}");
}
loopProperties.SetValue(toUpdate, excelResult.ParsedValue);
}
else if (loopProperties.PropertyType == typeof(bool))
{
var excelResult = GetBoolFromExcelRow(headerInfo, toProcess, loopProperties.Name);
if (excelResult.ValueParsed == null || !excelResult.ValueParsed.Value ||
excelResult.ParsedValue == null)
{
returnString.Add($"Row {toProcess.RowNumber()} - could not process {loopProperties.Name}");
}
loopProperties.SetValue(toUpdate, excelResult.ParsedValue);
}
else
{
returnString.Add(
$"Row {toProcess.RowNumber()} - could not process {loopProperties.Name}, not a recognized type");
}
}
if (toUpdate is PointContentDto pointDto)
{
var excelResult = GetPointDetails(headerInfo, toProcess);
if (excelResult.Any(x => x.ValueParsed == null) || excelResult.Any(x => !x.ValueParsed.Value) ||
excelResult.Any(x => x.ParsedValue == null))
{
returnString.Add($"Row {toProcess.RowNumber()} - could not process Point Details");
}
else
{
pointDto.PointDetails = excelResult.Select(x => x.ParsedValue).ToList();
pointDto.PointDetails.ForEach(x => x.PointContentId = pointDto.ContentId);
}
}
return(returnString);
}
public static ExcelValueParse <string> GetStringFromExcelRow(ExcelHeaderRow headerInfo, IXLRangeRow toProcess,
string columnName)
{
var contentIdColumn = headerInfo.Columns.Single(x => string.Equals(x.ColumnHeader,
columnName.TrimNullToEmpty(), StringComparison.CurrentCultureIgnoreCase));
var stringValue = toProcess.Worksheet.Cell(toProcess.RowNumber(), contentIdColumn.ExcelSheetColumn).Value
.ToString();
return(new ExcelValueParse <string>
{
ParsedValue = stringValue, StringValue = stringValue, ValueParsed = true
});
}
public static List <ExcelValueParse <PointDetail> > GetPointDetails(ExcelHeaderRow headerInfo,
IXLRangeRow toProcess)
{
var contentColumns = headerInfo.Columns.Where(x => x.ColumnHeader.StartsWith("PointDetail"));
var returnList = new List <ExcelValueParse <PointDetail> >();
foreach (var loopColumns in contentColumns)
{
var stringValue = toProcess.Worksheet.Cell(toProcess.RowNumber(), loopColumns.ExcelSheetColumn)
.GetString();
if (string.IsNullOrWhiteSpace(stringValue))
{
continue;
}
var toAdd = new ExcelValueParse <PointDetail> {
StringValue = stringValue
};
returnList.Add(toAdd);
var splitList = stringValue.RemoveNewLines().TrimNullToEmpty().Split("||")
.Select(x => x.TrimNullToEmpty()).ToList();
if (splitList.Count != 3)
{
toAdd.ParsedValue = null;
toAdd.ValueParsed = false;
continue;
}
PointDetail pointDetail;
//
// Content Id - new or db retrieved PointDetail()
//
if (splitList[0].Length <= 10 || !splitList[0].StartsWith("ContentId:"))
{
pointDetail = new PointDetail {
ContentId = Guid.NewGuid(), CreatedOn = DateTime.Now
};
}
else
{
var contentIdString = splitList[0].Substring(10, splitList[0].Length - 10).TrimNullToEmpty();
if (!Guid.TryParse(contentIdString, out var contentId))
{
toAdd.ParsedValue = null;
toAdd.ValueParsed = false;
continue;
}
var db = Db.Context().Result;
var possiblePoint = db.PointDetails.Single(x => x.ContentId == contentId);
//Content Id specified but no db entry - error, exit
if (possiblePoint == null)
{
toAdd.ParsedValue = null;
toAdd.ValueParsed = false;
continue;
}
pointDetail = possiblePoint;
pointDetail.LastUpdatedOn = DateTime.Now;
}
//
//Get the data type first so it can be used to create a new point if needed
//
if (splitList[1].Length <= 5 || !splitList[1].StartsWith("Type:"))
{
toAdd.ParsedValue = null;
toAdd.ValueParsed = false;
continue;
}
var dataTypeString = splitList[1].Substring(5, splitList[1].Length - 5).TrimNullToEmpty();
if (!Db.PointDetailDataTypeIsValid(dataTypeString))
{
toAdd.ParsedValue = null;
toAdd.ValueParsed = false;
continue;
}
pointDetail.DataType = dataTypeString;
//
// Point Detail Data
//
if (splitList[2].Length <= 5 || !splitList[2].StartsWith("Data:"))
{
//Empty Data - error
toAdd.ParsedValue = null;
toAdd.ValueParsed = false;
continue;
}
try
{
var jsonString = splitList[2].Substring(5, splitList[2].Length - 5);
var detailData = Db.PointDetailDataFromIdentifierAndJson(dataTypeString, jsonString);
var validationResult = detailData.Validate();
if (!validationResult.isValid)
{
toAdd.ParsedValue = null;
toAdd.ValueParsed = false;
continue;
}
pointDetail.StructuredDataAsJson = jsonString;
}
catch
{
toAdd.ParsedValue = null;
toAdd.ValueParsed = false;
continue;
}
toAdd.ParsedValue = pointDetail;
toAdd.ValueParsed = true;
}
return(returnList);
}
public static ExcelValueParse <bool?> GetBoolFromExcelRow(ExcelHeaderRow headerInfo, IXLRangeRow toProcess,
string columnName)
{
var contentIdColumn = headerInfo.Columns.Single(x => string.Equals(x.ColumnHeader,
columnName.TrimNullToEmpty(), StringComparison.CurrentCultureIgnoreCase));
var stringValue = toProcess.Worksheet.Cell(toProcess.RowNumber(), contentIdColumn.ExcelSheetColumn).Value
.ToString();
if (string.IsNullOrWhiteSpace(stringValue))
{
return new ExcelValueParse <bool?> {
ParsedValue = null, StringValue = stringValue, ValueParsed = true
}
}
;
if (bool.TryParse(stringValue, out var parsedValue))
{
return new ExcelValueParse <bool?>
{
ParsedValue = parsedValue, StringValue = stringValue, ValueParsed = true
}
}
;
return(new ExcelValueParse <bool?> {
ParsedValue = null, StringValue = stringValue, ValueParsed = false
});
}
public SubtotalGroup(int level, int column, string groupTitle, IXLRange range, IXLRangeRow summaryRow, bool pageBreaks)
{
Column = column;
SummaryRow = summaryRow;
PageBreaks = pageBreaks;
Level = level;
GroupTitle = groupTitle;
Range = range;
}