REvit.Excel.Python node for writing (new) excel files

###Export Excel - intended to replace malfunctioning excel node ### Ron.Allen@AECOM.com import clr import System 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= IN[2] ##RowOffset ColOffset= 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 if (System.IO.File.Exists(strFileName) == True): wb = xls.Workbooks.Open(strFileName, False) ####Open the file else: wb = xls.Workbooks.add(-4167)#excel macro ####Open the file wb.Saveas(strFileName,52) ##51=Workbook default ##52=open macro XLSM ##46=XML Spreadsheet ##-4143=Workbook normal 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. use () for object method ws.Name = sheetName ################################# #lastRow for iterating rows lastRow=ws.UsedRange.SpecialCells(xlCellTypeLastCell).Row #lastCol for iterating columns lastCol=ws.UsedRange.SpecialCells(xlCellTypeLastCell).Column ####################################################################### out=[] ###MESSAGE GATHERING c=0 r=0 val="" if Overwrite == False : ####Look ahead for non-empty cells to throw error for r, row in enumerate(Data): ####BASE 0## EACH ROW OF DATA ENUMERATED in the 2D array #range( RowOffset, lastRow + RowOffset): for c, col in enumerate (row): ####BASE 0## Each colmn in each row is a cell with data ### in range(ColOffset, lastCol + ColOffset): if col.Value2 >"" : OUT= "ERROR- Cannot overwrite" raise ValueError("ERROR- Cannot overwrite") ##out.append(Data[0]) ##append mesage for error ############################################################################ if isinstance(Data, list): for r, row in enumerate(Data): ####BASE 0## EACH ROW OF DATA ws.Cells[r+1+RowOffset,1+ColOffset].Value2 = row.__str__() else: for r, row in enumerate(Data): ####BASE 0## EACH ROW OF DATA ENUMERATED in the 2D array #range( RowOffset, lastRow + RowOffset): for c, col in enumerate (row): ####BASE 0## Each colmn in each row is a cell with data ### in range(ColOffset, lastCol + ColOffset): ws.Cells[r+1+RowOffset,c+1+ColOffset].Value2 = col.__str__() ##run macro disbled for debugging excel macro ##xls.Application.Run("Align_data_and_Highlight_Issues")