Number Format not refreshing cells?

Issue #376 invalid
vposud created an issue

To whom this may concern,

I don't know if this is an issue with openpyxl or not but when applying a Number Format to a cell it always sets the correct format in excel because when i right click and go to format cells it shows the correct format but it does not actual refresh the cell so the format doesn't apply until you double click on the cell to refresh it. So when loading up the excel after it gets created none of the cells have the formatting until you refresh the cells manually. Is anyone else experiencing this same issue?

Thank you,

-Vincent

Comments (10)

  1. CharlieC

    I suspect you're experiencing the same as #372. This has already been addressed and will be in the next release but it would be great if you could try this with a checkout of the 2.1 branch and let us know whether you still have the problem.

  2. vposud reporter

    Hi Charlie,

    I just tried it out with a 2.1 branch and i did have the same issue i was having with the 2.2 branch. Thank you for responding so quickly i appreciate it very much.

    Thank you and have a nice day!

    -Vincent

  3. CharlieC

    I didn't realise you were working with a 2.2 checkout. This already includes the fix from 2.1

    Can you provide an example of what you mean as I don't fully understand the description. Are you creating files from scratch? or working with existing ones?

  4. vposud reporter

    Oh ok, Well honestly i'm not even sure if this is a bug with openpyxl or not because it does seem to work in a way. Here is a snippet of the code that creates the excel file from scratch.

        wb = Workbook()
        ws = wb.active
        ws.title = "Test"
        col = 1
        i = 3
        ws['D3'].number_format = "#,###"
        while i < len(Row1) + 3:
            col = 1
            ws.cell(row=i,column=col).value = Row1[i - 3]
            ws.cell(row=i,column=col + 1).value = Row2[i - 3]
            ws.cell(row=i,column=col + 2).value = Row3[i - 3]
            ws.cell(row=i,column=col + 3).value = Row4[i - 3]
            ws.cell(row=i,column=col + 4).value = Row5[i - 3]
            ws.cell(row=i,column=col + 5).value = Row6[i - 3]
            ws.cell(row=i,column=col + 6).value = Row7[i - 3]
            i += 1
        ws.cell(row=1,column=1).value = "DASDSAD"
        wb.save(r"\\tga-ny-dc02\folderredirection$\vroth\Desktop\ewqewqe.xlsx")
    

    I format cell "D3" with a number format but when i open it up in excel 2010 it doesn't actually apply the number format until i double click to edit the cell then click off of it. This is what it looks like right after i create the excel.

    Capture.PNG

    As you can see the number format is not applied in the picture.

  5. CharlieC

    Looks like you're using formulae in those cells: is that what the Row2-7[] stuff does? We never evaluate formulae so there's a chance Excel is tripping over the non-existent cache values.

    The little green triangles probably have some helpful hints as to why Excel is confused. FWIW Excel has a concept of shared formula where the same underlying formula is applied across a range of cells. We actually support roundtripping on this but I don't think it's very easy to do in client code.

    The following works as expected:

    from openpyxl import Workbook
    wb = Workbook()
    ws = wb.active
    for i in range(10):
        c = ws['A%d' %(i+1)]
        c.value = 123546
        c.number_format = "#,###"
    
  6. vposud reporter

    Okay i got it to work now. The stuff i had stored in the Row2-7 i was storing as strings and not integers. When i changed the rows that were storing numbers to save as integers the format worked correctly. Thank you so much for your help and sorry for wasting your time with this as it was my stupid mistake causing the issue.

  7. CharlieC

    Glad you got it working and happy to help, though you might consider subscribing to the mailing list and asking questions (and maybe even answering them) there.

  8. CharlieC

    One final note: 2.2 is a development branch and likely to go through some significant changes. Only stick with the checkout if you're prepared to live with that (and provide feedback).

  9. Log in to comment