Column width issue

Issue #293 invalid
Igor Kovrigin
created an issue

Sample create workbook and set column width to 22.

Notice in excel column width is always lower on 0.7109375 than in openpyxl Same issue on read and write. So if you load file and save. Width in saved file will be same as in template file.

from openpyxl import Workbook

wb = Workbook()
ws = wb.get_active_sheet()
ws['A1'] = 'test'
ws.column_dimensions["A"].width = 22
wb.save(filename = 'test_book.xlsx')

Comments (13)

  1. CharlieC

    Bildschirmfoto 2014-03-23 um 17.02.22.png

    We store the value as 22. The problem is that for some reason Excel stores column widths in relation to the number of characters whereas everything else is either points or pixels which are convertible. So the same file on my Mac reports a column width of 6.21 cm.

    I'm not sure how much we can do about this to be honest.

  2. Igor Kovrigin reporter

    Yes you right result width is font dependant and lib read width from xml correctly. But excel stores value increased on same value (dependent on default font). Sorry. I will try to understand why stored and displayed values are different later.

  3. Igor Kovrigin reporter

    http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.column(v=office.14).aspx

    see 'width (Column Width)' description.

    So xml stored values and values displayed for user should be different and depends on default font selected in theme (for Colibri difference is 0.7109375).

    openpyxl does not load default font info and excel files created by openpyxl uses the same default (Colibri 11 font).

    So for files saved using openpyxl 0.7109375 is constant.

  4. CharlieC

    We don't care about the font - it's up to the client code. I think the default works with Arial as the typeface but I can't check at the moment.

    Are you proposing a change? There are some examples of calculating the value out there but they seem to be Windows specific so we can't implement them.

  5. CharlieC

    From the spec

    Column width measured as the number of characters of the maximum digit width of the numbers 0, 1, 2, ..., 9 as rendered in the normal style's font. There are 4 pixels of margin padding (two on each side), plus 1 pixel padding for the gridlines.
    width = Truncate([{Number of Characters} * {Maximum Digit Width} + {5 pixel padding}]/{Maximum Digit Width}*256)/256
    

    The thing that is OS specific is "maximum digit width". AFAIK there's no way in Python to get this.

  6. Elijas

    I've observed that the distortion from openpyxl can be exactly desribed in terms of

            if x < 0.0744:
                y = 0
            elif x < 0.2188:
                y = 0.08
            elif x < 0.3596:
                y = 0.17
            elif x < 0.5041:
                y = 0.25
            elif x < 0.6449:
                y = 0.33
            elif x < 0.7893:
                y = 0.42
            elif x < 0.9297:
                y = 0.5
            elif x < 1.0746:
                y = 0.58
            elif x < 1.2190:
                y = 0.67
            ...
    

    and

    y = x - 0.71
    

    for bigger column width integers (starting with 2)

    I've written and logical inverse for the first function (100% accurate) and the logical inverse (x = y + 0.71) for the second function (100% accurate), given the inputs are greater and equal to 2 and integers.

    All are covered with automated tests (both, the distortion simulation function with its logical inverse and the functional test, using xlwings and reading actual value from Excel)

    As I understand the problem, the exact distortion is dependent from Font, so this at least perfectly works for the default font.

  7. Log in to comment