How to set height of 1st row of title section excel export for jQuery Datatable

user_14145313 0
Viewed 956

I am exporting an excel sheet using jQuery DataTable. I need to set height of my 1st row that containing Title with 2 lines. But when I export the excel sheet, the title is showing in one line until I manually clicked on the row. When I clicked the row manually title showing in 2 line. So I need to set height for this title row.

I have tried with below code but it's not working. Can anyone help me?

[{
    extend: 'excelHtml5',            
    title: "May 1st test: " + '\n' + "Secound text ";               
    customize: function (xlsx) {
        var sheet = xlsx.xl.worksheets['sheet1.xml'];
        $('row, sheet').attr('ss:Height', '100');
        //$('row:first c', sheet).attr('s', '80');    
    },
}]
datatables jquery
1 Answers

To show both lines in the title, you need to

  1. Increase the height of the title row.
  2. Set the cell style wrap text flag to true.

Which can be achieved by

    extend: 'excelHtml5',
    title: "May 1st test: " + '\r\n' + "Second text ",
    customize: function(xlsx) {
      var sheet = xlsx.xl.worksheets['sheet1.xml'];
      var style = xlsx.xl['styles.xml'];
      // set wrapText to true for horizontal alignment center style (assume the header is centered)
      $('xf', style).find("alignment[horizontal='center']").attr("wrapText", "1");
      // set height
      $('row', sheet).first().attr('ht', '40').attr('customHeight', "1");
    }

Due to Download in Sandboxed Iframes (removed), the button in the code snippet will not work, you may copy the following code to an html file, and open the file with a browser to see the effect. 

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/dt/jszip-2.5.0/dt-1.10.21/b-1.6.2/b-flash-1.6.2/b-html5-1.6.2/b-print-1.6.2/datatables.min.css" />

<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.36/vfs_fonts.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/v/dt/jszip-2.5.0/dt-1.10.21/b-1.6.2/b-flash-1.6.2/b-html5-1.6.2/b-print-1.6.2/datatables.min.js"></script>

<script type="text/javascript">
  $(document).ready(function() {
    $('#dataTable').DataTable({
      dom: 'Bfrtip',
      buttons: [{
        extend: 'excelHtml5',
        title: "May 1st test: " + '\r\n' + "Second text ",
        customize: function(xlsx) {
          var sheet = xlsx.xl.worksheets['sheet1.xml'];
          var style = xlsx.xl['styles.xml'];
          // set wrapText to true for horizontal alignment center style (assume the header is centered)
          $('xf', style).find("alignment[horizontal='center']").attr("wrapText", "1");
          // set height
          $('row', sheet).first().attr('ht', '40').attr('customHeight', "1");
        },
      }]
    });
  });
</script>

<table id="dataTable" cellspacing="0" width="auto">
  <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>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>Garrett Winters</td>
      <td>Accountant</td>
      <td>Tokyo</td>
      <td>63</td>
      <td>2011/07/25</td>
      <td>$170,750</td>
    </tr>
  </tbody>
</table>

Reference:
Row properties
Alignment Wrap Text property

user_79287210
Related