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