Conditional Formatting / DataBar / Solid Color : can not be read correctly

Issue #1118 duplicate
created an issue


I have a xlsx file with some format.
source file

I want edit values, but keeping the format.


in ver 2.5.9

wb = load_workbook(filename=file)
ws = wb['KOL']
ws.cell(row=1, column=1).value = 'new value''new_test.xlsx')

result (1st open)

While the new_text.xlsx open, the excel(ver.2019) shows "try to fix it".
After fix it, I can open the file, but the dataBar has changed.
first time fixed

  • Color changed.
  • The bar length changed.

Save it to 'new_text2.xlsx', and try to open it again.

result (2nd open)

The excel also shows "try to fix it", but the length of bar is correct.

2nd & after

I save it again, and reopen it, the excel will also ask "try to fix", forever.

I've test it again, I found there is 2 different problem.
The color of DataBar is a single one.

And the other one about "try to fix" is cause by "table".
I tried "trans the table to normal area"(I don't know how it shows in English, I use Chinese Version.) If the sheet has no table, no sort, the problem is solved, no more "fix it".

Comments (8)

  1. CharlieC

    Thanks for the report. From what I can see of the file there is not a lot we can do. When you open the file with openpyxl you should see a warning about the conditional formatting extension that is not supported being lost.

    In the OOXML specification databars always have a gradient. Microsoft subsequently introduced the option to disable the gradient but such extensions are currently not supported. With the gradient the lines appear shorter because they fade to white.

    I'm not sure what you mean with table (to make things more confusing I have a German version of Excel) but I think the command is "convert to a range" (button below "remove duplicates"). However, I don't have any problems opening the files with Excel.

  2. Erimus reporter

    yeah, the gradient databar looks not so clear.
    And excel even can not set TWO color of gradient(otherwise, I can set same color).

    About the TABLE, for example:
    - create a new file, - fill 5 columns & 5 rows - then "select all"(25 cells) - press "Ctrl+T"

    It will trans these cells into the TABLE(what i mean), and the sort arrows will appear.

    I've tried do nothing change, only open xlsx and save it, the new file it's no problem while open.

    But if I do some edit with:

    ws.cell(row=1, column=1).value = 'new value'

    the problem appears.

    I think maybe I used wrong method to change it, shouldn't directly edit (row=1, column=1) of sheet.
    Is that some way to set position of a TABLE in the sheet, like:

    ws['table name'].cell(row=1,column=1).value

    I'm new to openpyxl, and have not read all the docs, do you know some about this?

  3. Erimus reporter

    OK, I've read it.

    And I have to say, the table is very useful for the excel GUI users.

    Table has many good feature:

    • write formula with column names, it's easy for read.
      like [@name]&[@age], instead of [Sheet1!$A2]&[Sheet1!$B2].
    • write formula in one cell of column, whole column will use same formula.
      if not table, they have to drag and duplicate.
    • write something in the row below last row, the style of all ceil in the row, will copy form the last row.
      if not table, they have to drag and copy style only.
    • etc. many useful feature. you can have a try.

    I use python filter data from database, and export to excel, for the peoples who can not coding.
    The home field of them is excel GUI, so keeping formats is important for them.

    I'm appreciate for your works, and just share my usage habit & scene.

    I've read this openpyxl.worksheet.table module — openpyxl 2.5.9 documentation
    but even not understand how to edit table with keeping format.

  4. Log in to comment