Large numbers exported to Excel

NoBullMan

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?

kthorngren

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

NoBullMan

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

1. Note that the first number is identical to what is in data table and second one is not
2. Copy this in the third row of "Sheet1": 8335201430007628; It is important not to copy from excel and paste. You can add "C" to column 2 if you want
3. In cell C3 of worksheet 1, add =VLOOKUP(A3,Sheet2!A:B,2,FALSE). This is to lookup the number in worksheet 2 and try to match it. Copy C3 to C4 and C5.
4. You can see it matches with the one you entered manually (C5) but not the one in C3 which is the one that was exported even though on the surface they are identical

kthorngren

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 customize function. 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 customize function.

Kevin

NoBullMan

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:

If you need advanced styling, I would very much encourage you to create the file server-side with a more comprehensive API. I only want Buttons to be simple.

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.

NoBullMan

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.

kthorngren

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