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 TestCase InitializeTestCase(string TestCaseFile)
{
object misValue = System.Reflection.Missing.Value;
TestCase testCase;
Microsoft.Office.Interop.Excel.Workbook workbook;
TestStep testStep;
List<string> header=new List<string>();
//Initialize test steps for testcase
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
workbook = excelApp.Workbooks.Open(TestCaseFile);
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 = TestCaseFile.ToCharArray();
Array.Reverse(inputArray);
string reversedName = new string(inputArray);
//now search for the /
int startPos = reversedName.IndexOf("\\");
int endPos = reversedName.IndexOf(".")+1;
string TestCaseName = TestCaseFile.Substring(TestCaseFile.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;
//setup the global variables - to do: search for correct config, from command line switch or global variable
testCase = new TestCase(TestCaseName);
testCase.TestSteps = new List<TestStep>();
for (int rowctr = 1; rowctr < lastRowUsed; rowctr++)
{
testStep = new TestStep();
testStep.StepNumber = (int)worksheet.Cells[rowctr + 1, 1].Value;
testStep.Action = (string)worksheet.Cells[rowctr + 1, 2].Value;
testStep.Target = (string)worksheet.Cells[rowctr + 1, 3].Value;
testStep.Description = (string)worksheet.Cells[rowctr + 1, 4].Value;
testStep.Value = (String.IsNullOrEmpty((string)worksheet.Cells[rowctr+1,5].Value))? 0 : (int)worksheet.Cells[rowctr + 1, 5].Value;
testStep.Value = 0;
testStep.FindLogic = (string)worksheet.Cells[rowctr + 1, 6].Value;
//get breakpoint value, if blank, set to false, if "yes" set to true
if (string.IsNullOrEmpty((string)worksheet.Cells[rowctr + 1, 7].Value))
testStep.BreakPoint = false;
else
testStep.BreakPoint = true;
testCase.TestSteps.Add(testStep);
}
//Initialize datasheet for this testcase
workbook = excelApp.Workbooks.Open(TestCaseFile);
//point to the second worksheet
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.get_Item(2);
//return the name of the testcase from the TestCaseFile name
inputArray = TestCaseFile.ToCharArray();
Array.Reverse(inputArray);
reversedName = new string(inputArray);
//now search for the /
startPos = reversedName.IndexOf("\\");
endPos = reversedName.IndexOf(".") + 1;
string Tempvar = "";
Tempvar = (string)worksheet.Cells[1, 1].Value;
//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;
if (lastRowUsed < 1)
{
//no data on 2nd worksheet
testCase.TestRowList = null;
}
else
{
//setup the test data dictionary
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);
}
testCase.TestRowList.Add(testRowData);
}
}
testCase.HeaderInfo = header;
testCase.LastRowUsed = lastRowUsed - 1;
}
else
{
testCase.TestRowList = null;
testCase.HeaderInfo = null;
testCase.LastRowUsed = 0;
}
workbook.Close(true, misValue, misValue);
excelApp.Quit();
CustomTestController.ControllerUtility.ReleaseExcelObject(worksheet);
CustomTestController.ControllerUtility.ReleaseExcelObject(workbook);
CustomTestController.ControllerUtility.ReleaseExcelObject(excelApp);
//TS_CustomTestController.GlobalConfig.ObjectRepositoryXml
return testCase;
}
