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?

Answer 1

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.