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