public async Task <IActionResult> FormatCasesByCron()
{
var path = Path.Combine(
_env.ContentRootPath,
String.Format("Data/Source/Trials/TexasCases.xlsx"));
using (var stream = new FileStream(
path,
FileMode.Open,
FileAccess.Read))
{
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using (var ep = new ExcelPackage(stream))
{
// get the first worksheet
var ws = ep.Workbook.Worksheets[0];
var lstCounties = _context.CasesByCounty.Select(s => s.County).Distinct().ToList();
var lstHostDates = _context.CasesByCounty.ToList();
int nums = lstCounties.Count;
int rowCnt = ws.Dimension.End.Row;
int colCnt = ws.Dimension.End.Column + 1;
DateTime startingDate = new DateTime(2020, 03, 04);
var nCountyCases = 0;
for (var i = 0; i < lstCounties.Count; i++)
{
for (var j = 4; j < 258; j++)
{
if (lstCounties[i] == ws.Cells[j, 1].Value.ToString())
{
for (var k = 35; k < colCnt; k++)
{
var dateId = startingDate.AddDays(k).Date;
if (lstHostDates.Where(d => d.Date == dateId).Count() == 0)
{
var casesByCounty = new CasesByCounty();
casesByCounty.Date = dateId;
casesByCounty.County = lstCounties[i];
casesByCounty.Cases = ws.Cells[j, k].GetValue <int>();
// create the hospbycounty entity and fill it with xlsx data
// save the hospbycounty to the db
_context.CasesByCounty.Add(casesByCounty);
await _context.SaveChangesAsync();
//increment the counter
nCountyCases++;
}
}
}
}
}
return(new JsonResult(new
{
CasesByCounty = nCountyCases
}));
}
}
}
public async Task <IActionResult> ImportCasesByCounty()
{
var path = Path.Combine(
_env.ContentRootPath,
String.Format("Data/Source/Covid/Cases/TexasCOVID19CaseCountDatabyCounty624.xlsx"));
using (var stream = new FileStream(
path,
FileMode.Open,
FileAccess.Read))
{
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using (var ep = new ExcelPackage(stream))
{
// get the first worksheet
var ws = ep.Workbook.Worksheets[0];
var lstCountyDates = _context.CasesByCounty.ToList();
// Initialize the record counters
var nCountyCases = 0;
// iterate through all rows, skipping the first one
for (int nRow = 2; nRow <= ws.Dimension.End.Row; nRow++)
{
var row = ws.Cells[nRow, 1, nRow, ws.Dimension.End.Column];
var dateId = row[nRow, 4].GetValue <DateTime>();
if (lstCountyDates.Where(d => d.Date == dateId).Count() == 0)
{
var casesByCounty = new CasesByCounty();
casesByCounty.Date = row[nRow, 4].GetValue <DateTime>();
casesByCounty.County = row[nRow, 1].GetValue <string>();
casesByCounty.Cases = row[nRow, 3].GetValue <int>();
// create the hospbycounty entity and fill it with xlsx data
// save the hospbycounty to the db
_context.CasesByCounty.Add(casesByCounty);
await _context.SaveChangesAsync();
//increment the counter
nCountyCases++;
}
}
return(new JsonResult(new
{
CasesByCounty = nCountyCases
}));
}
}
}