public async override Task Parse(string filePath)
{
using (var transaction = await _ctx.Database.BeginTransactionAsync()) {
_logger.LogInformation($"Created transaction for file: '{filePath}', beginning data parsing.");
try {
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using (var package = new ExcelPackage(new FileInfo(filePath))) {
foreach (var sheet in package.Workbook.Worksheets)
{
var startRow = sheet.Dimension.Start.Row;
var endRow = sheet.Dimension.End.Row;
var startColumn = 5;
var endColumn = sheet.Dimension.End.Column;
// Create Regions
var regions = new List <Region>();
for (int i = startColumn; i <= endColumn; i++)
{
var region = sheet.Cells[1, i].Text;
regions.Add(new Region {
Name = region
});
}
await _ctx.Regions.AddRangeAsync(regions);
var categories = new Stack <Category>();
var newCategories = new List <Category>();
var onsByRegion = new List <OnsByRegion>();
for (int i = startRow + 1; i < endRow; i++)
{
var sectionNumber = sheet.Cells[i, 1].Text;
if (IsCategory(sectionNumber))
{
// Create category
var category = _parserCache.GetOrCreateCategory <Category>(sheet.Cells[i, 2].Text, out var isCreated);
if (isCreated)
{
newCategories.Add(category);
}
categories.Push(category);
}
else if (IsSubcategory(sectionNumber))
{
// Create subcategory
var subcategory = _parserCache.GetOrCreateCategory <Subcategory>(sheet.Cells[i, 2].Text, out var isCreated);
if (isCreated)
{
categories.Peek().Subcategories.Add(subcategory);
}
// Create database entries for each data value in subcategory
var onsByRegions = new List <OnsByRegion>();
for (int j = 3; j < endColumn; j++)
{
var value = sheet.Cells[i, j].Text;
if (!string.IsNullOrWhiteSpace(value) && Decimal.TryParse(value, out var decimalValue) || Decimal.TryParse(sheet.Cells[i + 1, j].Text, out decimalValue))
{
// On the topic of "sheet.Cells[i + 1, j].Text"
// Sometimes the value is one row below due to a multiline category title
// i.e 4.3 Water supply and miscellaneous services in ONSByRegion2019.xlxs
var entry = new OnsByRegion {
Region = regions[j - startColumn], Value = decimalValue
};
subcategory.OnsByRegion.Add(entry);
regions[j - startColumn].OnsByRegion.Add(entry);
onsByRegions.Add(entry);
}
}
await _ctx.OnsByRegion.AddRangeAsync(onsByRegions);
}
else
{
continue;
}
}
await _ctx.Categories.AddRangeAsync(newCategories);
var changes = await _ctx.SaveChangesAsync();
_logger.LogDebug($"Created {changes} entities for file '{filePath}'");
_logger.LogInformation($"Successfully parsed data for entities for file '{filePath}', committing transaction.");
await transaction.CommitAsync();
}
}
} catch (Exception exc) {
_logger.LogError(exc, $"Exception occurred while parsing file '{filePath}' with {nameof(DatasetParserRegion)}");
await transaction.RollbackAsync();
}
}
}
