Import Excel more dynamic how?

Developers
1

Hello,

i want to import data from excel, the script runs well…

is it possible to make my script more comprehensive… ?

now it is very static, taking the elements parameter and get the information by index

(open excel is from PyRevit, so the part with the transaction i want to change)


# 🍉 Open excelfile
directory = forms.pick_excel_file(False, 'Select File')
wb = xlrd.open_workbook(directory)
sheet = wb.sheet_by_index(0)

data_dict = {}

for rw in range(1, sheet.nrows):
    key = sheet.cell_value(rw, 0)
    value = sheet.row_values(rw)[1:]
    data_dict[key] = value

# ❗ get and set Parameters
with Transaction(doc, __title__) as t:
    # 🔓 get,set Item by Id
    t.Start()

    for k, v in data_dict.items():
        print("{}:{}".format(k, v))
        # this is just a print statement to show you that the value is a list
        for item in v:     # this is to loop through the list in values
            if item == '':      # if item is None type, it will skip
                continue

            for element in all_elements:
                if element:
                    if element.Id == ElementId(int(k)):
                        fab_type = element.LookupParameter("Fabrikationsnummer/Type").Set(v[1])
                        gew_begi = element.LookupParameter("Gewaehrleistungsbeginn").Set(v[2])
                        gew_ende = element.LookupParameter("Gewaehrleistungsende").Set(v[3])
                        hersteller = element.LookupParameter("Hersteller").Set(v[4])
                        lieferant = element.LookupParameter("Lieferant").Set(v[5])
                        int_hers = element.LookupParameter("Intervall lt. Hersteller").Set(v[6])
                        fab_type = element.LookupParameter("Pruefintervall").Set(v[7])
                        beh_mpfl = element.LookupParameter("Behoerdliche Pruefpflicht").Set(v[8])
                        #gew_ende = element.LookupParameter("VSR_Nummer").Set(v[9])
                        arb_med = element.LookupParameter("Arbeitsmedium").Set(v[10])
                        abmess = element.LookupParameter("Abmessungen").Set(v[11])
                        antrieb = element.LookupParameter("Antriebsart").Set(v[12])
                        bau_aus = element.LookupParameter("Bau-/ Ausfuehrungsart").Set(v[13])
                        bet_dru = element.LookupParameter("Betriebsdruck").Set(v[14])
                        bet_tem = element.LookupParameter("Betriebstemperatur").Set(v[15])
                        feu_wie = element.LookupParameter("Feuerwiderstandsklasse_").Set(v[16])
                        nennwei = element.LookupParameter("Nennweite/DN").Set(v[17])
                        # vol_str = element.LookupParameter("Volumenstrom").Set(v[19])
                        # zug_ver = element.LookupParameter("Zugehöroger Verteiler").Set(v[20])
                        fil_kla = element.LookupParameter("Filterklasse").Set(v[20])
                        # eng_kla = element.LookupParameter("Energieeffizienzklasse").Set(v[21])
    t.Commit()
    # 🔒 done

i tried to create a function but it does not work, the element remains unchanged, look to my code

# 🍉 Open excelfile
directory = forms.pick_excel_file(False, 'Select File')
#wb = xlrd.open_workbook(directory)
#sheet = wb.sheet_by_index(0)

output = script.get_output()
output.close_others()

PATH_SCRIPT  = os.path.dirname(__file__)

# doc = revit.doc
with xlrd.open_workbook(directory) as wb:
    sheet = wb.sheet_by_index(0)
    DATA_DICT = {}
    for rw in range(1, sheet.nrows):
        key = int(sheet.cell_value(rw, 0))
        value = sheet.row_values(rw)[1:]
        DATA_DICT[key] = value
    PARAMETERS = sheet.row_values(0)[1:]

# 🎯 set Parameter based excel
def set_parameters(elements, parameters=PARAMETERS, data_dict = DATA_DICT):
    d = data_dict.keys()
    not_found = {}
    for element in elements:
        element_id = int(element.Id)
        if element_id in data_dict.keys(): # element and
            element_entry = data_dict[element_id]
            not_found[element_id] = []
            for i, parameter in enumerate(parameters[1:]):
                try:
                    element.LookupParameter(parameter).Set(element_entry[i])
                except Exception as e:
                    not_found[element.Id.IntegerValue].append(parameter)
    return not_found


all_elements = DB.FilteredElementCollector(doc, doc.ActiveView.Id).WhereElementIsNotElementType().ToElements()

# 🔓 🔒 🔑 Set Parameter
with revit.Transaction("Assign parameters from excel DB"):
    not_found = set_parameters(all_elements)
    OUT = not_found

here is the post in PyRevit

any idea ?

1 Like
2

Something like this?

with revit.Transaction("Assign parameters from excel DB") as t:
    t.Start()    
    not_found = set_parameters(all_elements)
    OUT = not_found
    t.Commit()
3

Pro tip - you can format code to Python syntax using ‘python’ without a space after the first three back-ticks like this
```python
<code>
```

2 Likes
4

@Mike.Buttery ,

yes , the script should run each transaction, and in the OUT there should be only the elements, which where not able to set!

5

Check you use of keys - are they the same types (ints for ints, strings for strings)
There are a lot of dictionary methods that can make things a bit easier and more fault tolerant

def set_parameters(elements, parameters=PARAMETERS, data_dict = DATA_DICT):
    not_found = {}
    for element in elements:
        element_id = int(element.Id.IntegerValue)
        if element_id in data_dict:
            element_entry = data_dict.get(element_id)
            for i, parameter in enumerate(parameters[1:]):
                try:
                    element.LookupParameter(parameter).Set(element_entry[i])
                except Exception:
                    not_found.setdefault(element_id, []).append(parameter)
    return not_found
1 Like