public async Task <IActionResult> ImportDeathByCounty() { var path = Path.Combine( _env.ContentRootPath, String.Format("Data/Source/Covid/Death/TexasCOVID19Deaths624.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 lstDeathDates = _context.DeathsByCounty.ToList(); // Initialize the record counters var nDeaths = 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 (lstDeathDates.Where(d => d.Date == dateId).Count() == 0) { var deathByCounty = new DeathByCounty(); deathByCounty.Date = row[nRow, 4].GetValue <DateTime>(); deathByCounty.County = row[nRow, 1].GetValue <string>(); deathByCounty.Deaths = row[nRow, 3].GetValue <int>(); // create the hospbycounty entity and fill it with xlsx data // save the hospbycounty to the db _context.DeathsByCounty.Add(deathByCounty); await _context.SaveChangesAsync(); //increment the counter nDeaths++; } } return(new JsonResult(new { DeathByCounty = nDeaths })); } } }
public async Task <IActionResult> FormatFatalitiesByCron() { var path = Path.Combine( _env.ContentRootPath, String.Format("Data/Source/Trials/TexasFatalities.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.DeathsByCounty.Select(s => s.County).Distinct().ToList(); var lstHostDates = _context.DeathsByCounty.ToList(); int nums = lstCounties.Count; int rowCnt = ws.Dimension.End.Row; int colCnt = ws.Dimension.End.Column + 1; int nFatalities = 0; DateTime startingDate = new DateTime(2020, 03, 01); 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 = 38; k < colCnt; k++) { var dateId = startingDate.AddDays(k).Date; if (lstHostDates.Where(d => d.Date == dateId).Count() == 0) { var fatalitiesByCounty = new DeathByCounty(); fatalitiesByCounty.Date = dateId; fatalitiesByCounty.County = lstCounties[i]; fatalitiesByCounty.Deaths = ws.Cells[j, k].GetValue <int>(); // create the hospbycounty entity and fill it with xlsx data // save the hospbycounty to the db _context.DeathsByCounty.Add(fatalitiesByCounty); await _context.SaveChangesAsync(); //increment the counter nFatalities++; } } } } } // Initialize the record counters // iterate through all rows, skipping the first one return(new JsonResult(new { DeathByCounty = nFatalities })); } } }