Repository navigation
string labels dumped to Excel should be formatted as strings #646
Description
Activity
Actually, the problem comes also at loading. So, the solution cannot limited to update the
dumpmethod.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.
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
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?
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...
>>> arr = ndtest('a=10E01,10E03') >>> def prefix_label(label): ... return "'" + label >>> arr.set_labels('a', prefix_label).to_excel() >>> # or (equivalently but less readable) >>> # arr.set_labels('a', "'".__add__).to_excel() >>> res = read_excel(-1) >>> res.equals(arr) True
produces something like:
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)
The solution is to set NumberFormat on the target cells before pasting the values (https://github.com/ZoomerAnalytics/xlwings/issues/436).
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).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?