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