// GET: TestDesign public async Task <string> GenerateTestDesign(int daId, HttpPostedFileBase txmPath, HttpPostedFileBase ScbPath) { try { Dictionary <string, List <TestScenarioMembers> > testScenarios = new Dictionary <string, List <TestScenarioMembers> >(); IList <tbl_Attribute> lstAttributes = new List <tbl_Attribute>(); TransactionMatrix transactionMatrix = new TransactionMatrix(); testDesignVM.lstHighLevelTransactions = testDesignVM.GetTransactionsList(daId).lstTransactions; ModuleViewModel moduleViewModel = new ModuleViewModel(); int colIndex = 1, rowIndex = 0; //Create new workbook for Test Design using (var package = new ExcelPackage()) { ExcelWorksheet ws2 = package.Workbook.Worksheets.Add("Design Document"); CreateHeadersForTD(ws2, out dtTD); //Read data from Test Scenario using (var objExcelPackage = new ExcelPackage(ScbPath.InputStream)) { //string scenarioId = ""; //string testConditionId = ""; ExcelWorksheet ws = excelCommonFunctions.OpenSheet(objExcelPackage, "Test Scenarios"); testScenarios = GetDataTableFromExcel(ScbPath); } //Read data from RuleOfNData DataSet transactionMatrixSheets = new DataSet(); //list of the rule reference datatables will come into this list List <DataTable> dtRuleOfNforTestDesignRuleReference = new List <DataTable>(); //Dictionary to add the fetched last tables from tm into dtRuleOFNForTestDesignRuleReference and pass as a parameter to addTestDesign method. Dictionary <string, List <DataTable> > ruleReferences = new Dictionary <string, List <DataTable> >(); using (var objExcelPackage = new ExcelPackage(txmPath.InputStream)) { foreach (var trans in testDesignVM.lstHighLevelTransactions) { int colIdex = 1, rowIdex = 0; ExcelWorksheet ws = excelCommonFunctions.OpenSheet(objExcelPackage, trans.HighLevelTxnDesc); //For Test design last tables dtRuleOfNforTestDesignRuleReference = transactionMatrix.GetRuleOfNDataForTestDesignRuleReferene(ws, ref colIdex, ref rowIdex); //Adding all the fetched tables into the dictionary from list foreach (var item in dtRuleOfNforTestDesignRuleReference) { if (ruleReferences.ContainsKey(ws.ToString())) { ruleReferences[ws.ToString()].Add(item); } else { ruleReferences.Add(ws.ToString(), new List <DataTable> { item }); } } DataTable dtRuleOfN = transactionMatrix.GetRuleOfNData(ws, ref colIdex, ref rowIdex); dtRuleOfN.TableName = trans.HighLevelTxnDesc; transactionMatrixSheets.Tables.Add(dtRuleOfN); } } tbl_DesignAccelerator da = new tbl_DesignAccelerator(); DAManager daManager = new DAManager(); da = daManager.FindDA(daId); var modId = da.ModuleId; tbl_Module daModule = new tbl_Module(); ModuleManager modManager = new ModuleManager(); daModule = modManager.FindModuleNameForRunPlan(modId); var moduleName = daModule.ModuleName; string tblName4 = moduleName + " - Test Design Document"; ws2.Cells[2, 1].Value = tblName4; //// Format Excel Sheet ws2.Cells[2, 1, 2, 5].Merge = true; //Merge columns start and end range ws2.Cells[2, 1, 2, 5].Style.Font.Bold = true; //Font should be bold ws2.Cells[2, 1, 2, 5].Style.Font.Size = 14; ws2.Cells[2, 1, 2, 5].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; // Alignment is Left ws2.Cells[2, 1, 2, 5].Style.Fill.PatternType = ExcelFillStyle.Solid; // Border ws2.Cells[2, 1, 2, 5].Style.Fill.BackgroundColor.SetColor(Color.LightGray); // Background Color excelCommonFunctions.CreateTableHeader(dtTD, ws2, ref colIndex, ref rowIndex, "tbl4"); ws2.Cells["A4:N4"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; AddTestDesign(testScenarios, transactionMatrixSheets, ws2, colIndex, rowIndex, dtTD, testDesignVM.lstHighLevelTransactions, ruleReferences); //Format Excel Sheet ws2.View.ShowGridLines = false; ws2.View.ZoomScale = 80; ws2.Cells.AutoFitColumns(); ws2.Column(4).Width = 50; ws2.Column(12).Width = 75; ws2.Column(13).Width = 75; ws2.Column(14).Width = 75; string filePath = excelCommonFunctions.SaveFile(package, da.daName, "", "TD"); return(filePath); } } catch (Exception) { throw; } }
public void AddRunPlanData(DataTable testDesignTableData, int daId, string moduleName) { try { int colIndex = 1; int rowIndex = 11; string scenarioNo = string.Empty; string functionality = string.Empty; string testCaseDesc = string.Empty; string preReqs = string.Empty; string testCaseId = string.Empty; string testScenarioId = string.Empty; string module = string.Empty; string status = string.Empty; string defectId = string.Empty; string remarks = string.Empty; string conditionNo = string.Empty; int counter = 1; int noRunCount = 1; int passCount = 0; int failCount = 0; int blockedCount = 0; int incompleteCount = 0; int notApplicableCount = 0; ExcelWorksheet runPlanws = null; Dictionary <string, List <DataRow> > totalDays = new Dictionary <string, List <DataRow> >(); int i = 0; for (; i < testDesignTableData.Rows.Count; i++) { var logicalDay = testDesignTableData.Rows[i]["Logical Day"].ToString(); logicalDay = Regex.Replace(logicalDay, @"\t|\n|\r", ""); logicalDay = logicalDay.Trim(); var checkExistingWs = package.Workbook.Worksheets.FirstOrDefault(w => w.Name == logicalDay.ToString()); // package.Workbook.Worksheets.OrderBy(package.Workbook.Worksheets); if (checkExistingWs == null) { runPlanws = package.Workbook.Worksheets.Add(logicalDay.ToString()); CreateHeadersForRunPlan(runPlanws, out dtRPstatus); CreateHeadersForRunPlanData(runPlanws, out dtRPData); string displayName = "Run Plan"; runPlanws.Cells[2, 1].Value = displayName; var cellFormat = runPlanws.Cells[2, 1, 2, 4]; cellFormat.Merge = true; cellFormat.Style.Font.Bold = true; cellFormat.Style.Font.Size = 20; cellFormat.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; cellFormat.Style.Fill.PatternType = ExcelFillStyle.Solid; cellFormat.Style.Fill.BackgroundColor.SetColor(Color.LightGray); excelCommonFunctions.CreateTableHeader(dtRPstatus, runPlanws, ref colIndex, ref rowIndex, "RunPlanStatus"); excelCommonFunctions.CreateTableHeader(dtRPData, runPlanws, ref colIndex, ref rowIndex, "RunPlanData"); colIndex = 1; rowIndex++; runPlanws.View.ShowGridLines = false; runPlanws.View.ZoomScale = 80; runPlanws.Cells.AutoFitColumns(); runPlanws.Column(1).Width = 20; runPlanws.Column(2).Width = 30; runPlanws.Column(3).Width = 20; runPlanws.Column(4).Width = 20; runPlanws.Column(5).Width = 20; runPlanws.Column(6).Width = 20; runPlanws.Column(7).Width = 55; runPlanws.Column(8).Width = 17; runPlanws.Column(9).Width = 18; runPlanws.Column(10).Width = 15; DataRow[] dRow = testDesignTableData.Select("[Logical Day]= '" + logicalDay + "'"); var count = dRow.Length; if (dRow.Length > 0) { foreach (var dr in dRow) { scenarioNo = dr["Scenario No."].ToString(); functionality = dr["Functionality"].ToString(); testCaseDesc = dr["Test Case Description"].ToString(); preReqs = dr["Pre-Requisites"].ToString(); conditionNo = dr["Condition No."].ToString(); if (totalDays.ContainsKey(logicalDay.ToString())) { totalDays[logicalDay.ToString()].Add(dr); //for main data table //count i.e serial No. var cell = runPlanws.Cells[rowIndex, colIndex]; cell.Value = counter; ScenarioBuilder.FormatCell(cell); cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; cell.Style.WrapText = true; colIndex++; //Module name cell = runPlanws.Cells[rowIndex, colIndex]; cell.Value = moduleName; ScenarioBuilder.FormatCell(cell); cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; cell.Style.WrapText = true; colIndex++; //functionality cell = runPlanws.Cells[rowIndex, colIndex]; cell.Value = functionality; ScenarioBuilder.FormatCell(cell); cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; cell.Style.WrapText = true; colIndex++; //Test Scenario Id. string scnId = scenarioNo.Replace("SL", "RD"); cell = runPlanws.Cells[rowIndex, colIndex]; cell.Value = scnId; ScenarioBuilder.FormatCell(cell); cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; cell.Style.WrapText = true; colIndex++; //Prerequisites cell = runPlanws.Cells[rowIndex, colIndex]; cell.Value = preReqs; ScenarioBuilder.FormatCell(cell); cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; cell.Style.WrapText = true; colIndex++; //Test Case Id string testcaseId = conditionNo.Substring(conditionNo.Length - 6); cell = runPlanws.Cells[rowIndex, colIndex]; if (scnId == "") { cell.Value = ""; } else { cell.Value = scnId + "_" + testcaseId; } ScenarioBuilder.FormatCell(cell); cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; cell.Style.WrapText = true; colIndex++; //Test Case Description. cell = runPlanws.Cells[rowIndex, colIndex]; cell.Value = testCaseDesc; ScenarioBuilder.FormatCell(cell); cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; cell.Style.WrapText = true; colIndex++; //Status var formulaCell = runPlanws.Cells[rowIndex, colIndex]; formulaCell.Value = "No Run"; var selectStatus = runPlanws.DataValidations.AddListValidation(formulaCell.ToString()); selectStatus.ShowErrorMessage = true; selectStatus.ErrorStyle = OfficeOpenXml.DataValidation.ExcelDataValidationWarningStyle.stop; selectStatus.ErrorTitle = "Error"; selectStatus.Error = "Please select a value present in the dropdown list"; selectStatus.Formula.Values.Add("Pass"); selectStatus.Formula.Values.Add("Fail"); selectStatus.Formula.Values.Add("No Run"); selectStatus.Formula.Values.Add("Blocked"); selectStatus.Formula.Values.Add("Not Completed"); selectStatus.Formula.Values.Add("Not Applicable"); ScenarioBuilder.FormatCell(formulaCell); cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; cell.Style.WrapText = true; colIndex++; //Defect Id cell = runPlanws.Cells[rowIndex, colIndex]; cell.Value = ""; ScenarioBuilder.FormatCell(cell); cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; cell.Style.WrapText = true; colIndex++; //Remarks cell = runPlanws.Cells[rowIndex, colIndex]; cell.Value = ""; ScenarioBuilder.FormatCell(cell); cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; cell.Style.WrapText = true; //for status table int statusRowno = 5; int statusColno = 1; //module name var statusCell = runPlanws.Cells[statusRowno, statusColno++]; statusCell.Value = moduleName; ScenarioBuilder.FormatCell(statusCell); statusCell.Style.VerticalAlignment = ExcelVerticalAlignment.Top; statusCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; //total cases(planned cases) statusCell = runPlanws.Cells[statusRowno, statusColno++]; statusCell.Value = count; ScenarioBuilder.FormatCell(statusCell); statusCell.Style.VerticalAlignment = ExcelVerticalAlignment.Top; statusCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; //pass statusCell = runPlanws.Cells[statusRowno, statusColno++]; statusCell.Value = passCount; statusCell.Formula = "COUNTIF(" + rowIndex + ":" + colIndex + "," + @"""Pass""" + ")"; ScenarioBuilder.FormatCell(statusCell); statusCell.Style.VerticalAlignment = ExcelVerticalAlignment.Top; statusCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; //fail statusCell = runPlanws.Cells[statusRowno, statusColno++]; statusCell.Value = failCount; statusCell.Formula = "COUNTIF(" + rowIndex + ":" + colIndex + "," + @"""Fail""" + ")"; ScenarioBuilder.FormatCell(statusCell); statusCell.Style.VerticalAlignment = ExcelVerticalAlignment.Top; statusCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; //Blocked statusCell = runPlanws.Cells[statusRowno, statusColno++]; statusCell.Value = blockedCount; statusCell.Formula = "COUNTIF(" + rowIndex + ":" + colIndex + "," + @"""Blocked""" + ")"; ScenarioBuilder.FormatCell(statusCell); statusCell.Style.VerticalAlignment = ExcelVerticalAlignment.Top; statusCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; //not completed statusCell = runPlanws.Cells[statusRowno, statusColno++]; statusCell.Value = 0; statusCell.Formula = "COUNTIF(" + rowIndex + ":" + colIndex + "," + @"""Not Completed""" + ")"; ScenarioBuilder.FormatCell(statusCell); statusCell.Style.VerticalAlignment = ExcelVerticalAlignment.Top; statusCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; //No run statusCell = runPlanws.Cells[statusRowno, statusColno++]; statusCell.Value = count; statusCell.Formula = "COUNTIF(" + rowIndex + ":" + colIndex + "," + @"""No Run""" + ")"; ScenarioBuilder.FormatCell(statusCell); statusCell.Style.VerticalAlignment = ExcelVerticalAlignment.Top; statusCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; //not applicable statusCell = runPlanws.Cells[statusRowno, statusColno++]; statusCell.Value = notApplicableCount; statusCell.Formula = "COUNTIF(" + rowIndex + ":" + colIndex + "," + @"""Not Applicable""" + ")"; ScenarioBuilder.FormatCell(statusCell); statusCell.Style.VerticalAlignment = ExcelVerticalAlignment.Top; statusCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; //Total executed cases statusCell = runPlanws.Cells[statusRowno, statusColno++]; statusCell.Value = count; ScenarioBuilder.FormatCell(statusCell); statusCell.Style.VerticalAlignment = ExcelVerticalAlignment.Top; statusCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; rowIndex++; colIndex = 1; } else { totalDays.Add(logicalDay.ToString(), new List <DataRow> { dr }); //for main data table //count i.e serial No. var cell = runPlanws.Cells[rowIndex, colIndex]; cell.Value = counter; ScenarioBuilder.FormatCell(cell); cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; colIndex++; //Module name cell = runPlanws.Cells[rowIndex, colIndex]; cell.Value = moduleName; ScenarioBuilder.FormatCell(cell); cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; colIndex++; //functionality cell = runPlanws.Cells[rowIndex, colIndex]; cell.Value = functionality; ScenarioBuilder.FormatCell(cell); cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; colIndex++; //Test Scenario Id. string scnId = scenarioNo.Replace("SL", "RD"); cell = runPlanws.Cells[rowIndex, colIndex]; cell.Value = scnId; ScenarioBuilder.FormatCell(cell); cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; colIndex++; //Prerequisites cell = runPlanws.Cells[rowIndex, colIndex]; cell.Value = preReqs; ScenarioBuilder.FormatCell(cell); cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; colIndex++; //Test Case Id string testcaseId = conditionNo.Substring(conditionNo.Length - 6); cell = runPlanws.Cells[rowIndex, colIndex]; // since test case id depends on the scenario id, so if scenarioid is blank then test case id too will be blank. if (scnId == "") { cell.Value = ""; } else { cell.Value = scnId + "_" + testcaseId; } ScenarioBuilder.FormatCell(cell); cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; colIndex++; //Test Case Description. cell = runPlanws.Cells[rowIndex, colIndex]; cell.Value = testCaseDesc; ScenarioBuilder.FormatCell(cell); cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; colIndex++; //Status cell = runPlanws.Cells[rowIndex, colIndex]; cell.Value = "No Run"; var selectStatus = runPlanws.DataValidations.AddListValidation(cell.ToString()); selectStatus.Formula.Values.Add("Pass"); selectStatus.Formula.Values.Add("Fail"); selectStatus.Formula.Values.Add("No Run"); selectStatus.Formula.Values.Add("Blocked"); selectStatus.Formula.Values.Add("Not Completed"); selectStatus.Formula.Values.Add("Not Applicable"); ScenarioBuilder.FormatCell(cell); cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; colIndex++; //Defect Id cell = runPlanws.Cells[rowIndex, colIndex]; cell.Value = ""; ScenarioBuilder.FormatCell(cell); cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; colIndex++; //Remarks cell = runPlanws.Cells[rowIndex, colIndex]; cell.Value = ""; ScenarioBuilder.FormatCell(cell); cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; //for status table int statusRowno = 5; int statusColno = 1; //module name var statusCell = runPlanws.Cells[statusRowno, statusColno++]; statusCell.Value = moduleName; ScenarioBuilder.FormatCell(statusCell); statusCell.Style.VerticalAlignment = ExcelVerticalAlignment.Top; statusCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; //total cases(planned cases) statusCell = runPlanws.Cells[statusRowno, statusColno++]; statusCell.Value = count; ScenarioBuilder.FormatCell(statusCell); statusCell.Style.VerticalAlignment = ExcelVerticalAlignment.Top; statusCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; //pass statusCell = runPlanws.Cells[statusRowno, statusColno++]; statusCell.Value = passCount; statusCell.Formula = "COUNTIF(" + rowIndex + ":" + colIndex + "," + @"""Pass""" + ")"; ScenarioBuilder.FormatCell(statusCell); statusCell.Style.VerticalAlignment = ExcelVerticalAlignment.Top; statusCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; //fail statusCell = runPlanws.Cells[statusRowno, statusColno++]; statusCell.Value = failCount; statusCell.Formula = "COUNTIF(" + rowIndex + ":" + colIndex + "," + @"""Fail""" + ")"; ScenarioBuilder.FormatCell(statusCell); statusCell.Style.VerticalAlignment = ExcelVerticalAlignment.Top; statusCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; //Blocked statusCell = runPlanws.Cells[statusRowno, statusColno++]; statusCell.Value = blockedCount; statusCell.Formula = "COUNTIF(" + rowIndex + ":" + colIndex + "," + @"""Blocked""" + ")"; ScenarioBuilder.FormatCell(statusCell); statusCell.Style.VerticalAlignment = ExcelVerticalAlignment.Top; statusCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; //Not completed statusCell = runPlanws.Cells[statusRowno, statusColno++]; statusCell.Value = 0; statusCell.Formula = "COUNTIF(" + rowIndex + ":" + colIndex + "," + @"""Not completed""" + ")"; ScenarioBuilder.FormatCell(statusCell); statusCell.Style.VerticalAlignment = ExcelVerticalAlignment.Top; statusCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; //No run statusCell = runPlanws.Cells[statusRowno, statusColno++]; statusCell.Value = count; statusCell.Formula = "COUNTIF(" + rowIndex + ":" + colIndex + "," + @"""No Run""" + ")"; ScenarioBuilder.FormatCell(statusCell); statusCell.Style.VerticalAlignment = ExcelVerticalAlignment.Top; statusCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; //Not applicable statusCell = runPlanws.Cells[statusRowno, statusColno++]; statusCell.Value = notApplicableCount; statusCell.Formula = "COUNTIF(" + rowIndex + ":" + colIndex + "," + @"""Not Applicable""" + ")"; ScenarioBuilder.FormatCell(statusCell); statusCell.Style.VerticalAlignment = ExcelVerticalAlignment.Top; statusCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; //Total executed cases statusCell = runPlanws.Cells[statusRowno, statusColno++]; statusCell.Value = count; ScenarioBuilder.FormatCell(statusCell); statusCell.Style.VerticalAlignment = ExcelVerticalAlignment.Top; statusCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; rowIndex++; colIndex = 1; } //Run No. counter++; } } //resetting the counter for another new sheet counter = 1; //resetting the count for another sheet noRunCount = 1; failCount = 1; passCount = 1; incompleteCount = 1; blockedCount = 1; notApplicableCount = 1; } } //sorting sheets in order using linq lambda var a = package.Workbook.Worksheets.OrderBy(w => PadNumbers(w.ToString()));//using this PadNumbers method to sort out the sheets in order,it is assigning in order although. //looping and arranging the sorted sheets back. foreach (var item1 in a) { package.Workbook.Worksheets.MoveToEnd(item1.Index); } } catch (Exception) { throw; } }
public async Task <string> GenerateTransactionMatrix(int daId) { try { IList <sp_GetMappingViewModelData_Result> lstMappingViewModel = new List <sp_GetMappingViewModelData_Result>(); TransactionsManager transactions = new TransactionsManager(); MappingViewModel mappingViewModel = new MappingViewModel(); lstNegativeAttributeValues = mappingViewModel.GetNegativeAttributeValues(daId); lstMappingViewModel = mappingViewModel.GetMappedData(daId); var highLevelTransactions = transactions.GetAllTransactions(daId); ExcelCommonFunctions excelCommonFunctions = new ExcelCommonFunctions(); IList <Required> rList = ReturnRequired(lstMappingViewModel, mappingViewModel, highLevelTransactions); string filePath = ""; using (ExcelPackage objExcelPackage = new ExcelPackage()) { // Format Excel Sheet int i = 0; foreach (var item in highLevelTransactions) { var dtMappingTable = from a in rList where a.dtMappingTable.TableName == item.HighLevelTxnDesc select a; var dtTM = from a in rList where a.dtTM.TableName == item.HighLevelTxnDesc select a; ExcelWorksheet ws = excelCommonFunctions.CreateSheet(objExcelPackage, item.HighLevelTxnDesc, i); ws.Cells[1, 1].Value = "Transaction - " + item.HighLevelTxnDesc; // Format Excel Sheet ws.Cells[1, 1, 1, 7].Merge = true; //Merge columns start and end range ws.Cells[1, 1, 1, 7].Style.Font.Bold = true; //Font should be bold ws.Cells[1, 1, 1, 7].Style.Font.Size = 20; ws.Cells[1, 1, 1, 7].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; // Alignment is center ws.Cells[1, 1, 1, 7].Style.Fill.PatternType = ExcelFillStyle.Solid; // Border ws.Cells[1, 1, 1, 7].Style.Fill.BackgroundColor.SetColor(Color.LightGray); // Background Color int colIndex = 1, rowIndex = 0; excelCommonFunctions.CreateTableHeader(dtMappingTable.First().dtMappingTable, ws, ref colIndex, ref rowIndex, "tbl1"); excelCommonFunctions.AddRows(dtMappingTable.First().dtMappingTable, ws, ref colIndex, ref rowIndex); excelCommonFunctions.CreateTableHeader(dtTM.First().dtTM, ws, ref colIndex, ref rowIndex, "tbl2"); excelCommonFunctions.AddRows(dtTM.First().dtTM, ws, ref colIndex, ref rowIndex); //Format Excel Sheet ws.View.ShowGridLines = false; ws.View.ZoomScale = 80; ws.Cells.AutoFitColumns(); i++; } tbl_DesignAccelerator da = new tbl_DesignAccelerator(); DAManager daManager = new DAManager(); da = daManager.FindDA(daId); filePath = excelCommonFunctions.SaveFile(objExcelPackage, da.daName, "", "Rule of N - Txn Matrix"); } return(filePath); } catch (Exception) { throw; } }
public string GenerateBusinessRuleMappingTable(int daId, HttpPostedFileBase path) { try { ExcelCommonFunctions excelCommonFunctions = new ExcelCommonFunctions(); TransactionMatrix transactionMatrix = new TransactionMatrix(); InterfaceMappingViewModel interfaceMappingView = new InterfaceMappingViewModel(); interfaceMappingView.lstHighLevelTxns = interfaceMappingView.GetTransactionsList(daId).lstTransactions; BusinessRuleMappingViewModel buzRulesMappingView = new BusinessRuleMappingViewModel(); buzRulesMappingView.lstBuzRulesData = buzRulesMappingView.GetBuzRulesList(daId); using (ExcelPackage objExcelPackage = new ExcelPackage(path.InputStream)) { int cnt = 1; foreach (var trans in interfaceMappingView.lstHighLevelTxns) { int colIndex = 1, rowIndex = 0; int rowCountRuleofN; ExcelWorksheet ws = excelCommonFunctions.OpenSheet(objExcelPackage, trans.HighLevelTxnDesc); DataTable dtRuleOfN = transactionMatrix.GetRuleOfNDataForAllTables(ws, ref colIndex, ref rowIndex, "Business Rules - " + trans.HighLevelTxnDesc); //get merged cells to find the end row of Rule of N table if (ws.MergedCells.Count == 1) { rowCountRuleofN = dtRuleOfN.Rows.Count; } else { //the header row of Rule of N is fixed as 17 var c = ws.MergedCells[1]; //Assuming the first merged cell is of one mapping table. ExcelAddress cellAddr = new ExcelAddress(c); int row = cellAddr.Start.Row; //to get the row number of the merged cell int lastRow = row - 2; //to get last row of Rule of N table rowCountRuleofN = lastRow - 17; // to get count of rows of RuleofN table excluding header. } //create datatable for each transaction DataTable dtBuzRules = BusinessRuleMappingViewModel.CreateBuzRulesDataTable(buzRulesMappingView.lstBuzRulesData, trans.TransactionSeq, dtRuleOfN); //Table#2 if (dtBuzRules.Rows.Count != 0) { rowIndex = rowIndex + 2; string tblName4 = "Business Rules - " + trans.HighLevelTxnDesc; ws.Cells[rowIndex, 1].Value = tblName4; // ws.Cells[rowIndex, 1, rowIndex, 3].Merge = true; //Merge columns start and end range ws.Cells[rowIndex, 1, rowIndex, 3].Style.Font.Bold = true; //Font should be bold ws.Cells[rowIndex, 1, rowIndex, 3].Style.Font.Size = 20; ws.Cells[rowIndex, 1, rowIndex, 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; // Alignment is center ws.Cells[rowIndex, 1, rowIndex, 3].Style.Fill.PatternType = ExcelFillStyle.Solid; // Aligmnet is center ws.Cells[rowIndex, 1, rowIndex, 3].Style.Fill.BackgroundColor.SetColor(Color.LightGray); // Alignment is center colIndex = 1; rowIndex = rowIndex + 2; excelCommonFunctions.CreateTableHeader(dtBuzRules, ws, ref colIndex, ref rowIndex, tblName4); Dictionary <string, string> dictBuzRules = excelCommonFunctions.GetMappingID(dtBuzRules); excelCommonFunctions.AddRowsMapping(dtBuzRules, ws, dictBuzRules, rowCountRuleofN, ref colIndex, ref rowIndex); } ws.View.ShowGridLines = false; ws.View.ZoomScale = 80; //ws.Cells.AutoFitColumns(); //rearranged cnt++; } tbl_DesignAccelerator da = new tbl_DesignAccelerator(); DAManager daManager = new DAManager(); da = daManager.FindDA(daId); string filePath = excelCommonFunctions.SaveFile(objExcelPackage, da.daName, path.FileName, "BR"); return(filePath); } } catch (Exception) { throw; } }