public static void SetTable(string TestDataFile)
{
object misValue = System.Reflection.Missing.Value;
TestCase testCase;
Microsoft.Office.Interop.Excel.Workbook workbook;
List<string> header = new List<string>();
List<TestRowData> TestRowList=new List<TestRowData>();
//Initialize test steps for testcase
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
workbook = excelApp.Workbooks.Open(TestDataFile);
Microsoft.Office.Interop.Excel.Worksheet worksheet;
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.get_Item(1);
//return the name of the testcase from the TestCaseFile name
char[] inputArray = TestDataFile.ToCharArray();
Array.Reverse(inputArray);
string reversedName = new string(inputArray);
//now search for the /
int startPos = reversedName.IndexOf("\\");
int endPos = reversedName.IndexOf(".") + 1;
string TestCaseName = TestDataFile.Substring(TestDataFile.Length - startPos, startPos - endPos);
int lastRowUsed = worksheet.Cells.Find("*", System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows, Microsoft.Office.Interop.Excel.XlSearchDirection.xlPrevious, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value).Row;
int ctr;
for (ctr = 0; ctr < lastRowUsed; ctr++)
{
if (((string)worksheet.Cells[ctr + 1, 2].Value) == null)
{
break;
}
}
lastRowUsed = ctr;
testCase = new TestCase("DataSheet");
string columnHeader = "";
//check if just the first column is not blank
if (((string)worksheet.Cells[1, 1].Value) != null)
{
int lastColumnUsed = worksheet.Cells.Find("*", System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows, Microsoft.Office.Interop.Excel.XlSearchDirection.xlPrevious, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value).Column;
lastRowUsed = worksheet.Cells.Find("*", System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows, Microsoft.Office.Interop.Excel.XlSearchDirection.xlPrevious, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value).Row;
testCase.TestRowList = new List<TestRowData>();
//get the data header
for (int colheader = 1; colheader <= lastColumnUsed; colheader++)
{
header.Add((string)worksheet.Cells[1, colheader].Value);
}
for (int rowctr = 1; rowctr < lastRowUsed; rowctr++)
{
TestRowData testRowData = new TestRowData(); string ColumnHeader, ColumnValue;
for (int colctr = 0; colctr < lastColumnUsed; colctr++)
{
ColumnHeader = header.ElementAt<string>(colctr);
ColumnValue = (string)worksheet.Cells[rowctr + 1, colctr + 1].Value;
testRowData.DataValue.Add(ColumnHeader, ColumnValue);
columnHeader = columnHeader + ","+ColumnHeader;
}
testCase.TestRowList.Add(testRowData);
}
columnHeader = columnHeader.Substring(1, columnHeader.Length - 1);
}
testCase.HeaderInfo = header;
testCase.LastRowUsed = lastRowUsed - 1;
GlobalConfig.GlobalTCVar = testCase;
CustomTestController.ControllerUtility.GenericDictionary = null;
Dictionary<string, string> DictValues;
GenericListDictionary = new List<Dictionary<string, string>>();
for (int rowctr = 0; rowctr < lastRowUsed-1; rowctr++)
{
DictValues = new Dictionary<string, string>();
string Key, Value;
foreach (string Target in testCase.HeaderInfo)
{
if (Target.Contains('['))
Key = Target.Substring(1, Target.Length - 2);
else
Key = Target.Substring(0, Target.Length);
Value = testCase.TestRowList[rowctr].DataValue[Key];
DictValues.Add(Key, Value);
}
GenericListDictionary.Add(DictValues);
}
workbook.Close(true, misValue, misValue);
excelApp.Quit();
CustomTestController.ControllerUtility.ReleaseExcelObject(worksheet);
CustomTestController.ControllerUtility.ReleaseExcelObject(workbook);
CustomTestController.ControllerUtility.ReleaseExcelObject(excelApp);
//TS_CustomTestController.GlobalConfig.ObjectRepositoryXml
return;
}
public static Dictionary<string, string> ReplaceDictValues(TestCase TC, string PassValues, int CurrentRow)
{
Dictionary<string, string> DictValues = new Dictionary<string, string>();
string Key, Value;
foreach (string Target in PassValues.Split(','))
{
if (Target.Contains('['))
//Colctr = TC.HeaderInfo.IndexOf(Target.Substring(1,Target.Length-2));
Key = Target.Substring(1, Target.Length - 2);
else
//Colctr = TC.HeaderInfo.IndexOf(Target.Substring(0, Target.Length));
Key = Target.Substring(0, Target.Length);
//Key = Target.Substring(1, Target.Length - 2);
Value = TC.TestRowList[CurrentRow].DataValue[Key];
DictValues.Add(Key, Value);
}
return DictValues;
}