Search code examples
pythonopenpyxlxlsx

Use openpyxl to format the xlsx (number format, fit the text and shrink the texts)

I want to use openpyxl(do not use other libs) to edit the xlsx with following code:

from openpyxl import Workbook
book = Workbook()
sheet = book.active
rows = [['name', 'This is number1', 'This is number2', 'longstr'],
['a', 312318.231, -2312.2, 'aaaaaaaaaaaa\naaaaaaaaaaaa\naaaaaaaaaaaa'],
['a', 312318.231, -2312.2, 'aaaaaaaaaaaa\naaaaaaaaaaaa\naaaaaaaaaaaa']
]
for row in rows:
    sheet.append(row)
book.save('text.xlsx')

and here is the result:

enter image description here

What I want are

  1. Format the numbers in column 2, 3 with comma style (thousands separator) and decreasing into no decimal. I used "{:,.0f}".format() in the code, but the output in the xlsx becomes a string.

  2. Let cells fit with the text for column 1, 2, 3

  3. Shrink the texts to fit the cells for column 4 (not for title). In excel, the operations is Format Cells -> Alignment -> Shrink to fit

enter image description here

Above is the simplified example, in the real case we have thousands of rows and known number of columns (say 4 columns).

I am new in using openpyxl , could you help me to obtain my goal?

Solution

  • column format

    To control the number display format in Excel, you set the cell attribute like this:

    cell.number_format = "0.0000" # 4 decimal places

    column width

    Set the width is similar, but as a column attribute:

    sheet.column_dimensions['A'].width = 12.5

    Unfortunately, you need to calculate the width manually and there is no good way to do that. I found an example on the web that list out all width metrics for Calibri 11 font (default in Excel). You can take a look on how the column width is calculated over there.