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?

Cakewalk

Answer 1

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"

Excavate

Answer 2

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

Florenceflorencia

Answer 3

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)

Tiloine

How do you calculate the cell width in get_height_for_row() for merged cells? – Hutchings

As far as I remember, in merged cells, the first cell is considered the cell address, for example, if you merged cells A3: C3, the address is A3 – Margoriemargot

Answer 4

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

Daina

Can you explain bit more. The only thing that I found for StyleFrame is that is some library to work with excel files. Is there way to switch to StyleFrame using the openpyxl library? Also StyleFrame doesn't work for the newest openpyxl versions. – Willable

We have some data and since we know that we plan to export it to excel file then it makes sense to store it in data structure that will be alike excel files (have columns and rows) and pandas DataFrame is the perfect solution. Since we also would like to design the excel file we can use StyleFrame which allows us to style the DataFrame and once we export it to excel- the style is applied aswell. I recommend you to do the tutorial of StyleFrame in its github repo. – Daina

Answer 5

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.

Advised

Answer 6

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

Twit

Answer 7

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.

Lamentable

Answer 8

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

Capelin