python - Simulate autofit column in xslxwriter

Question

I would like to simulate the Excel autofit function in Python's xlsxwriter. According to this url, it is not directly supported: http://xlsxwriter.readthedocs.io/worksheet.html

However, it should be quite straightforward to loop through each cell on the sheet and determine the maximum size for the column and just use worksheet.set_column(row, col, width) to set the width.

The complications that is keeping me from just writing this are:

1. That URL does not specify what the units are for the third argument to set_column.
2. I can not find a way to measure the width of the item that I want to insert into the cell.
3. xlsxwriter does not appear to have a method to read back a particular cell. This means I need to keep track of each cell width as I write the cell. It would be better if I could just loop through all the cells, that way a generic routine could be written.

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