public static string[] getTestList(string xlPth) { Excel.Application xlApp; Excel.Workbooks tmp; Excel.Workbook wrkBook; Excel.Worksheet wrkSheet; Process[] initXLProc; int numRows; string[] outArray; outArray = null; wrkBook = null; numRows = 0; //Get the Process Id of any Excel processes that are currently running initXLProc = Process.GetProcessesByName("EXCEL"); //Establish Excel objects xlApp = new Excel.Application(); tmp = xlApp.Workbooks; if (File.Exists(xlPth)) { wrkBook = tmp.Open(xlPth); wrkSheet = wrkBook.Sheets[1]; //Get the number of lines used in the data sheet for (int x = 2; x < 10000; x++) { if (wrkSheet.Cells[x, 1].Value == null) { numRows = x - 1; break; } } //Instantiate outarray outArray = new string[numRows - 1]; //populate outArray for (int x = 2; x <= numRows; x++) { outArray[x - 2] = wrkSheet.Cells[x, 1].Value; } //kill the Excel process just opened TestSuite.killXLProc(initXLProc); } else { MessageBox.Show("The Excel workbook at the path " + xlPth + " is not present", "Error", MessageBoxButtons.OK, MessageBoxIcon.Stop); } return(outArray); }
public static string[,] getXLData(string tstName, String xlPath, string shtName) { //This function will extract the data from the data sheets. First, we need get the list from the Master sheet by matching //the first column to the test. Excel.Application xlApp; Excel.Workbook wrkBook; Excel.Workbooks tmp; Excel.Worksheet wrkSheet; Process[] initXLProc; string[,] arrString; string[] tmpArray; int tstRows; //the sheet row containing the test being processed int numRows; //the number of used rows in the spreadsheet int numCols; //the number of used columns row (tstRow) //Get the Process Id of any Excel processes that are currently running initXLProc = Process.GetProcessesByName("EXCEL"); //Setting the Excel objects xlApp = new Excel.Application(); tmp = xlApp.Workbooks; wrkBook = tmp.Open(xlPath); wrkSheet = wrkBook.Sheets[shtName]; //Get the number of lines used in the data sheet numRows = wrkSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Row; //Set the numCols variable tstRows = 0; numCols = 0; //Check for test name in the Master sheet for (int x = 2; x <= numRows; x++) { if (wrkSheet.Cells[x, 1].Value == tstName) { //Set tstRow = the row of the spreadsheet that matches the test tstRows = x; //Call numCols function to get the number of steps in the test numCols = TestSuite.getCols(wrkSheet, "Master", x); //Exit the for loop once the the test has been found and number of steps identified break; } } //set tmpArray to list of steps in the Master sheet and go get the values tmpArray = new string[numCols - 2]; //set arrString to a two dimesional array that will contain the arrString = new string[numCols - 2, 3]; tmpArray = TestSuite.getArray(wrkSheet, tstRows, numCols); //Call array to split the test step and line number in the test sheet (Testname|X) arrString = TestSuite.splitArray(wrkBook, tmpArray); //kill the Excel process just opened TestSuite.killXLProc(initXLProc); //return the arrString value return(arrString); }