CODEWITHSUNDEEP
pythonexceldatexlwings
Gustav K
Gustav K

Reputation: 1

How do I disable any kind of automatic conversion into excel when using xlwings?

When using xlwings, I want to, for example, write the string "4 / 5" into excel. However, if I write.

sheet[0,0].value = "4 / 5"

I get: string converted to date

I do not want to write a date. I want to write "4 / 5". Any way to fix this?

I tried looking for different format options or such, but was unable to find any.

Upvotes: 0

Views: 96

Answers (1)

moken
moken

Reputation: 6620

You cannot disable this action, it's how Excel works.
To have the value show as entered basically needs the cell to be set as text or formatted as a fraction.

Any of the following would work;

import xlwings as xw


file = 'foo.xlsx'
with xw.App(visible=True) as app:
    wb = xw.Book(file)
    ws = wb.sheets('Sheet1')

    ### Set the cell format before writting the value to the cell
    ### Set cell format to TEXT
    ws['A1'].number_format = "@"
    ws['A1'].value = '4/5'
    ### Set the cell format to a fraction. This results in a numeric value
    ws['A2'].number_format = "# ?/?"
    ws['A2'].value = '4/5'

    ### Use TEXT formula to set cell value to TEXT format as fraction
    ws['A3'].formula = "=TEXT(4/5,\"# ?/?\")"

    ### Use Space, Single Quote or 0 prefix to set value to TEXT
    ws['A4'].value = " 4/5"
    ws['A5'].value = "'4/5"
    ws['A6'].value = "0 4/5"  # This results in a numeric value

    wb.save(file)

enter image description here

Upvotes: 1

Related Questions