CODEWITHSUNDEEP
pythonexcelopenpyxl
sans0909
sans0909

Reputation: 435

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

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?

Upvotes: 0

Views: 5069

Answers (1)

Abbas
Abbas

Reputation: 4069

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:

enter image description here

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

enter image description here

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>

Upvotes: 1

Related Questions