How to: Autosize columns in excel output - Macro attached

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)

auto_adjust_column_width.yxmc

auto_adjust_example.yxmd

Python

Macros

Accepted answers

All comments

momaeder

This is awesome, AlexEntz! 😄

DavidSta

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

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

Hi sunilk,

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

kcollado

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

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).

kcollado

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!

dsandmann

Hi -

The base of this was a great starting point! I made some updates to get things going a bit more like expected, I'm sure there are further updates that could be made to improve things further. One of the unexpected behaviors of your current code is that on multi-sheet workbooks, the cells across the sheets all end up the same width (even if they shouldn't be based on the contents).

Here is what I came up with to get around that issue -

#regex import to limit data getting sent to by sheet processing
import re

#Looping through the tables to auto-adjust specific cells
for workbook in wb_list:
    wb = openpyxl.load_workbook(workbook)
    #move the formula workbook calculation up, so it doesn't call multiple times
    xl_model = formulas.ExcelModel().load(workbook).finish()
    solutions = xl_model.calculate()
    all_items = solutions.items()
    
    for sheet in wb.sheetnames:
        ws = wb[sheet]
        wb.save(workbook)

        #regex matching to sheet name on the key values, only sends the vals for the current sheet for width changes
        sheet_items = {k:v for k,v in all_items if re.match(f".*?\]{sheet}'.*?", k)}
 
        #self generated column list
        cols = []
        vals = []

        # Iterate through cells and values to generate a string that matches openpyxl cell definition
        for k, v in zip(sheet_items.keys(), sheet_items.values()):
            v = list(v.value)
            v = list(v[0])
            v = v[0]
            vals.append(v)
            k = k.partition("!")[2]
            cols.append(k)
            
        #Creating a new dictionary with changed values
        zip_iterator = zip(cols, vals)
        dictionary = dict(zip_iterator)

        for col in ws.columns:
            max_length = 0
            column = col[0].column_letter  # Get the column name
            for cell in col:
                new_cell = cell.coordinate
                for item in dictionary:
                    if new_cell == item:
                        val_length = dictionary[item]
                        try:  # Necessary to avoid error on empty cells
                            if int(val_length) and "e" not in str(val_length):  # Necessary for exponential mathematical writing
                                max_length = len(str(val_length)) + 7
                            elif "e" in str(val_length):
                                new_number = int(val_length)
                                max_length = len(str(new_number)) + 13 * 1.2
                        except:
                            try:
                                if len(str(val_length)) > max_length:
                                    max_length = len(str(val_length)) + 5
                            except:
                                pass
            adjusted_width = max_length
            ws.column_dimensions[column].width = adjusted_width  # applying width from data only to the file with formulas

        wb.save(workbook)

Nuno_Meneses

Hi Dsandmann

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

dsandmann

@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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703