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.
Read from multiple Excel sources in one test case
- Support Team
- Site Admin
- Posts: 12167
- Joined: Fri Jul 07, 2006 4:30 pm
- Location: Graz, Austria
Re: Read from multiple Excel sources in one test case
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
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
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.
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
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.
This does, however, require a little bit of user code to accomplish.
If this or any response has helped you, please reply to the thread stating that it worked so other people with a similar issue will know how you fixed your issue!
Ciege...
Ciege...
Re: Read from multiple Excel sources in one test case
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
).
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
Sure... I'll put a coupld of example methods here. There are more, but this should get you started...Could you please share some code examples?
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
If this or any response has helped you, please reply to the thread stating that it worked so other people with a similar issue will know how you fixed your issue!
Ciege...
Ciege...
Re: Read from multiple Excel sources in one test case
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?
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
Did you google the error code?
http://support.microsoft.com/kb/320369
http://support.microsoft.com/kb/320369
If this or any response has helped you, please reply to the thread stating that it worked so other people with a similar issue will know how you fixed your issue!
Ciege...
Ciege...
Re: Read from multiple Excel sources in one test case
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!
I'll download the Office Multilingual User Interface Pack and hope it helpes. Thanks for pointing me i the right direction!
- artur_gadomski
- Posts: 207
- Joined: Mon Jul 19, 2010 6:55 am
- Location: Copenhagen, Denmark
- Contact:
Re: Read from multiple Excel sources in one test case
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.