RevitPythonShell: Excel Files
Introduction
Like the section on reading and writing text files, we can also use Excel as a way to access data externally. Unlike the text file, however, this approach requires that an Excel workbook be open so the script can access the running Excel instance and move information in and out.
Reading
Excel files can be accessed using .NET InteropServices with System.Runtime.InteropServices.Marshal.GetActiveObject('Excel.Application'). An example for reading a file is as follows…
import clr import System clr.AddReference('RevitAPI') clr.AddReference('RevitAPIUI') from Autodesk.Revit.DB import * app = __revit__.Application doc = __revit__.ActiveUIDocument.Document t = Transaction(doc, 'Read Excel spreadsheet.') t.Start() #Accessing the Excel applications. xlApp = System.Runtime.InteropServices.Marshal.GetActiveObject('Excel.Application') #Worksheet, Row, and Column parameters worksheet = 1 rowStart = 1 rowEnd = 4 column = 1 #Using a loop to read a range of values and print them to the console. for i in range(rowStart, rowEnd): #Worksheet object specifying the cell location to read. data = xlApp.Worksheets(worksheet).Cells(i, column).Text print data t.Commit()
Writing
The technique for writing to Excel is very similar to reading. To place values we simply need to assign the Excel worksheet object a value. The following example will write values to cells 1 through 10 with an value that is incremented in a for loop.
import clr import System clr.AddReference('RevitAPI') clr.AddReference('RevitAPIUI') from Autodesk.Revit.DB import * app = __revit__.Application doc = __revit__.ActiveUIDocument.Document t = Transaction(doc, 'Write Excel.') t.Start() #Accessing the Excel applications. xlApp = System.Runtime.InteropServices.Marshal.GetActiveObject('Excel.Application') #Worksheet, Row, and Column parameters worksheet = 1 rowStart = 1 columnStart = 1 val = 2 for i in range(0, 10): #Worksheet object specifying the cell location. data = xlApp.Worksheets(worksheet).Cells(rowStart + i, columnStart) #Assigning a value to the cell. data.Value = val val = val * 2 t.Commit() __window__.Close()