Python open Excel using interop, create a sheet, **_rename the sheet_**

Developers
, ,
1

Creating a python node to replace our excel node as it bombed out due to gremlins(recent restrictions or changes on the network??? Reinstall didn’t work) So I am recoding excel.read and excel.write in a python node.

The read was a breeze. The write back has issues; Need to open an (existing) file and add a sheet, then rename the sheet- but cannot rename the sheet. Last line bombs out with error:

Warning: IronPythonEvaluator.EvaluateIronPythonScript operation failed.
Traceback (most recent call last):
File “”, line 43, in
AttributeError: ‘DispCallable’ object has no attribute ‘Name’

Not sure how/if an UNWRAP could be done on an excel node to expose the classes directly.

###Import Excel - intended to replace malfunctioning excel node
### Ron.Allen@AECOM.com

import clr
clr.AddReferenceByName('Microsoft.Office.Interop.Excel, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c')
##AddReferenceGUID("{00020813-0000-0000-C000-000000000046}") ''Excel                            C:\Program Files\Microsoft Office\Office15\EXCEL.EXE 
##Need to Verify interop for version 2015 is 15 and node attachemnt for it.
from Microsoft.Office.Interop import  * ##Excel
################################Initialize FP and Sheet ID
##Same functionality as the excel node
strFileName = IN[0]				##Filename
sheetName = IN[1]				##Sheet
RowOffset=0 #IN[2]				##RowOffset
ColOffset=0 #IN[3]				##COL OFfset
Data=IN[4]						##Data
Overwrite=IN[5]					##Check for auto-overwtite
XLVisible = False 	#IN[6]		##XL Visible for operation or not?

RowOffset=0
if IN[2]>0:
	RowOffset=IN[2]				##RowOffset

ColOffset=0
if IN[3]>0:
	ColOffset=IN[3]				##COL OFfset

if IN[6]<>False:
	XLVisible = True #IN[6]		##XL Visible for operation or not?

################################Initialize FP and Sheet ID
xlCellTypeLastCell = 11 				#####define special sells value constant
################################
xls = Excel.ApplicationClass()			####Connect with application
xls.Visible = XLVisible					##VISIBLE YES/NO
xls.DisplayAlerts = False				### ALerts
wb = xls.Workbooks.Open(strFileName, False) 	####Open the file 
wb.application.visible = XLVisible		####Show Excel
try:
	ws = wb.Worksheets(sheetName)  		####Get the sheet in the WB base
	
except:
	ws = wb.sheets.add ()					####If it doesn't exist- add it. ** "()" Required to instantiate method for object!
	ws.Name = sheetName					####<<<<<<<BOMBS OUT HERE "AttributeError: 'DispCallable' object has no attribute 'Name'"
2

@Ron_Allen

Maybe this will help. Just notice that you dont have “Work” on Worksheets.

except: ws = wb.Worksheets.add
ws.Name = sheetName

Maybe…thay would help…

3

Tried both. Doesn’t error on the worksheets.add.

Errors on the WS.name=

Will double check to see if WS is null. (How do we check if an object is null in python? param !> “” ?)

4

param == None

5

@Nick_Boyts Well I feel sheepish : )

So if I set ws=wb.worksheet.new it should != None if it worked. Imagine it would throw an error if it didn’t create the sheet.

6

Missing Parenthesis ti instantiuate the add and return an OBJECT (Worksheet) to rename. Thanks @Mitko Venkov for spotting this.

	ws = wb.sheets.add ()					####If it doesn't exist- add it. ** "()" Required to instantiate method for object!
1 Like