cancel
Showing results for 
Search instead for 
Did you mean: 

Third Party Python Modules For Excel Read

SOLVED
Highlighted
Occasional Contributor

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

 

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)

1 ACCEPTED SOLUTION

Accepted Solutions
Occasional Contributor

Re: Third Party Python Modules For Excel Read

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.

View solution in original post

6 REPLIES 6
Community Hero

Re: Third Party Python Modules For Excel Read

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.


Robert Martin
[Community Expert Group]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Extensions available
Occasional Contributor

Re: Third Party Python Modules For Excel Read

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

Occasional Contributor

Re: Third Party Python Modules For Excel Read

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

 

 

 

Community Hero

Re: Third Party Python Modules For Excel Read

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

 

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


Robert Martin
[Community Expert Group]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Extensions available
Community Hero

Re: Third Party Python Modules For Excel Read

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

  


Robert Martin
[Community Expert Group]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Extensions available
Occasional Contributor

Re: Third Party Python Modules For Excel Read

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.

View solution in original post

New Here?
Join us and watch the welcome video:
Top Kudoed Authors