Cell Displayed Value

[Dario de Judicibus]

Is there a way to get the displayed value of a cell rather than the real value?

For example, if A1 contains 4.678956734 and the cell is formatted as a number with two decimals, the value shown in A1 is 4.68. Similarly, if A2 contains 44197 and the cell is formatted as a date, I may see 1-jan-2021. What I need is a function that, WHATEVER BE THE DATA TYPE AND FORMAT (that is, I do not know a priori what is the format), returns to me what I see in cell and not what cell contains.

Thank you in advance.

[Charlie Clark]

On 14 Nov 2021, at 12:46, Dario de Judicibus wrote:

> Is there a way to get the displayed value of a cell rather than the real
> value?

No.

> For example, if A1 contains 4.678956734 and the cell is formatted as a
> number with two decimals, the value shown in A1 is 4.68. Similarly, if A2
> contains 44197 and the cell is formatted as a date, I may see 1-jan-2021.
>
> What I need is a function that, WHATEVER BE THE DATA TYPE AND FORMAT (that
> is, I do not know a priori what is the format), returns to me what I see in
> cell and not what cell contains.

To do this you will need to implement something that can convert Excel's number formatting to the printf format used in Python. For some simple formats you can probably get away with a simple dictionary.

Charlie

--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Waldlehne 23
Düsseldorf
D- 40489
Tel: +49-203-3925-0390
Mobile: +49-178-782-6226

[Nathan McCorkle]

I have some cells formatted with "Text" style (in Excel), which I've written some numbers in... however when I load the cell in openpyxl, it continues being cast to a float. Why is this?

I use "data_only=True" for the call to "load_workbook"

What am I missing? If I have a string (text) in Excel, why isn't it coming out in Python as a string type?

[Charlie Clark]

On 24 Feb 2022, at 22:17, Nathan McCorkle wrote:

> I have some cells formatted with "Text" style (in Excel), which I've
>
> written some numbers in... however when I load the cell in openpyxl, it
>
> continues being cast to a float. Why is this?
>
> I use "data_only=True" for the call to "load_workbook"
>
> What am I missing? If I have a string (text) in Excel, why isn't it coming
>
> out in Python as a string type?

Because even though it's formatted as a string, it's still a number…

Charlie

--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office

Sengelsweg 34

[Charlie Clark]

On 25 Feb 2022, at 22:41, Nathan McCorkle wrote:

Is that because my numbers are calculated (based on a formula), or Excel is

making that choice to actually store any number, when the characters are

entered and I press "Enter"? It is just deciding that numbers store more

compactly in native format? Versus if I type a column with some sort of

names, it knows it cannot be stored in numeric type, but rather

ASCII/UTF/UNICODE?

Numbers are numbers, text is text. Excel lets some numbers be formatted as text for display purposes but they are still numbers. When working in Excel you won't really notice the difference but in Python there is a big difference between "1234" and 1234. As with dates and times, you have lots of options when displaying them.

There is one situation in Excel where you may need to store numbers as strings and that is when they're more than 15 digits long, such as credit card numbers. Excel only has 15-digit precision and rounds every number with more. :-( In such cases you must convert the numbers to text before you store them in Excel and from text in Python if you want to do any processing.