/// <summary> /// /// </summary> /// <param name="uwRelationshipId"></param> /// <returns>Name of the file generated</returns> public async Task <string> GenerateAsync(int Id) { try { if (!this.ReloadTemplate("MODEL")) { throw new Exception("Template could not be loaded :("); } /* Start Your Sheet Creation Code Here */ var standardStyle = new XSSFNPoiStyle() { Border = CellBorder.All, BorderStyle = BorderStyle.Thin, FontColor = IndexedColors.Red.AsXSSFColor(), BackgroundColor = IndexedColors.Green.AsXSSFColor() }; var boldStyle = new XSSFNPoiStyle() { FillPattern = FillPattern.SolidForeground, FillForegroundColor = IndexedColors.PaleBlue.AsXSSFColor(), IsBold = true, VerticalAlignment = VerticalAlignment.Top, HorizontalAlignment = HorizontalAlignment.Left, WrapText = true }; string sSQL2 = @"SET ANSI_WARNINGS OFF; SELECT * FROM [UW].[vw_Relationship] WHERE BidPoolId=@p0;SELECT GETDATE() as ThisDate, 'SQL LITERAL' as ThisString;"; var retDataSet = await MarsDb.QueryAsDataSetAsync(sSQL2, Id); DataTable firstResultSet = retDataSet.Tables[0]; foreach (DataRow row in firstResultSet.Rows) { sheet = workbook.CloneSheet(this.workbook.GetSheetIndex("MODEL")); workbook.SetSheetName(workbook.NumberOfSheets - 1, row["RelationshipName"].ToString().AsSheetName()); sheet.SetCellValue(0, "D", "@DR1->"); sheet.SetCellValue(0, "E", row, "uwRelationshipId").SetCellStyle(standardStyle); sheet.SetCellValue(1, "D", "@DR2->"); sheet.SetCellValue(1, "E", row, "Underwriter").SetCellStyle(standardStyle); sheet.SetCellValue(2, "D", "@DR2->").SetCellStyle(standardStyle);; sheet.SetCellValue(2, "E", row, "UPBSum").SetCellStyle(standardStyle.SetFormatStyle(FormatStyle.Currency)); sheet.SetCellValue(3, "D", "@DR3->").SetCellStyle(standardStyle.SetFormatStyle(FormatStyle.Default)); sheet.SetCellValue(3, "E", row, "CurrentStatus"); sheet.SetCellValue(4, "D", "@DR4->").SetCellStyle(boldStyle); sheet.SetCellValue(4, "E", row, "ProFormaStatus").SetCellStyle(boldStyle); sheet.SetCellValue(5, "D", "@DR5->").SetCellStyle(boldStyle); sheet.SetCellValue(5, "E", row, "ExitStrategyText").SetCellStyle(boldStyle); sheet.GetRow(5).Height = 1540; sheet.SetColumnWidth("E", 9800); } workbook.RemoveSheetAt(this.workbook.GetSheetIndex("MODEL")); SaveToFile(this.GeneratedFileName); return(this.GeneratedFileName); } catch (Exception) { throw; } }
/// <summary> /// This will generate a Business Asset Report for a BidPool or Relationship /// </summary> /// <param name="BidPoolId">If this is 0, then we will assume that we are going to use uwRelationshipId</param> /// <param name="uwRelationshipId">If this is zero, then we will assume that we are going top use BidPoolId</param> /// <returns>Name of the file generated</returns> public async Task <string> GenerateAsync(int BidPoolId, int uwRelationshipId) { try { var ModelSheetName = "1"; if (!this.ReloadTemplate(ModelSheetName)) { throw new Exception("Template could not be loaded :("); } // Generate a Sheet for each relationship. If uwRelationshipId >0 then onl one sheet is needed. string sSQL1 = ""; if (uwRelationshipId == 0) { sSQL1 = @"SET ANSI_WARNINGS OFF; SELECT COUNT(*) AS TabCnt FROM UW.tbl_Relationship WHERE BidPoolId =@p0;"; } else { sSQL1 = @"SET ANSI_WARNINGS OFF; SELECT 1 AS TabCnt ;"; } var retTabCnt = await MarsDb.QueryAsDataSetAsync(sSQL1, BidPoolId); System.Data.DataTable aResultSet = retTabCnt.Tables[0]; var iTabCnt = 0; foreach (System.Data.DataRow a in aResultSet.Rows) { iTabCnt = (int)a["TabCnt"]; } for (int x = 2; x < iTabCnt + 1; x++) { sheet = workbook.CloneSheet(this.workbook.GetSheetIndex(ModelSheetName)); workbook.SetSheetName(workbook.NumberOfSheets - 1, x.ToString().AsSheetName()); } // Return to sheet "1" this.sheet = this.workbook.GetSheetAt(this.workbook.GetSheetIndex(ModelSheetName)); // Get Dataset for report using ADO; If uwRelationshipId <> 0 use uwRelationshipId else use BidPoolId string sSQL2 = ""; DataSet retDataSet = null; if (uwRelationshipId == 0) { sSQL2 = @"SET ANSI_WARNINGS OFF; SELECT * FROM [UW].[vw_Loans] WHERE [BidPoolId]=@p0 ORDER BY uwRelationshipId ASC, uwLoanId ASC;"; retDataSet = await MarsDb.QueryAsDataSetAsync(sSQL2, BidPoolId); } else { sSQL2 = @"SET ANSI_WARNINGS OFF; SELECT * FROM [UW].[vw_Loans] WHERE [uwRelationshipId]=@p0 ORDER BY uwRelationshipId ASC, uwLoanId ASC;"; retDataSet = await MarsDb.QueryAsDataSetAsync(sSQL2, uwRelationshipId); } System.Data.DataTable firstResultSet = retDataSet.Tables[0]; var iRow = 1; var iRel = 0; var iLnCnt = 1; foreach (System.Data.DataRow row in firstResultSet.Rows) { if (iRow == 1) { iRel = (int)row["uwRelationshipId"]; } else if (iRel != (int)row["uwRelationshipId"]) { iSheet++; this.sheet = this.workbook.GetSheetAt(this.workbook.GetSheetIndex(iSheet.ToString())); iRow = 1; iLnCnt = 1; iRel = (int)row["uwRelationshipId"]; } var formatStr = @"_(* #,##0_);_(* (#,##0);_(* "" - ""??_);_(@_)"; var LnCellStyle = new XSSFNPoiStyle() { Border = CellBorder.All, BorderStyle = BorderStyle.Thin, CellFormat = formatStr, VerticalAlignment = VerticalAlignment.Top, HorizontalAlignment = HorizontalAlignment.Left }; sheet.SetCellValue(2, "B", row, "RptHeader"); sheet.CreateRow(iRow + 5); LnCellStyle.WrapText = true; sheet.SetCellValue(iRow + 5, "B", row, "LoanShortName").SetCellStyle(LnCellStyle); sheet.SetCellValue(iRow + 5, "C", row, "LoanDescriptionTxt").SetCellStyle(LnCellStyle); sheet.SetCellValue(iRow + 5, "D", row, "BorrowerTxt").SetCellStyle(LnCellStyle); sheet.SetCellValue(iRow + 5, "E", row, "GuarantorTxt").SetCellStyle(LnCellStyle); LnCellStyle.CellFormat = "mm/dd/yyy"; sheet.SetCellValue(iRow + 5, "F", row, "OriginationDate").SetCellStyle(LnCellStyle); if ((row["MaturityDateText"] == System.DBNull.Value) || ((string)row["MaturityDateText"] == "")) { LnCellStyle.CellFormat = "mm/dd/yyy"; sheet.SetCellValue(iRow + 5, "G", row, "MaturityDate").SetCellStyle(LnCellStyle); } else { LnCellStyle.CellFormat = "@"; sheet.SetCellValue(iRow + 5, "G", row, "MaturityDatetext").SetCellStyle(LnCellStyle); } LnCellStyle.CellFormat = "#,##0.00"; sheet.SetCellValue(iRow + 5, "H", row, "OriginalUPB").SetCellStyle(LnCellStyle); sheet.SetCellValue(iRow + 5, "I", row, "UPB").SetCellStyle(LnCellStyle); if ((row["InterestRateText"] == System.DBNull.Value) || ((string)row["InterestRateText"] == "")) { LnCellStyle.CellFormat = "0.0%"; sheet.SetCellValue(iRow + 5, "J", row, "InterestRate").SetCellStyle(LnCellStyle); } else { LnCellStyle.CellFormat = "@"; sheet.SetCellValue(iRow + 5, "J", row, "InterestRateText").SetCellStyle(LnCellStyle); } LnCellStyle.CellFormat = "#,###"; sheet.SetCellValue(iRow + 5, "K", row, "SIMValueLoan").SetCellStyle(LnCellStyle); if (iLnCnt == (int)row["LoansCnt"]) { sheet.CreateRow(iRow + 7); LnCellStyle.IsBold = true; sheet.SetCellValue(iRow + 6, "C", "Totals:").SetCellStyle(LnCellStyle); LnCellStyle.CellFormat = "#,##0.00"; sheet.SetCellValue(iRow + 6, "H", 0.0).SetCellStyle(LnCellStyle).SetCellFormula(string.Format("SUM(H7:H{0})", (6 + iRow))); sheet.SetCellValue(iRow + 6, "I", 0.0).SetCellStyle(LnCellStyle).SetCellFormula(string.Format("SUM(I7:I{0})", (6 + iRow))); LnCellStyle.CellFormat = "#,###"; sheet.SetCellValue(iRow + 6, "K", 0.0).SetCellStyle(LnCellStyle).SetCellFormula(string.Format("SUM(K7:K{0})", (6 + iRow))); LnCellStyle.IsBold = false; } iRow++; iLnCnt++; } SaveToFile(this.GeneratedFileName); return(this.GeneratedFileName); } catch (Exception) { throw; } }
/// <summary> /// This will generate a Business Asset Report for a BidPool or Relationship /// </summary> /// <param name="BidPoolId">If this is 0, then we will assume that we are going to use uwRelationshipId</param> /// <param name="uwRelationshipId">If this is zero, then we will assume that we are going top use BidPoolId</param> /// <returns>Name of the file generated</returns> public async Task <string> GenerateAsync(int BidPoolId, int uwRelationshipId) { try { var ModelSheetName = "1"; if (!this.ReloadTemplate(ModelSheetName)) { throw new Exception("Template could not be loaded :("); } // Generate a Sheet for each relationship that has real estate collateral. string sSQL1 = ""; if (uwRelationshipId == 0) { sSQL1 = @"SET ANSI_WARNINGS OFF; SELECT COUNT(*) AS TabCnt FROM (SELECT DISTINCT r.uwRelationshipId FROM UW.tbl_Relationship AS r INNER JOIN UW.tbl_CollateralRE AS c ON r.uwRelationshipId = c.uwRelationshipId WHERE r.BidPoolId =@p0) AS a;"; } else { sSQL1 = @"SET ANSI_WARNINGS OFF; SELECT 1 AS TabCnt ;"; } var retTabCnt = await MarsDb.QueryAsDataSetAsync(sSQL1, BidPoolId); System.Data.DataTable aResultSet = retTabCnt.Tables[0]; var iTabCnt = 0; foreach (System.Data.DataRow a in aResultSet.Rows) { iTabCnt = (int)a["TabCnt"]; } // Get Dataset for report using ADO; If uwRelationshipId <> 0 use uwRelationshipId else use BidPoolId string sSQL2 = ""; DataSet retDataSet = null; var id = 0; if (uwRelationshipId == 0) { sSQL2 = @"SET ANSI_WARNINGS OFF; SELECT * FROM [UW].[vw_CollateralRE] WHERE [BidPoolId]=@p0 ORDER BY uwRelationshipId ASC, uwRECollateralId ASC;"; retDataSet = await MarsDb.QueryAsDataSetAsync(sSQL2, BidPoolId); id = BidPoolId; } else { sSQL2 = @"SET ANSI_WARNINGS OFF; SELECT * FROM [UW].[vw_CollateralRe] WHERE [uwRelationshipId]=@p0 ORDER BY uwRelationshipId ASC, uwRECollateralId ASC;"; retDataSet = await MarsDb.QueryAsDataSetAsync(sSQL2, uwRelationshipId); id = uwRelationshipId; } for (int x = 2; x < iTabCnt + 1; x++) { sheet = workbook.CloneSheet(this.workbook.GetSheetIndex(ModelSheetName)); workbook.SetSheetName(workbook.NumberOfSheets - 1, x.ToString().AsSheetName()); } // Return to sheet "1" this.sheet = this.workbook.GetSheetAt(this.workbook.GetSheetIndex(ModelSheetName)); //string sSQL2 = @"SET ANSI_WARNINGS OFF; SELECT * FROM [UW].[vw_CollateralRE] WHERE [BidPoolId]=@p0 ORDER BY uwRelationshipId ASC, uwRECollateralId ASC;"; //var retDataSet = await MarsDb.QueryAsDataSetAsync(sSQL2, BidPoolId); System.Data.DataTable firstResultSet = retDataSet.Tables[0]; var iRow = 1; var iRel = 0; var iColCnt = 1; foreach (System.Data.DataRow row in firstResultSet.Rows) { if (iRow == 1) { iRel = (int)row["uwRelationshipId"]; } else if (iRel != (int)row["uwRelationshipId"]) { iSheet++; this.sheet = this.workbook.GetSheetAt(this.workbook.GetSheetIndex(iSheet.ToString())); iRow = 1; iColCnt = 1; iRel = (int)row["uwRelationshipId"]; } var formatStr = @"_(* #,##0_);_(* (#,##0);_(* "" - ""??_);_(@_)"; var RECellStyle = new XSSFNPoiStyle() { Border = CellBorder.All, BorderStyle = BorderStyle.Thin, CellFormat = formatStr, VerticalAlignment = VerticalAlignment.Top, HorizontalAlignment = HorizontalAlignment.Left }; RECellStyle.CellFormat = "@"; sheet.SetCellValue(2, "B", row, "RptHeader"); sheet.CreateRow(iRow + 6); RECellStyle.WrapText = true; sheet.SetCellValue(iRow + 6, "B", row, "CollateralDescriptionTxt").SetCellStyle(RECellStyle); RECellStyle.CellFormat = "#,##0.00"; sheet.SetCellValue(iRow + 6, "C", row, "Size").SetCellStyle(RECellStyle); RECellStyle.CellFormat = "@"; sheet.SetCellValue(iRow + 6, "D", row, "SizeMetricDesc").SetCellStyle(RECellStyle); sheet.SetCellValue(iRow + 6, "E", row, "CollateralFullAddress").SetCellStyle(RECellStyle); sheet.SetCellValue(iRow + 6, "F", row, "Comments").SetCellStyle(RECellStyle); RECellStyle.CellFormat = "mm/dd/yyy"; RECellStyle.WrapText = false; sheet.SetCellValue(iRow + 6, "G", row, "MRAppraisalDate").SetCellStyle(RECellStyle); RECellStyle.CellFormat = "#,##0.00"; sheet.SetCellValue(iRow + 6, "H", row, "MRAppraisalValue").SetCellStyle(RECellStyle); sheet.SetCellValue(iRow + 6, "I", row, "MRAppraisalValuetoMetric").SetCellStyle(RECellStyle); RECellStyle.CellFormat = "#,##0.00"; sheet.SetCellValue(iRow + 6, "J", row, "BPOValueCRE").SetCellStyle(RECellStyle); sheet.SetCellValue(iRow + 6, "K", row, "BPOValueCREtoMetric").SetCellStyle(RECellStyle); sheet.SetCellValue(iRow + 6, "L", row, "SIMValue").SetCellStyle(RECellStyle); sheet.SetCellValue(iRow + 6, "M", row, "SIMValuetoMetric").SetCellStyle(RECellStyle); if (iColCnt == (int)row["CollateralRECnt"]) { //sheet.CreateRow(18 + iRow); //sheet.SetCellValue(18 + iRow, "C", 0.0).SetCellFormat(formatStr).SetCellFormula(string.Format("SUM(C18:C{0})", (18 + iRow - 2))); sheet.CreateRow(iRow + 7); RECellStyle.IsBold = true; sheet.SetCellValue(iRow + 7, "C", "Totals:").SetCellStyle(RECellStyle); sheet.SetCellValue(iRow + 7, "H", 0.0).SetCellStyle(RECellStyle).SetCellFormula(string.Format("SUM(H8:H{0})", (7 + iRow))); sheet.SetCellValue(iRow + 7, "J", 0.0).SetCellStyle(RECellStyle).SetCellFormula(string.Format("SUM(J8:J{0})", (7 + iRow))); sheet.SetCellValue(iRow + 7, "L", 0.0).SetCellStyle(RECellStyle).SetCellFormula(string.Format("SUM(L8:L{0})", (7 + iRow))); RECellStyle.IsBold = false; } iRow++; iColCnt++; } SaveToFile(this.GeneratedFileName); return(this.GeneratedFileName); } catch (Exception) { throw; } }
/// <summary> /// Special function that gives much more flexibility in applying style with different formats /// </summary> /// <param name="cell">Cell to apply this on</param> /// <param name="npoiStyle">NPoi Object previously created</param> /// <returns></returns> public static ICell SetCellStyle(this ICell cell, XSSFNPoiStyle npoiStyle) { cell.CellStyle = npoiStyle.Render(cell); return(cell); }
public static CellRangeAddress SetRangeStyle(this CellRangeAddress range, ISheet sheet, XSSFNPoiStyle npoiStyle) { npoiStyle.ApplyBorderToRange(sheet, range); return(range); }
/// <summary> /// /// </summary> /// <param name="BidPoolId"></param> /// <returns>Name of the file generated</returns> public async Task <string> GenerateAsync(int BidPoolId) { try { if (!this.ReloadTemplate("DS")) { throw new Exception("Template could not be loaded :("); } string sSQL = @"SET ANSI_WARNINGS OFF; SELECT * FROM [UW].[vw_DeanSheet] WHERE [BidPoolId]=@p0 ORDER BY uwRelationshipId ASC;SELECT * FROM [UW].[vw_DeanSheet_Totals] WHERE [BidPoolId]=@p0"; var retDataSet = await MarsDb.QueryAsDataSetAsync(sSQL, BidPoolId); System.Data.DataTable resultSet = retDataSet.Tables[0]; var iRow = 1; var formatStr = @"_(* #,##0_);_(* (#,##0);_(* "" - ""??_);_(@_)"; var DSCellStyle = new XSSFNPoiStyle() { Border = CellBorder.All, BorderStyle = BorderStyle.Thin, CellFormat = formatStr }; foreach (System.Data.DataRow row in resultSet.Rows) { if (iRow == 1) // Populate Bid Pool Header { sheet.SetCellValue(1, "B", row, "BidPool"); } sheet.CreateRow(iRow + 3); DSCellStyle.CellFormat = "@"; DSCellStyle.HorizontalAlignment = HorizontalAlignment.Left; sheet.SetCellValue(iRow + 3, "B", row, "RelationshipName").SetCellStyle(DSCellStyle); sheet.SetCellValue(iRow + 3, "C", row, "BidSubPoolName").SetCellStyle(DSCellStyle); sheet.SetCellValue(iRow + 3, "D", row, "UW").SetCellStyle(DSCellStyle); DSCellStyle.HorizontalAlignment = HorizontalAlignment.Right; DSCellStyle.CellFormat = "#,###"; sheet.SetCellValue(iRow + 3, "E", row, "LoanCount").SetCellStyle(DSCellStyle); DSCellStyle.CellFormat = "#,###"; sheet.SetCellValue(iRow + 3, "F", row, "UPBSum").SetCellStyle(DSCellStyle); sheet.SetCellValue(iRow + 3, "G", row, "BidAmount").SetCellStyle(DSCellStyle); DSCellStyle.CellFormat = "0.0%"; sheet.SetCellValue(iRow + 3, "H", row, "BidUPB").SetCellStyle(DSCellStyle); sheet.SetCellValue(iRow + 3, "I", row, "DiscountRate").SetCellStyle(DSCellStyle); if ((double)row["TrailConC"] == -1) { DSCellStyle.CellFormat = "@"; //DSCellStyle.HorizontalAlignment = HorizontalAlignment.Right; sheet.SetCellValue(iRow + 3, "J", "N/A").SetCellStyle(DSCellStyle); DSCellStyle.CellFormat = "0.0%"; } else { sheet.SetCellValue(iRow + 3, "J", row, "TrailConC").SetCellStyle(DSCellStyle); } if ((double)row["ProjConC"] == -1) { DSCellStyle.CellFormat = "@"; //DSCellStyle.HorizontalAlignment = HorizontalAlignment.Right; sheet.SetCellValue(iRow + 3, "K", "N/A").SetCellStyle(DSCellStyle); DSCellStyle.CellFormat = "0.0%"; } else { sheet.SetCellValue(iRow + 3, "K", row, "ProjConC").SetCellStyle(DSCellStyle); } sheet.SetCellValue(iRow + 3, "L", row, "Recovery").SetCellStyle(DSCellStyle); DSCellStyle.CellFormat = "#,###.00"; sheet.SetCellValue(iRow + 3, "M", row, "MOIC").SetCellStyle(DSCellStyle); DSCellStyle.CellFormat = "mm/dd/yyyy"; sheet.SetCellValue(iRow + 3, "N", row, "AppraisalDate").SetCellStyle(DSCellStyle); DSCellStyle.CellFormat = "#,###"; sheet.SetCellValue(iRow + 3, "O", row, "AppraisalValue").SetCellStyle(DSCellStyle); sheet.SetCellValue(iRow + 3, "P", row, "BusinessAssets").SetCellStyle(DSCellStyle); sheet.SetCellValue(iRow + 3, "Q", row, "BankTotal").SetCellStyle(DSCellStyle); sheet.SetCellValue(iRow + 3, "R", row, "BPOValue").SetCellStyle(DSCellStyle); sheet.SetCellValue(iRow + 3, "S", row, "SIMValue").SetCellStyle(DSCellStyle); DSCellStyle.CellFormat = "0.0%"; if ((double)row["BidAppr"] == -1) { DSCellStyle.CellFormat = "@"; //DSCellStyle.HorizontalAlignment = HorizontalAlignment.Right; sheet.SetCellValue(iRow + 3, "T", "N/A").SetCellStyle(DSCellStyle); DSCellStyle.CellFormat = "0.0%"; } else { sheet.SetCellValue(iRow + 3, "T", row, "BidAppr").SetCellStyle(DSCellStyle); } if ((double)row["BidBPO"] == -1) { DSCellStyle.CellFormat = "@"; //DSCellStyle.HorizontalAlignment = HorizontalAlignment.Right; sheet.SetCellValue(iRow + 3, "U", "N/A").SetCellStyle(DSCellStyle); DSCellStyle.CellFormat = "0.0%"; } else { sheet.SetCellValue(iRow + 3, "U", row, "BidBPO").SetCellStyle(DSCellStyle); } if ((double)row["BidSIMValue"] == -1) { DSCellStyle.CellFormat = "@"; //DSCellStyle.HorizontalAlignment = HorizontalAlignment.Right; sheet.SetCellValue(iRow + 3, "V", "N/A").SetCellStyle(DSCellStyle); DSCellStyle.CellFormat = "0.0%"; } else { sheet.SetCellValue(iRow + 3, "V", row, "BidSIMValue").SetCellStyle(DSCellStyle); } DSCellStyle.CellFormat = "#,###"; sheet.SetCellValue(iRow + 3, "W", row, "PHLast3mth").SetCellStyle(DSCellStyle); sheet.SetCellValue(iRow + 3, "X", row, "PHLast6mth").SetCellStyle(DSCellStyle); sheet.SetCellValue(iRow + 3, "Y", row, "PHLast9mth").SetCellStyle(DSCellStyle); sheet.SetCellValue(iRow + 3, "Z", row, "PHLast12mth").SetCellStyle(DSCellStyle); DSCellStyle.CellFormat = "@"; DSCellStyle.HorizontalAlignment = HorizontalAlignment.Center; sheet.SetCellValue(iRow + 3, "AA", row, "Recourse").SetCellStyle(DSCellStyle); DSCellStyle.HorizontalAlignment = HorizontalAlignment.Left; sheet.SetCellValue(iRow + 3, "AB", row, "PrimaryCollateralType").SetCellStyle(DSCellStyle); DSCellStyle.CellFormat = "#"; DSCellStyle.HorizontalAlignment = HorizontalAlignment.Right; sheet.SetCellValue(iRow + 3, "AC", row, "YearBuilt").SetCellStyle(DSCellStyle); DSCellStyle.CellFormat = "@"; sheet.SetCellValue(iRow + 3, "AD", row, "REUnit").SetCellStyle(DSCellStyle); DSCellStyle.CellFormat = "#,###"; sheet.SetCellValue(iRow + 3, "AE", row, "REBasis").SetCellStyle(DSCellStyle); DSCellStyle.CellFormat = "@"; DSCellStyle.HorizontalAlignment = HorizontalAlignment.Left; sheet.SetCellValue(iRow + 3, "AF", row, "PrimaryLocation").SetCellStyle(DSCellStyle); DSCellStyle.HorizontalAlignment = HorizontalAlignment.Center; sheet.SetCellValue(iRow + 3, "AG", row, "Eyes").SetCellStyle(DSCellStyle); iRow++; } resultSet = retDataSet.Tables[1]; foreach (System.Data.DataRow row in resultSet.Rows) { sheet.CreateRow(iRow + 4); DSCellStyle.HorizontalAlignment = HorizontalAlignment.Right; DSCellStyle.IsBold = true; DSCellStyle.CellFormat = "#,###"; sheet.SetCellValue(iRow + 4, "C", "Totals:").SetCellStyle(DSCellStyle); sheet.SetCellValue(iRow + 4, "D", "").SetCellStyle(DSCellStyle); DSCellStyle.CellFormat = "#,###"; sheet.SetCellValue(iRow + 4, "E", row, "LoanCount").SetCellStyle(DSCellStyle); DSCellStyle.CellFormat = "#,###"; sheet.SetCellValue(iRow + 4, "F", row, "UPBSum").SetCellStyle(DSCellStyle); sheet.SetCellValue(iRow + 4, "G", row, "BidAmount").SetCellStyle(DSCellStyle); DSCellStyle.CellFormat = "0.0%"; sheet.SetCellValue(iRow + 4, "H", row, "BidUPB").SetCellStyle(DSCellStyle); sheet.SetCellValue(iRow + 4, "I", row, "DiscountRate").SetCellStyle(DSCellStyle); sheet.SetCellValue(iRow + 4, "J", row, "TrailConC").SetCellStyle(DSCellStyle); sheet.SetCellValue(iRow + 4, "K", row, "ProjConC").SetCellStyle(DSCellStyle); sheet.SetCellValue(iRow + 4, "L", row, "Recovery").SetCellStyle(DSCellStyle); DSCellStyle.CellFormat = "#,###.00"; sheet.SetCellValue(iRow + 4, "M", row, "MOIC").SetCellStyle(DSCellStyle); sheet.SetCellValue(iRow + 4, "N", "").SetCellStyle(DSCellStyle); DSCellStyle.CellFormat = "#,###"; sheet.SetCellValue(iRow + 4, "O", row, "AppraisalValue").SetCellStyle(DSCellStyle); sheet.SetCellValue(iRow + 4, "P", row, "BusinessAssets").SetCellStyle(DSCellStyle); sheet.SetCellValue(iRow + 4, "Q", row, "BankTotal").SetCellStyle(DSCellStyle); sheet.SetCellValue(iRow + 4, "R", row, "BPOValue").SetCellStyle(DSCellStyle); sheet.SetCellValue(iRow + 4, "S", row, "SIMValue").SetCellStyle(DSCellStyle); DSCellStyle.CellFormat = "0.0%"; sheet.SetCellValue(iRow + 4, "T", row, "BidAppr").SetCellStyle(DSCellStyle); sheet.SetCellValue(iRow + 4, "U", row, "BidBPO").SetCellStyle(DSCellStyle); sheet.SetCellValue(iRow + 4, "V", row, "BidSIMValue").SetCellStyle(DSCellStyle); DSCellStyle.CellFormat = "#,###"; sheet.SetCellValue(iRow + 4, "W", row, "PHLast3mth").SetCellStyle(DSCellStyle); sheet.SetCellValue(iRow + 4, "X", row, "PHLast6mth").SetCellStyle(DSCellStyle); sheet.SetCellValue(iRow + 4, "Y", row, "PHLast9mth").SetCellStyle(DSCellStyle); sheet.SetCellValue(iRow + 4, "Z", row, "PHLast12mth").SetCellStyle(DSCellStyle); DSCellStyle.IsBold = false; } SaveToFile(this.GeneratedFileName); return(this.GeneratedFileName); } catch (Exception) { throw; } }
/// <summary> /// This will generate a Business Asset Report for a BidPool and Relationship /// </summary> /// <param name="BidPoolId">If this is 0, then we will assume that we are going to use uwRelationshipId</param> /// <param name="uwRelationshipId">If this is zero, then we will assume that we are going top use BidPoolId</param> /// <returns>Name of the file generated</returns> private async Task <string> GenerateAsync(int BidPoolId, int uwRelationshipId) { try { var ModelSheetName = "1"; if (!this.ReloadTemplate(ModelSheetName)) { throw new Exception("Template could not be loaded :("); } // Generate a Sheet for each relationship. If uwRelationshipId <> 0 then only 1 sheet is needed. string sSQL1 = ""; if (uwRelationshipId == 0) { sSQL1 = @"SET ANSI_WARNINGS OFF; SELECT COUNT(*) AS TabCnt FROM (SELECT DISTINCT r.uwRelationshipId FROM UW.tbl_Relationship AS r INNER JOIN UW.tbl_CollateralNRE AS c ON r.uwRelationshipId = c.uwRelationshipId WHERE r.BidPoolId =@p0) AS a;"; } else { sSQL1 = @"SET ANSI_WARNINGS OFF; SELECT 1 AS TabCnt ;"; } var retTabCnt = await MarsDb.QueryAsDataSetAsync(sSQL1, BidPoolId); System.Data.DataTable aResultSet = retTabCnt.Tables[0]; var iTabCnt = 0; foreach (System.Data.DataRow a in aResultSet.Rows) { iTabCnt = (int)a["TabCnt"]; } for (int x = 2; x < iTabCnt + 1; x++) { sheet = workbook.CloneSheet(this.workbook.GetSheetIndex(ModelSheetName)); workbook.SetSheetName(workbook.NumberOfSheets - 1, x.ToString().AsSheetName()); } // Return to sheet "1" this.sheet = this.workbook.GetSheetAt(this.workbook.GetSheetIndex(ModelSheetName)); // Get Dataset for report using ADO; If uwRelationshipId <> 0 use uwRelationshipId else use BidPoolId string sSQL2 = ""; DataSet?retDataSet = null; if (uwRelationshipId == 0) { sSQL2 = @"SET ANSI_WARNINGS OFF; SELECT * FROM [UW].[vw_CollateralNRE] WHERE [BidPoolId]=@p0 ORDER BY uwRelationshipId ASC, uwNRECollateralId ASC;"; retDataSet = await MarsDb.QueryAsDataSetAsync(sSQL2, BidPoolId); } else { sSQL2 = @"SET ANSI_WARNINGS OFF; SELECT * FROM [UW].[vw_CollateralNRE] WHERE [uwRelationshipId]=@p0 ORDER BY uwRelationshipId ASC, uwNRECollateralId ASC;"; retDataSet = await MarsDb.QueryAsDataSetAsync(sSQL2, uwRelationshipId); } System.Data.DataTable firstResultSet = retDataSet.Tables[0]; var iRow = 1; var iRel = 0; var iNRECnt = 1; foreach (System.Data.DataRow row in firstResultSet.Rows) { if (iRow == 1) { iRel = (int)row["uwRelationshipId"]; } else if (iRel != (int)row["uwRelationshipId"]) { iSheet++; this.sheet = this.workbook.GetSheetAt(this.workbook.GetSheetIndex(iSheet.ToString())); iRow = 1; iNRECnt = 1; iRel = (int)row["uwRelationshipId"]; } var formatStr = @"_(* #,##0_);_(* (#,##0);_(* "" - ""??_);_(@_)"; var BACellStyle = new XSSFNPoiStyle() { Border = CellBorder.All, BorderStyle = BorderStyle.Thin, CellFormat = formatStr, VerticalAlignment = VerticalAlignment.Top, HorizontalAlignment = HorizontalAlignment.Left }; sheet.SetCellValue(2, "B", row, "RptHeader"); sheet.CreateRow(iRow + 5); BACellStyle.WrapText = true; sheet.SetCellValue(iRow + 5, "B", row, "NREItemLabel").SetCellStyle(BACellStyle); sheet.SetCellValue(iRow + 5, "C", row, "NREItemComments").SetCellStyle(BACellStyle); BACellStyle.CellFormat = "#,###.00"; sheet.SetCellValue(iRow + 5, "D", row, "NREItemBookVal").SetCellStyle(BACellStyle); BACellStyle.CellFormat = "0.0%"; sheet.SetCellValue(iRow + 5, "E", row, "NREItemCollPcnt").SetCellStyle(BACellStyle); BACellStyle.CellFormat = "#,###.00"; sheet.SetCellValue(iRow + 5, "F", row, "NRESIM").SetCellStyle(BACellStyle); if (iNRECnt == (int)row["CollateralNRECnt"]) { //sheet.CreateRow(18 + iRow); //sheet.SetCellValue(18 + iRow, "C", 0.0).SetCellFormat(formatStr).SetCellFormula(string.Format("SUM(C18:C{0})", (18 + iRow - 2))); sheet.CreateRow(iRow + 7); BACellStyle.IsBold = true; sheet.SetCellValue(iRow + 6, "C", "Totals:").SetCellStyle(BACellStyle); BACellStyle.CellFormat = "#,###.00"; sheet.SetCellValue(iRow + 6, "D", 0.0).SetCellStyle(BACellStyle).SetCellFormula(string.Format("SUM(D6:D{0})", (6 + iRow))); sheet.SetCellValue(iRow + 6, "F", 0.0).SetCellStyle(BACellStyle).SetCellFormula(string.Format("SUM(F6:F{0})", (6 + iRow))); BACellStyle.IsBold = false; } iRow++; iNRECnt++; } SaveToFile(this.GeneratedFileName); return(this.GeneratedFileName); } catch (Exception) { throw; } }
private bool GenerateSheetForRelationship(ISheet sheet, DataRow uwRelItem, DataTable _relCFdata) { try { sheet.SetCellValue(1, "B", uwRelItem, "uwRelationshipId"); sheet.SetCellValue(1, "E", uwRelItem, "Underwriter"); sheet.SetCellValue(1, "G", uwRelItem, "PrimaryCollateralType"); sheet.SetCellValue(2, "B", uwRelItem, "RelationshipName"); sheet.SetCellValue(2, "E", uwRelItem, "SiteVisitFlag"); sheet.SetCellValue(2, "G", uwRelItem, "RecourseFlag"); sheet.SetCellValue(2, "I", uwRelItem, "TotalSIM"); sheet.SetCellValue(2, "K", uwRelItem, "TotalApprBook"); sheet.SetCellValue(2, "M", uwRelItem, "UPBSum"); sheet.SetCellValue(2, "N", uwRelItem, "BidAmount"); sheet.SetCellValue(2, "O", uwRelItem, "BidUPB"); sheet.SetCellValue(2, "P", uwRelItem, "BidSIM"); sheet.SetCellValue(4, "B", uwRelItem, "BidPoolName"); sheet.SetCellValue(4, "G", uwRelItem, "YearBuilt"); sheet.SetCellValue(5, "B", uwRelItem, "BidSubPoolName"); sheet.SetCellValue(5, "G", uwRelItem, "BPOValueCRE"); sheet.SetCellValue(5, "I", uwRelItem, "SIMValue"); sheet.SetCellValue(5, "J", uwRelItem, "AppraisalDate"); sheet.SetCellValue(5, "K", uwRelItem, "AppraisalValue"); sheet.SetCellValue(5, "M", uwRelItem, "DiscountRate"); sheet.SetCellValue(5, "N", uwRelItem, "Recovery"); sheet.SetCellValue(5, "O", uwRelItem, "MOIC"); sheet.SetCellValue(5, "P", uwRelItem, "WAL"); sheet.SetCellValue(6, "B", uwRelItem, "PrimaryCity"); sheet.SetCellValue(6, "D", uwRelItem, "PrimaryCounty"); sheet.SetCellValue(6, "F", uwRelItem, "PrimaryState"); sheet.SetCellValue(7, "B", uwRelItem, "CollateralDescText"); sheet.SetCellValue(8, "I", 0.0); sheet.SetCellValue(8, "M", uwRelItem, "CashOnCash"); sheet.SetCellValue(8, "N", uwRelItem, "GrossCashFlow"); sheet.SetCellValue(8, "O", uwRelItem, "NetCashFlow"); sheet.SetCellValue(8, "P", uwRelItem, "LegalGross"); sheet.SetCellValue(9, "B", uwRelItem, "PrimaryLienPoisition"); sheet.SetCellValue(9, "E", uwRelItem, "ExitTypeDesc"); sheet.SetCellValue(10, "B", uwRelItem, "CurrentStatus"); sheet.SetCellValue(10, "E", uwRelItem, "ExitStrategyText"); sheet.SetCellValue(11, "B", uwRelItem, "ProFormaStatus"); sheet.SetCellValue(11, "M", uwRelItem, "PHLast3mth"); sheet.SetCellValue(11, "N", uwRelItem, "PHLast6mth"); sheet.SetCellValue(11, "O", uwRelItem, "PHLast9mth"); sheet.SetCellValue(11, "P", uwRelItem, "PHLast12mth"); sheet.SetCellValue(12, "B", uwRelItem, "PerformingRate"); sheet.SetCellValue(12, "I", uwRelItem, "Size"); sheet.SetCellValue(12, "J", uwRelItem, "SizeMetricDesc"); sheet.SetCellValue(12, "K", uwRelItem, "BidMetric"); sheet.SetCellValue(12, "L", uwRelItem, "SIMMetric"); sheet.SetCellValue(16, "E", uwRelItem, "MiscIncome3Label"); sheet.SetCellValue(16, "F", uwRelItem, "MiscIncome4Label"); sheet.SetCellValue(16, "G", uwRelItem, "MiscIncome5Label"); sheet.SetCellValue(16, "H", uwRelItem, "MiscIncome6Label"); var formatStr = @"_(* #,##0_);_(* (#,##0);_(* "" - ""??_);_(@_)"; var cashFlowCellStyle = new XSSFNPoiStyle() { Border = CellBorder.All, BorderStyle = BorderStyle.Thin, CellFormat = formatStr }; var iRow = 0; var relCFdata = _relCFdata.Select(string.Format("uwRelationshipId={0}", int.Parse(uwRelItem["uwRelationshipId"].ToString()))); foreach (var cashFlowItem in relCFdata) { sheet.CreateRow(17 + iRow); if ((int)cashFlowItem["MatyFlag"] == 1) { cashFlowCellStyle.IsBold = true; cashFlowCellStyle.FillPattern = FillPattern.SolidForeground; cashFlowCellStyle.FillForegroundColor = IndexedColors.LightYellow.AsXSSFColor(); } else { cashFlowCellStyle.IsBold = false; cashFlowCellStyle.FillPattern = FillPattern.SolidForeground; cashFlowCellStyle.FillForegroundColor = IndexedColors.White.AsXSSFColor(); } sheet.SetCellValue(17 + iRow, "A", cashFlowItem, "CashFlowDate").SetCellFormat("mmm-yy"); sheet.SetCellValue(17 + iRow, "B", (double)(iRow + 1)).SetCellFormat("0");; sheet.SetCellValue(17 + iRow, "C", cashFlowItem, "Principal").SetCellStyle(cashFlowCellStyle); sheet.SetCellValue(17 + iRow, "D", cashFlowItem, "Interest").SetCellStyle(cashFlowCellStyle); sheet.SetCellValue(17 + iRow, "E", cashFlowItem, "MiscIncome3").SetCellStyle(cashFlowCellStyle); sheet.SetCellValue(17 + iRow, "F", cashFlowItem, "MiscIncome4").SetCellStyle(cashFlowCellStyle); sheet.SetCellValue(17 + iRow, "G", cashFlowItem, "MiscIncome5").SetCellStyle(cashFlowCellStyle); sheet.SetCellValue(17 + iRow, "H", cashFlowItem, "MiscIncome6").SetCellStyle(cashFlowCellStyle); sheet.SetCellValue(17 + iRow, "I", (double)(iRow + 1)).SetCellFormat("0");; sheet.SetCellValue(17 + iRow, "J", cashFlowItem, "BackTaxes").SetCellStyle(cashFlowCellStyle); sheet.SetCellValue(17 + iRow, "K", cashFlowItem, "Legal").SetCellStyle(cashFlowCellStyle); sheet.SetCellValue(17 + iRow, "L", cashFlowItem, "Travel").SetCellStyle(cashFlowCellStyle); sheet.SetCellValue(17 + iRow, "M", cashFlowItem, "BrokerFee").SetCellStyle(cashFlowCellStyle); sheet.SetCellValue(17 + iRow, "N", cashFlowItem, "REOTax").SetCellStyle(cashFlowCellStyle); sheet.SetCellValue(17 + iRow, "O", cashFlowItem, "REOins").SetCellStyle(cashFlowCellStyle); sheet.SetCellValue(17 + iRow, "P", cashFlowItem, "CapEx").SetCellStyle(cashFlowCellStyle); sheet.SetCellValue(17 + iRow, "Q", cashFlowItem, "TiLc").SetCellStyle(cashFlowCellStyle); sheet.SetCellValue(17 + iRow, "R", cashFlowItem, "Environ").SetCellStyle(cashFlowCellStyle); sheet.SetCellValue(17 + iRow, "S", cashFlowItem, "Misc").SetCellStyle(cashFlowCellStyle); iRow++; } iRow++; sheet.CreateRow(17 + iRow); sheet.SetCellValue(17 + iRow, "C", 0.0).SetCellFormat(formatStr).SetCellFormula(string.Format("SUM(C18:C{0})", (18 + iRow - 2))); sheet.SetCellValue(17 + iRow, "D", 0.0).SetCellFormat(formatStr).SetCellFormula(string.Format("SUM(D18:D{0})", (18 + iRow - 2))); sheet.SetCellValue(17 + iRow, "E", 0.0).SetCellFormat(formatStr).SetCellFormula(string.Format("SUM(E18:E{0})", (18 + iRow - 2))); sheet.SetCellValue(17 + iRow, "F", 0.0).SetCellFormat(formatStr).SetCellFormula(string.Format("SUM(F18:F{0})", (18 + iRow - 2))); sheet.SetCellValue(17 + iRow, "G", 0.0).SetCellFormat(formatStr).SetCellFormula(string.Format("SUM(G18:G{0})", (18 + iRow - 2))); sheet.SetCellValue(17 + iRow, "H", 0.0).SetCellFormat(formatStr).SetCellFormula(string.Format("SUM(H18:H{0})", (18 + iRow - 2))); sheet.SetCellValue(17 + iRow, "J", 0.0).SetCellFormat(formatStr).SetCellFormula(string.Format("SUM(I18:I{0})", (18 + iRow - 2))); sheet.SetCellValue(17 + iRow, "K", 0.0).SetCellFormat(formatStr).SetCellFormula(string.Format("SUM(J18:J{0})", (18 + iRow - 2))); sheet.SetCellValue(17 + iRow, "L", 0.0).SetCellFormat(formatStr).SetCellFormula(string.Format("SUM(K18:K{0})", (18 + iRow - 2))); sheet.SetCellValue(17 + iRow, "M", 0.0).SetCellFormat(formatStr).SetCellFormula(string.Format("SUM(L18:L{0})", (18 + iRow - 2))); sheet.SetCellValue(17 + iRow, "N", 0.0).SetCellFormat(formatStr).SetCellFormula(string.Format("SUM(M18:M{0})", (18 + iRow - 2))); sheet.SetCellValue(17 + iRow, "O", 0.0).SetCellFormat(formatStr).SetCellFormula(string.Format("SUM(N18:N{0})", (18 + iRow - 2))); sheet.SetCellValue(17 + iRow, "P", 0.0).SetCellFormat(formatStr).SetCellFormula(string.Format("SUM(O18:O{0})", (18 + iRow - 2))); sheet.SetCellValue(17 + iRow, "Q", 0.0).SetCellFormat(formatStr).SetCellFormula(string.Format("SUM(P18:P{0})", (18 + iRow - 2))); sheet.SetCellValue(17 + iRow, "R", 0.0).SetCellFormat(formatStr).SetCellFormula(string.Format("SUM(Q18:Q{0})", (18 + iRow - 2))); sheet.SetCellValue(17 + iRow, "S", 0.0).SetCellFormat(formatStr).SetCellFormula(string.Format("SUM(R18:R{0})", (18 + iRow - 2))); iRow = iRow + 17 + 3; //line up past the model last row and the last row of all the generated rows var titleStyle = new XSSFNPoiStyle() { IsBold = true, FontHeightInPoints = 30 }; var notesStyle = new XSSFNPoiStyle() { Border = CellBorder.All, BorderStyle = BorderStyle.Thin, HorizontalAlignment = HorizontalAlignment.Left, VerticalAlignment = VerticalAlignment.Top, WrapText = true }; // Remove Notes from Report (12/18/2019) // The are going to add a Special Notes doc to eacj relationship. //sheet.SetCellValue(iRow, "A", "Asset Notes").SetCellStyle(titleStyle); //iRow++; //this.sheet.MergeCellsRange(iRow, iRow + 21, "A", "Z").SetRangeStyle(this.sheet, notesStyle); //sheet.SetCellValue(iRow, "A", uwRelItem, "AssetNotes").SetCellStyle(notesStyle); //iRow = iRow + 22; //sheet.SetCellValue(iRow, "A", "Collateral Valuation Notes").SetCellStyle(titleStyle); //iRow++; //this.sheet.MergeCellsRange(iRow, iRow + 21, "A", "Z").SetRangeStyle(this.sheet, notesStyle); //sheet.SetCellValue(iRow, "A", uwRelItem, "CollateralValuationNotes").SetCellStyle(notesStyle); //iRow = iRow + 22; //sheet.SetCellValue(iRow, "A", "Title UCC Notes").SetCellStyle(titleStyle); //iRow++; //this.sheet.MergeCellsRange(iRow, iRow + 21, "A", "Z").SetRangeStyle(this.sheet, notesStyle); //sheet.SetCellValue(iRow, "A", uwRelItem, "TitleUCCNotes").SetCellStyle(notesStyle); //iRow = iRow + 22; //sheet.SetCellValue(iRow, "A", "Environmental Notes").SetCellStyle(titleStyle); //iRow++; //this.sheet.MergeCellsRange(iRow, iRow + 21, "A", "Z").SetRangeStyle(this.sheet, notesStyle); //sheet.SetCellValue(iRow, "A", uwRelItem, "EnvironmentalNotes").SetCellStyle(notesStyle); //iRow = iRow + 22; } catch (Exception) { throw; } return(true); }