CODEWITHSUNDEEP
pythonuser-interfaceopenpyxl
Iceandele
Iceandele

Reputation: 660

Python get the height or number of lines for a string given a font and container width

I am trying to generate a speadsheet using openpyxl that contains data in the following format. where all cells should be wrapped for their given width.

+-----------+-------------------+-----+
| Long text | More Long text h..| num |
| Here....  +-------------------+-----+
|           | More Long text h..| num |
+-----------+-------------------+-----+

The problem is that the row heights are fixed and the text gets cut off.

openpyxl doesn't seem to have a auto row height feature, so I would have to calculate what the height or line-count is for a given font and width.

I have looked around for packages that do this but with no luck. Any ideas or approaches to solve this problem would be greatly appreciated, Thanks!

Edit:

# this generates the example above
from openpyxl.styles import *
from openpyxl import Workbook

alignment = Alignment (
    horizontal='center',
    vertical='center',
    wrap_text= True,
)
font = Font(name='Calibri',
    size=11,
)

wb = Workbook()
ws = wb.active

ws.merge_cells("A1:A2")
ws["A1"] = "Long text here..."

long_string = "More Long text here..."

ws["B1"], ws["B2"] = long_string
# sets a fixed width for column `B`
ws.column_dimensions["B"].width = 50
# sets a height for row 1
ws.row_dimensions[1].height = get_row_height(text=long_text, width=50)

ws["C1"], ws["C2"] = 0

def get_row_height(text, width):
    # this magic function returns how many lines `text` takes up

Before posting this question, I tried playing around with tkinter, but the result wasn't exactly what I wanted

from tkinter import Tk, BOTH, Y, END, Text, font as f
root = Tk()
font = f.Font(family="Calibri", size=11)
line_height = font.metrics("linespace")
T = Text(root, width=30, font=font)
T.pack(fill=BOTH, expand=Y)
T.insert(END, "THIS IS A VERY VERY VERY VERY VERY VERY VERY LONG TEXT")
num_lines = int(T.index('end-1c').split('.')[0])

Upvotes: 3

Views: 1752

Answers (1)

Charlie Clark
Charlie Clark

Reputation: 19557

Unfortunately, the formatting that openpyxl can apply is less than you hope. Particularly row heights and column widths are entirely dependent upon the reading application and rely on things like the operating system.

Furthermore, openpyxl follows the specification rather than Excel so, for example, applying a style to a Row or ColumnDimension is an instruction for Excel to format future cells not existing ones. The specification contains no information whatsoever about how merged cells should be formatted. A bug report was submitted over a year ago but we're still waiting for clarification.

To achieve the best results you should be prepared to dissect an existing worksheet and recreate the styles from it. NB. this is a bit tricky due to the use of pointers in the OOXML format.

Upvotes: 3

Related Questions