Forum Discussion

PythonAndTC's avatar
PythonAndTC
Occasional Contributor
6 years ago

Third Party Python Modules For Excel Read

We've been trying to implement use of third party python modules in TestComplete but we're running into everyone's issues in compatibility with TC and external Python modules.  At this point, we are running into issue that on secondary run that "import failed with 'NoneType' object is not callable." (https://community.smartbear.com/t5/TestComplete-General-Discussions/Python-fail-to-import-lib-during-the-second-attempt-to-run/td-p/153683).

 

We've been trying to import module xlwings that requires a module name win32api .  It seems that TestComplete has issues running this module.  We've follow all the install instructions on the forums but we can't seem to get the excel module to work.  Has anyone been able to intergrate win32api with their TestComplete code successfully?

 

Edit: We are performing more dynamic tests than bellow including using the COM server for find, createworkbook, etc.  Sample code was in Python but Python code took 15 minutes compare to VB which took 1 minute to read a 1375 line workbook)

  • TestComplete cannot work correctly with the win32com Python library because this library cannot work after the reinitialization of the Python interpreter. You can find the same information on the official SourceForge page (https://sourceforge.net/p/pywin32/bugs/445/): "pywin32 will never be able to play with the multiple interpreter API, nor work correctly with multiple interpreter initializations and cleanups". And, that's why, it could work successfully only one time, and, after this, you needed to relaunch the tool.

  • tristaanogre's avatar
    tristaanogre
    Esteemed Contributor

    I know there's a lot of attraction for using 3rd party Python modules because, well, that's what you do in Python in general...

     

    However, TestComplete has built in capability for working with Excel (DDT.ExcelDriver) for data driven loops and it also includes the ability to instantiate an Excel Application object (Sys.OleObject('Excel.Application')).  So, with these, the need to incorporate 3rd party modules is not as necessary.

     

    Likewise the win32api... there already is a built in Win32API object available in TestComplete no matter your licenses so there is no need for it.

    So...  let's back track... you're trying to incorporate a 3rd party module... what do you actually NEED to do with Excel in TestComplete?  Because there may be a way of doing it without needing to bring in that module.

    • PythonAndTC's avatar
      PythonAndTC
      Occasional Contributor

      We are not doing straight reads of excel like what a DDT expects and we perform a lot of excel COM application functions such as find, createworkbook, etc. that DDT simply can't provide.

       

      We are moving from VBScript to Python so we try translating Python to use the same COM application capability but when tested, VB Script took about a minute to run what took Python 15 minutes (Excel sheet contained 1375 rows, 3 columns of data).  See example code -

       

      from timeit import default_timer as timer
      
      def Excel():
      	# Connect to the excel application
      	excelApp = Sys.OleObject["Excel.Application"]
      
      	# Make excel invisible and don't update the GUI (makes excel run faster)
      	excelApp.Visible = False
      	excelApp.ScreenUpdating = False
      	Log.Message(Project.Path)
      	excelApp.Workbooks.Open(Project.Path + "\\example.xlsx")
      
      	# The first worksheet in the example workbook
      	worksheet = excelApp.Workbooks.Item["example.xlsx"].Worksheets.Item["Sheet1"]
      
      	# Set up a list to store the test data
      	test = []
      
      	# Set the row and column count to 1, 1
      	(row, column) = (1, 1)
      	
      	# Start the timer immediately before loops
      	start = timer()
      	
      	# Read in the data
      	while worksheet.Cells.item[row, 1].Value2 is not None:
      		# List to store this row of the test
      		testRow = []
      
      		# Append a folder to the log for the row
      		# COMMENTED OUT FOR PERFORMANCE
      		# Log.AppendFolder(worksheet.Cells.item[row, column].Value2)
      
      		# Read in a row of data
      		while worksheet.Cells.item[row, column].Value2 is not None:
      			# COMMENTED OUT FOR PERFORMANCE
      			# Log.Message("Column " + str(column) + ": " + str(worksheet.Cells.item[row, column].Value2))
      			# COMMENTED Out to test reading things in without string casting them
      			# testRow.append(str(worksheet.Cells.item[row, column].Value2))
      			testRow.append(worksheet.Cells.item[row, column].Value2)
      
      			# Next column
      			column += 1
      			pass
      
      		# Add the row to the test
      		test.append(testRow)
      
      		# Bring the folder out a level from the row folder
      		# COMMENTED OUT FOR PERFORMANCE
      		# Log.PopLogFolder()
      
      		# Reset the column and increment the row to start the next level
      		column = 1
      		row += 1
      		pass
      	
      	# End the Timer immediately after loops
      	end = timer()
      	Log.Message("Done Loading Excel in " + str(end - start) + " seconds", str(test))
      	
      	# Save and close excel
      	excelApp.Workbooks.item["example.xlsx"].Close(True)
      
      	# Close excel
      	excelApp.Quit()
      
      	# Disconnect from excel
      	excelApp = None

       

      So due to the drastic performance decrease, we seeked out third party modules to solve this

      • PythonAndTC's avatar
        PythonAndTC
        Occasional Contributor

        Compare to run of VBScript, bellow script was much slower

         

        from timeit import default_timer as timer
        
        def Excel():
        	# Connect to the excel application
        	excelApp = Sys.OleObject["Excel.Application"]
        
        	# Make excel invisible and don't update the GUI (makes excel run faster)
        	excelApp.Visible = False
        	excelApp.ScreenUpdating = False
        	Log.Message(Project.Path)
        	excelApp.Workbooks.Open(Project.Path + "\\example.xlsx")
        
        	# The first worksheet in the example workbook
        	worksheet = excelApp.Workbooks.Item["example.xlsx"].Worksheets.Item["Sheet1"]
        
        	# Set up a list to store the test data
        	test = []
        
        	# Set the row and column count to 1, 1
        	(row, column) = (1, 1)
        	
        	# Start the timer immediately before loops
        	start = timer()
        	
        	# Read in the data
        	while worksheet.Cells.item[row, 1].Value2 is not None:
        		# List to store this row of the test
        		testRow = []
        
        		# Append a folder to the log for the row
        		Log.AppendFolder(worksheet.Cells.item[row, column].Value2)
        
        		# Read in a row of data
        		while worksheet.Cells.item[row, column].Value2 is not None:
        			Log.Message("Column " + str(column) + ": " + str(worksheet.Cells.item[row, column].Value2))
        			testRow.append(str(worksheet.Cells.item[row, column].Value2))
        			testRow.append(worksheet.Cells.item[row, column].Value2)
        
        			# Next column
        			column += 1
        			pass
        
        		# Add the row to the test
        		test.append(testRow)
        
        		# Bring the folder out a level from the row folder
        		Log.PopLogFolder()
        
        		# Reset the column and increment the row to start the next level
        		column = 1
        		row += 1
        		pass
        	
        	# End the Timer immediately after loops
        	end = timer()
        	Log.Message("Done Loading Excel in " + str(end - start) + " seconds", str(test))
        	
        	# Save and close excel
        	excelApp.Workbooks.item["example.xlsx"].Close(True)
        
        	# Close excel
        	excelApp.Quit()
        
        	# Disconnect from excel
        	excelApp = None
        

         

         

         

  • PythonAndTC's avatar
    PythonAndTC
    Occasional Contributor

    TestComplete cannot work correctly with the win32com Python library because this library cannot work after the reinitialization of the Python interpreter. You can find the same information on the official SourceForge page (https://sourceforge.net/p/pywin32/bugs/445/): "pywin32 will never be able to play with the multiple interpreter API, nor work correctly with multiple interpreter initializations and cleanups". And, that's why, it could work successfully only one time, and, after this, you needed to relaunch the tool.