Ironpython write to Excel

Question

I am trying to write data from Revit to Excel with RevitPythonShell.

So far I have collected all data in a zipped list and made an enumerated for loop to write the data to the corresponding rows and columns as following:

for index, data in enumerate(wall_zipped_list):
    for count, parameters in enumerate(data):
        wall_cell = worksheet_wanden.Range[(str(column_list[count]))+str(index+5)]      
        wall_cell.Value2 = data[count]

This is incredibly slow because the loop is calling Value2 everytime. A Revit model containing about 800 walls takes 2 minutes to write to Excel. So I tried a different method using a dictionary.

for k , v in data_dict.iteritems():
    #print k, v[0]
    worksheet_wanden.Range["A"+str(count)].Value2 = k
    worksheet_wanden.Range["B"+str(count)].Value2 = v[0]
    worksheet_wanden.Range["C"+str(count)].Value2 = v[1]
    worksheet_wanden.Range["D"+str(count)].Value2 = v[2]
    worksheet_wanden.Range["E"+str(count)].Value2 = v[3]
    worksheet_wanden.Range["F"+str(count)].Value2 = v[4]
    worksheet_wanden.Range["G"+str(count)].Value2 = v[5]
    worksheet_wanden.Range["H"+str(count)].Value2 = v[6]
    worksheet_wanden.Range["I"+str(count)].Value2 = v[7]
    worksheet_wanden.Range["J"+str(count)].Value2 = v[8]
    worksheet_wanden.Range["K"+str(count)].Value2 = v[9]
    count += 1

This method is already a lot quicker. This takes about 20 seconds to fill about 800 rows with 10 columns in Excel. Am I missing some IronPython functionality which you can write dictionary or lists to Excel rows or columns?

I also looked at installing 3d party modules. But this is not really an option since RevitPythonShell usising IronPython 2.7.3 and I can't get pip install to work.

Thanks in advance.

Is it maybe faster to write to csv first in IronPython and then import it some way into excel?

score 2 · Answer 1 · answered Dec 18 '17 at 10:03

This is more of a question on .NET/Excel interop. I think, based on this SO question you should be able to assign an array to a range.

That is, your current range is just one cell. You could try creating a 2d System.Array and assign it to the range... I tried it out here:

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

import Microsoft.Office.Interop.Excel as Excel excel = Excel.ApplicationClass() excel.Visible = True # makes the Excel application visible to the user workbook = excel.Workbooks.Add() worksheet = workbook.Worksheets.Add()

from System import Array xlrange = worksheet.Range["A1:c3"]

a = Array.CreateInstance(object, 3, 3) i = 0 for row in range(3): for column in range(3): a[row, column] = i i += 1

xlrange.Value2 = a ```

This produces a result like this:

More information on IronPython and Excel can be found here: http://www.ironpython.info/index.php?title=Interacting_with_Excel

You could try installing the xlwt module and use that - avoiding COM interop.

Claus · Answer 2 · 2018-09-03T06:41:05.590

0

It works

i = 0 

xlrange_revit_type = worksheet_data.Range["C2:C" + str(len(revit_type_list)+1)]
a = Array.CreateInstance(object,len(revit_type_list), 3) 

while i < len(revit_type_list):
    a[i,0] = revit_type_list[i]
    i += 1

xlrange_revit_type.Value2 = a

Is it possible to install a 3d party module like xlwt for RevitPythonShell? I can't find any documentation.

edited Sep 03 '18 at 06:41

answered Dec 21 '17 at 09:37

Claus

119
1
12

score 0 · Answer 3 · answered Apr 24 '19 at 20:19

If you're referring to the Cells notation that you can use in VB, I don't know why it does not work, but you could create a definition:

def Cells(a,b):
    return str(chr(a+96) + str(b))

Now you could call something like:

x1range = ws.Range(Cells(1,1),Cells(5,10))

and it will work the same. That is what I do.

Ironpython write to Excel

3 Answers3