Page object Model- Object repo creation script in Python using MS Excel

In UI Test automation, its important that we create an object repository. Object repository can be created in many ways and one of the ways is that each element is represented as a class property. Telerik Test automation framework is considered in this example. Telerik is a useful UI test automation tool compared to other commercial and open-source solutions 

  
Public class object_repo  
   
  {           
       public Element UserName  
         {  
           get  
             {  
               return _manager.ActiveBrowser.Find.ById("user9874");  
             }  
         }       
  }  

where this can be accessed via a class object in your code. Creating above class structure for many elements manually can be a time taking boring job. A simple script can do the job in a second. We add the required information to an excel file ex: Element name, attribute id, attribute value, and find By criteria, and the script will read each row and convert them in to a property in the class

The script was written in Python 3.4. I used PyCharm community version as the IDE.

You import following modules

 import os  
 import sys  
 import xlrd  
 import string  

Python module "xlrd" is dealing with Excel.
If it complains xlrd is not available, you can do a quick installation via easy_install.py which is located in 
%python_installed_dir%/Lib/site_packges
>>easy_install xlrd  will install the module.

• workbook = xlrd.open_workbook(read_excel) will open the excel to access
• sh = workbook.sheet_by_name(sheet) specifies which work sheet in the excel to be used
• sh.nrows specifies the number of rows that data is available in the sheet
• sh.ncols specifies the number of columns that data is available in the sheet

       for row in range(1, sh.nrows):
            for column in range(sh.ncols):
                item = sh.cell_value(row, column)
                list.append(item)

above piece of code will read cell of each row column by column until condition satisfies and append data to a python list. And list is read by its index and used where required.

The script is as following:

 #importing required modules  
 import os  
 import sys  
 import xlrd  
 import string  
 #variables declaration  
 read_excel = os.curdir + "\\" + "element_file.xlsx"  
 write_file = os.curdir + "\\" + "element_output.txt"  
 class_name = 'Public class element_Class \n { \n'  
 class_constructor = '''  
         private Manager _manager;  
   
         public constructor(Manager m)  
           {  
             _manager = m;  
           }  
       '''  
 end_bracket = '\n }'  
 #main function which reads the excel and write to the text file  
 def read_from_excel():  
   file_cleanup()  
   sheet = input("\nIndicate the sheet name of the excel: Press [Enter] if the default is 'Sheet1' : ")  
   if sheet is '':  
     sheet = "Sheet1"  
   workbook = open_excel()  
   try:  
     sh = workbook.sheet_by_name(sheet)  
     list = []  
     write_to_file(class_name)  
     write_to_file(class_constructor)  
     for row in range(1, sh.nrows):  
       for column in range(sh.ncols):  
         item = sh.cell_value(row, column)  
         list.append(item)        
       msg = '''  
       public Element ''' + list[0] + '''  
         {  
           get  
             {  
               return _manager.ActiveBrowser.Find.By''' + string.capwords(list[1]) + '''("''' + list[2] + '''");  
             }  
         }  
       '''  
       list = []  
       write_to_file(msg)  
     write_to_file(end_bracket)  
     input("\nOperation successful. Press [Enter] to Exit...")  
   except Exception as e:  
     print(str(e)+' available. Please retry. (Hint: check case and spelling...)')  
     read_from_excel()  
   
 #function for opening the excel file  
 def open_excel():  
   try:  
     workbook = xlrd.open_workbook(read_excel)  
     return workbook  
   except FileNotFoundError as e:  
     print(str(e))  
     sys.exit()  
   
 # function for writing the result to the text file  
 def write_to_file(msg):  
   try:  
     file = open(write_file, "a+")  
     file.write(msg)  
     file.close()  
   except Exception as e:  
     print(str(e))  
   
 #function for delete the existing file for next round  
 def file_cleanup():  
   if os.path.exists(write_file):  
     os.remove(write_file)  
   
 #calling the function to get the job done  
 read_from_excel()