win32com Excel rounds currency and accounting formats but not general or number formats (Python)-CodePudding

If I have the following saved Excel document with 26007.930562 in every cell, where the column names represent the Excel formatting I am using for the given cell:

and I run the following Python code:

from openpyxl.utils import get_column_letter, column_index_from_string
import win32com.client # https://www.youtube.com/watch?v=rh039flfMto

ExcelApp = win32com.client.GetActiveObject('Excel.Application')
wb = ExcelApp.Workbooks('test.xlsx')
ws = wb.Worksheets(1)
excelRange = ws.Range(ws.Cells(1, 1), ws.Cells(2, 4))
listVals = [[*row] for row in excelRange.Value]

print(listVals)

I get the following output:

[['general', 'currency', 'accounting', 'number'], [26007.930562, Decimal('26007.9306'), Decimal('26007.9306'), 26007.930562]]

Notice how there is a loss of precision for the "currency" and "accounting" formats. They get turned into some decimal that rounds off several of the later decimal places. Is it possible to read in currency and accounting formatted cells while still keeping full precision? If so, how?

This is what I mean when I say "currency formatting":

EDIT:

BigBen's solution works in this example. But if you have dates, Value2 doesn't treat them like dates which causes errors in Python where you intend to treat them like dates. I ended up having to write this instead:

listVals = [] # https://stackoverflow.com/a/71375004
  for rowvalue, rowvalue2 in zip([[*row] for row in excelRange.Value], [[*row] for row in excelRange.Value2]):
    rowlist = []
    for value, value2 in zip(rowvalue, rowvalue2):
      if type(value) == pywintypes.TimeType:
        rowlist.append(value)
      else:
        rowlist.append(value2)
    listVals.append(rowlist)

I'm sure there's a faster / more efficient way to do it than that but I don't know what it is.

CodePudding user response：

Use .Value2 instead of .Value:

listVals = [[*row] for row in excelRange.Value2]

Result:

[['general', 'currency', 'accounting', 'number'], [26007.93056, 26007.93056, 26007.93056, 26007.93056]]