Truncated Decimals while writing a numpy array to excel

[PrzemyslawKepka]

OS (e.g. Windows 10 or macOS Sierra)

Windows 10

Versions of xlwings, Excel and Python (e.g. 0.11.8, Office 365, Python 3.7)

xlwings 0.23.4, Excel 2016, Python 3.7

Describe your issue (incl. Traceback!)

I'm using xlwings to write data from a database to a pre-formatted excel file. So firstly i query the database (on Teradata), result is loaded in python as a pandas dataframe, and then particular columns are converted to numpy array and loaded to excel file.

While doing it, I've noticed that decimal precision is lost in the process, and instead of a number with e.g. 10 decimal places, that number in excel lands with only 2 decimal points. Additionally, certain formats are changed to 'Currency'.

Firstly, as a workaround I've been adding additional characters for each value inside numpy array, converting data format to '@' (doing only this conversion without additional characters didn't help), and then changing the format back and removing all redundant characters, and it worked that way.

But later I did some more digging, and have found the real issue here. So when I've been taking single float columns with many digits from a pandas dataframe to a numpy array, their data type was decimal.Decimal (Decimal from decimal, additional python module), not standard float data type. Thus it looks like whenever data type inside numpy array is Decimal, the numbers in excel will be truncated only to 2 decimal places. So my aforementioned workaround works because I no longer have Decimals inside numpy array, but strings.

Include a minimal code sample to reproduce the issue (and attach a sample workbook if required!)

Fully reproducible example here (format change can also be observed):

import decimal
import numpy as np
import xlwings as xw

wb = xw.Book()
sheet = wb.sheets['Sheet1']

sheet.range('A1').value = 'Standard data type - float'
sheet.range('F1').value = 'Decimal data type'
sheet.range('A1:E1').merge()
sheet.range('F1:J1').merge()
sheet.range('A1').api.HorizontalAlignment = xw.constants.HAlign.xlHAlignCenter
sheet.range('F1').api.HorizontalAlignment = xw.constants.HAlign.xlHAlignCenter
sheet.range('A2').value = 'No_format'
sheet.range('B2').value = 'Number_no_decimals'
sheet.range('C2').value = 'Number with_decimals'
sheet.range('D2').value = 'Percentage'
sheet.range('E2').value = 'Text_placeholder'
sheet.range('F2').value = 'No_format'
sheet.range('G2').value = 'Number_no_decimals'
sheet.range('H2').value = 'Number with_decimals'
sheet.range('I2').value = 'Percentage'
sheet.range('J2').value = 'Text_placeholder'
sheet.range('B3:B52').number_format = '# ##0'
sheet.range('C3:C52').number_format = '0.00'
sheet.range('D3:D52').number_format = '0.00%'
sheet.range('E3:E52').number_format = '@'
sheet.range('G3:G52').number_format = '# ##0'
sheet.range('H3:H52').number_format = '0.00'
sheet.range('I3:I52').number_format = '0.00%'
sheet.range('J3:J52').number_format = '@'

np_array = np.random.uniform(0, 100000, 50)
np_array_1_standard = np.array([[i] for i in np_array])
np_array_2_decimal = np.array([[decimal.Decimal(i)] for i in np_array])

print('standard - numpy float', np_array_1_standard[0][0],type(np_array_1_standard[0][0]))
print('decimal', np_array_2_decimal[0][0],type(np_array_2_decimal[0][0]))

sheet[f'A3'].value = np_array_1_standard
sheet[f'B3'].value = np_array_1_standard
sheet[f'C3'].value = np_array_1_standard
sheet[f'D3'].value = np_array_1_standard
sheet[f'E3'].value = np_array_1_standard
sheet[f'F3'].value = np_array_2_decimal
sheet[f'G3'].value = np_array_2_decimal
sheet[f'H3'].value = np_array_2_decimal
sheet[f'I3'].value = np_array_2_decimal
sheet[f'J3'].value = np_array_2_decimal

sheet.autofit(axis='columns')

I've been looking for such issue being mentioned already, but I've found only these two topics:
https://stackoverflow.com/questions/69213138/xlwings-reading-range-of-floats-as-decimals-and-truncates-decimals
#1644
So I'm not sure if this more of an excel issue with not handling Decimals conversion rather than xlwings issue itself (not talking about automatic conversion of course, although it could be possible i guess), but I think this is at least worth mentioning.

[fzumstein]

See my comment in #2131, it's indeed a limitation of Excel and COM data type conversion. Bottom line: don't send Decimals but floats to Excel and don't use Currency in Excel or read them using .api.Value2.

[PrzemyslawKepka]

Well, so if there's no automatic conversion on xlwings end, then I think it could be at least stated explicitly somewhere in the documentation, that Decimals data type cannot be used (if I recall correctly i haven't seen such information in the docs anywhere).

In my case it didn't even cross my mind to verify data types inside numpy array, as my naive assumption was that the output in excel should be the same as the input from numpy array, no matter the type.

Thanks for the clarification :)

[fzumstein]

Sure, feel free to open a PR with the docs clarifications!