Fixing Python xlsxwriter That Applies Wrong Row Height After autofit

You call worksheet.autofit(), open the file in Excel, and rows are either clipped so the text is cut off or so tall that half the screen is wasted. The method sounds like it should just work, but xlsxwriter's autofit has real limitations that bite you as soon as your data includes wrapped text, multi-line strings, or custom fonts.

This article explains the root cause, shows you the math Excel uses to size rows, and gives you a drop-in helper function you can use instead.

What you'll learn

• Why autofit() gets row heights wrong for wrapped and multi-line cells
• How Excel converts font size to row height in points
• How to calculate the correct row height for single-line and wrapped cells
• How to apply those heights with worksheet.set_row()
• Edge cases to watch for with merged cells and custom fonts

What actually goes wrong

Consider a simple report where some cells have long descriptions and wrap_text is enabled. You write the file, call autofit(), and the row heights are wrong. Specifically, autofit sizes rows based on column widths it thinks are correct, but it cannot simulate how Excel actually reflows text during rendering.

The result: rows are too short when text wraps across more lines than xlsxwriter predicted, or too tall when autofit adds extra padding that Excel would not.

Why autofit undershoots (or overshoots) row height

xlsxwriter's autofit() was added primarily for column widths. The row-height portion is a best-effort approximation based on character counts and assumed column widths — it does not call any Excel rendering engine. When text wraps, the actual number of visual lines depends on the rendered pixel width of each character in the chosen font, which varies by font family, weight, and size.

xlsxwriter uses a fixed average character width for this estimate. For common fonts like Calibri 11pt it is close enough for short strings. For longer strings, narrow columns, or fonts with very different character metrics (like Courier New vs Arial Narrow), the estimate drifts enough to clip or pad rows visibly.

If you are also writing wrapped cells with newline characters (\n), autofit does not always count those newlines when calculating lines, which causes it to set a height that fits only the first line.

How xlsxwriter's autofit works under the hood

When you call worksheet.autofit(), xlsxwriter iterates over every cell it has recorded, estimates the string width in characters, and derives a column width. For row height, it checks whether wrap_text is set and tries to estimate the number of wrapped lines. The estimate is based on dividing the string length by the column width in characters — a very rough heuristic.

There is no font metrics lookup and no layout pass. The library cannot know that "W" is three times wider than "i" in a proportional font. Once you accept that, the fix becomes obvious: calculate the row height yourself before writing, using the data you already have.

For more context on how xlsxwriter handles formatting edge cases, the article on fixing xlsxwriter that truncates long text in wrapped cells covers the related problem of cells that clip content even after you set heights manually.

The font-height-to-row-height formula Excel uses

Excel stores row height in points (1 point = 1/72 inch). The default row height for Calibri 11pt is 15 points. The general formula for the minimum row height to show n lines of text at a given font size is:

row_height = (font_size_pt * 1.2) * n + 3

The 1.2 multiplier is the standard line-height ratio Excel uses internally. The + 3 is the top and bottom cell padding combined (approximately 1.5pt each). For Calibri 11pt with one line: (11 * 1.2) * 1 + 3 = 16.2, which rounds to Excel's actual default of around 15–16pt depending on the system DPI. You can tune the padding constant if your template looks off, but 3 is a solid starting point.

Calculating correct row height for single-line cells

For rows where wrap_text is off or all values fit on one line, the calculation is straightforward. Pass the font size for the tallest cell in the row:

def single_line_height(font_size_pt: float, padding_pt: float = 3.0) -> float:
    """Minimum row height in points for a single line of text."""
    return round(font_size_pt * 1.2 + padding_pt, 2)

Use this with worksheet.set_row(row_index, height) right after you write the row data. Never mix this with a subsequent autofit() call — autofit will overwrite heights you set.

import xlsxwriter

workbook = xlsxwriter.Workbook("report.xlsx")
worksheet = workbook.add_worksheet()

font_size = 11
height = single_line_height(font_size)  # 16.2

worksheet.write(0, 0, "Product")
worksheet.write(0, 1, "Price")
worksheet.set_row(0, height)

workbook.close()

Calculating correct row height for wrapped multi-line cells

This is where autofit breaks down most visibly. For a wrapped cell, you need to estimate how many lines the text will occupy given the column width. You can do this with a character-width estimate per font, or use the Python math module for a quick approximation.

import math

# Average character width in "Excel character units" for Calibri 11pt.
# One Excel column-width unit ≈ the width of the digit '0' in the default font.
# For Calibri 11pt, that is approximately 7 pixels at 96 DPI.
AVG_CHAR_WIDTH = 7  # pixels at 96 DPI for Calibri 11pt

def col_width_to_pixels(col_width_chars: float, max_digit_width: int = 7) -> float:
    """Convert an xlsxwriter column width (character units) to pixels."""
    # Excel's internal formula for column width in pixels:
    # pixels = int(col_width * max_digit_width) + 5  (for widths > 1)
    return int(col_width_chars * max_digit_width) + 5

def estimate_line_count(
    text: str,
    col_width_chars: float,
    avg_char_width_px: int = AVG_CHAR_WIDTH,
) -> int:
    """Estimate how many lines a string occupies in a wrapped cell."""
    if not text:
        return 1
    # Count explicit newlines first
    lines = text.split("\n")
    total = 0
    col_px = col_width_to_pixels(col_width_chars)
    for line in lines:
        text_px = len(line) * avg_char_width_px
        wrapped = math.ceil(text_px / col_px) if col_px > 0 else 1
        total += max(wrapped, 1)
    return total

def wrapped_row_height(
    text: str,
    col_width_chars: float,
    font_size_pt: float = 11,
    padding_pt: float = 3.0,
) -> float:
    """Return the row height in points for a wrapped cell."""
    n = estimate_line_count(text, col_width_chars)
    return round(font_size_pt * 1.2 * n + padding_pt, 2)

Now apply it when building your sheet:

import xlsxwriter

workbook = xlsxwriter.Workbook("wrapped_report.xlsx")
worksheet = workbook.add_worksheet()

wrap_fmt = workbook.add_format({"text_wrap": True, "valign": "top"})

# Column B is 40 character-units wide
COL_B_WIDTH = 40
worksheet.set_column(1, 1, COL_B_WIDTH)

rows = [
    ("SKU-001", "Short description"),
    ("SKU-002", "This is a much longer product description that will definitely wrap across multiple lines when rendered inside Excel's grid view."),
    ("SKU-003", "Line one of notes.\nLine two of notes.\nLine three."),
]

for row_idx, (sku, desc) in enumerate(rows):
    worksheet.write(row_idx, 0, sku)
    worksheet.write(row_idx, 1, desc, wrap_fmt)
    height = wrapped_row_height(desc, COL_B_WIDTH)
    worksheet.set_row(row_idx, height)

workbook.close()

When a row has multiple wrapped columns, take the maximum estimated height across all wrapped cells in that row before calling set_row().

Handling merged cells and custom fonts

Merged cells

Merged cells are the hardest case. Excel does not auto-expand row height for merged cells at all — it only auto-sizes the height of rows that contain unmerged wrapped content. If you merge cells across rows, you need to distribute the required height across those rows manually or accept that you are essentially working with a fixed layout.

For single-row merges (cells merged across columns), the height calculation is the same as a normal wrapped cell. The only twist is that the effective column width is the sum of the widths of all merged columns:

merged_col_width = sum([col_widths[c] for c in range(first_col, last_col + 1)])
height = wrapped_row_height(text, merged_col_width)
worksheet.set_row(row_idx, height)

For more detail on how xlsxwriter handles merged cell formatting, see the article on fixing xlsxwriter that ignores cell formats when writing merged cells.

Custom fonts

The AVG_CHAR_WIDTH constant of 7 pixels is calibrated for Calibri 11pt at 96 DPI. If you use a different font, you need to adjust it. Here are approximate values for common fonts at 11pt:

For fonts significantly larger than 11pt, scale proportionally: a 14pt Calibri cell has an average character width of roughly 7 * (14 / 11) ≈ 9 pixels.

If you are generating Excel files that originate from a Pandas DataFrame, the article on fixing Pandas DataFrame that exports wrong column order to Excel walks through a practical xlsxwriter integration pattern that pairs well with the height-setting approach here.

Common pitfalls

• Calling autofit() after set_row() — autofit will silently overwrite every row height you set. Either use autofit exclusively for column widths and skip it for rows, or skip autofit entirely and manage both manually.
• Forgetting to split on \n before estimating line count — a 200-character string with two newlines is three short strings, not one long one. The estimate_line_count() function above handles this, but if you write your own version, it is easy to miss.
• Assuming column width in pixels equals character units times 7 — Excel adds a small padding constant per column (roughly 5px). Ignoring it causes slight underestimates on narrow columns and barely matters on wide ones. The col_width_to_pixels() helper above includes it.
• Applying the same height to all rows — a common shortcut that works for uniform data but clips rows with unexpectedly long values. Always calculate per-row.
• Not accounting for bold text — bold characters are wider than their regular-weight equivalents. If your headers use bold 11pt Calibri, bump avg_char_width_px to 8 for those rows.

If you are running into other xlsxwriter formatting issues in the same project, the article on fixing xlsxwriter that writes numbers as text and breaks SUM formulas covers another common source of silent errors in generated Excel files.

Wrapping up

xlsxwriter's autofit is a useful shortcut for column widths, but it is not reliable for row heights — especially when your cells use wrapped text, explicit newlines, or non-default fonts. The fix is to skip autofit for rows and calculate heights yourself using the font-size-to-points formula and a character-width estimate.

Here are your concrete next steps:

1. Copy the estimate_line_count() and wrapped_row_height() helpers into a shared utility module in your project.
2. Remove any worksheet.autofit() call that was intended to fix row heights — leave it only if you still want it for column widths, and note that it will not touch rows you have already sized with set_row().
3. Iterate over your data before writing and build a row_heights dict keyed by row index, taking the maximum height across all wrapped columns per row.
4. Tune AVG_CHAR_WIDTH for your specific font by generating a test file, measuring a few rows visually in Excel, and adjusting until they match.
5. Add a regression check to your test suite: open the generated file with openpyxl and assert that row heights are above a minimum threshold for rows containing long strings.