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:
Thanks!
Technically yes, but you’ll have to do it through a Python node using Excel Interop. See below:
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.
Uau, thanks thanks! But, there is one problem: my Excel contains more than one sheets in the same file…
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)
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.
You can also look at https://github.com/Amoursol/dynamoPython for heavily annotated, community generated Python 
Thanks for the tip!