How to: Autosize columns in excel output - Macro attached

[AlexEntz]

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)

[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!

[liwagab]

Hi,

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

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

Below are the configuration each tool:

Input text to declare you input & output path

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

Blob Output - save your file on this specified path

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

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

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