public List <PdSnPCummulativeDefaultRatesAssumption> ReadFromExcel()
{
var dataList = new List <PdSnPCummulativeDefaultRatesAssumption>();
var filePath = $"{Path.Combine(_dataAccess.GetFilePath(), "AssumptionTemplate.xlsx")}";
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using (var package = new ExcelPackage(new FileInfo(filePath)))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[13]; //PD_CummulativeRate Sheet
int rows = worksheet.Dimension.Rows;
for (int i = 2; i <= rows; i++)
{
var AffiliateId = worksheet.Cells[i, 1].Value;
var rating = worksheet.Cells[i, 2].Value;
for (int year = 1; year <= 15; year++)
{
var value = worksheet.Cells[i, year + 2].Value;
if (AffiliateId == null)
{
//Console.WriteLine("Row is empty: " + i.ToString());
}
else if (string.IsNullOrWhiteSpace(AffiliateId.ToString()))
{
//Console.WriteLine("Row is empty: " + i.ToString());
}
else
{
var data = new PdSnPCummulativeDefaultRatesAssumption();
data.Rating = rating.ToString();
data.Year = year;
try { data.AffiliateId = Convert.ToInt64(AffiliateId); } catch { data.AffiliateId = -1; }
try { data.Value = Convert.ToDouble(value); } catch { data.Value = 0.0; }
dataList.Add(data);
}
}
}
}
return(dataList);
}
public static string UpdatePdSnPCummulativeDefaultRatesAssumption(PdSnPCummulativeDefaultRatesAssumption input)
{
return($"Update [PdInputAssumptionSnPCummulativeDefaultRates] set [Value] = '{input.Value}' where [Rating] = '{input.Rating}' and [Years] = '{input.Year}' and [OrganizationUnitId] = {input.AffiliateId}; ");
}
