public static Dictionary <string, List <string> > MyWebSSOInputData(String path, String excelfileName)
{
Dictionary <string, List <string> > excelDataDict = new Dictionary <string, List <string> >();
XLSExcelDataAccess xlsfile = new XLSExcelDataAccess(path, excelfileName);
xlsfile.DatasheetName = xlsfile.GetSheetName(0);
int totalrow = xlsfile.GetLastRowNum();
for (int i = 1; i <= totalrow; i++)
{
List <string> list = new List <string>();
list.Add(xlsfile.GetValue(i, 0).Trim());
list.Add(xlsfile.GetValue(i, 1).Trim());
list.Add(xlsfile.GetValue(i, 2).Trim());
string TenantURL = xlsfile.GetValue(i, 3).Trim();
list.Add(TenantURL);
list.Add(xlsfile.GetValue(i, 4).Trim());
list.Add(xlsfile.GetValue(i, 5).Trim());
list.Add(xlsfile.GetValue(i, 6).Trim());
if (!string.IsNullOrEmpty(TenantURL))
{
excelDataDict.Add("User" + i, list);
}
}
return(excelDataDict);
}
/// <summary>
/// Write data to Excel Sheet --> Main Method
/// </summary>
/// <param name="uniqueFieldName">Unique Field Name</param>
/// <param name="uniqueFieldValue">Unique Field Value: Probably the Test Case Name</param>
/// <param name="targetFieldName">Target Field Name: header name of the data</param>
/// <param name="targetFieldValue">Target Field Value: data which we want to write into excel</param>
public static void WriteToDataSheet(String path, String excelfile, String sheetName, string targetFieldName, string targetFieldValue)
{
string InputDataSheet = path + "\\" + excelfile;
string uniquefieldname = "false";
string targetfieldname = "false";
int uniquefieldcellnum = -1;
int targetfieldcellnum = -1;
IWorkbook workbook = GetWorkBook(path, excelfile);
ISheet sheet = workbook.GetSheet(sheetName);
for (int row = 0; row <= sheet.LastRowNum; row++)
{
if (row == 0)
{
IRow headerRow = sheet.GetRow(row);
foreach (ICell headerCell in headerRow)
{
if (headerCell.ToString() == uniqueFieldName)
{
uniquefieldname = "true";
uniquefieldcellnum = headerCell.ColumnIndex;
}
if (headerCell.ToString() == targetFieldName)
{
targetfieldname = "true";
targetfieldcellnum = headerCell.ColumnIndex;
}
if (uniquefieldname == "true" & targetfieldname == "true")
{
break;
}
}
if (uniquefieldname == "true" & targetfieldname == "false")
{
targetfieldcellnum = headerRow.LastCellNum;
String fileExtn = excelfile.Substring(excelfile.IndexOf("."));
if (fileExtn != null)
{
if (fileExtn.ToLower() == ".xls")
{
XLSExcelDataAccess xlsfile = new XLSExcelDataAccess(path, excelfile);
xlsfile.DatasheetName = sheetName;
xlsfile.AddColumn(targetFieldName);
}
else
{
throw new FrameworkException("Error ", "Not a valid excel format either .xls file");
}
}
}
}
if (sheet.GetRow(row).GetCell(uniquefieldcellnum).ToString() == BaseUtilities.scenarioName)
{
String fileExtn = excelfile.Substring(excelfile.IndexOf("."));
if (fileExtn != null)
{
if (fileExtn.ToLower() == ".xls")
{
XLSExcelDataAccess xlsfile = new XLSExcelDataAccess(path, excelfile);
xlsfile.DatasheetName = sheetName;
xlsfile.SetValue(row, targetfieldcellnum, targetFieldValue);
}
else
{
throw new FrameworkException("Error ", "Not a valid excel format either .xls");
}
}
break;
}
}
}
