Formating Excel with Dynamo... Is it possible?

Hello, is there any method in dynamo to automatically adjust the column widths of excel spreadsheets according to the contents of each instead of visually narrow? Is it possible to add formatting such as bold, font size, all through the dynamo itself?

See the problem:

image

Thanks!

Technically yes, but you’ll have to do it through a Python node using Excel Interop. See below:


Excel Autosize.dyn (3.8 KB)

import clr
clr.AddReferenceByName('Microsoft.Office.Interop.Excel, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c')
from Microsoft.Office.Interop import Excel

ex = Excel.ApplicationClass()   
ex.DisplayAlerts = False
ex.Visible = True
filepath = IN[0]

workbook = ex.Workbooks.Open(filepath)
ws = workbook.Worksheets[1]
used_range = ws.UsedRange
for column in used_range.Columns:
	column.AutoFit()
	
workbook.Save()

This essentially just opens the workbook, resizes each column in the used range, and saves it.

3 Likes

Uau, thanks thanks! But, there is one problem: my Excel contains more than one sheets in the same file…

image

The python code need to be different… is it ok?

Yes, rather than accessing a single worksheet by index you can loop through each of the worksheets.

import clr
clr.AddReferenceByName('Microsoft.Office.Interop.Excel, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c')
from Microsoft.Office.Interop import Excel

ex = Excel.ApplicationClass()   
ex.DisplayAlerts = False
ex.Visible = True
filepath = IN[0]

workbook = ex.Workbooks.Open(filepath)
for ws in workbook.Worksheets:
	used_range = ws.UsedRange
	for column in used_range.Columns:
		column.AutoFit()
	
workbook.Save()

Excel Autosize.dyn (3.8 KB)

2 Likes

Hi!
Thank you very much, you were very kind!!! :risonho:

Where can I to learn more about code python for Dynamo?

There are a few tutorials on youtube (example), but there are a lot more resources for just learning Python (without dynamo). Corey Schafer has some good videos for getting started.

1 Like

You can also look at https://github.com/Amoursol/dynamoPython for heavily annotated, community generated Python :slight_smile:

2 Likes

Thanks for the tip!