Using Excel in Automations

Let's say you want your non-coding coworkers to be able to control certain results of your scripts. There are several ways to do this, but by far the simplest is to create a shared Excel file. Your coworkers can input values into this file, which can then be read by your script at execution time. There are countless possible use-cases for this method, but for this example, we'll keep it simple and look at creating levels at certain elevations.

Setting Up Excel

Before we create any code, we need to set up our Excel file. Make sure to save it somewhere your coworkers can access. We'll create two columns, one called "LevelName" and the other "Elevation". We'll call our sheet "New Level Names".

NOTE: Once the file is created, we'll want to add guards to make sure our script runs smoothly. We don't want people to be able to add non-integers to the Elevation column, for instance. For more information on protecting Excel files from faulty entries, click here.

Setting Up the Script

With that sorted, we'll turn to our code. The first thing our code needs to do is read our Excel file and convert the information it finds into a python dictionary. There's a few libraries we could use here, but I'll stick to pandas. In the example below, we use read_excel() to create a dataframe ("df"). We then convert our dataframe to a dictionary. To do this, we then use set_index() to tell pandas that we want to use "LevelName" column as our index, so its values will become the keys of our dictionary. We set the "Elevation" column as our values column. We then use to_dict() to actually create the dictionary. 
    doc = DocumentManager.Instance.CurrentDBDocument
 
    target_path = fr"lorem_ipsum"
 
    # Create dataframe from excel path
    df = pandas.read_excel(target_path, sheet_name = "New Level Names", header = 0, dtype = str)
    # Convert the DataFrame to a dictionary
    df_dict = df.set_index("LevelName")["Elevation"].astype(int).to_dict()
We should now have a dictionary called df_dict that looks something like this: 
    {
        "Apple":1,
        "Banana":2,
        "Cherry":3
    }
We can now iterate through this dictionary to create our levels. Before we do this, we'll collect all our existing levels so we can skip any that already exist. 
    existing_levels = FilteredElementCollector(doc).OfClass(Level).ToElements()
    existing_level_names = [level.Name for level in existing_levels]
  
    TransactionManager.Instance.EnsureInTransaction(doc)
    for levelname, elevation in df_dict.items():
        if levelname not in existing_level_names:
            new_level = Level.Create(doc, elevation)
            new_level.Name = levelname
    TransactionManager.Instance.TransactionTaskDone()
And that's it! Of course, we can beef this code up. For instance, we can update levels' elevations if these values are changed in our Excel sheet. To do this, we'll change the way we structure existing_level_names to make switching between our layer_names and layer_objects a little easier: 
    existing_levels = FilteredElementCollector(doc).OfClass(Level).ToElements()
    existing_level_names = {}
    for level_object in existing_levels:
        level_name = level_object.Name 
        existing_level_names[level_name] = level_object
        
    TransactionManager.Instance.EnsureInTransaction(doc)
    for levelname, target_elevation in df_dict.items():
        if levelname in existing_level_names:
            target_level = existing_level_names[levelname]
            elevation_parameter = target_level.get_Parameter(BuiltInParameter.LEVEL_ELEV)
            current_elevation = elevation_parameter.AsDouble()
            if current_elevation != target_elevation:
                elevation_parameter.Set(target_elevation)
    TransactionManager.Instance.TransactionTaskDone()
There's a few more things we can do, such as adding functionality for deleting levels, but it all works on the same principle.

If people in our team add or change values in our excel sheet, we'll see those changes after the next execution of our script without having to change a thing ourselves.

Comments

Post a Comment

Popular posts from this blog

Check which user owns an element in a shared model

When working with the Revit API in shared models, we are unable to execute commands on elements that are owned by other users. The following code snippet shows how to handle this issue. For the sake of arguement, let's imagine we want to perform an action on our walls. doc = DocumentManager.Instance.CurrentDBDocument walls = FilteredElementCollector(doc).OfCategory(BuiltInCategory.OST_Walls).WhereElementIsNotElementType() owned_walls = [] unowned_walls = [] for wall in walls: wall_id = wall.Id tooltipInfo = WorksharingUtils.GetWorksharingTooltipInfo(doc, wall_id) owner = tooltipInfo.Owner #If you're interested, you can also check who created the element and who edited it last. creator = tooltipInfo.Creator lastchangedby = tooltipInfo.LastChangedBy if owner != "" and owner != "lorem.ipsum": #The element is owned (it doesn't belong to an empty string), and it...
Read more

Create dimensions

Image
It is possible to automate the creation of dimensions in Revit. This process is highly customisable, but for the sake of argument, let's say we want to dimension the horizontal extents of a series of walls (to learn how to dimension room separation lines, click here ). To do this, we'll be using the NewDimension() method, which takes four arguments: View. In this case, we'll be using the doc.ActiveView, but you can specify any view you'd like. A line. This line will form the dimension. We will create this line before creating the dimension. A reference array. We need to tell the dimension which wall planes to base itself on. That way, if the wall moves, the dimension moves too. Dimension type. This input is optional. To keep this tutorial as simple as possible, we'll ignore this for now. Before we begin, let's set up a simple rounding function (to deal with tiny imprecisions in our model), and an instance of Opt...
Read more

Create Dimensions - Rooms

Image
In this post, we discussed creating dimensions with the Revit API. But we only discussed dimensioning walls. Sometimes, however, we want to dimensions to include other elements - balconies, for instance, which are not typically drawn with wall elements. In this case, we can use our room separation lines. def r2(num): return round(num, 2) doc = DocumentManager.Instance.CurrentDBDocument rooms = FilteredElementCollector(doc).OfCategory(BuiltInCategory.OST_Rooms).WhereElementIsNotElementType().ToElements() Once that's done, we can iterate through all these lines to find their maximum extents (for the purposes of this post, I'll assume we just want to dimension the rooms' x extents). leftmost_x = float('inf') rightmost_x = 0 line_left = None line_right = None for line in roomlines: info = line.Location.Curve direction = info.Direction origin = info.Origin if r2...
Read more