Hi Forum,
i’m lookin for a way to read and write to excel in pyrevit (ironpython).
I know there is the csv functionality implemented, but not excel as far as i can find.
The only thing i know of is file selection with the .forms.
I found some info on using Cpython and openpyxl.
Problem whit this is that the pyrevit librarie isn’t working in Cpython for me.
Any expierences on this?
Hi @AlexanderVDB
have you tried the ‘builtin’ module pyRevit/site-packages/xlsxwriter at master · eirannejad/pyRevit · GitHub
thx @Jean-Marc, your always the man with a plan!
i’ll give it a shot, it seems exaclty what i was looking for.
Also look at IronPython and interop.
Doesn’t work with CPython but you can dive into Excel a little farther than with other approaches.
For instance - my reports come out filly formatted with fun colors, proper column spacing and fonts. runs a little slower, but worth the few seconds.
excelTypeLibGuid = System.Guid("00020813-0000-0000-C000-000000000046")
clr.AddReferenceToTypeLibrary(excelTypeLibGuid)
from Excel import Application
from Excel import *
excel = Application()
clr.AddReferenceByName('Microsoft.Office.Interop.Excel, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c' )
from Microsoft.Office.Interop import Excel
from Microsoft.Office.Interop.Excel import ApplicationClass
from System.Runtime.InteropServices import Marshal
Hello everyone,
In the good spirit of sharing knowledge 
, here are the Cypthon and Ironpython version of a simple script I made to test the excel reading application.
I think they are easiest to read/understand and directly use the excel api so there are plenty of possibilities to extend. That’s the idea in my head right now 
… will find out if it is true 
!
One thing I did find with the Cpython version is that excel opens slower and this seems to cause a crash in the script because it cannot read out immediately. Any ideas on how to prevent this?
Cpython:
#! python3
# -*- coding: utf-8 -*-
import clr
import System
clr.AddReference('System.Windows.Forms')
from System.Windows.Forms import OpenFileDialog
clr.AddReference('Microsoft.Office.Interop.Excel')
from Microsoft.Office.Interop import Excel
def open_excel_file():
# Create Excel application object
excel_app = Excel.ApplicationClass()
excel_app.Visible = True
# Create an open file dialog
dialog = OpenFileDialog()
dialog.Filter = 'Excel Files|*.xlsx'
# Show the dialog and get the selected file
result = dialog.ShowDialog()
if result == System.Windows.Forms.DialogResult.OK:
filename = dialog.FileName
# Open the selected Excel workbook
workbook = excel_app.Workbooks.Open(filename)
# For example, print the value of cell A1 in the first worksheet:
worksheet = workbook.Worksheets[1]
cell = worksheet.Range["A1"]
print(cell.Value2)
# Don't forget to clean up when you're done:
workbook.Close()
excel_app.Quit()
open_excel_file()
Ironpython:
# -*- coding: utf-8 -*-
import clr
import System
clr.AddReference('Microsoft.Office.Interop.Excel')
from Microsoft.Office.Interop import Excel
from pyrevit import forms
def open_excel_file():
# Create Excel application object
excel_app = Excel.ApplicationClass()
excel_app.Visible = True
filename = forms.pick_excel_file()
# Open the selected Excel workbook
workbook = excel_app.Workbooks.Open(filename)
# For example, print the value of cell A1 in the first worksheet:
worksheet = workbook.Worksheets[1]
cell = worksheet.Range["A1"]
print(cell.Value2)
# Don't forget to clean up when you're done:
workbook.Close()
excel_app.Quit()
open_excel_file()
@aaronrumple i’ll put your code through chatgpt if thats ok .
But i like to ask humans also
What are the first line and the Marshall import for?
Because i don’t seems to need those to make things work, maybe its this difference that causes my CPython to open excel a bit to slow?
I’m still quite new to programming in python ( or in any language for that matter) and trying to understand everyones input .
@AlexanderVDB I tried the CPython version and it didn’t even work for me. Looking back at Jean-Marc’s response why not use the same packages that pyrevit uses to read and write excel files. They are both native python packages so the performance may be better.
Here’s some identical code that reads the first row and column and prints it out. Try it out and see if it works better.
#! python3
import os
import sys
import clr
import System
clr.AddReference('System.Windows.Forms')
from System.Windows.Forms import OpenFileDialog
PYREVIT_LIBPATH = os.path.join(os.path.join(os.getenv('APPDATA'), 'pyRevit-Master'), 'site-packages')
sys.path.append(PYREVIT_LIBPATH)
import xlrd
dialog = OpenFileDialog()
dialog.Filter = 'Excel Files|*.xlsx'
# Show the dialog and get the selected file
result = dialog.ShowDialog()
if result == System.Windows.Forms.DialogResult.OK:
file_path = dialog.FileName
excel_sheet = xlrd.open_workbook(file_path)
sheets = excel_sheet.sheets()
print(sheets[0].row(0)[0].value)
You are loading type libraies with the first two lines.
Some more info on Interop…
Interop Marshaling - .NET Framework | Microsoft Learn
IronPython and COM object… (Excel)
IronPython .NET Integration
Using IronPython and Interop
import clr
import sys
import System
from System import Array
from System.Collections.Generic import *
clr.AddReference("RevitAPI")
clr.AddReference("RevitAPIUI")
from Autodesk.Revit.DB import *
from Autodesk.Revit.UI import *
import os
from System import Array
import time
from pyrevit import forms
# t = System.Type.GetTypeFromProgID("Excel.Application")
# excel = System.Activator.CreateInstance(t)
excelTypeLibGuid = System.Guid("00020813-0000-0000-C000-000000000046")
clr.AddReferenceToTypeLibrary(excelTypeLibGuid)
from Excel import Application
from Excel import *
excel = Application()
clr.AddReferenceByName('Microsoft.Office.Interop.Excel, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c' )
from Microsoft.Office.Interop import Excel
from Microsoft.Office.Interop.Excel import ApplicationClass
from System.Runtime.InteropServices import Marshal
xlDirecDown = System.Enum.Parse(Excel.XlDirection, "xlDown")
xlDirecRight = System.Enum.Parse(Excel.XlDirection, "xlToRight")
doc = __revit__.ActiveUIDocument.Document
app = __revit__.Application
uidoc = __revit__.ActiveUIDocument
title = doc.Title
#excel = ApplicationClass()
excel.DisplayAlerts = False
myexcelfile = os.path.expanduser('~\Desktop\\Revit Object Styles.xlsx')
fileexists = os.path.isfile(myexcelfile)
if fileexists:
workbook = excel.Workbooks.Open(myexcelfile)
worksheets = workbook.WorkSheets
exists = False
for s in worksheets:
if s.Name == title:
exists = True
break
if exists:
ws = worksheets(title)
ws.Activate
worksheets(title).Cells.Clear
else:
ws = worksheets.Add()
ws.Name = title
worksheets(title).Activate
else:
workbook = excel.Workbooks.Add()
ws = workbook.ActiveSheet
ws.Name = title
cell = ws.Range["A1"]
workbook.SaveAs(myexcelfile)
def WriteRange(sheet, data):
rows = len(data) - 1
ts = time.time()
for i in range(rows):
cellrow = i + 1
xlrange = sheet.Range[sheet.Cells(cellrow,1), sheet.Cells(cellrow,11)]
r = data[i]
myarray = Array[str](r)
xlrange.Value = myarray
te = time.time()
t = te - ts
def getLinePattern(graphicstyleid):
if graphicstyleid.ToString() == str(-3000010):
linepattern = "Solid"
else:
linepattern = doc.GetElement(graphicstyleid)
if linepattern:
linepattern = linepattern.Name
else: linepattern = None
return linepattern
def getCategoryDataTranspose(cat):
cattype = cat.CategoryType.ToString()
id = cat.Id
bic = cat.BuiltInCategory
if bic == BuiltInCategory.INVALID:
isbic = "False"
else:
isbic = "True"
name = cat.Name
lineprojection = None
linecut = None
projectionId = cat.GetLinePatternId(GraphicsStyleType.Projection)
lineprojection = getLinePattern(projectionId)
projectionpen = cat.GetLineWeight(GraphicsStyleType.Projection)
cutId = cat.GetLinePatternId(GraphicsStyleType.Cut)
linecut = getLinePattern(cutId)
cutpen= cat.GetLineWeight(GraphicsStyleType.Cut)
linecolor = cat.LineColor
r = linecolor.Red
g = linecolor.Green
b = linecolor.Blue
lineRGB = "(" + str(r) + "," + str(g) + "," +str(b) + ")"
material = cat.Material
if material:
materialname = material.Name
else:
materialname = "None"
if cat.IsCuttable == 0:
cuatable = "False"
else:
cutable = "True"
if cat.CanAddSubcategory == 0:
canaddsubcategory = "False"
else:
canaddsubcategory = "True"
parent = cat.Parent
if parent:
parent = parent.Name
else:
parent = "None"
data = [cattype, str(id.IntegerValue), isbic, name, lineRGB, str(lineprojection), str(linecut), materialname, parent]
dataB = tuple(zip(*data))
return(dataB)
def getCategoryData(cat):
cattype = cat.CategoryType.ToString()
id = cat.Id
bic = cat.BuiltInCategory
if bic == BuiltInCategory.INVALID:
isbic = "False"
else:
isbic = "True"
name = cat.Name
#builtincategoeyname = LabelUtils(bic)
lineprojection = None
linecut = None
projectionId = cat.GetLinePatternId(GraphicsStyleType.Projection)
lineprojection = getLinePattern(projectionId)
projectionpen = cat.GetLineWeight(GraphicsStyleType.Projection)
cutId = cat.GetLinePatternId(GraphicsStyleType.Cut)
linecut = getLinePattern(cutId)
cutpen= cat.GetLineWeight(GraphicsStyleType.Cut)
linecolor = cat.LineColor
r = linecolor.Red
g = linecolor.Green
b = linecolor.Blue
lineRGB = "(" + str(r) + "," + str(g) + "," +str(b) + ")"
material = cat.Material
if material:
materialname = material.Name
else:
materialname = "None"
if cat.IsCuttable == 0:
cuatable = "False"
else:
cutable = "True"
if cat.CanAddSubcategory == 0:
canaddsubcategory = "False"
else:
canaddsubcategory = "True"
parent = cat.Parent
if parent:
parent = parent.Name
else:
parent = "None"
data = [cattype, str(id.IntegerValue), isbic, name, lineRGB, str(lineprojection), str(projectionpen), str(linecut), str(cutpen), materialname, parent]
return(data)
def getCategories():
header = ["Category Type",
"Category Id",
"Is BuiltInCategory",
"Category Name",
"Category Color",
"Porjection Linetype",
"Projection Pen",
"Cut Linetype",
"Cut Pen",
"Material",
"Parent Category"]
dataout = []
categories = doc.Settings.Categories
for cat in categories:
catdata = getCategoryData(cat)
dataout.append(catdata)
try:
subcats = cat.SubCategories
for sc in subcats:
subcatdata = getCategoryData(sc)
dataout.append(subcatdata)
except:
pass
#sorted(dataout)
dataout.sort(key = lambda dataout: dataout[0])
dataout.insert(0, header)
return dataout
def getCategoriesTranspose():
header = ["Category Type", "Category Id", "Is BuiltInCategory", "Category Name", "Category Color", "Porjection Linetype", "Cut Linetype", "Material", "Parent Category"]
dataout = []
categories = doc.Settings.Categories
for cat in categories:
catdata = getCategoryData(cat)
dataout.append(catdata)
try:
subcats = cat.SubCategories
for sc in subcats:
subcatdata = getCategoryData(sc)
dataout.append(subcatdata)
except:
pass
#sorted(dataout)
dataout.sort(key = lambda dataout: dataout[0])
dataout.insert(0, header)
d = tuple(zip(*data))
return d
def getObjectStyles():
objectstyles = uidoc.ObjectStyles
return objectstylestyles
def rgbint(rgb):
r = rgb[0]
g = rgb[1]
b = rgb[2]
rgbint = 65536 * r +256 * g + b
return rgbint
def FormatExcel(ws):
count = 1
with forms.ProgressBar(title = 'Well hold on. This will take a second...', steps=10) as pb:
ws.Range("A1:K1").Font.FontStyle = "Bold"
ws.Range("A1:K1").Columns.AutoFit
excel.ActiveSheet.Columns.AutoFit()
r = 192
g = 192
b = 192
ws.Range("A1:K1").Interior.Color = 65536 * r +256 * g + b
ws.Columns.HorizontalAlignment = XlHAlign.xlHAlignCenter
ws.Columns("B:D").HorizontalAlignment = XlHAlign.xlHAlignRight
ws.Columns("I").HorizontalAlignment = XlHAlign.xlHAlignLeft
colorcount = ws.Range("E1").End(xlDirecDown).Row
colorrange = ws.Range("E1:" + "E" + str(colorcount))
for c in colorrange:
rgb = c.Text
rgb = rgb[1:]
rgb = rgb[:-1]
rgb = rgb.split(",")
try:
rgb = list(map(int, rgb))
rgb = rgbint(rgb)
except:
rgb = None
if rgb is not None:
c.Interior.Color = rgb
if rgb == int(0):
c.Font.Color = 16777215
elif rgb < 2210752:
c.Font.Color = 16777215
# else:
# c.Font.Color = 0
isbiccount = ws.Range("C1").End(xlDirecDown).Row
isbicrange = ws.Range("C1:" + "C" + str(isbiccount))
for c in isbicrange:
if c.Value() == "False":
myrow = ws.Range("A" + str(c.Row) + ":" + "D" + str(c.Row))
myrow.Interior.Color = 12632256
myrow = ws.Range("F" + str(c.Row) + ":" + "K" + str(c.Row))
myrow.Interior.Color = 12632256
pb.update_progress(count, isbiccount)
count = count + 1
categorydata = getCategories()
WriteRange(ws, categorydata)
#excel.Visible = True
FormatExcel(ws)
forms.toast(
"We're all done here. Have a Nice day. Click me for your Excel.",
title = "Otto Form 1.0",
appid ="Otto Pilot",
click = "https://www.yaaeger.com/",
actions={
"Open Excel":myexcelfile
})
#workbook.Save()
workbook.SaveAs(myexcelfile)
workbook.Close()
excel.Quit()
i’m only a novice in these thing so i tried understanding and digging in the code from Jean-Marc but i could not get my head wrapped around it. I found some code that use these ( i think it was from Aaron? in the export schedule tool?)
This wasn’t meant disrespectfull towards Jean-Marc his work and input but the code (module/api) is abit above my head in how i need to use it or read it.
That was really the only thing, i don’t won’t to bother everyone to much with my learning so i went for the solution i posted.
Are there recourses for learning this better/easier?
@Nicholas.Miles thx also for your input on this and i will pick up on it after the weekend. i’m eager to see what i can learn further from it. The way your code looks “it seems simple engough” … I just don’t manage yet to retrieve this myself from the modules.