Read from multiple Excel sources in one test case : Automation Tools

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

[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-from-csv-file-t2395.html

I hope this posts will help you.

Regards,
Martin
Ranorex Support Team

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

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

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

[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


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


[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?

[Ciege]

Did you google the error code?
http://support.microsoft.com/kb/320369

[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!

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