Large numbers exported to Excel
Large numbers exported to Excel
NoBullMan
Posts: 61Questions: 17Answers: 2
Link to test case:
Debugger code (debug.datatables.net):
Error messages shown:
Description of problem: I had an issue when exporting data table content to Excel where a column had 15+ character strings comprised of numbers only. Excel would display the column data in scientific notation.
I searched and found This solution that worked just fine.
$('#example').DataTable({
dom: 'Bfrtip',
buttons: [{
extend: 'excelHtml5',
title: 'Transaction',
customizeData: function(data) {
for(var i = 0; i < data.body.length; i++) {
for(var j = 0; j < data.body[i].length; j++) {
data.body[i][j] = '\u200C' + data.body[i][j];
}
}
},
orientation: 'landscape'
}]
});
Then today I searched for a value from that column that I know exists in Excel file and it couldn't find it. I am pretty sure it is due to ZWNJ character. So, back to square one!
Is there any remedy for this?
Answers
I updated the example to use the latest Datatables and Buttons code.
http://live.datatables.net/dulexefo/1/edit
I opened the export into Excel and was able to successfully search for
7628. Can you provide a link to your page or a test case showing the problem so we can take a look?https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case
Kevin
Thank you Kevin. Unfortunately, this is an intranet app but I reproduced it using Excel.
Also, you need to search for entire string since non-visible character is at the beginning.
Using your example:
1. Export to Excel
2. Create another worksheet "Sheet2" and add two rows, with two columns:
8335201430007628; Test
8335201430007644; test2
Oh, I see. I used the search input in Excel not a vlookup
There is this thread that the last person posting in the thread seems to have found a way with the
customizefunction. I haven't tried it so not sure if its correct.This thread shows how to dynamically add styles. Give it a shot. If you need help then please create a test case or update the above with what you are trying with the
customizefunction.Kevin
Thank you Ken.
Nothing I have tried so far worked. Inserting \200C before the large number value, like this , seemed like perfect solution for a while until I had to use VLOOKUP in exported Excel and found that it cannot find the column value that has /200C in it.
I tried using Jim Li's solution, at the end of this post you mentioned, using "customize" but that seems to work on the first row first column cell only, the remaining values in the column remain problematic (my large number values are in the first column)
In this post I saw a comment by Allan that suggested exporting to Excel using server side code:
Right now it seems a reliable method although it is going to be painful compared to datatable way of exporting to Excel and using repetitive server side code. Prior to using Datatable.net I used a third party s/w from GemBox that is very nice and easy to use.
I forgot to ask: is it possible to keep the button group (Excel, CSV, PDF, Copy) on top of the data table but have Excel button use a server side code?
I can use the Export button for CSV, PDF and Copy only and use a separate button to Excel but like to keep it all inside "Export" button.
You can use Custom buttons like this example. If you want to stick with the Datatables export please build an example of what you. are trying so we can take a look. not sure how Ji Li's solution works but we can take a look to see why it applies to only the first row and column.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case
Kevin