private static IEnumerable <StatisticalArea> GetMsoaPopulationEstimates(XLWorkbook xb)
{
if (!xb.TryGetWorksheet("Population estimates (NIMS)", out IXLWorksheet xs))
{
throw new InvalidOperationException("Really were expected that tab to exist. Boo.");
}
// Verify headings are as we expect.
const string startColumn = "S";
var sanityCheck = xs.Cell($"{startColumn}10");
if (!string.Equals(sanityCheck.Value.ToString(), "NIMS population mapped to MSOA", StringComparison.OrdinalIgnoreCase))
{
throw new InvalidOperationException("Excel sheet not in the expected format - have additional age bands been added?");
}
var range = xs.Range($"{startColumn}16", $"AF{LastMsoaRow}");
var rowCount = range.RowCount();
for (var i = 1; i <= rowCount; i++)
{
var row = range.Row(i);
var result = new StatisticalArea
{
Code = row.Cell(1).Value.ToString(),
Name = row.Cell(2).Value.ToString(),
Population16To39 = (double)row.Cell(4).Value,
Population40To44 = (double)row.Cell(5).Value,
Population45To49 = (double)row.Cell(6).Value,
Population50To54 = (double)row.Cell(7).Value,
Population55To59 = (double)row.Cell(8).Value,
Population60To64 = (double)row.Cell(9).Value,
Population65To69 = (double)row.Cell(10).Value,
Population70To74 = (double)row.Cell(11).Value,
Population75To79 = (double)row.Cell(12).Value,
PopulationOver80 = (double)row.Cell(13).Value
};
yield return(result);
}
}
private static IEnumerable <StatisticalArea> GetSecondVaccinations(IXLWorksheet xs, int lastRow)
{
// Verify headings are as we expect.
const string lastColumn = "AB";
var sanityCheck = xs.Cell($"{lastColumn}13");
if (!string.Equals(sanityCheck.Value.ToString(), "80+", StringComparison.OrdinalIgnoreCase))
{
throw new InvalidOperationException("Excel sheet not in the expected format - have additional age bands been added?");
}
var range = xs.Range("F16", $"{lastColumn}{lastRow}");
var rowCount = range.RowCount();
for (var i = 1; i <= rowCount; i++)
{
var row = range.Row(i);
var result = new StatisticalArea
{
Code = row.Cell(1).Value.ToString(),
Name = row.Cell(2).Value.ToString(),
Population16To39 = (double)row.Cell(14).Value,
Population40To44 = (double)row.Cell(15).Value,
Population45To49 = (double)row.Cell(16).Value,
Population50To54 = (double)row.Cell(17).Value,
Population55To59 = (double)row.Cell(18).Value,
Population60To64 = (double)row.Cell(19).Value,
Population65To69 = (double)row.Cell(20).Value,
Population70To74 = (double)row.Cell(21).Value,
Population75To79 = (double)row.Cell(22).Value,
PopulationOver80 = (double)row.Cell(23).Value
};
yield return(result);
}
}