2

I am trying to read in a 2 dimensional range of values from a ".xlsb" file using xlwings. The range contains a series of formulas, that returns floats. When I read in the values, it gets read in as Decimals rather than floats. The problem is Decimals beyond 4 spots get truncated. For example, I have a value in excel of 0.0913495 but it gets read in as Decimal('0.0913'). To make matters worse, when I try converting these decimals to floats, I see that any precision beyond 4 decimal places has been completely ignored. For example calling float(Decimal('0.0913')) returns 0.0913!

So far I have tried the following to fix this problem, none have worked:

  1. Set precision to 28 by calling decimal.getcontext().prec = 28. I have also tried 7, 8, etc. This seems to change nothing.
  2. Use the .options method: sheet.range("myrange").options(numbers = lambda x : float(x)).value
  3. Tried ".raw_value"

Ironically (2) still returns numbers as decimals, it is as if my options got ignored.

This is a problem as for my particular application I rely on a higher degree of accuracy than 4 decimals places, yet xlwings refuses to read in the estimated values at any precision beyond 4 decimal places. How do I fix this?

For reference, I am using xlwings 0.23.0 with Python 3.8.8 and Excel version 2108 (Build 14326.20238)

srpythondev
  • 21
  • 1
  • according to https://docs.xlwings.org/en/stable/converters.html, `By default cells with numbers are read as float`, can you share a sample xlsb file? – user16822867 Sep 23 '21 at 15:18
  • Please provide enough code so others can better understand or reproduce the problem. – Community Sep 23 '21 at 15:20
  • I figured out that this only happens if the number is formatted as "Currency" or "Accounting". If I change the number format to '@', then read it in, I get floats. Thus when is currency type formats the default behavior is actually to read it in as Decimals. I am not sure if Xlwings did this on purpose, but that should be made more clear in the documentation! – srpythondev Sep 23 '21 at 21:07

0 Answers0