jquerydatatabledatatables

DataTables export to excel without keeping the white spaces defined

I have created a table with DateTables and at first the spaces defined in the table itself were not displayed, which was very important. I fixed it using the html tag pre.

Now the problem I have is that when exporting to excel, the generated excel has the same problem, it does not keep the blank spaces already defined at the beginning and end of the data. It's as if at some point a trim is made.

I leave you my relevant code in case you can give me a hand. Please don't hesitate to ask me for anything you need.

<table id="tbl" class="table table-bordered table-hover">
    <thead>
        <tr>
            @foreach (DataColumn column in Model.Columns)
            {
                <th class="bg-primary border-1 text-white">@column.ColumnName</th>  
            }
        </tr>
    </thead>
    <tbody>
        @foreach (DataRow row in Model.Rows)
        {
            <tr>
                @foreach (DataColumn column in Model.Columns)
                {
                    <td><pre>@row[column.ColumnName]</pre></td>
                }
            </tr>
        }
    </tbody>
</table>

 $('#tbl').DataTable({
     paging: true,
     scrollX: true,
     language: {
         paginate: {
             first: 'First',
             previous: 'Previous',
             next: 'Next',
             last: 'Last'
         },
         Show: 'Show',
         lengthMenu: 'Show records',
         search: 'Search:',
         info: 'info',
         infoFiltered: 'infoFiltered'

     },
     dom: 'Bfrtip',
     buttons: [
         {
             extend: 'excel',
             text: 'Export a Excel',
             className: 'btn btn-primary',
             title: '@Model.Title.ToString()',
             filename: '@Model.Title.ToString()',
             customize: function (xlsx) {
                 var sheet = xlsx.xl.worksheets['sheet1.xml'];
                 var rows = $('row', sheet);

                 $(rows[0]).find('c').each(function (index) {
                     var cell = $(this);
                     var columnIndex = index + 1;
                     cell.attr('s', '2');
                 });

                 rows.slice(1).each(function (rowIndex) {
                     if (rowIndex % 2 === 1) {
                         $(this).find('c').each(function () {
                             var cell = $(this);
                             cell.attr('s', '5');
                         });
                     }
                 });
             }
         }]
 });
Solution

  • In your specific case, it would be simpler to use the format option, which is part of the exportData function provided by DataTables Buttons.

    This is because all you want to do is manipulate the source data extracted from your DataTable. You do not need the customize option to solve your specific problem, because you do not need to format or manipulate the created spreadsheet - you just need to manage the raw data you send to the spreadsheet.

    (You can use both exportOptions and customize if you need to.)

    You are correct that, by default, HTML tags are stripped from each DataTable's cell data - and then the resulting text data is trimmed. That is why <pre> foo bar </pre> becomes just foo bar when the data is sent to Excel.

    The format option gives you access to each HTML node in each DataTable cell. From that, you can detect when there is a <pre> tag in the node - and you can override the default stripping/truncation behavior. This allows you to preserve the original text inside the <pre> tag, including any leading and trailing spaces:

    exportOptions: {
    format: {
        body: function ( inner, rowidx, colidx, node ) {
            if ($(node).children("pre").length > 0) {
                return $(node).children("pre").first().text();
            } else {
                return inner;
            }
        }
    }
}

    So, if you have some test data like this...

    <!doctype html>
<html>
<head>
  <meta charset="UTF-8">
  <title>Demo</title>

  <link href="https://cdn.datatables.net/2.0.3/css/dataTables.dataTables.min.css" rel="stylesheet">
  <link href="https://cdn.datatables.net/buttons/3.0.1/css/buttons.dataTables.min.css" rel="stylesheet">
 
  <script src="https://code.jquery.com/jquery-3.7.0.min.js"></script>
  <script src="https://cdn.datatables.net/2.0.3/js/dataTables.min.js"></script>
  <script src="https://cdn.datatables.net/buttons/3.0.1/js/dataTables.buttons.min.js"></script>
  <script src="https://cdn.datatables.net/buttons/3.0.1/js/buttons.html5.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.10.1/jszip.min.js"></script>

  <link rel="stylesheet" type="text/css" href="https://datatables.net/media/css/site-examples.css">
</head>

<body>

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

    <table id="tbl" class="display dataTable cell-border" style="width:100%">
        <thead>
            <tr>
                <th>Name</th>
                <th>Position</th>
                <th>Office in Country</th>
                <th>Age</th>
                <th>Start date</th>
                <th>Salary</th>
            </tr>
        </thead>
        <tbody>
            <tr>
                <td>Tiger Nixon</td>
                <td>System Architect</td>
                <td>Edinburgh</td>
                <td>61</td>
                <td>2011/04/25</td>
                <td>$320,800</td>
            </tr>
            <tr>
                <td><pre>    Garrett     Winters    </pre></td>
                <td>Accountant</td>
                <td>Tokyo</td>
                <td>63</td>
                <td>2011/07/25</td>
                <td>$170,750</td>
            </tr>
        </tbody>
    </table>

</div>

<script>

$(document).ready(function() {

 $('#tbl').DataTable({
     paging: true,
     scrollX: true,
     language: {
         paginate: {
             first: 'First',
             previous: 'Previous',
             next: 'Next',
             last: 'Last'
         },
         Show: 'Show',
         lengthMenu: 'Show records',
         search: 'Search:',
         info: 'info',
         infoFiltered: 'infoFiltered'

     },
     dom: 'Bfrtip',
     buttons: [
         {
             extend: 'excel',
             text: 'Export a Excel',
             className: 'btn btn-primary',
             title: 'some title here',
             filename: 'my_filename',

             exportOptions: {
                 format: {
                     body: function ( inner, rowidx, colidx, node ) {
                         if ($(node).children("pre").length > 0) {
                             return $(node).children("pre").first().text();
                         } else {
                             return inner;
                         }
                     }
                 }
             }
         }]
    });

} );

</script>

</body>
</html>

    Then what you see in Excel is this:

    enter image description here