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