CODEWITHSUNDEEP
pythonpython-3.xexcelopenpyxl
ssd
ssd

Reputation: 2422

Why "column width" is set to a different value?

I'm trying to set the dimensions (height & width) of 100 × 100 cells in an already existing Excel workbook but it fails to set the width of columns to the correct value (though row heights are set to correct values). I'm trying to set the column width to 10 but after opening the actual Excel file, the column widths turn out to be 9.29. What am I doing wrong?

from os import name
import openpyxl
from openpyxl.descriptors.base import DateTime
from openpyxl.utils import get_column_letter
import datetime


def format_wb(wb):
    for ws in wb.sheetnames:
        for i in range(1, 101):
            wb[ws].column_dimensions[get_column_letter(i)].width = 10
            wb[ws].row_dimensions[i].height = 20

    return wb


def main():
    file_name = "test"
    wb = openpyxl.load_workbook(f"{file_name}.xlsx")
    wb = format_wb(wb)
    wb.save(f"{file_name}.xlsx")


if __name__ == "__main__":
    main()

Upvotes: 3

Views: 797

Answers (1)

Laurent
Laurent

Reputation: 13458

[EDIT] This, I think, is a more satisfying explanation:

"In Excel, xml stored values and values displayed for user should be different and depend on default font selected in theme (for Colibri difference is 0.7109375).

openpyxl does not load default font info and Excel files created by it use the same default font (Colibri 11).

So, for files saved using openpyxl, 0.7109375 is constant."

Source: https://foss.heptapod.net/openpyxl/openpyxl/-/issues/293

As far as I understand, nothing is wrong with your code. Had the same problem recently, found empirically that if you want a column width equal to an integer number in Excel (10 in your case), you have to set it to that number + 0.71 with openpyxl (10.71 in your case).

Upvotes: 2

Related Questions