Inspire EMEA 2022 On-Demand is live! Watch now, and be sure to save the date for Inspire 2023 in Las Vegas next May.

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer and Intelligence Suite.

How to: Autosize columns in excel output - Macro attached

AlexEntz
8 - Asteroid

Hello all,

 

today I want to present to you a macro, that uses Python inside of Alteryx to autosize your excel output (indirectly, mind you). To use the macro, you have to put in the full file path of the excel you want to have altered. It works even if you input multiple excel workbooks and sheets.

 

Here a short explanation on how the code inside python works:

 

  1. Loading in the openpyxl and formulas (PYTHON) tool
  2. Calculating every cell using formulas
  3. Creating a directory that stores the value of the calculation with the location
  4. Looping through the columns with openpyxl and the new directory
  5. Assessing the max length of each column
  6. Adjusting the colum width with openpyxl
  7. Saving the workbook

 

The real challenge was to combine the Openpyxl and Formulas librarys inside of Python, since openpyxl can only read the lengths of cells that have values in them. If, for example, there is an excel formula created with Alteryx and stored inside a workbook, openpyxl will not be able to read its length because the result of the formula was not yet calculated by excel.

 

This problem I circumvent by using the formulas library. It calculates the excel formula just how excel would, thus making it possible to asses the length of the cell without actually opening excel and letting it calculate it. I also added the correct length for cases where excel converts a very large number to exponential math writing. Please look at the makro and the code and improve upon to your hearts content. I am pretty new to python programming so I am sure that you can further optimize my method.

 

Feel free to contact me if any questions arise, the macro and code are not perfect. There is also an example workflow attached.

 

(Special thanks to David Stanitzki and Dominik Angermaier who challenged me to create this macro)

8 REPLIES 8
momaeder
Alteryx Alumni (Retired)

This is awesome, AlexEntz! 😄

DavidSta
Alteryx
Alteryx

Awesome you took the challenge @AlexEntz !

 

This is very useful for a huge amount of users out there. Will have it bookmarked to share this post with other users out there.

sunilk
6 - Meteoroid

This seems to be the best answer for my issue. Could you perhaps assist me in incorporating this macro into my workflow?

 

Thank you very much!

AlexEntz
8 - Asteroid

Hi sunilk,

 

I would like to help you! I will pn you my personal mail account, then we can set something up :).

kcollado
5 - Atom

Thank you very much for this very helpful tips and tricks, AlexEntz! Could you also be able to assist me on running the workflows you attached, please? It's not running on my end. Thank you!

liwagab
6 - Meteoroid

Thanks for sharing Alex, what I usually do to go around on column sizes is create a template for my desired output and use the output data tool to retain source formatting (need to declare the range).

 

 

liwagab_0-1661841753472.png

 

kcollado
5 - Atom

Hi liwagab, thank you for your advice. Could you give an example using those tools, please? I'm quite lost in navigating the tools. thank you!

liwagab
6 - Meteoroid

Hi,

 

Here is a rough sample on how I would approach it.

Apologies can't share the workflow due to company policy.

 

liwagab_0-1662617153539.png

 

Below are the configuration each tool:

 

Input text to declare you input & output path

liwagab_1-1662617190893.png

 

Blob Input configuration - to enable alteryx to locate your template (configure column width on this file)

liwagab_2-1662617261366.png

 

Blob Output - save your file on this specified path

liwagab_3-1662617269941.png

 

Output data will overwrite your Blob Output file but ensure to have below:

 

1. Full path with sheet name and range declared as this is required to preserve formatting

C:\temp\templateoutput.xlsx|||Sheet$A1:AZ1000

 

2. Tick "Preserve Formatting on Overwrite" - as stated this requires range declaration thus "$A1:AZ1000" modified per your use case.

 

3. Use output path and change entire file path to overwrite you

liwagab_4-1662617299985.png

 

 

Tip: You can make use of interface tools to make file name and file path dynamic. 

 

 

Labels