public void ParseTo(string path, ILiteDatabase db)
{
var sourceFile = new SourceFile {
Filename = Path.GetFileName(path),
LastWriteTimeUtc = File.GetLastWriteTimeUtc(path),
Length = new FileInfo(path).Length
};
sourceFiles.Insert(sourceFile);
db.Commit();
using (var fileStream = new FileStream(path, FileMode.Open, FileAccess.Read)) {
IWorkbook book;
if (Path.GetExtension(path).ToLowerInvariant() == ".xls")
{
book = new HSSFWorkbook(fileStream);
}
else if (Path.GetExtension(path).ToLowerInvariant() == ".xlsx")
{
book = new XSSFWorkbook(fileStream);
}
else
{
throw new ArgumentException("Unsupported file type!");
}
var formatter = new DataFormatter();
var context = new WorkbookParserContext();
for (var s = 0; s < book.NumberOfSheets; s++)
{
var sheet = book.GetSheetAt(s);
for (var r = sheet.FirstRowNum; r <= sheet.LastRowNum; r++)
{
var row = sheet.GetRow(r);
if (row == null)
{
continue;
}
foreach (var cell in row.Cells)
{
var cellStrValue = formatter.FormatCellValue(cell).Trim();
var cellRangeAddress = GetCellRangeAddress(cell, sheet);
ParseCell(cell, cellStrValue, cellRangeAddress, context, out bool skipRow);
if (skipRow)
{
break;
}
}
}
}
}
}
void InsertEduProgram(WorkbookParserContext context)
{
var eduProgram = eduPrograms.FindOne(ep => ep.Title == context.EduProgram.Title &&
ep.ProfileTitle == context.EduProgram.ProfileTitle &&
ep.EduLevelId == context.EduLevel.Id &&
ep.DivisionId == context.Division.Id);
if (eduProgram == null)
{
eduProgram = context.EduProgram;
eduProgram.Id = 0;
var id = eduPrograms.Insert(eduProgram);
db.Commit();
eduProgram.Id = id.AsInt32;
}
context.EduProgram = eduProgram;
}
public void ParseCellOnce(ICell cell, string cellStrValue, CellRangeAddress cellRangeAddress, WorkbookParserContext context, out bool parseCellAgain, out bool skipRow)
{
parseCellAgain = false;
skipRow = false;
if (context.State == WorkbookParserState.Initial)
{
context.State = WorkbookParserState.Header;
parseCellAgain = true;
return;
}
if (context.State == WorkbookParserState.Header)
{
if (!cell.IsMergedCell)
{
if (cell.ColumnIndex == 0 && cellStrValue.Equals("№ п/п", StringComparison.CurrentCultureIgnoreCase))
{
context.State = WorkbookParserState.TableHeader;
return;
}
}
else if (cell.IsMergedCell && cellRangeAddress.NumberOfCells >= 10)
{
if (Regex.IsMatch(cellStrValue, "факультет|институт", RegexOptions.IgnoreCase))
{
var divisionTitle = cellStrValue.ToLower();
var division = divisions.FindOne(d => d.Title == divisionTitle);
if (division == null)
{
division = new Division {
Title = divisionTitle
};
var id = divisions.Insert(division);
db.Commit();
division.Id = id.AsInt32;
}
context.Division = division;
}
if (Regex.IsMatch(cellStrValue, "форма обучения", RegexOptions.IgnoreCase))
{
var eduForm = eduForms.FindOne(ef => ef.Title == cellStrValue);
if (eduForm == null)
{
eduForm = new EduForm {
Title = cellStrValue
};
var id = eduForms.Insert(eduForm);
db.Commit();
eduForm.Id = id.AsInt32;
}
context.EduForm = eduForm;
}
if (Regex.IsMatch(cellStrValue, "бюджет|договор", RegexOptions.IgnoreCase))
{
var financingTitle = cellStrValue.ToLower();
var financing = financings.FindOne(f => f.Title == financingTitle);
if (financing == null)
{
financing = new Financing {
Title = financingTitle
};
var id = financings.Insert(financing);
db.Commit();
financing.Id = id.AsInt32;
}
context.Financing = financing;
}
if (Regex.IsMatch(cellStrValue, "бакалавриат|специалитет|магистратур|подготовки кадров|основного общего|среднего общего", RegexOptions.IgnoreCase))
{
var eduLevelStrValue = GetEduLevelString(cellStrValue);
var eduLevel = eduLevels.FindOne(el => el.Title == eduLevelStrValue);
if (eduLevel == null)
{
eduLevel = new EduLevel {
Title = eduLevelStrValue
};
var id = eduLevels.Insert(eduLevel);
db.Commit();
eduLevel.Id = id.AsInt32;
}
context.EduLevel = eduLevel;
if (context.EduLevel.Title.StartsWith("специалитет СПО", StringComparison.CurrentCultureIgnoreCase))
{
context.IsCollegeList = true;
}
else
{
context.IsCollegeList = false;
}
var eduProgramTitle = Regex.Match(cellStrValue, @"«[^»]+»", RegexOptions.Singleline | RegexOptions.IgnoreCase).Value;
var eduProfileTitle = Regex.Match(cellStrValue, @"Профиль:(.*)", RegexOptions.Singleline | RegexOptions.IgnoreCase).Groups [1].Value;
if (string.IsNullOrEmpty(eduProfileTitle))
{
eduProfileTitle = Regex.Match(cellStrValue, @"на базе .* общего образования", RegexOptions.Singleline | RegexOptions.IgnoreCase).Value;
}
eduProgramTitle = FormatEduProgramTitle(eduProgramTitle);
eduProfileTitle = FormatEduProgramTitle(eduProfileTitle);
if (context.EduProgram == null)
{
context.EduProgram = new EduProgram();
}
context.EduProgram.Title = eduProgramTitle;
context.EduProgram.ProfileTitle = eduProfileTitle;
context.EduProgram.EduLevelId = context.EduLevel.Id;
context.EduProgram.DivisionId = context.Division.Id;
}
}
return;
}
if (context.State == WorkbookParserState.TableHeader && context.IsCollegeList)
{
if (cell.ColumnIndex == 5)
{
context.EduProgram.Exam1Title = cellStrValue;
}
else if (cell.ColumnIndex == 7)
{
context.EduProgram.Exam2Title = cellStrValue;
}
else if (cell.ColumnIndex > 7)
{
context.State = WorkbookParserState.List;
context.Order = 0;
skipRow = true;
InsertEduProgram(context);
}
return;
}
if (context.State == WorkbookParserState.TableHeader && !context.IsCollegeList)
{
if (cell.ColumnIndex == 4)
{
context.EduProgram.Exam1Title = cellStrValue;
}
else if (cell.ColumnIndex == 5)
{
context.EduProgram.Exam2Title = cellStrValue;
}
else if (cell.ColumnIndex == 6)
{
context.EduProgram.Exam3Title = cellStrValue;
}
else if (cell.ColumnIndex > 6)
{
context.State = WorkbookParserState.List;
context.Order = 0;
skipRow = true;
InsertEduProgram(context);
}
return;
}
if (context.State == WorkbookParserState.List)
{
if (cell.IsMergedCell && cellRangeAddress.NumberOfCells >= 10)
{
context.State = WorkbookParserState.Header;
parseCellAgain = true;
return;
}
if (cell.ColumnIndex == 0)
{
context.Applicant = new Applicant();
context.Applicant.Order = ++context.Order;
if (int.TryParse(cellStrValue, out int rankedOrder))
{
context.Applicant.RankedOrder = rankedOrder;
}
}
else if (cell.ColumnIndex == 1)
{
context.Applicant.Name = cellStrValue;
}
}
if (context.State == WorkbookParserState.List && context.IsCollegeList)
{
if (cell.ColumnIndex == 3)
{
context.Applicant.HasOriginal = cellStrValue.Equals("Оригинал", StringComparison.CurrentCultureIgnoreCase);
}
else if (cell.ColumnIndex == 5)
{
if (decimal.TryParse(cellStrValue, out decimal exam1Rate))
{
context.Applicant.Exam1Rate = exam1Rate;
}
}
else if (cell.ColumnIndex == 7)
{
context.Applicant.Exam2Mark = cellStrValue;
}
else if (cell.ColumnIndex == 9)
{
if (decimal.TryParse(cellStrValue, out decimal totalRate))
{
context.Applicant.TotalRate = totalRate;
}
}
else if (cell.ColumnIndex == 10)
{
context.Applicant.Status = cellStrValue;
}
else if (cell.ColumnIndex == 11)
{
context.Applicant.RejectReason = cellStrValue;
}
else if (cell.ColumnIndex > 11)
{
context.Applicant.EduProgramId = context.EduProgram.Id;
context.Applicant.EduFormId = context.EduForm.Id;
context.Applicant.FinancingId = context.Financing.Id;
applicants.Insert(context.Applicant);
skipRow = true;
}
return;
}
if (context.State == WorkbookParserState.List && !context.IsCollegeList)
{
if (cell.ColumnIndex == 2)
{
context.Applicant.HasOriginal = cellStrValue.Equals("Оригинал", StringComparison.CurrentCultureIgnoreCase);
}
else if (cell.ColumnIndex == 3)
{
context.Applicant.HasAgreement = cellStrValue.Equals("Да", StringComparison.CurrentCultureIgnoreCase);
}
if (cell.ColumnIndex == 4)
{
if (decimal.TryParse(cellStrValue, out decimal exam1Rate))
{
context.Applicant.Exam1Rate = exam1Rate;
}
}
else if (cell.ColumnIndex == 5)
{
if (decimal.TryParse(cellStrValue, out decimal exam2Rate))
{
context.Applicant.Exam2Rate = exam2Rate;
}
}
else if (cell.ColumnIndex == 6)
{
if (decimal.TryParse(cellStrValue, out decimal exam3Rate))
{
context.Applicant.Exam3Rate = exam3Rate;
}
}
else if (cell.ColumnIndex == 7)
{
if (decimal.TryParse(cellStrValue, out decimal achRate))
{
context.Applicant.AchRate = achRate;
}
}
else if (cell.ColumnIndex == 8)
{
if (decimal.TryParse(cellStrValue, out decimal totalRate))
{
context.Applicant.TotalRate = totalRate;
}
}
else if (cell.ColumnIndex == 9)
{
context.Applicant.Category = cellStrValue;
}
else if (cell.ColumnIndex == 10)
{
if (cellStrValue.Equals("Да", StringComparison.CurrentCultureIgnoreCase))
{
context.Applicant.HasPreemptiveRight = true;
}
}
else if (cell.ColumnIndex == 11)
{
context.Applicant.Status = cellStrValue;
}
else if (cell.ColumnIndex == 12)
{
context.Applicant.RejectReason = cellStrValue;
}
else if (cell.ColumnIndex > 12)
{
context.Applicant.EduProgramId = context.EduProgram.Id;
context.Applicant.EduFormId = context.EduForm.Id;
context.Applicant.FinancingId = context.Financing.Id;
applicants.Insert(context.Applicant);
skipRow = true;
}
return;
}
}
/// <summary>
/// Parses the cell, possibly multiple times according to the state changes.
/// </summary>
/// <param name="cell">Cell.</param>
/// <param name="cellStrValue">Cell string value.</param>
public void ParseCell(ICell cell, string cellStrValue, CellRangeAddress cellRangeAddress, WorkbookParserContext context, out bool skipRow)
{
var parseCellAgain = false;
do
{
ParseCellOnce(cell, cellStrValue, cellRangeAddress, context, out parseCellAgain, out skipRow);
} while (parseCellAgain);
}