A Reliable mechanism to read data from Excel Sheets

The following code Loads the entire excel sheet into buffer and returns selected records into a List

[-] ANYTYPE ReadExcel(String SheetName,String keyColumn,String keyValue,String InputFilePath)

                    [ ]

                    [ ] LIST OF ANYTYPE TableBuffer

                    [ ] LIST OF ANYTYPE SelectedRecords

                    [ ] HSQL hSQL

                    [ ] HDATABASE hDB = NULL

                    [ ]

                    [ ] integer i

                    [ ]

                    [ ] String strName,strIgnore,strTable,strColumn,strDtype

                    [ ]

                    [ ]               

[ ] // Clearing the Buffer //

                    [-] for i=1 to listcount(TableBuffer)

                                        [ ] ListDelete(TableBuffer,1)

                                        [ ]

                    [ ] //Connect to the Excel Workbook using Excel DSN //

[ ]

                    [ ] hDB = DB_Connect ("DSN=Excel Files; DBQ={InputFilePath};READONLY=FALSE;UID=; PWD= ;")

                    [ ]

[ ]//Read the First Row (Table Header) to a List (SelectedRecords)

[ ]

                    [ ] hSQL = DB_Columns (hDB,null, null, "{SheetName}$", "%")

                    [ ]

                    [-] while(DB_FetchNext (hSQL, strIgnore, strIgnore, strTable, strColumn, strIgnore, strDtype)== TRUE)

                                        [ ] ListAppend(SelectedRecords,strColumn)

                    [ ]

                    [ ]//Copy the Table header to the TableBuffer//

                    [ ]

                    [ ] ListAppend(TableBuffer,SelectedRecords)

                    [ ]

                    [ ]//Copy the Entire content of the Table to the TableBuffer

                    [ ]

                    [ ] hSQL = DB_ExecuteSQL (hDB, "SELECT * FROM [{SheetName}$]")

                    [ ]

                    [-] while DB_FetchNext(hSQL,SelectedRecords)

                                        [ ] ListAppend(TableBuffer,SelectedRecords)

                    [ ]

                    [ ]

                    [ ] // Selects the Records from the entire sheet based on the KaeyValue and the KeyColumn

                    [ ]

                    [ ] LIST OF ANYTYPE Record

                    [ ] LIST OF ANYTYPE Temp

                    [ ]

                    [ ]//Copy the Table Header to the Temp

                    [ ] ListAppend(Temp,TableBuffer[1])

                    [ ]

                    [ ]// Find the column index (cursor) for the given column name

[ ] Integer Cursor=ListFind (TableBuffer[1],keyColumn)

[ ]

[ ]//Copy the Records from TableBuffer that have the given KeyValue at KeyColumn //

[ ]

                    [-] for each Record in TableBuffer

                                        [-] if(Record[Cursor]==keyValue)

                                                            [ ] ListAppend(Temp,Record)

[ ]

                    [ ] SelectedRecords=Temp

                    [ ]

                    [ ] DB_Disconnect(hDB)

                    [ ] return SelectedRecords

Pass the List returned by the above method as argument 'Table' to the below method, it'll read values from the selected records.

Note : The List returned by the above method has the Header Row included in it. So the actual record starts at '2' i.e., 'Table[1]' has the Header and the records starts at 'Table[2]'

[-] ANYTYPE ReadValue(LIST OF ANYTYPE Table,String ColumnName,Integer row optional)

                    [ ]

                    [ ] Integer column = ListFind (Table[1],ColumnName)

                    [ ]

                    [-] if(isNull(row))

                                        [ ] row=2

                    [ ]

                    [ ] return Table[row][column]

Thank you for viewing this Post, Will try to post the more optimized version of this component in my next post.

Silk Test [OPAT] Framework

[Defining a Framework for Web Applications - UI Automation Testing]

The OPAT framework

Components of the Framework
[ObjectRepository]
[ProjectFunctionLibrary]

[ApplicationFunctionLibrary]
[Test Script / Plan / Suite]



    [ObjectRepository] (*.inc)

        Object Repository is the collection of all UI elements required for testing.
        It can be defined as an Include File (*.inc).
        Capture the UI elements using the "Locator Spy".
        Edit the captured XPath if required to define a unique locator.

        Silk has predefined DOM library for most of the UI elements like DOMTextField, DOMButton,
        DOMRadioButton, DOMCheckBox, etc. The SuperCalss for all these classes is DoMElement.
        If the UI has some customised control, it can be captured as a "DomElement"
               
        Sample,
       
        DomTextField userName
            locator "//DomTextField[@id='username']"
        DomElement imageButton
            locator "//DomElement[@class='imageBtn']"
       

    [ProjectFunctionLibrary] (*.inc)

        ProjectFunctionLibrary is the collection of functions (methods) that are independent of the Web Application(s) to be tested using the Framework.

        This includes methods used for Database Connectivity, Data Reading modules, Report Writing, etc.

        This can also include any customized methods / definitions of silk library. Silk provides the advantage of inheriting and overriding the default library.

        For Instance,

        You can Inherit the DomTextField class and customize it with your requirements.
        You can also override the Default silk methods like ScritpEnter(), ScriptExit(), TestcaseEnter(), TestCaseExit(), etc. 
       
    [ApplicationFunctionLibrary] (*.inc)

        ApplicationFunctionLibrary is an exclusive collection of functions (methods) that performs the UI Actions on the Web Application to be tested.

        You can define UI actions that are repeated while testing a web application as methods.

        For Instance,

        Void LogIn()
               
            // Note : Browser, ApplicationWindow and UI elements like (userName, password and SignInButton) are declared in "ObjectRepository"
           
            Browser.ApplicationWindow.userName.SetText("admin")
            Browser.ApplicationWindow.password.SetText("adminpwd")
            Browser.ApplicationWindow.SignInButton.DomClick()       

    [Test Script / Plan / Suite]
   
        [TestScript] (*.t)
       
            TestScript is the collection of TestCases. TestCases can be defined by utilizing the ApplicationFunctionLibrary, ProjectFunctionLibrary and ObjectRepository if required.

            Sample,
           
            use "ApplicationFunctionLibrary.inc"
            use "ProjectFunctionLibrary.inc"
            use "ObjectRepository.inc"


            testcase TestLogin()
                    ReadTestData()     // this method belongs to ProjectFunctionLibrary
                    Login()     // belongs to ApplicationFunctionLibrary
                    verify(actual,expected,"Message on Fail")

            on Executing this Scripts, the results are recorded in a *.res file.

        [TestPlan] and [TestSuites]

            - will give a detailed explanation on TestCase management, Defining Function Library, Data Reading Mechanisms in my next Post.
            Thank you viewing this Post. Please write back your comments.