Documentation of colum_dimension styles lacking

Issue #390 resolved
Wolfgang Scherer created an issue

From the styles documentation, I came to expect that column styles are automatically applied to all cells without an explict style. And LibreOffice Calc does in fact behave so. However Excel 2010/2013 does not.

I think it would be nice to document this behaviour, so other users do not have to re-discover the Excel-Quirks.

Here is, what I finally came up with:

def set_column_style(worksheet, start, end, style, force=None, default_width=None): # ||:fnc:||
    """Set default column style and apply to cells in range.

    :param worksheet: worksheet
    :param start: first column (index or letter)
    :param end: last column (index or letter)
    :param style: :class:`openpyxl.styles.Style` instance.
    :param force: if True, overwrite explicit cell style. Otherwise, only set style for cell without an explict style.
    :param default_width: If the column width is not set,
      the column is displayed

      - with width = 0 in Excel 2010, which does not allow expanding the column with the mouse
      - with minimal width in Excel 2013, which allows expanding the column with the mouse
      - with standard width in LibreOffice

    While LibreOffice applies a column style to all cells in the
    column, when the workbook is loaded, Excel 2010/2013 does no
    such thing.

    However, if a value is entered in an unused column cell it does
    aquire the default column style in Excel 2010/2013 and

    if default_width is None:
        default_width = 10.5703125 # base width 10.0 + extra

    col_interval = get_column_interval(start, end)

    for col in col_interval:
        col_dim = worksheet.column_dimensions[col]
        # There is a bug in openpyxl < 2.1.3: worksheet is not passed to
        # RowDimension()/ColumnDimension() in :meth:`openpyxl.worksheet.Worksheet.append`.
        # Status: Pull request supplied and accepted.
        col_dim.worksheet = worksheet = style
        if col_dim.width is None:
            col_dim.width = default_width
            # col_dim.bestFit = True # no effect

    col_range = sformat('{0}1:{1}{2}', col_interval[0], col_interval[-1], worksheet.max_row)
    for row in worksheet[col_range]:
        for cell in row:
            if force or not cell.has_style:
       = style

Comments (4)

  1. CharlieC

    Styles are simply dreadful in Office OpenXML. I don't there's any other way to express it. The specification contains no information on how to interpret the various possibilities so we avoid them and leave it to client code (to guess). 2.2 should contain support for named styles which have an implicit relationship with cell styles where cell styles override named styles. This makes some kind of sense, though the implementation is unclear: you can easily have multiple named styles with the same name, with no hint of how to resolve this. I've submitted a request for clarification to the ECMA group secretary.

  2. Wolfgang Scherer reporter

    I heartily agree! Looking at the XML-Files I get the impression that the whole thing somehow resembles the assembler output from a C-Compiler :) And I bow in respect for the task you are undertaking!

    Just to make my point clear: I do not propose to add this function to the body of openpyxl. It is meant to clarify, how Calc/Excel differ in interpretation of column styles. (With Excel being very counter-intuitive).

    I started out with the sample code in styles.rst and spent quite some time to figure out why Calc behaves as expected and why Excel effectively hides the styled columns without ever applying the style to any generated cells.

    So, I thought that augmenting the section in styles.rst with the (reverse-engineered) information and an example function similar to the one above would help with pragmatic generation of workbooks. Basically, if you leave this issue open for others to find, the goal is already reached.

    If you wish, I can also take the time to enhance the corresponding section in style.rst and prepare a pull request.

  3. CharlieC

    Happy to accept improvements to the documentation. That's much better than hoping people will read a bug report.

  4. CharlieC

    The documentation has been updated to reflect the specification: column and row styles will only be applied to cells subsequently created (in Excel).

  5. Log in to comment