Read from multiple Excel sources in one test case

Ranorex Spy, Recorder, and Studio.
User avatar
Nicklas
Posts: 20
Joined: Mon Sep 26, 2011 2:53 pm
Location: Sweden

Read from multiple Excel sources in one test case

Post by Nicklas » Wed Dec 21, 2011 3:01 pm

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.

User avatar
Support Team
Site Admin
Site Admin
Posts: 11709
Joined: Fri Jul 07, 2006 4:30 pm
Location: Graz, Austria

Re: Read from multiple Excel sources in one test case

Post by Support Team » Wed Dec 21, 2011 3:17 pm

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
.
Image

User avatar
Nicklas
Posts: 20
Joined: Mon Sep 26, 2011 2:53 pm
Location: Sweden

Re: Read from multiple Excel sources in one test case

Post by Nicklas » Wed Dec 21, 2011 3:46 pm

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.

User avatar
Ciege
Ranorex Guru
Posts: 1335
Joined: Thu Oct 16, 2008 6:46 pm
Location: Arizona, USA

Re: Read from multiple Excel sources in one test case

Post by Ciege » Wed Dec 21, 2011 4:04 pm

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.
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...

User avatar
Nicklas
Posts: 20
Joined: Mon Sep 26, 2011 2:53 pm
Location: Sweden

Re: Read from multiple Excel sources in one test case

Post by Nicklas » Wed Dec 21, 2011 4:17 pm

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 :) ).

User avatar
Ciege
Ranorex Guru
Posts: 1335
Joined: Thu Oct 16, 2008 6:46 pm
Location: Arizona, USA

Re: Read from multiple Excel sources in one test case

Post by Ciege » Wed Dec 21, 2011 4:31 pm

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
Open Workbook Method:

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
Read a Single Cell Method

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...

User avatar
Nicklas
Posts: 20
Joined: Mon Sep 26, 2011 2:53 pm
Location: Sweden

Re: Read from multiple Excel sources in one test case

Post by Nicklas » Wed Dec 28, 2011 3:52 pm

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?

User avatar
Ciege
Ranorex Guru
Posts: 1335
Joined: Thu Oct 16, 2008 6:46 pm
Location: Arizona, USA

Re: Read from multiple Excel sources in one test case

Post by Ciege » Wed Dec 28, 2011 3:55 pm

Did you google the error code?
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...

User avatar
Nicklas
Posts: 20
Joined: Mon Sep 26, 2011 2:53 pm
Location: Sweden

Re: Read from multiple Excel sources in one test case

Post by Nicklas » Thu Jan 05, 2012 10:52 am

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!

User avatar
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

Post by artur_gadomski » Wed Feb 29, 2012 9:39 am

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.