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