Openpyxl auto-height row

Question

I'm trying to set wrap text. But when i using wrap text row doesn't change height automatically. How can I set auto-height row?

Answer 1

worksheet.row_dimensions[1].auto_height = True

Tested with openpyxl==3.1.2

Works even with locked document, when you can't manually change row height

Answer 2

What has worked for me is:

worksheet.row_dimensions[17] = worksheet.row_dimensions[-1]

So how does this work? Row 17 is the row which I want to set auto-height for. Row -1 doesn't exist, and has no height settings. Assigning the -1 to 17 clears height information. And when you open the excel file, row's height gets adjusted to wrapped text.

Answer 3

For me setting height to None does not work, since I am not using MS Excel, so I built an extension of the Егор Зенкин answer. It is respecting merged cells and multiline cells as well!

def set_row_auto_height(wb: openpyxl.Workbook):
    """Set auto row height for the whole excel file."""
    sheets = wb.sheetnames
    for sheet in sheets:
        for row_idx in range(0, wb[sheet].max_row):
            wb[sheet].row_dimensions[row_idx + 1].height = _get_height_for_row(wb[sheet], row_idx)
    return wb


def _get_column_width_by_cell(sheet: Worksheet, cell: Cell):
    """Returns width of the column. Calculates merged cells as well."""
    if cell.coordinate not in sheet.merged_cells:
        return sheet.column_dimensions[get_column_letter(cell.column)].width
    ranges = [range_ for range_ in sheet.merged_cells.ranges if cell.coordinate in range_]
    if not ranges:
        return sheet.column_dimensions[get_column_letter(cell.column)].width
    range_ = ranges[0]
    return sum(sheet.column_dimensions[get_column_letter(col[1])].width for col in range_.top)


def _get_height_for_row(sheet: Worksheet, row_number: int, font_size: int = 12):
    """Returns height for row, respecting merge columns and multiline cell values."""
    font_params = factor_of_font_size_to_width[font_size]
    row = list(sheet.rows)[row_number]
    height = font_params["height"]
    max_height = 0
    for cell in row:
        words_count_at_one_row = _get_column_width_by_cell(sheet, cell) / font_params["factor"]
        # Calculating amount of lines
        lines = ceil(len(str(cell.value)) / words_count_at_one_row)
        new_lines = len(str(cell.value).split("\n"))
        # If amount of characters is low, but it is multiline cell
        # we should count that as well
        lines = max(lines, new_lines)
        height = max(height, lines * font_params["height"])
        max_height = max(max_height, height)
    return height

Answer 4

You can easily do the following steps to auto-height Excel row with openpyxl.

For cell by which you want to auto-height by its content (or for each of cells in the row):

cell.alignment = Alignment(wrap_text=True)

Then for your row:

worksheet.row_dimensions[row_num].height = None

Doing that way you'll achieve the same behaviour like using "AutoHeight" in Excel.

Answer 5

You can just set row height to None. If you do that, then within the .xlsx file's x1/worksheets/sheet.xml file the row element will have no explicit value set for row height. Excel will then automatically adjust the row height based on the content.

#Autofit the row height
sheet.row_dimensions[1].height = None

Answer 6

Try like this, its work for me:

from math import ceil

factor_of_font_size_to_width = {
    # TODO: other sizes
    12: {
        "factor": 0.8,  # width / count of symbols at row
        "height": 16
    }
}


def get_height_for_row(sheet, row_number, font_size=12):
    font_params = factor_of_font_size_to_width[font_size]
    row = list(sheet.rows)[row_number]
    height = font_params["height"]

    for cell in row:
        words_count_at_one_row = sheet.column_dimensions[cell.column_letter].width / font_params["factor"]
        lines = ceil(len(str(cell.value)) / words_count_at_one_row)
        height = max(height, lines * font_params["height"])

    return height

for i in range(0, sheet.max_row):
    # [i + 1] - because the lines are numbered starting at 1
    sheet.row_dimensions[i + 1].height = get_height_for_row(sheet, i)

Answer 7

You can use row_dimensions or column_dimensions property to set height or width:

# set the height of the row 
sheet.row_dimensions[1].height = 20
  
# set the width of the column 
sheet.column_dimensions['B'].width = 20

Answer 8

Try:

col_width = []
for i in range(len(next(ws.iter_rows()))):
    col_letter = get_column_letter(i + 1)

    minimum_width = 20
    current_width = ws.column_dimensions[col_letter].width
    if not current_width or current_width < minimum_width:
        ws.column_dimensions[col_letter].width = minimum_width

    col_width.append(ws.column_dimensions[col_letter].width)

for i, row in enumerate(ws):
    default_height = 12.5  # Corresponding to font size 12

    multiples_of_font_size = [default_height]
    for j, cell in enumerate(row):
        wrap_text = True
        vertical = "top"
        if cell.value is not None:
            mul = 0
            for v in str(cell.value).split('\n'):
                mul += math.ceil(len(v) / col_width[j]) * cell.font.size

            if mul > 0:
                multiples_of_font_size.append(mul)

        cell.alignment = Alignment(wrap_text=wrap_text, vertical=vertical)

    original_height = ws.row_dimensions[i + 1].height
    if original_height is None:
        original_height = default_height

    new_height = max(multiples_of_font_size)
    if original_height < new_height:
        ws.row_dimensions[i + 1].height = new_height

Most updated version.

It's not perfect though. If you want better, you might have to use either monospace fonts or pillow.

Answer 9

You need to look at the RowDimension object for the relevant row, specifically the height attribute:

rd = ws.row_dimensions[3] # get dimension for row 3
rd.height = 25 # value in points, there is no "auto"

Answer 10

If your data stored in DataFrame, I would recommend you to use StyleFrame. It automatically auto-adjust columns width and rows height and also have some nice features.

styleframe