There is another workaround to simulate Autofit that I've found Github site of xlsxwriter. I've modified it to return the approximate size of horizontal text (column width) or 90° rotated text (row height):

from PIL import ImageFont

def get_cell_size(value, font_name, font_size, dimension="width"):
    """ value: cell content
        font_name: The name of the font in the target cell
        font_size: The size of the font in the target cell """
    font = ImageFont.truetype(font_name, size=font_size)
    (size, h) = font.getsize(str(value))
    if dimension == "height":
        return size * 0.92   # fit value experimentally determined
    return size * 0.13       # fit value experimentally determined

This doesn't address bold text or other format elements that might affect the text size. Otherwise it works pretty well.

To find the width for your columns for autofit:

def get_col_width(data, font_name, font_size, min_width=1):
    """ Assume 'data' to be an iterable (rows) of iterables (columns / cells)
    Also, every cell is assumed to have the same font and font size.
    Returns a list with the autofit-width per column """
    colwidth = [min_width for col in data[0]]
    for x, row in enumerate(data):
        for y, value in enumerate(row):
            colwidth[y] = max(colwidth[y], get_cell_size(value, font_name, font_size))
    return colwidth    

There is another workaround to simulate Autofit that I've found on the Github site of xlsxwriter. I've modified it to return the approximate size of horizontal text (column width) or 90° rotated text (row height):

from PIL import ImageFont

def get_cell_size(value, font_name, font_size, dimension="width"):
    """ value: cell content
        font_name: The name of the font in the target cell
        font_size: The size of the font in the target cell """
    font = ImageFont.truetype(font_name, size=font_size)
    (size, h) = font.getsize(str(value))
    if dimension == "height":
        return size * 0.92   # fit value experimentally determined
    return size * 0.13       # fit value experimentally determined

This doesn't address bold text or other format elements that might affect the text size. Otherwise it works pretty well.

To find the width for your columns for autofit:

def get_col_width(data, font_name, font_size, min_width=1):
    """ Assume 'data' to be an iterable (rows) of iterables (columns / cells)
    Also, every cell is assumed to have the same font and font size.
    Returns a list with the autofit-width per column """
    colwidth = [min_width for col in data[0]]
    for x, row in enumerate(data):
        for y, value in enumerate(row):
            colwidth[y] = max(colwidth[y], get_cell_size(value, font_name, font_size))
    return colwidth    

There is another workaround to simulate Autofit that I've found Github site of xlsxwriter. I've modified it to return the approximate size of horizontal text (column width) or 90° rotated text (row height):

from PIL import ImageFont

def get_cell_size(value, font_name, font_size, dimension="width"):
    """ value: cell content
        font_name: The name of the font in the target cell
        font_size: The size of the font in the target cell """
    font = ImageFont.truetype(font_name, size=font_size)
    (size, h) = font.getsize(str(value))
    if dimension == "height":
        return size * 0.92   # fit value experimentally determined
    return size * 0.13       # fit value experimentally determined

This doesn't address bold text or other format elements that might affect the text size. Otherwise it works pretty well.

There is another workaround to simulate Autofit that I've found Github site of xlsxwriter. I've modified it to return the approximate size of horizontal text (column width) or 90° rotated text (row height):

from PIL import ImageFont

def get_cell_size(value, font_name, font_size, dimension="width"):
    """ value: cell content
        font_name: The name of the font in the target cell
        font_size: The size of the font in the target cell """
    font = ImageFont.truetype(font_name, size=font_size)
    (size, h) = font.getsize(str(value))
    if dimension == "height":
        return size * 0.92   # fit value experimentally determined
    return size * 0.13       # fit value experimentally determined

This doesn't address bold text or other format elements that might affect the text size. Otherwise it works pretty well.

To find the width for your columns for autofit:

def get_col_width(data, font_name, font_size, min_width=1):
    """ Assume 'data' to be an iterable (rows) of iterables (columns / cells)
    Also, every cell is assumed to have the same font and font size.
    Returns a list with the autofit-width per column """
    colwidth = [min_width for col in data[0]]
    for x, row in enumerate(data):
        for y, value in enumerate(row):
            colwidth[y] = max(colwidth[y], get_cell_size(value, font_name, font_size))
    return colwidth