Datatables export Excel

1
votes

I'm exporting the datatables in csv. And when I open the file with excel, I've got problem with big numbers (around 20 digits). I also have problem with the special characters. I guess it's a formatting problem. But I don't know How to correct the problem.

The code in my Js file: 

dom: 'Bfrtip',
    buttons: [
        {
            extend: 'csv',
            text: 'csv',
            fieldSeparator: ';' // with ';' we can export the file in csv and each column is in one column. Without ';' everything is in one column
        },
        'pdf',
        'print'
    ]

An image of the problem: enter image description here

Thanks for your help.

1
datatables

1 Answers

3
votes

There is a self-contained example at the end of this answer, but here are your two problems:

Large Numbers

The best way to fix this is to use 'excel' instead of 'csv' here:

dom: 'Bfrtip',
"buttons": [
  'excel'
]

This will ensure the Excel cell format is "number" instead of "general".

enter image description here

I don't know of a way to automatically control the Excel cell format when using the CSV export option - unless you are prepared to save the CSV as a text file, then import into Excel and format it during the import (a manual process).

Accented Characters

There are various reasons why you could be having this issue - many of which are outside the scope of DataTables - so the following may not help you, but...

Make sure your HTML page contains this inside the head tag:

<meta charset="UTF-8">

This is sufficient for me to get my demo working (see below). For example:

enter image description here

However, like I say, there could be many other reasons - for example, see here.

Full Example

Paste the following HTML into a text file (use Notepad++ not Notepad, if you are on Windows). Assuming Notepad++, make sure the file is saved as UTF-8 - menu > Encoding > UTF-8. Then open the file in any browser.

You don't need all of those JS imports provided below (for example the PDF one); feel free to remove extra ones. (I have them for a fuller demo and was too lazy to remove them.)

<!doctype html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <title>Export to Excel</title>
  <script src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
  <script src="https://cdn.datatables.net/1.10.20/js/jquery.dataTables.min.js"></script>
  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.20/css/jquery.dataTables.min.css">
  <link rel="stylesheet" type="text/css" href="https://datatables.net/media/css/site-examples.css">

  <!-- buttons -->
  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/buttons/1.6.1/css/buttons.dataTables.min.css">
  <script src="https://code.jquery.com/jquery-3.3.1.js"></script>
  <script src="https://cdn.datatables.net/1.10.20/js/jquery.dataTables.min.js"></script>
  <script src="https://cdn.datatables.net/buttons/1.6.1/js/dataTables.buttons.min.js"></script>
  <script src="https://cdn.datatables.net/buttons/1.6.1/js/buttons.flash.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/pdfmake.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/vfs_fonts.js"></script>
  <script src="https://cdn.datatables.net/buttons/1.6.1/js/buttons.html5.min.js"></script>
  <script src="https://cdn.datatables.net/buttons/1.6.1/js/buttons.print.min.js"></script>

</head>

<body>

<div style="margin: 20px;">

<table id="example" class="display nowrap dataTable cell-border" style="width:100%">
        <thead>
            <tr>
                <th>Name</th>
                <th>Position</th>
                <th>Office</th>
                <th>Age</th>
                <th>Start date</th>
                <th>Salary</th>
            </tr>
        </thead>
        <tbody>
            <tr>
                <td>Adélaïde Nixon</td>
                <td>System Architect</td>
                <td>Edinburgh</td>
                <td>6123456789012345</td>
                <td>2011/04/25</td>
                <td>$320,800</td>
            </tr>
        </tbody>
        <tfoot>
            <tr>
                <th>Name</th>
                <th>Position</th>
                <th>Office</th>
                <th>Age</th>
                <th>Start date</th>
                <th>Salary</th>
            </tr>
        </tfoot>
    </table>

</div>

<script type="text/javascript">

  $(document).ready(function() {
    $('#example').DataTable({

      dom: 'Bfrtip',
      "buttons": [
        'excel'
      ]
    });
  });

</script>

</body>

Note on the CSV Option

If you do use "csv" instead of "excel" in your button definition, and if you open the resulting file in a text editor, instead of Excel, you will see this data:

"Name","Position","Office","Age","Start date","Salary"
"Adélaïde Nixon","System Architect","Edinburgh","6123456789012345","2011/04/25","$320,800"

The data is the way you need it to be - it's just that Excel will make various assumptions about how to format the data when opening the csv file.