How to get float value from excel using openpyxl in python?

Question

I'm reading an Excel sheet and printing the data returned from it. But when the data in the cells are float values, the output is printed as Long int. How do I get the float value as is from the Excel sheet using openpyxl (using openpyxl because it has good documentation). I know I could convert using float keyword but this doesn't work if used inside a loop which gives string as output as well.
Here is the sample excel data

      A         B              C              D

1  Device    Manufacturer   LMP Version  No.of Devices
2   DUT       CSR           4.0            1
3   REF1      Broadcom      4.0            3

code is:

import openpyxl
wb=openpyxl.load_workbook('/home/workspace/python/Book1.xlsx')
s = wb.get_sheet_by_name('Setup')     
for j in s['A1':'D3']:
            for cel in j:
                    print cel.coordinate,cel.value
            print '-------------'

output is:

A1 Device
B1 Manufacturer
C1 LMP Version
D1 No. of Devices
-------------
A2 DUT
B2 CSR
C2 4
D2 1
-------------
A3 REF1
B3 Broadcom 
C3 4
D3 3

The LMP version is Long int, how do I get the float value?

Excel, to my knowledge, doesn't have integer and floating point types. It has NUMBER. Which means it can't tell if your number is an integer or a float, and sees an exact value, and so OpenPyXl returns an integer. The solution is to call float. — Alex Huszagh
– Alex Huszagh, Commented May 10, 2016 at 5:01
The "4.0" you're seeing is due to the way the the cells are formatted. — Charlie Clark
– Charlie Clark, Commented May 10, 2016 at 7:41
@CharlieClark 4.0 is bluetooth's LMP version. there are many such versions, like 2.0,1.0 etc. So, I wanted it to parse 4.0 which isn't happening. — sans0909
– sans0909, Commented May 10, 2016 at 8:02
As @AlexanderHuszagh noted Excel doesn't differentiate between ints and floats. If you know the type for this column you can convert it yourself. — Charlie Clark
– Charlie Clark, Commented May 10, 2016 at 9:14
While it's true Excel doesn't differentiate between integers and floats, it would be more accurate to say that EVERY numeric value in Excel is a float. If any value is showing up as a Python int, it's because openpyxl has implicitly converted it to int. — John Y
– John Y, Commented May 10, 2016 at 21:02

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

If there is a number stored as 4.0 actually excel stores it as 4 only and doesn't store the .0 in its internal structure. Hence in excel you need convert them in to text, rather than numbers.

Consider this example excel file:

Now unzip it using 7.zip or any other tool and navigate to sheet1.xml like this:

Open the xml in any text editor (recommended notepad++)

and you will find that the .0 are not stored in it.

<c r="B4" s="2"><v>4</v></c>

<c r="B5" s="2"><v>5</v></c>

<c r="C5"><v>0.82548104138781497</v></c>

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered May 11, 2016 at 15:33

Abbas

4,0897 gold badges45 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to get float value from excel using openpyxl in python?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related