string labels dumped to Excel should be formatted as strings

[gdementen]

>>> arr = ndtest('a=10E01,10E03')
>>> arr.to_excel()

produces something like:

 a | 1.00E+02 | 1.00E+04
   |        0 |        1

This is not a visual transformation, it actually converts the values to numbers (whatever the actual dtype of labels were -- .dump() converts numpy types to raw Python types anyway)

>>> arr.a.labels.dtype
dtype('<U5')
>>> arr.dump()
[['a', '10E01', '10E03'], ['', 0, 1]]

The solution is to set NumberFormat on the target cells before pasting the values (xlwings/xlwings#436).

>>> wb = open_excel()
>>> # produces bad result
>>> wb[0]['A1'] = '10E03'
>>> # produces good result
>>> wb[0]['A2'].api.NumberFormat = "@"
>>> wb[0]['A2'] = '10E03'

Note that set_labels (currently) returns object arrays. This should be irrelevant when we go via .dump(), which is always the case, I think (when using wb[0] = array, .dump() is called automatically).

>>> ndtest(3).set_labels('a', {'a1': '10E03'}).a.labels.dtype
dtype('O')

Ideally, this should be fixed in xlwings.

Honestly, this issue seems easy to fix for small arrays (by scanning through each value and setting the format accordingly) but hard to do efficiently (set the format in chunks). Maybe .dump should not return a simple list of list but a custom object which retains the notion of axes etc. so that we can set the format for each axis in a single operation?

[alixdamman]

Actually, the problem comes also at loading. So, the solution cannot limited to update the dump method.

[gdementen]

I cannot reproduce the problem during load. Could you provide a test case?

arr = ndtest('a=10E01,10E03')
wb = open_excel()
wb[0]['A1:C2'].api.NumberFormat = "@"
wb[0]['A1'] = arr.dump()
res1 = wb[0].load()
res2 = read_excel(-1)

both res1 and res2 contain the correct labels.

[alanmanning]

One possible work-around: prepending the strings with an apostrophe.

test1 = ['1e10','08-Nov-19','1234.0','text']
sheet = wb.sheets['Sheet1']
sheet.range('A1').value = test1 #get number, date, number, text types in A1:D1:
test2 = ["'"+x for x in test1]
sheet.range('E1').value = test2 #get text, text, text types in E1:H1
# Note: text types in E1:G1 are preceded by an apostrophe

[gdementen]

Hi @apmanning. IIRC, this works but is annoying because you then have to change the values when reading them back.

Also, as a user I find it ugly to have apostrophes in my Excel files when it's not necessary. Since there is another way (formatting as numbers) which has fewer disadvantages, I would rather implement that. Or do you see any reason why/case where the apostrophe method is better than the numberformat method?