Python pyrevit reading from excel

Revit API
1

Hello,

I’m currently writing a script to read from an excel file and match the data with specific values from the families in Revit. For some reason its updating the Dust Pickup family just fine but not the Dust Main Line Branch. The problem I’m running into is from the print statement:

Comparing Excel value: 1.0 with Revit param value: 1
Excel value 1.0 does not match param value 1

It’s having trouble reading from excel and matching with the values in the Revit family. The parameter its supposed to be matching with is “Branch Number” , Text unit, but even if I manually set the Branch Number to 1.0 it still doesn’t match. It’s very difficult to explain but I’ve be working on this for a long time and have no idea what I’m doing wrong.

Here’s the code:

import clr
clr.AddReference("System.Windows.Forms")
clr.AddReference("System.Drawing")

from System.Windows.Forms import Form, Button, DialogResult, Label, OpenFileDialog, FormBorderStyle, FormStartPosition, AnchorStyles
from System.Drawing import Point

__title__ = 'Update\nSchematic'
__doc__ = 'This Button Updates Dust Schematic OneLine, .'

clr.AddReference("RevitAPI")
clr.AddReference("RevitServices")
import RevitServices
from RevitServices.Persistence import DocumentManager
from Autodesk.Revit.DB import FamilyInstance, FilteredElementCollector, BuiltInCategory, Transaction, StorageType

clr.AddReference("Microsoft.Office.Interop.Excel")
import Microsoft.Office.Interop.Excel as Excel

doc = __revit__.ActiveUIDocument.Document

class FilePickerForm(Form):
    def __init__(self):
        self.InitializeComponent()

    def InitializeComponent(self):
        self.Text = "Select Excel File Path"
        self.Width = 400
        self.Height = 150
        self.FormBorderStyle = FormBorderStyle.FixedDialog
        self.MaximizeBox = False
        self.StartPosition = FormStartPosition.CenterScreen

        self.label = Label()
        self.label.Text = "Select Dust System Sizing Excel File:"
        self.label.Location = Point(20, 20)
        self.label.AutoSize = True
        self.label.Anchor = AnchorStyles.Top
        self.Controls.Add(self.label)

        self.browse_button = Button()
        self.browse_button.Text = "Browse"
        self.browse_button.Location = Point(20, 60)
        self.browse_button.Click += self.BrowseButton_Click
        self.Controls.Add(self.browse_button)

    def BrowseButton_Click(self, sender, args):
        file_dialog = OpenFileDialog()
        file_dialog.Title = "Select Excel File"
        file_dialog.Filter = "Excel files (*.xlsx; *.xlsm)|*.xlsx; *.xlsm|All files (*.*)|*.*"
        file_dialog.InitialDirectory = "C:\\"
        
        dialog_result = file_dialog.ShowDialog()

        if dialog_result == DialogResult.OK:
            self.filePath = file_dialog.FileName
            self.label.Text = "Selected File Path: {}".format(self.filePath)
            self.DialogResult = DialogResult.OK
        else:
            self.label.Text = "No file selected."
            self.DialogResult = DialogResult.Cancel

def get_detail_components(family_names):
    view = doc.ActiveView
    collector = FilteredElementCollector(doc, view.Id).OfCategory(BuiltInCategory.OST_DetailComponents).OfClass(FamilyInstance).WhereElementIsNotElementType().ToElements()
    components = [inst for inst in collector if inst.Symbol.FamilyName in family_names]
    return components

def update_parameters(instance, parameters):
    for param_name, value in parameters.items():
        param = instance.LookupParameter(param_name)
        if param:
            if param.StorageType == StorageType.Double:
                try:
                    param.Set(float(value))
                except ValueError:
                    print("Failed to set {} with value {} as float.".format(param_name, value))
            elif param.StorageType == StorageType.Integer:
                try:
                    param.Set(float(value))
                except ValueError:
                    print("Failed to set {} with value {} as integer.".format(param_name, value))
            else:
                param.Set(str(value))

def match_excel_data_with_revit_instances(instances, column_index, parameter_names, ws):
    matched_rows = []

    for instance in instances:
        for parameter_name in parameter_names:
            param = instance.LookupParameter(parameter_name)
            if param:
                param_value = param.AsString()
                for row_index in range(20, 121):
                    excel_value = str(ws.Cells(row_index, column_index).Value2)
                    if excel_value == param_value:
                        row_data = [str(ws.Cells(row_index, col).Value2) for col in range(1, 26)]
                        matched_rows.append(row_data)
                        break

    return matched_rows

def match_excel_data_with_revit_instances_string(instances, column_index, parameter_names, ws):
    matched_rows = []

    for instance in instances:
        for parameter_name in parameter_names:
            param = instance.LookupParameter(parameter_name)
            if param:
                param_value = param.AsString()
                for row_index in range(20, 121):
                    excel_value = ws.Cells(row_index, column_index).Value2
                    if excel_value is None:
                        continue  # Skip rows with None values
                    excel_value_str = str(excel_value)
                    print("Comparing Excel value: {} with Revit param value: {}".format(excel_value_str, param_value))  # Debug
                    if excel_value_str == param_value:
                        row_data = [ws.Cells(row_index, col).Value2 for col in range(1, 26)]
                        matched_rows.append(row_data)
                        break
                    else:
                        print("Excel value {} does not match param value {}".format(excel_value_str, param_value))  # Debug

    return matched_rows
  
def main():
    file_picker_form = FilePickerForm()
    result = file_picker_form.ShowDialog()

    if result == DialogResult.OK:
        filePath = file_picker_form.filePath
        xlApp = Excel.ApplicationClass()
        xlApp.Visible = False
        xlApp.DisplayAlerts = False
        xlApp.ScreenUpdating = False

        workbook = xlApp.Workbooks.Open(filePath)
        ws = workbook.Worksheets[1]

        family_names = ["Dust Pickup", "Dust Line Main Branch"]
        instances = get_detail_components(family_names)

        # Matching Dust Pickup data
        dust_pickup_instances = [inst for inst in instances if inst.Symbol.FamilyName == "Dust Pickup"]
        matched_pickup_rows = match_excel_data_with_revit_instances(dust_pickup_instances, 2, ["dustPickUpNumber"], ws)

        # Matching Dust Line Main Branch data
        dust_line_main_branch_instances = [inst for inst in instances if inst.Symbol.FamilyName == "Dust Line Main Branch"]
        matched_branch_rows = match_excel_data_with_revit_instances_string(dust_line_main_branch_instances, 15, ["Branch Number"], ws)

        transaction = Transaction(doc, "Update Parameters")
        transaction.Start()

        for row_data in matched_pickup_rows:
            parameters = {
                "dustDescription": row_data[0],
                "Diameter": row_data[2],
                "dustCFM": row_data[9]
            }
            for instance in dust_pickup_instances:
                if instance.LookupParameter("dustPickUpNumber").AsString() == row_data[1]:
                    print("Updating parameters for Dust Pickup instance with dustPickUpNumber: {}".format(row_data[1]))
                    print("New parameter values: {}".format(parameters))
                    update_parameters(instance, parameters)

        for row_data in matched_branch_rows:
            parameters = {
                "CFM2": row_data[21],
                "FPM2": row_data[24],
                "numDiameter": row_data[23]
            }
            for instance in dust_line_main_branch_instances:
                if instance.LookupParameter("Branch Number").AsString() == row_data[14]:
                    print("Updating parameters for Dust Line Main Branch instance with Branch Number: {}".format(branch_number))
                    print("New parameter values: {}".format(parameters))
                    update_parameters(instance, parameters)

        transaction.Commit()

        workbook.Close(False)
        xlApp.Quit()
    else:
        print("No file selected or operation canceled.")


if __name__ == "__main__":
    main()
2

Hi @aalaimo, welcome to the community!

How is the branch number parameter stored? Is it a number or a string? I suppose it is a number, and the AsString just takes the value, regardless of how it is shown in revit, and converts it into a text… so the number 1.0 is a 1.
You could retrieve the value as number instead of string and use the str format method to properly format it

val = param.AsDouble()
param_value = "{.1d}".format(val)

Some unsolicited tips:

  • since you’re only reading data from the excel, I suggest you to use the xlrd package, as it is already included in pyRevit and doesn’t need Excel to run.
  • you keep accessing the excel table to retrieve the values for each parameters; this is an expensive operation, especially using excel interop. You could read all the table at once and store it as a dictionary, let’s call it db, with the value at column_index as key and row_data as value, so that you just need to db.get(param_value) to retrieve the corresponding data (or get None on no match).
  • you can use pyrevit’s Transaction object to automatically start and commit the transaction using with Transaction: context manager.
  • in your main function, if you flip the if control to check if the dialog result is not OK and the return immediately, you can then de-dent the rest of the code:
    if result != DialogResult.OK:
        print("No file selected or operation canceled.")
        return 
    # rest of the code here, no need for "else"
  • the same can be done for the if param conditional:
if not param:
    continue
# rest of the code
2 Likes
3

Well, your advice fixed the number problem but for some reason its still not reading the data and matching but this is what Revit says…:

Comparing Excel value: 1.0 with Revit param value: 1.0
Excel value 1.0 does not match param value 1.0

I’m sorry, I forgot to mention that I’m very new with pyrevit python coding so I use ChatGTP for most of the problems I run into. That being said, I have no doubt that your unsolicited tips would help me, I just wouldn’t know where to start.

4

Try to change the debug message with this:

print("Comparing Excel value: {!r} with Revit param value: {!r}".format(excel_value_str, param_value))

It will enclose the string in quotes, maybe there’s a space in there that we’re missing…

5

The problem with chatGPT is that it has little “knowledge” of pyrevit and the revit API in general, because there’s not much data available on this topic to train the model.
It’s useful for simple, generic code and widely used libraries, but not for niche applications.

In my tests I found out that chatgpt 3.5 cant give you a good answer unless after 7-8 iterations; but by that time it might enter a loop of wrong answers. It sometimes insists to tell you that there’s a magical python library that does what you’re asking it to solve, when in fact there’s no such library…

6

I’m sorry @sanzoghenzo, your first solution had solved it, I changed one of the statements to read AsDouble but I had missed the other in the Def Main group. The script works very well so thanks for the help!

3 Likes
7

Can you tell me. Is there any result in the end? I also want to achieve this function, please :rofl: