public string CreateRunPlanFile(int daId, HttpPostedFileBase rpFilePath) { try { DataTable getTestDesignTable = new DataTable(); using (package = new ExcelPackage()) { using (var objExcelPackage = new ExcelPackage(rpFilePath.InputStream)) { ExcelWorksheet ws = excelCommonFunctions.OpenSheet(objExcelPackage, "Design Document"); //testDesignData getTestDesignTable = GetDataTableFromExcel(rpFilePath, true); } 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; AddRunPlanData(getTestDesignTable, daId, moduleName); string filePath = excelCommonFunctions.SaveFile(package, da.daName, "", "RunPlanFile"); return(filePath); } } catch (Exception) { throw; } }
// 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 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; } }
public string ExportDAFile(int id) { try { ExportDesignAcceleratorViewModel ExportDAViewModel = new ExportDesignAcceleratorViewModel(); ProductsViewModel prodviewmodel = new ProductsViewModel(); //if (id == null) // id = (int)TempData["daId"]; var lstprods = prodviewmodel.GetProducts(id); var lstExportProducts = ExportDAViewModel.GetProducts(id); var lstExportTransactions = ExportDAViewModel.GetTransactions(id); IList <tbl_Attribute> lstExportAttributes = ExportDAViewModel.GetAttributes(id); List <string> lstSheetNames = new List <string>(); lstSheetNames.Add("Products"); lstSheetNames.Add("Transactions"); lstSheetNames.Add("Transaction Attributes"); lstSheetNames.Add("Business Rules"); lstSheetNames.Add("Interfaces"); lstSheetNames.Add("Channels"); lstSheetNames.Add("Reports"); int i = 1; string tbl = ""; using (ExcelPackage objExcelPackage = new ExcelPackage()) { foreach (var sheet in lstSheetNames) { DataTable dt = new DataTable(); switch (sheet) { case "Products": dt = CreateProductTable(); break; case "Transactions": dt = CreateTransactionsTable(); break; case "Transaction Attributes": dt = CreateAttributesTable(lstExportAttributes, id); break; case "Business Rules": dt = CreateBusinessRulesTable(id, tbl); break; case "Interfaces": dt = CreateInterfaceTable(id); break; case "Channels": dt = CreateChannelsAlertsTable(id); break; case "Reports": dt = CreateReportsTable(id); break; default: break; } ExcelWorksheet ws = CreateSheet(objExcelPackage, sheet, dt, i); if (sheet == "Transaction Attributes" || sheet == "Interfaces" || sheet == "Channels" || sheet == "Reports" || sheet == "Business Rules") { ws.Cells[2, 1].Value = sheet; ws.Cells[2, 1, 2, 3].Merge = true; //Merge columns start and end range ws.Cells[2, 1, 2, 3].Style.Font.Bold = true; //Font should be bold ws.Cells[2, 1, 2, 3].Style.Font.Size = 20; ws.Cells[2, 1, 2, 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; // Alignment is center ws.Cells[2, 1, 2, 3].Style.Fill.PatternType = ExcelFillStyle.Solid; // Alignment is center ws.Cells[2, 1, 2, 3].Style.Fill.BackgroundColor.SetColor(Color.LightGray); // Alignment is center var border = ws.Cells[2, 1, 2, 3].Style.Border; border.Bottom.Style = ExcelBorderStyle.Medium; border.Top.Style = ExcelBorderStyle.Thin; border.Left.Style = ExcelBorderStyle.None; border.Right.Style = ExcelBorderStyle.None; int colIndex, rowIndex; if (sheet == "Business Rules") { CreateTableHeaderForBR(dt, ws, out colIndex, out rowIndex, "brtbl", count); } else if (sheet == "Reports") { CreateTableHeaderForReports(dt, ws, out colIndex, out rowIndex, "Reports", count); } else if (sheet == "Transaction Attributes") { CreateTableHeaderForBR(dt, ws, out colIndex, out rowIndex, "txnAttr", count); } else if (sheet == "Interfaces") { CreateTableHeaderForBR(dt, ws, out colIndex, out rowIndex, "Interfaces", count); } else if (sheet == "Channels") { CreateTableHeaderForBR(dt, ws, out colIndex, out rowIndex, "Channels", count); } else { CreateTableHeaderForBR(dt, ws, out colIndex, out rowIndex, "", count); } AddRowsHLT(dt, ws, ref colIndex, ref rowIndex, count, id); ws.View.ShowGridLines = false; ws.View.ZoomScale = 80; ws.Cells.AutoFitColumns(); int j; if (sheet == "Business Rules") { for (j = 5; j <= count + 4; j++) { ws.Column(j).Width = 3.57; } } else if (sheet == "Reports") { for (j = 6; j <= count + 5; j++) { ws.Column(j).Width = 3.57; } } else if (sheet == "Transaction Attributes") { for (j = 4; j <= count + 3; j++) { ws.Column(j).Width = 3.57; } } else if (sheet == "Interfaces") { for (j = 8; j <= count + 7; j++) { ws.Column(j).Width = 3.57; } } else if (sheet == "Channels") { for (j = 5; j <= count + 4; j++) { ws.Column(j).Width = 3.57; } } if (sheet == "Business Rules") { ws.DeleteColumn(3); } else if (sheet == "Interfaces") { ws.DeleteColumn(4); } else if (sheet == "Channels") { var cnt = ws.Dimension.End.Column; ws.DeleteColumn(cnt); } else if (sheet == "Transaction Attributes") { ws.DeleteColumn(count + 6); } else if (sheet == "Reports") { ws.DeleteColumn(4); var cnt = ws.Dimension.End.Column; ws.DeleteColumn(cnt); } i++; } else { ws.Cells[2, 1].Value = sheet; ws.Cells[2, 1, 2, 3].Merge = true; //Merge columns start and end range ws.Cells[2, 1, 2, 3].Style.Font.Bold = true; //Font should be bold ws.Cells[2, 1, 2, 3].Style.Font.Size = 20; ws.Cells[2, 1, 2, 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; // Alignment is center ws.Cells[2, 1, 2, 3].Style.Fill.PatternType = ExcelFillStyle.Solid; // Alignment is center ws.Cells[2, 1, 2, 3].Style.Fill.BackgroundColor.SetColor(Color.LightGray); // Alignment is center var border = ws.Cells[2, 1, 2, 3].Style.Border; border.Bottom.Style = ExcelBorderStyle.Medium; border.Top.Style = ExcelBorderStyle.Thin; border.Left.Style = ExcelBorderStyle.None; border.Right.Style = ExcelBorderStyle.None; int colIndex, rowIndex; CreateTableHeader(dt, ws, out colIndex, out rowIndex, tbl); AddRows(dt, ws, ref colIndex, ref rowIndex); 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(id); ExcelCommonFunctions excelCommonfunctions = new ExcelCommonFunctions(); string filePath = excelCommonfunctions.SaveFile(objExcelPackage, da.daName, "", "ExportDA"); return(filePath); } } catch (Exception) { throw; } }