I am trying to write 3 lists of parameters previously extracted from elements in the model into a single Excel workbook, each list into a worksheet.
Attached is how I’ve managed to execute Excel Application and create a book with one worksheet, and even a range of cells.
The issue is that I haven’t found out the way to write the lists (in the inputs) into a certain range of cells.
¿Does anybody have an idea how to achieve this using Pyhton Script?.(Not packages)
You can find some examples about Excel in ironpython on their Web page.
Your approach seems promising. If I read it correctly, adding the end of the range into the indexing will select it e.g.
@Kibar Thanks for your answer.
I’ve already achieved the way to write given string values into defined cell ranges. But haven’t found the way to take the elements from the list and write them into those ranges. I don’t know if it would be necessary to iterate with for loop or somehow through the list of values and then write them, somehow, into my Excel workbook.
I will keep on trying but if you have a clue in this issue I will be grateful.
I don’t have access to excel until next week.
You could upload an image, showing your input and how your goal in excel should look like, to clarify your problem. Maybe someone else can chip in earlier
Why not use the out of the box node for this?
I want to collect these 3 lists of parameters into a single Excel worbook, each list into a separate worksheet as seen in the image.
Cause I find very useful to learn the way to write data from Revit into Excel without depending on predefined inputs of OOTB nodes.
Hello, if you want really use Ironpython and Microsoft API, the length of the input list and the size of the Excel Range must be egal.
1/create a Range with size it’s same of the input list
2/create a System.Array with size it’s same of the input list
3/populate the Array with the input list
4/copy the Array into the Range with the variable Value2
See an example in my blog (French)
@c.poupin Thanks for sharing your work.
So, if the size of the range has to be the same as the length of the input I guess that wouldn’t be as flexible as I want since what I want is to extract information from the model periodically as it continues to change over time…
for adapt dynamically the size of the range use the len() function and Cells property
inputlst = [1,2,3,4,5]
xlrange = ws.Range[ws.Cells(1, 1), ws.Cells(1, len(inputlst))]
the result is same as ws.Range[“A1”,“E1”]
@c.poupin Thank you for your help.
I am still trying to understand how to populate the range with the array. I’ve read your blog and documents regarding interoperability between python and Excel but find it a bit difficult to understand how arrays work. Could you have a look at what I’m doing in order to see what is missing?, Am I doing it right? Thanks!!
CreateInstance need (Type, int, int) entries, why did you type there “object”?
there are 2 errors
It’s working amazingly great!
Now I’ve learned how to populate arrays and write them into the ranges.
The only trouble I have now is that I want to write each list of parameters into the columns instead of the rows.
As you can see I created 3 ranges and 3 arrays, one per inputlist. Then populated the arrays with the lists and wrote them into the ranges.
How do you transpose these lists in order to have them organized by columns instead of rows?
@DavidMena reverse indexes of range and Array (https://docs.microsoft.com/en-us/dotnet/visual-basic/programming-guide/language-features/arrays/array-dimensions)
ex = Excel.ApplicationClass()
ex.Visible = True
ex.DisplayAlerts = False
workbook = ex.Workbooks.Add()
ws = workbook.Worksheets
inputlst = range(1,10,2)
xlrange = ws.Range[ws.Cells(1, 1), ws.Cells(len(inputlst), 1)]
a = Array.CreateInstance(object, len(inputlst), 1)
for index, i in enumerate(inputlst):
a[index, 0] = i
xlrange.Value2 = a
OUT = inputlst
@c.poupin I really, really appreciate your help. Now I have finally achieved what I was looking for.
Just one more question: Do you know how to overwrite the name of the worksheets as you create them?
If you have the Worksheet object you can directly set its Name property:
ws.Name = "Your Name Here"
Reading the documentation, it looks like the return type is the new Worksheet so you may be able to do this:
wb.Worksheets.Add().Name = "Your Name Here"
If you provide a list of names, you can do this in a much cleaner way as well:
names = ["Name1", "Name2", "Name3"]
for name in names:
wb.Worksheets.Add().Name = name
Awesome! Thank you guys a lot !