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:
- Loading in the openpyxl and formulas (PYTHON) tool
- Calculating every cell using formulas
- Creating a directory that stores the value of the calculation with the location
- Looping through the columns with openpyxl and the new directory
- Assessing the max length of each column
- Adjusting the colum width with openpyxl
- 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)
