Jquery Question posted in
The official Jquery Api Documentation can be found here.

Jquery – 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');
                         });
                     }
                 });
             }
         }]
 });
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    The solution posted by @andrewJames is the most correct of those proposed, which is why it is the one accepted as the solution to the problem. I have found a workaround that I post here only educationally, offering another type of approach, as well as knowledge.

    The idea is to continue using the HTML tag pre, to present the data in the table with the spaces already defined. On the other hand, so that the export to an Excel file maintains the blank spaces already marked, I have used this configuration of the datatable:

    exportOptions: {
                 trim : false;
               }

    You can find more interesting information at this link: DataTable Official


  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search