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