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}; "); }