1

I am developing a Python script that uses xlrd to obtain data from xls files. I cannot figure out, however, how to print floating point numbers with the same precision as they have in the Excel file.

I tried using repr as suggested in https://stackoverflow.com/a/3481575/1296490 but this still gives different precision from the one I want.

For example, the Excel file has a cell with value -1.62717010683527 , then using
str(worksheet.cell(i,j).value) returns -1.62717010684,
while repr( worksheet.cell(i,j).value) returns -1.6271701068352695.
Using str(Decimal(worksheet.cell(i,j).value)) produces -1.6271701068352695095187044671547482721507549285888671875

None of the above gives me the original value from Excel. I have to process many such numbers each with different number of digits after the dot and it is not suitable to use %.10f etc.

Community
  • 1
  • 1
user1296490
  • 97
  • 1
  • 5
  • 13
  • How did the value get into the cell in the first place? `repr` is giving me back `-1.62717010683527` just fine. If `repr` is giving you something else, it's because something else is in that cell. – John Y Mar 27 '12 at 21:52
  • It is entered in the cell- `-1.62717010683527` and then the cell is formated- Percentage with two decimal places. Double clicking on the cell in Excel gives me `-1.62717010683527` as well as using libraries with COM objects to obtain the value of the cell. Having much data in the file I see that using `repr` on the xlrd cell.value returns the same floating-point number with two additional deciaml places- `-1.6271701068352695`. However, there are few values breaking this pattern which are returned as they are in excel. – user1296490 Mar 28 '12 at 09:20
  • Furthermore, the following values entered in Excel (Percentage format with 2 decimal places applied afterwards on the cell): `0.008`, `0.0135`, `0.0713`, etc.; `repr` returns as `0.00800000000000001`, ... – user1296490 Mar 28 '12 at 09:24
  • When I enter `-1.62717010683527` into Excel, I get back `-1.62717010683527` using `repr`, no matter what the formatting in Excel. It is actually not straightforward to enter `-1.6271701068352695` into Excel. I "entered" the longer value by adding two cells together. Note that Excel's precision is not *quite* as much as IEEE standard `double`, which is used by `repr`. `repr` can tell the difference between `-1.62717010683527` and `-1.6271701068352695`, but Excel cannot. – John Y Mar 28 '12 at 23:11

4 Answers4

4

Precision in Excel is confined to 15 significant figures.

Use str( "%0.15g" % cell.value ) to achieve what you want:

g - Same as "e" if exponent is greater than -4 or less than precision, "f" otherwise.
(http://docs.python.org/release/2.4.4/lib/typesseq-strings.html)

Do not forget 0. in "%0.15".

nmadzharov
  • 482
  • 2
  • 6
  • 12
2

Excel uses IEEE standard 64-bit floating-point in its calculations. However it futzes around trying to pretend that it is using decimal floating-point with 15 decimal digits of precision.

"the original value from excel" is a very nebulous concept. You appear to want what Excel displays to you. This is NOT what it puts in the file. What it puts in the file is an IEEE standard 64-bit binary floating-point value.

xlrd retrieves that value exactly.

Python's repr provides an exact string representation of that value, in a retrievable manner ... float(repr(value)) == value is absolutely guaranteed.

Using str(value) loses precision.

Using decimal.Decimal with more than 17 significant decimal digits is pointless.

John Machin
  • 81,303
  • 11
  • 141
  • 189
0

I've answered with a link to the code (using number format to convert a number to a string as Excel does) here: https://stackoverflow.com/a/19661081/327725

Community
  • 1
  • 1
olpa
  • 1,167
  • 10
  • 28
0

From a float value alone Python (or any other program) cannot determine the desired display precision, so first you have to find out how many places you want to show, e.g. By parsing the excel number format string ( maybe '0.00000' -> 5 decimal places )

A starting point for getting the number format might be http://groups.google.com/group/python-excel/browse_thread/thread/2d07febfa031dfa5

Once you have determined the number of places you want to show, you can use a two argument form of string formatting

value = -1.6271701068352695   # value stored ( not displayed ) in excel
precision = 5    # to be found out from excel number format
print '%.*f' % (precision, value)

Unfortunately formatting to arbitrary Excel formats is hard, so if the formating is more complex than 0.0000....0 you probably have to write the formatter from scratch.

Leovt
  • 313
  • 2
  • 4
  • I can obtain the Excel format but then it is not a good guide to format my floating-point number in python. Cell with value `-1.62717010683527` and format `0.00%` will display `-1.63%` in Excel while I still want to obtain the original value in the cell using xlrd. – user1296490 Mar 28 '12 at 09:29