private ParseResult RequiredString(ParsingCell cell, int maxLength = 0, int minLength = 0)
{
var input = CellValue(cell);
var Result = new ParseResult();
if (string.IsNullOrWhiteSpace(input))
{
Result.Errors.Add(AddCellError(cell, $"Field is required"));
}
else if ((maxLength > 0) && (input.Length > maxLength))
{
Result.Errors.Add(AddCellError(cell, $"Input string exceeds the maximum length of {maxLength}"));
}
else if ((minLength > 0) && (input.Length < minLength))
{
Result.Errors.Add(AddCellError(cell, $"Input string must be at least {minLength} characters"));
}
if (Result.InError == false)
{
Result.Result = input;
}
return(Result);
}
private ParseResult DelimetedDigitsOnly(ParsingCell cell, List <char> delimiters, int maxLength = 0, int minLength = 0, bool truncateSpaces = false)
{
// As of now, none of the Delimeted fields are Required
var input = CellValue(cell);
var Result = new ParseResult();
input = truncateSpaces ? input.Replace(" ", "") : input;
if ((maxLength > 0) && (input.Length > maxLength))
{
Result.Errors.Add(AddCellError(cell, $"Input string exceeds the maximum length of {maxLength}"));
}
else if ((minLength > 0) && (input.Length < minLength) && (input.Length > 0))
{
Result.Errors.Add(AddCellError(cell, $"Input string must be at least {minLength} characters"));
}
if (!input.All(c => (((c >= '0') && (c <= '9')) || (delimiters.Contains(c)))))
{
Result.Errors.Add(AddCellError(cell, $"Input string contains characters other than digits and any of the desired delimiters"));
}
if (Result.InError == false)
{
Result.Result = string.IsNullOrWhiteSpace(input) ? null : input;
}
return(Result);
}
private ParseResult NonRequiredDigitsOnly(ParsingCell cell, int maxLength = 0, int minLength = 0)
{
var input = CellValue(cell);
var Result = new ParseResult();
if ((maxLength > 0) && (input.Length > maxLength))
{
Result.Errors.Add(AddCellError(cell, $"Input string exceeds the maximum length of {maxLength}"));
}
else if ((minLength > 0) && (input.Length < minLength) && (input.Length > 0))
{
Result.Errors.Add(AddCellError(cell, $"Input string must be at least {minLength} characters"));
}
if (!input.All(c => ((c >= '0') && (c <= '9'))))
{
Result.Errors.Add(AddCellError(cell, $"Input string contains characters other than digits"));
}
if (Result.InError == false)
{
Result.Result = string.IsNullOrWhiteSpace(input) ? null : input;
}
return(Result);
}
private ParsingError AddCellError(ParsingCell cell, string message)
{
return(new ParsingError {
Cell = cell,
Message = message,
});
}
private string CellValue(ParsingCell cell)
{
var Result = "";
if (cell.Cell != null)
{
var cellType = cell.GetType();
switch (cell.Cell.CellType)
{
case CellType.String:
Result = cell.Cell.StringCellValue.Trim();
break;
case CellType.Numeric:
if (DateUtil.IsCellDateFormatted(cell.Cell))
{
DateTime date = cell.Cell.DateCellValue;
ICellStyle style = cell.Cell.CellStyle;
// Excel uses lowercase m for month whereas .Net uses uppercase
string format = style.GetDataFormatString().Replace('m', 'M');
Result = date.ToString(format);
}
else
{
Result = cell.Cell.NumericCellValue.ToString().Trim();
}
break;
case CellType.Boolean:
Result = cell.Cell.BooleanCellValue ? "TRUE" : "FALSE";
break;
case CellType.Error:
Result = FormulaError.ForInt(cell.Cell.ErrorCellValue).String;
break;
}
}
return(Result);
}
private ParseResult RequiredDate(ParsingCell cell)
{
var input = CellValue(cell);
var Result = new ParseResult();
DateTime TheDate = DateTime.Now;
if (string.IsNullOrWhiteSpace(input))
{
Result.Errors.Add(AddCellError(cell, $"Field is required"));
}
else if (!DateTime.TryParse(input, out TheDate))
{
Result.Errors.Add(AddCellError(cell, $"Unable to parse the given date"));
}
if (Result.InError == false)
{
Result.Result = TheDate.ToString("MM/dd/yyyy");
}
return(Result);
}
public ParsingError(ParsingCell cell, string message)
{
this.Cell = cell;
this.Message = message;
}
private Func <GUIContent> getSyntaxErrorContent(string message, string tooltip, ParsingCell cell)
{
switch (cell)
{
case ParsingCell.Label:
if (this.LabelErrorContent == null)
{
this.LabelErrorContent = new GUIContent(message);
}
this.LabelErrorContent.tooltip = tooltip;
return(() => this.LabelErrorContent);
case ParsingCell.Value:
if (this.ValueErrorContent == null)
{
this.ValueErrorContent = new GUIContent(message);
}
this.ValueErrorContent.tooltip = tooltip;
return(() => this.ValueErrorContent);
default:
return(() => new GUIContent(message));
}
}
private Delegate parseFunctionScript(string script, ParsingCell cell)
{
LambdaExpression scriptExpression = null;
try
{
ScriptParser parser = new ScriptParser(script);
scriptExpression = parser.Parse();
if ((scriptExpression.Parameters?.Count ?? 0) > 0)
{
if (scriptExpression.Parameters[0].Type == typeof(int))
{
object defaultValue = parser.ParameterSignatures[0].DefaultValue;
int defaultParam = 0;
if (defaultValue != null && defaultValue is int)
{
defaultParam = (int)defaultValue;
}
switch (cell)
{
case ParsingCell.Label:
this.LabelIsVarPrecision = true;
this.LabelVarPrecisionDigits = defaultParam;
break;
case ParsingCell.Value:
this.ValueIsVarPrecision = true;
this.ValueVarPrecisionDigits = defaultParam;
break;
default:
throw new NotImplementedException();
}
}
}
Logging.PostDebugMessage(this, "Parsed expression '{0}' from '{1}'.", scriptExpression, script);
return(scriptExpression.Compile());
}
catch (VOIDScriptSyntaxException sx)
{
return(this.getSyntaxErrorContent("Syntax Error", sx.Message, cell));
}
catch (InvalidOperationException iox)
{
return(this.getSyntaxErrorContent("Syntax Error",
string.Format("{0}: {1}", iox.GetType().Name, iox.Message), cell));
}
catch (FormatException fx)
{
return(this.getSyntaxErrorContent("Syntax Error",
string.Format("{0}: {1}", fx.GetType().Name, fx.Message), cell));
}
catch (VOIDScriptParserException px)
{
return(this.getSyntaxErrorContent("Parser Error", px.Message + " Please report!", cell));
}
catch (Exception ex)
{
Logging.PostErrorMessage(
"Compiler error processing VOIDScript line '{0}'. Please report!\n{1}: {2}\n{3}",
script, ex.GetType().Name, ex.Message, ex.StackTrace);
return(this.getSyntaxErrorContent("Compiler Error", ex.GetType().Name + ": " + ex.Message, cell));
}
}
public FileStreamResult ProcessExcelFile(Stream stream)
{
var size = stream.Length;
var Errors = new List <ParsingError>();
var NewRecords = new List <Record>();
var TotalErrors = 0;
var LatLongTypeID = 2; // enumProps.GetDBID(LocationTypes.LatLong);
IWorkbook wb = WorkbookFactory.Create(stream);
try {
IsError = CreateErrorStyle(wb);
IsComment = CreateCommentStyle(wb);
ISheet sheet = wb.GetSheetAt(0);
// Loop through each Row
// Loop through each Column in the current row
var UseDate = DateTime.UtcNow;
// Row is zero based, skip row 0 (title row), 1 = Example, 2 = Validation
for (var r = 3; r <= sheet.LastRowNum; r++)
{
IRow row = sheet.GetRow(r);
var staged = new Record {
UploadedByID = UsersID,
UploadedDate = UseDate,
Active = false,
};
var RowErrors = new List <ParsingError>();
for (var c = 2; c <= 24; c++) // c = 2 (Column C to start), 24 (Column Y to end)
// # - Req - Title - Validation
// -----------------------------------------------
// A - No - Parsing Errors - None
// B - No - Comments - None
// C - Yes - Map Image Name - 5 digits only
// D - Yes - City, Village - None (max 50)
// E - Yes - County - Minimum length = 5 (max 50)
// F - Yes - Defunct Twp - None (max 50)
// G - No - Lot Number - Numbers only, seperated by comma's, no spaces
// H - No - Section - "
// I - No - Tract - None
// J - No - Range - None
// K - Yes - Survey Date - MM/DD/YYYY
// L - Yes - Surveyor Name - None
// M - No - Surveyor No. - Digits only
// N - Yes - Address - Full address
// O - No - Cross Street - None
// P - No - Parcel Numbers - ###-##-###,###-##-###...
// Q - No - Volume - Digits only
// R - No - Page - "
// S - No - AFN... - None
// T - No - Subdivision - None
// U - No - Subd. Lot - Digits only, seperated by commas
// V - No - Survey Name - None
// W - No - Location - Latitude on Longitude seperated by commas
// X - No - Client - None
// Y - No - Notes - None
{
var pCell = new ParsingCell(row.GetCell(c), r, c);
if (c == 2)
{
// Column C: Map Image Name
var Check = RequiredString(pCell, 15, 5);
if (Check.InError)
{
RowErrors.AddRange(Check.Errors);
}
else
{
staged.MapImageName = Check.Result;
}
}
else if (c == 3)
{
// Column D: City, Village, Township
var Check = RequiredString(pCell, 25, 5);
if (Check.InError)
{
RowErrors.AddRange(Check.Errors);
}
else
{
staged.CityVillageTownship = Check.Result;
}
}
else if (c == 4)
{
// Column E: County
var Check = RequiredString(pCell, 15, 5);
if (Check.InError)
{
RowErrors.AddRange(Check.Errors);
}
else
{
staged.County = Check.Result;
}
}
else if (c == 5)
{
// Column F: Defunct Township
var Check = RequiredString(pCell, 20, 5);
if (Check.InError)
{
RowErrors.AddRange(Check.Errors);
}
else
{
staged.DefunctTownship = Check.Result.Replace("township", "");
}
}
else if (c == 6)
{
// Column G: Lot Numbers
var Check = DelimetedDigitsOnly(pCell, ',', 15, 0, true);
if (Check.InError)
{
RowErrors.AddRange(Check.Errors);
}
else
{
staged.LotNumbers = Check.Result;
}
}
else if (c == 7)
{
// Column H: Section
var Check = DelimetedDigitsOnly(pCell, ',', 15, 0, true);
if (Check.InError)
{
RowErrors.AddRange(Check.Errors);
}
else
{
staged.Section = Check.Result;
}
}
else if (c == 8)
{
// Column I: Tract
var Check = NonRequiredString(pCell, 15);
if (Check.InError)
{
RowErrors.AddRange(Check.Errors);
}
else
{
staged.Tract = Check.Result;
}
}
else if (c == 9)
{
// Column J: Range
var Check = NonRequiredString(pCell, 15);
if (Check.InError)
{
RowErrors.AddRange(Check.Errors);
}
else
{
staged.Range = Check.Result;
}
}
else if (c == 10)
{
// Column K: Survey Date
var Check = RequiredDate(pCell);
if (Check.InError)
{
RowErrors.AddRange(Check.Errors);
}
else
{
staged.SurveyDate = DateTime.Parse(Check.Result);
}
}
else if (c == 11)
{
// Column L: Surveyor Name
var Check = RequiredString(pCell, 25, 5);
// TODO: This Name needs to be transmuted to an actual ApplicationUser ID
if (Check.InError)
{
RowErrors.AddRange(Check.Errors);
}
else
{
staged.SurveyorName = Check.Result;
// TODO: Lookup the name in the security.AspNetUser table
// If found, add the ID to the staged.SurveyorID field
}
}
else if (c == 12)
{
// Column M: Surveyor Number
var Check = NonRequiredDigitsOnly(pCell, 5, 2);
if (Check.InError)
{
RowErrors.AddRange(Check.Errors);
}
else
{
staged.SurveyorNumber = Check.Result;
}
}
else if (c == 13)
{
// Column N: Address
var Check = RequiredString(pCell, 100, 5);
if (Check.InError)
{
RowErrors.AddRange(Check.Errors);
}
else
{
staged.Address = Check.Result;
}
}
else if (c == 14)
{
// Column O: Cross Street Name
var Check = NonRequiredString(pCell, 30);
if (Check.InError)
{
RowErrors.AddRange(Check.Errors);
}
else
{
staged.CrossStreet = Check.Result;
}
}
else if (c == 15)
{
// Column P: Parcel Numbers
var Check = DelimetedDigitsOnly(pCell, new List <char> {
',', '-'
}, 30);
if (Check.InError)
{
RowErrors.AddRange(Check.Errors);
}
else
{
staged.ParcelNumbers = Check.Result;
}
}
else if (c == 16)
{
// Column Q: Volume
var Check = NonRequiredDigitsOnly(pCell, 8, 1);
if (Check.InError)
{
RowErrors.AddRange(Check.Errors);
}
else
{
if (int.TryParse(Check.Result, out int value))
{
staged.DeedVolume = value; // TODO: This needs to be a TryParse so we can handle overflows to long
}
}
// TODO: Idealy, the method (in this case) NonRequiredDigitsOnly will return a Null or push an Error
}
else if (c == 17)
{
// Column R: Page
var Check = NonRequiredDigitsOnly(pCell, 6, 1);
if (Check.InError)
{
RowErrors.AddRange(Check.Errors);
}
else if (int.TryParse(Check.Result, out int value))
{
staged.DeedPage = value; // TODO: This needs to be a TryParse so we can handle overflows to long
}
}
else if (c == 18)
{
// Column S: AFN
var Check = NonRequiredString(pCell, 18, 3);
if (Check.InError)
{
RowErrors.AddRange(Check.Errors);
}
else
{
staged.AutomatedFileNumber = Check.Result;
}
}
else if (c == 19)
{
// Column T: Subdivision
var Check = NonRequiredString(pCell, 50, 5);
if (Check.InError)
{
RowErrors.AddRange(Check.Errors);
}
else
{
staged.Subdivision = Check.Result;
}
}
else if (c == 20)
{
// Column U: Subdivision-Sublot
var Check = DelimetedDigitsOnly(pCell, ',', 10, 0, true);
if (Check.InError)
{
RowErrors.AddRange(Check.Errors);
}
else
{
staged.Sublot = Check.Result;
}
}
else if (c == 21)
{
// Column V: Survey Name
var Check = NonRequiredString(pCell, 50);
if (Check.InError)
{
RowErrors.AddRange(Check.Errors);
}
else
{
staged.SurveyName = Check.Result;
}
}
else if (c == 22)
{
// Column W: Location
var Check = DelimetedDigitsOnly(pCell, new List <char> {
',', '.', '-'
}, 30, truncateSpaces: true);
int Parts = string.IsNullOrWhiteSpace(Check.Result) ? 0 : Check.Result.Split(',').Count();
if (Check.InError)
{
RowErrors.AddRange(Check.Errors);
}
else if ((Parts == 1) || (Parts > 2))
{
RowErrors.Add(new ParsingError(pCell, "Invalid Format for Location"));
}
else if (!string.IsNullOrWhiteSpace(Check.Result))
{
var Loc = new Location {
LocationTypeID = LatLongTypeID,
Latitude = Convert.ToDecimal(Check.Result.Split(',')[0]),
Longitude = Convert.ToDecimal(Check.Result.Split(',')[1])
};
staged.Location = Loc;
}
}
else if (c == 23)
{
// Column X: Client
var Check = NonRequiredString(pCell, 50);
if (Check.InError)
{
RowErrors.AddRange(Check.Errors);
}
else
{
staged.ClientName = Check.Result;
}
}
else if (c == 24)
{
// Column Y: Notes
var Check = NonRequiredString(pCell, 2000);
if (Check.InError)
{
RowErrors.AddRange(Check.Errors);
}
else
{
staged.Notes = Check.Result; // notes
}
}
} // foreach of the Columns
var CommentCell = row.GetCell(0) ?? row.CreateCell(0);
if (RowErrors.Count > 0)
{
CommentCell.SetCellValue(new XSSFRichTextString($"{RowErrors.Count} Errors:\n{string.Join("\n", RowErrors.Select(x => $"[{ColumnIndexToLetter(x.Cell.Column + 1)}] {x.Message}"))}"));
CommentCell.CellStyle = IsComment;
foreach (var error in RowErrors)
{
var cell = error.Cell.Cell == null?row.CreateCell(error.Cell.Column) : row.GetCell(error.Cell.Column);
cell.CellStyle = IsError;
}
}
else
{
NewRecords.Add(staged); // Only add the record if the row is valid
CommentCell.SetCellValue("Validation: OK");
}
Errors.AddRange(RowErrors);
if (RowErrors.Count > 0)
{
TotalErrors += RowErrors.Count;
}
else
{
// The row has validated successfully, check to see if it exists
var HasChodeMatches = DataContext.Records
.Where(x => x.HashCode == staged.GetHashCode())
.Select(x => x.ID)
.ToList();
if (HasChodeMatches.Count > 0)
{
// Hash code found, should be already in the database
// Add to validation column
TotalErrors++;
var MatchIDs = "";
foreach (var match in HasChodeMatches)
{
MatchIDs += $"{match},";
}
CommentCell.SetCellValue($"Validation: OK - Found {HasChodeMatches.Count} Potential hashcode matches - IDs: {MatchIDs}");
}
else
{
var Matches = DataContext.Records
.Where(x =>
x.MapImageName == staged.MapImageName &&
x.CityVillageTownship == staged.CityVillageTownship &&
x.State == staged.State &&
x.County == staged.County &&
x.DefunctTownship == staged.DefunctTownship &&
x.SurveyDate == staged.SurveyDate &&
x.Address == staged.Address
)
.Select(x => x.ID)
.ToList();
if (Matches.Count > 0)
{
TotalErrors++;
var MatchIDs = "";
foreach (var match in Matches)
{
MatchIDs += $"{match},";
}
CommentCell.SetCellValue($"Validation: OK - Found {Matches.Count} Potential field matches - IDs: {MatchIDs}");
}
}
}
} // foreach of the Rows
} catch (Exception e) {
Console.WriteLine($"Exceiption: {e.Message} - {e.StackTrace}");
}
if (TotalErrors > 0)
{
Console.WriteLine($"There were a total of {TotalErrors} errors.");
}
else
{
int WriteErrors = 0;
var bob = WriteErrors;
Console.WriteLine($"Saving {NewRecords.Count} Records to the database.");
ISheet sheet = wb.GetSheetAt(0);
for (int index = 0; index < NewRecords.Count; index++)
{
IRow row = sheet.GetRow(3 + index);
var r = NewRecords[index];
r.HashCode = r.GetHashCode();
try {
DataContext.Records.Add(r);
DataContext.SaveChanges(1);
row.GetCell(0).SetCellValue($"{row.GetCell(0).StringCellValue}, Save: OK - {r.ID}");
} catch (Exception e) {
row.GetCell(0).SetCellValue($"{row.GetCell(0).StringCellValue}, Save: Failed - {(e.InnerException != null ? e.InnerException.Message : e.Message)}");
WriteErrors++;
}
} // foreach of the Records
if (WriteErrors > 0)
{
Console.WriteLine($"There were a total of {WriteErrors} Write errors.");
}
}
var TheStream = new NPOIMemoryStream {
AllowClose = false
};
try {
wb.Write(TheStream);
} catch (Exception e) {
TheStream.Dispose();
logger.LogError(1, e, "Unable to export Blank Template to Excel");
}
TheStream.Seek(0, SeekOrigin.Begin);
TheStream.AllowClose = true;
return(File(TheStream, "application/vnd.ms-excel", $"{DateTime.Now.ToString("yy-MMdd")}-Testing.xlsx"));
}
