Simulate autofit column in xslxwriter

Solution 1:

As a general rule, you want the width of the columns a bit larger than the size of the longest string in the column. The with of 1 unit of the xlsxwriter columns is about equal to the width of one character. So, you can simulate autofit by setting each column to the max number of characters in that column.

Per example, I tend to use the code below when working with pandas dataframes and xlsxwriter.

It first finds the maximum width of the index, which is always the left column for a pandas to excel rendered dataframe. Then, it returns the maximum of all values and the column name for each of the remaining columns moving left to right.

It shouldn't be too difficult to adapt this code for whatever data you are using.

def get_col_widths(dataframe):
    # First we find the maximum length of the index column   
    idx_max = max([len(str(s)) for s in dataframe.index.values] + [len(str(dataframe.index.name))])
    # Then, we concatenate this to the max of the lengths of column name and its values for each column, left to right
    return [idx_max] + [max([len(str(s)) for s in dataframe[col].values] + [len(col)]) for col in dataframe.columns]

for i, width in enumerate(get_col_widths(dataframe)):
    worksheet.set_column(i, i, width)

Solution 2:

I agree with Cole Diamond. I needed to do something very similar, it worked fine for me. where self.columns is my list of columns

def set_column_width(self):
    length_list = [len(x) for x in self.columns]
    for i, width in enumerate(length_list):
        self.worksheet.set_column(i, i, width)