Simulate autofit column in xslxwriter

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)

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)

That URL does not specify what the units are for the third argument to set_column.

The column widths are given in multiples of the width of the '0' character in the font Calibri, size 11 (that's the Excel standard).

I can not find a way to measure the width of the item that I want to insert into the cell.

In order to get a handle on the exact width of a string, you can use tkinter's ability to measure string lengths in pixels, depending on the font/size/weight/etc. If you define a font, e.g.

reference_font = tkinter.font.Font(family='Calibri', size=11)

you can afterwards use its measure method to determine string widths in pixels, e.g.

reference_font.measure('This is a string.')

In order to do this for a cell from your Excel table, you need to take its format into account (it contains all the information on the used font). That means, if you wrote something to your table using worksheet.write(row, col, cell_string, format), you can get the used font like this:

used_font = tkinter.font.Font(family     = format.font_name,                              size       = format.font_size,                              weight     = ('bold' if format.bold else 'normal'),                              slant      = ('italic' if format.italic else 'roman'),                              underline  = format.underline,                              overstrike = format.font_strikeout)

and afterwards determine the cell width as

cell_width = used_font.measure(cell_string+' ')/reference_font.measure('0')

The whitespace is added to the string to provide some margin. This way the results are actually very close to Excel's autofit results, so that I assume Excel is doing just that.

For the tkinter magic to work, a tkinter.Tk() instance (a window) has to be open, therefore the full code for a function that returns the required width of a cell would look like this:

import tkinterimport tkinter.fontdef get_cell_width(cell_string, format = None):  root = tkinter.Tk()  reference_font = tkinter.font.Font(family='Calibri', size=11)  if format:    used_font = tkinter.font.Font(family     = format.font_name,                                  size       = format.font_size,                                  weight     = ('bold' if format.bold else 'normal'),                                  slant      = ('italic' if format.italic else 'roman'),                                  underline  = format.underline,                                  overstrike = format.font_strikeout)  else:    used_font = reference_font  cell_width = used_font.measure(cell_string+' ')/reference_font.measure('0')  root.update_idletasks()  root.destroy()  return cell_width

Of course you would like to get the root handling and reference font creation out of the function, if it is meant to be executed frequently. Also, it might be faster to use a lookup table format->font for your workbook, so that you do not have to define the used font every single time.

Finally, one could take care of line breaks within the cell string:

pixelwidths = (used_font.measure(part) for part in cell_string.split('\n'))cell_width = (max(pixelwidths) + used_font.measure(' '))/reference_font.measure('0')

Also, if you are using the Excel filter function, the dropdown arrow symbol needs another 18 pixels (at 100% zoom in Excel). And there might be merged cells spanning multiple columns... A lot of room for improvements!

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.

If you do not like to keep track within your own data structure, there are at least three ways to go:

(A) Register a write handler to do the job:
You can register a write handler for all standard types. In the handler function, you simply pass on the write command, but also do the bookkeeping wrt. column widths. This way, you only need to read and set the optimal column width in the end (before closing the workbook).

# add worksheet attribute to store column widthsworksheet.colWidths = [0]*number_of_used_columns# register write handlerfor stdtype in [str, int, float, bool, datetime, timedelta]:  worksheet.add_write_handler(stdtype, colWidthTracker)def colWidthTracker(sheet, row, col, value, format):  # update column width  sheet.colWidths[col] = max(sheet.colWidths[col], get_cell_width(value, format))  # forward write command  if isinstance(value, str):    if value == '':      sheet.write_blank(row, col, value, format)    else:      sheet.write_string(row, col, value, format)  elif isinstance(value, int) or isinstance(value, float):    sheet.write_number(row, col, value, format)  elif isinstance(value, bool):    sheet.write_boolean(row, col, value, format)  elif isinstance(value, datetime) or isinstance(value, timedelta):    sheet.write_datetime(row, col, value, format)  else:    raise TypeError('colWidthTracker cannot handle this type.')# and in the end...for col in columns_to_be_autofitted:      worksheet.set_column(col, col, worksheet.colWidths[col])

(B) Use karolyi's answer above to go through the data stored within XlsxWriter's internal variables. However, this is discouraged by the module's author, since it might break in future releases.

(C) Follow the recommendation of jmcnamara: Inherit from and override the default worksheet class and add in some autofit code, like this example: xlsxwriter.readthedocs.io/example_inheritance2.html