// Function added by Rahul public static string readExcelDataByRowAndColumnName(string key, string value) { //string pathToDir = CommonUtilities.GetProjectDirectory() + "ParameterAndUserDataMgn\\"; //string pathToDir = Environment.CurrentDirectory + "\\Test_Data.xlsx"; string pathToDir = ConfigurationManager.AppSettings["TEST_DATA_PATH"]; string fileName = "Test_Data.xlsx"; string cellValue = SAFINCA.ParameterAndUserDataMgn.Parameters.NULL_CHAR; Excel.Application xlApp = new Excel.Application(); try { cellValue = getCell(pathToDir, fileName, key, value, xlApp).Value.ToString(); xlApp.Workbooks.Close(); xlApp.Quit(); ProcessMgn.killProcessByNames(SAFINCA.ParameterAndUserDataMgn.Parameters.EXCEL_PROCESS_NAME); } catch (Exception e) { xlApp.Workbooks.Close(); xlApp.Application.Quit(); ProcessMgn.killProcessByNames(SAFINCA.ParameterAndUserDataMgn.Parameters.EXCEL_PROCESS_NAME); } return(cellValue); }
// Function added by Rahul public static void writeDataToExcelFile(string pathToDir, string fileName, string key, string value) { Excel.Workbook workbook; Excel.Application xlApp = new Excel.Application(); try { //String SHEET_NAME = getSheetName(key, value); String SHEET_NAME = "USER_DATA_TEST"; workbook = xlApp.Workbooks.Open(pathToDir + fileName); Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[SHEET_NAME]; Excel.Range range = worksheet.UsedRange; int rowCount = range.Rows.Count; int rowNameIndex = 0; for (int r = 1; r <= rowCount; r++) { string rowNameForCell = range.Cells[r, 1].Value.ToString(); if (rowNameForCell.Equals(key)) { rowNameIndex = r; break; } } if (rowNameIndex == 0) { range.Cells[rowCount + 1, 1] = key; range.Cells[rowCount + 1, 2] = value; } else { range.Rows[rowNameIndex].EntireRow.Delete(); range.Cells[rowCount, 1] = key; range.Cells[rowCount, 2] = value; } xlApp.DisplayAlerts = false; long maxTimeToWait = CommonUtilities.GetCurrentTimeMillis() + CommonUtilities.GLOBAL_TIMEOUT_TIME_IN_MSEC; xlApp.Workbooks.Application.ActiveWorkbook.Save(); while (!xlApp.Workbooks.Application.ActiveWorkbook.Saved && maxTimeToWait > CommonUtilities.GetCurrentTimeMillis()) { xlApp.Workbooks.Application.ActiveWorkbook.Save(); } xlApp.Workbooks.Close(); xlApp.Quit(); ProcessMgn.killProcessByNames(SAFINCA.ParameterAndUserDataMgn.Parameters.EXCEL_PROCESS_NAME); } catch (Exception e) { xlApp.DisplayAlerts = false; xlApp.Workbooks.Close(); xlApp.Quit(); ProcessMgn.killProcessByNames(SAFINCA.ParameterAndUserDataMgn.Parameters.EXCEL_PROCESS_NAME); throw new Exception(e.ToString()); } }
///// <summary> ///// Read value from excel sheet based on rowName and columnName. Note the column name start with TD_, ///// indicating the file name Test_Data.xlsx or UD_ indicating User_Data.xlsx ///// </summary> ///// <param name="key"></param> ///// <param name="value"></param> ///// <returns></returns> public static string readInputDataByRowAndColumnName(string key, string value) { //string pathToDir = CommonUtilities.GetProjectDirectory() + "ParameterAndUserDataMgn\\"; //string pathToDir = Environment.CurrentDirectory + "\\Test_Data.xlsx"; string pathToDir = ConfigurationManager.AppSettings["TEST_DATA_PATH"]; string fileName = getFileName(key, value); if (!fileName.Contains(TEST_DATA_XLSX)) { pathToDir = @ConfigParameters.PATH_DYNAMIC_TEST_DATA; if (!Directory.Exists(pathToDir)) { Directory.CreateDirectory(pathToDir); } if (!File.Exists(pathToDir + fileName)) { return(ParameterAndUserDataMgn.Parameters.DELETED); } return(readValueFromTextFileForGivenKey(pathToDir, fileName, key)); } else { string cellValue = SAFINCA.ParameterAndUserDataMgn.Parameters.NULL_CHAR; Excel.Application xlApp = new Excel.Application(); try { cellValue = getCell(pathToDir, fileName, key, value, xlApp).Value.ToString(); xlApp.Workbooks.Close(); xlApp.Quit(); ProcessMgn.killProcessByNames(SAFINCA.ParameterAndUserDataMgn.Parameters.EXCEL_PROCESS_NAME); } catch (Exception e) { xlApp.Workbooks.Close(); xlApp.Application.Quit(); ProcessMgn.killProcessByNames(SAFINCA.ParameterAndUserDataMgn.Parameters.EXCEL_PROCESS_NAME); } return(cellValue); } }