Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop 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)

11 REPLIES 11
Nuno_Meneses
7 - Meteor

Hi Dsandmann

 

Is that the full code or just the part of it that to changed? Thanks

dsandmann
8 - Asteroid

@Nuno_Meneses - 

It's the full updated code, but I will call out that I was running into some pretty major performance/crashing issues with workflows that were using a lot of complex formulas or that had a lot of data, since it's doing row by row checks and I'm sure there are some technical limitations to what formulas can be adequately autofit.

 

If you're not using formulas in your workflows or in excel, you may not need those parts of the code.

Labels