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 ...
Data-Driven Testing
Scripting
  • PythonAndTC's avatar
    PythonAndTC
    6 years ago

    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
6 years ago

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
    6 years ago

    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

     

     

     

    • tristaanogre's avatar
      tristaanogre
      Esteemed Contributor
      6 years ago

      So...you're iterating through rows in an Excel sheet and populate information...

       

      Sounds like a perfect candidate of DDT.ExcelDriver.   Look into it

      • tristaanogre's avatar
        tristaanogre
        Esteemed Contributor
        6 years ago

        Here's the sample code from https://support.smartbear.com/testcomplete/docs/reference/program-objects/ddt/exceldriver.html.

         

        This iterates through the spreadsheet, row by row, and logs the contents of column 0... all you need to do is just include some code for going through the columns.  There should be, on DDT.CurentDriver a "ColumnCount" property that you could use as the index for a for loop.

         

        Sub CurDriverExample
  
  ' Creates a driver
  Call DDT.ExcelDriver("C:\MyFile.xls", "Sheet1")
   
  ' Iterates through records
  While Not DDT.CurrentDriver.EOF()

  ' Gets a value from the storage and posts it to the log
    Log.Message(DDT.CurrentDriver.Value(0))
    DDT.CurrentDriver.Next()
  WEnd
   
  ' Closes the driver
  DDT.CloseDriver(DDT.CurrentDriver.Name)
  
End Sub