Repository navigation
string labels dumped to Excel should be formatted as strings #646
Description
>>> 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 (https://github.com/ZoomerAnalytics/xlwings/issues/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?
Activity
alixdamman commentedon Jun 21, 2018
Actually, the problem comes also at loading. So, the solution cannot limited to update the
dumpmethod.gdementen commentedon Jun 21, 2018
I cannot reproduce the problem during load. Could you provide a test case?
both res1 and res2 contain the correct labels.
alanmanning commentedon Aug 22, 2019
One possible work-around: prepending the strings with an apostrophe.
gdementen commentedon Aug 23, 2019
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?
gdementen commentedon Jan 5, 2021
Contrary to what I thought, one does not have to remove the ' when reading the values back. Either Excel or xlwings is doing that for us. I guess that's the easiest workaround then. It's just kinda ugly to have them in Excel but well...