Page 1 of 1
Read from multiple Excel sources in one test case
Posted: Wed Dec 21, 2011 3:01 pm
by Nicklas
Hi,
I want to do some automated tests on our registration. First I want to tryout some invalid email addresses then I move on and tryout some invalid usernames and last some invalid passwords. As data source I want to use an Excel document. The document has one sheet for invalid emails, one for invalid usernames and another for passwords. Since you can't use multiple data sources in one test case (correct?) I was wondering if I can use code to open and read the document.
I've been browsing the forum but can't seem to get a hang of it.
Re: Read from multiple Excel sources in one test case
Posted: Wed Dec 21, 2011 3:17 pm
by Support Team
Hi,
I found a similar post on working with data sources on your own - I know it is about CSV files but it should help you to get an idea how to do that with excel. I also remember that there were some postings working with Excel too.
http://www.ranorex.com/forum/test-data- ... t2395.html
I hope this posts will help you.
Regards,
Martin
Ranorex Support Team
Re: Read from multiple Excel sources in one test case
Posted: Wed Dec 21, 2011 3:46 pm
by Nicklas
Thank you for pointing me in the right direction, it helped alot! Though I still can't get it to work. Now I get the following error message: The type or namespace name 'ExcelDataConnector' could not be found (are you missing a using directive or an assembly reference?).
What assembly do I need to get it working? I havn't removed any references from the default solution.
Re: Read from multiple Excel sources in one test case
Posted: Wed Dec 21, 2011 4:04 pm
by Ciege
Using the Excel Interop you can open however many Excel spreadsheets you want and interact with which ever sheet you want from within those spreadsheets.
This does, however, require a little bit of user code to accomplish.
Re: Read from multiple Excel sources in one test case
Posted: Wed Dec 21, 2011 4:17 pm
by Nicklas
Thanks for replying Ciege, but I have also tried Microsoft.Office.Interop.Excel but couldn't get it working though. Could you please share some code examples?
Still would like to know how to get it working with ExcelDataConnector as well, it seems like a simple and clean solution (once you get it working that is
).
Re: Read from multiple Excel sources in one test case
Posted: Wed Dec 21, 2011 4:31 pm
by Ciege
Could you please share some code examples?
Sure... I'll put a coupld of example methods here. There are more, but this should get you started...
Open Excel Method:
Code: Select all
public static Microsoft.Office.Interop.Excel.Application OpenExcel()
{
/************************************************************************
* Function : OpenExcel()
*
* Description : This method will open and return an Excel COM object.
*
* Parameters : N/A
*
* Return Type : Microsoft.Office.Interop.Excel.Application
*
* Return Value : Valid Excel Object for success, null for failure
*
* Revision History
* Date : Author : Reason/Change
* ---------- : ------------------------- : ------------------------------
* 03/23/2009 : Chris Gendreau : Initial Creation
************************************************************************/
Microsoft.Office.Interop.Excel.Application objXL;
try
{
//Create the Excel Object
objXL = new Microsoft.Office.Interop.Excel.Application();
//Return the Excel Object
return objXL;
}
catch (Exception ex)
{
Report.Failure("Unable to open Excel: " + ex.ToString());
return null;
}
} //End OpenExcel
Code: Select all
public static Microsoft.Office.Interop.Excel.Workbook OpenExcelWorkbook(Microsoft.Office.Interop.Excel.Application objXL, string strExcelFileName)
{
/************************************************************************
* Function : OpenExcelWorkbook()
*
* Description : This method will open and return an Excel Workbook COM object.
*
* Parameters : objXL - An existing Excel Object
* : strExcelFileName - path & filename of the Excel Workbook
*
* Return Type : Microsoft.Office.Interop.Excel.Workbook
*
* Return Value : Valid Excel Workbook Object for success, null for failure
*
* Revision History
* Date : Author : Reason/Change
* ---------- : ------------------------- : ------------------------------
* 07/10/2009 : Chris Gendreau : Initial Creation
* 11/30/2009 : Chris Gendreau : Added objXLWorkBook.Saved = true
************************************************************************/
Microsoft.Office.Interop.Excel.Workbook objXLWorkBook;
if (objXL != null)
{
try
{
objXLWorkBook = objXL.Workbooks.Open(strExcelFileName, 0, true, System.Reflection.Missing.Value, System.Reflection.Missing.Value,
System.Reflection.Missing.Value, true, System.Reflection.Missing.Value, System.Reflection.Missing.Value, true, false,
System.Reflection.Missing.Value, false, false, false);
//Mark the workbook as saved so that the "Do you want to save" dialog does not appear.
objXLWorkBook.Saved = true;
//Return the Excel Workbook Object
return objXLWorkBook;
}
catch (Exception ex)
{
Report.Failure("Unable to open Excel: " + ex.ToString());
return null;
}
}
return null;
} //End OpenExcelWorkbook
Code: Select all
public static string ExcelReadSingleCell(string strExcelFileName, string strCell, string strSheet)
{
/************************************************************************
* Function : ExcelReadSingleCell(string strExcelFileName, string strCell, string strSheet)
*
* Description : This method will read and return the data of a single cell
* : in the specified Excel workbook.
*
* Parameters : strExcelFileName - Full path and filename of the Excel spreadsheet to open
* : strCell - Cell to return (i.e. "A1")
* : strSheet - OPTIONAL - Name of the sheet in the spreadsheet the cell is on
* : - If not supplied will default to the 1st sheet.
*
* Return Type : String
*
* Return Value : Data from the cell for success, null for failure
*
* Revision History
* Date : Author : Reason/Change
* ---------- : ------------------------- : ------------------------------
* 03/23/2009 : Chris Gendreau : Initial Creation
************************************************************************/
Microsoft.Office.Interop.Excel.Application objXL;
Microsoft.Office.Interop.Excel.Workbook objXLWorkBook;
Microsoft.Office.Interop.Excel.Sheets objXLSheets;
Microsoft.Office.Interop.Excel.Worksheet objXLWorkSheet;
Microsoft.Office.Interop.Excel.Range objXLRange;
if (strSheet == "")
{
Report.Debug("Reading data from Cell: '" + strCell + "' in spreadsheet: '" + strExcelFileName + "' on sheet: '1'.");
}
else
{
Report.Debug("Reading data from Cell: '" + strCell + "' in spreadsheet: '" + strExcelFileName + "' on sheet: '" + strSheet + "'.");
}
//Open Excel
objXL = OpenExcel();
if (objXL != null)
{
try
{
//Open the Excel Workbook
objXLWorkBook = objXL.Workbooks.Open(strExcelFileName, 0, true, System.Reflection.Missing.Value, System.Reflection.Missing.Value,
System.Reflection.Missing.Value, true, System.Reflection.Missing.Value, System.Reflection.Missing.Value, true, false,
System.Reflection.Missing.Value, false, false, false);
//Get the sheets from the workbook
objXLSheets = objXLWorkBook.Sheets;
if (strSheet == "")
{
//Select the first worksheet
objXLWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)objXLSheets[1];
}
else
{
//Select the requested worksheet
objXLWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)objXLSheets[strSheet];
}
//Select the cell in the workbook
objXLRange = (Microsoft.Office.Interop.Excel.Range)objXLWorkSheet.get_Range(strCell + ":" + strCell, Type.Missing);
//Read the data from the cell
string ExcelString = objXLRange.Cells.Value2.ToString();
////Print the value of the cell for a single cell selection
//Console.WriteLine(" Text in cell " + strCell + " is: " + ExcelString);
//Close Excel
objXL.Quit();
//Return the data
return ExcelString;
}
catch (Exception ex)
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat(errorMessage, ex.Message);
errorMessage = String.Concat(errorMessage, " Line: ");
errorMessage = String.Concat(errorMessage, ex.Source);
errorMessage = String.Concat(errorMessage, ex.ToString());
Report.Failure("Error reading a cell from Excel: " + errorMessage);
//Close Excel
objXL.Quit();
return null;
}
}
return null;
} //End ExcelReadSingleCell
Re: Read from multiple Excel sources in one test case
Posted: Wed Dec 28, 2011 3:52 pm
by Nicklas
Thanks for the exampels. How ever I keep getting an error when opening up my Excel doc:
System.Runtime.InteropServices.COMException (0x80028018):
Old format or invalid type library. (Exception from HRESULT:0x80028018)
Any ideas?
Re: Read from multiple Excel sources in one test case
Posted: Wed Dec 28, 2011 3:55 pm
by Ciege
Re: Read from multiple Excel sources in one test case
Posted: Thu Jan 05, 2012 10:52 am
by Nicklas
Ehm, no I did not, sorry. I thought it was a Ranorex error code.
I'll download the Office Multilingual User Interface Pack and hope it helpes. Thanks for pointing me i the right direction!
Re: Read from multiple Excel sources in one test case
Posted: Wed Feb 29, 2012 9:39 am
by artur_gadomski
Unless you really need workbook or some advanced Excel features I would recommend comma separated files and Ranorex CSVConnector. csv files open easilly in Excell and notepad/text editors, are easy to change and easy to read.