如何配置DataTables导出Excel：实现自动换行与格式保留

要实现你提出的两个需求，可以通过DataTables的Excel导出按钮扩展功能，结合自定义内容格式化和单元格样式设置来完成：

核心实现思路 #

1. 单元格自动换行：通过customize函数操作Excel工作簿，为所有单元格设置自动换行样式。
2. 保留HTML格式：在导出前将HTML标签转换为Excel支持的文本格式，比如将<br>、<p>替换为换行符\n，将<ul>/<li>转换为带项目符号的列表格式。

修改后的完整代码 #

JavaScript部分 #

$(document).ready(function() {
  var table = $('#example').DataTable({
    responsive: true,
    searching: true,
    columnDefs: [{
      target: 6,
      visible: false,
      searchable: true,
    }],
    dom: 'Bfrtip',
    buttons: [{
        extend: 'excelHtml5',
        exportOptions: {
          columns: ':visible',
          format: {
            body: function(data, row, column, node) {
              // 处理HTML格式转换
              let formattedData = data
                // 替换p标签为换行
                .replace(/<p[^>]*>/g, '\n')
                .replace(/<\/p>/g, '')
                // 替换br标签为换行
                .replace(/<br[^>]*>/g, '\n')
                // 处理ul/li列表，转换为带项目符号的行
                .replace(/<ul[^>]*>/g, '')
                .replace(/<\/ul>/g, '')
                .replace(/<li[^>]*>/g, '• ')
                .replace(/<\/li>/g, '\n')
                // 移除剩余的HTML标签（如a标签）
                .replace(/<[^>]+>/g, '');
              // 去除多余的首尾换行和空格
              return formattedData.trim();
            }
          }
        },
        customize: function(xlsx) {
          var sheet = xlsx.xl.worksheets['sheet1.xml'];
          // 设置所有单元格自动换行
          $('row c', sheet).attr('s', '55'); // 样式55对应自动换行+垂直居中，可根据需求调整
        }
      },
      {
        extend: 'pdfHtml5',
        orientation: 'landscape',
        exportOptions: {
          columns: ':visible'
        }
      },
      {
        extend: 'print',
        exportOptions: {
          columns: ':visible'
        }
      },
      //  'colvis'
    ],
  });

  buildSelect(table);

  table.on('draw', function() {
    buildSelect(table);
  });
  $('#test').on('click', function() {
    table.search('').columns().search('').draw();
  });
});

function buildSelect(table) {
  var counter = 0;
  table.columns([0, 1, 2]).every(function() {
    var column = table.column(this, {
      search: 'applied'
    });
    counter++;
    var select = $('<select><option value="">select me</option></select>')
      .appendTo($('#dropdown' + counter).empty())
      .on('change', function() {
        var val = $.fn.dataTable.util.escapeRegex(
          $(this).val()
        );

        column
          .search(val ? '^' + val + '$' : '', true, false)
          .draw();
      });

    column.data().unique().sort().each(function(d, j) {
      select.append('<option value="' + d + '">' + d + '</option>');
    });

    // 重建时恢复选中状态
    var currSearch = column.search();
    if (currSearch) {
      select.val(column.data().unique().toArray().find((e) => e.match(new RegExp(currSearch))));
    }
  });
}

HTML部分 #

<!DOCTYPE html>
<html>

<head>
  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.15/css/jquery.dataTables.min.css" />
  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/buttons/1.4.0/css/buttons.dataTables.min.css" />
  <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script>
  <script src="https://cdn.rawgit.com/bpampuch/pdfmake/0.1.27/build/pdfmake.min.js"></script>
  <script src="https://cdn.rawgit.com/bpampuch/pdfmake/0.1.27/build/vfs_fonts.js"></script>
  <script src="https://cdn.datatables.net/1.10.15/js/jquery.dataTables.min.js"></script>
  <script src="https://cdn.datatables.net/buttons/1.4.0/js/dataTables.buttons.min.js"></script>
  <script src="https://cdn.datatables.net/buttons/1.4.0/js/buttons.flash.min.js"></script>
  <script src="https://cdn.datatables.net/buttons/1.4.0/js/buttons.html5.min.js"></script>
  <script src="https://cdn.datatables.net/buttons/1.4.0/js/buttons.print.min.js"></script>
  <meta charset=utf-8 />
  <title>DataTables - Excel导出格式保留示例</title>
</head>
<div class="searchbox">
  <p>Name:
    <span id="dropdown1">
  </span>
  </p>
  <p>Postion: <span id="dropdown2">
  </span>
  </p>
  <p>Office: <span id="dropdown3">
</span>
  </p>
  <button type="button" id="test">Clear Filters</button>
</div>
<table id="example" class="cell-border row-border stripe dataTable no-footer dtr-inline" role="grid" style=" width: 100%; padding-top: 10px;">
  <thead>
    <tr>
      <th>&#160;</th>
      <th>&#160;</th>
      <th>&#160;</th>
      <th colspan="3" style=" text-align: center;">Information</th>
      <th>&#160;</th>
    </tr>
    <tr>
      <th>Name</th>
      <th>Position</th>
      <th>Office</th>
      <th>Age</th>
      <th>Start date</th>
      <th>Salary</th>
      <th>Hidden Column</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>
        <p><a href="Test1">AALorem Ipsum is simply dummy text of the printing and typesetting industry. 
  <br>Lorem Ipsum has been the industry's standard
  <ul>
    <li>
      dummy text ever since the 1500s</li>
    <li>when an unknown printer took a galley of type and scrambled</li>
  </ul>
  it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.</p>

<p><a  href="Test2"> Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.</p>
              <p><a href="Test3">qsdfdfdgffg</a></p>
      </td>
      <td>AALorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book.
        It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with
        desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.</td>
      <td>Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book.
        It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with
        desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.</td>
      <td>Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book.
        It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with
        desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.</td>
      <td>2011/04/25</td>
      <td>$3,120</td>
      <td>Sys Architect</td>
    </tr>
    <tr>
      <td>Garrett -2</td>
      <td>
        <p>Director: fgghghjhjhjhkjkj
          <p>
      </td>
      <td>Edinburgh</td>
      <td>63</td>
      <td>2011/07/25</td>
      <td>$5,300</td>
      <td>Director:</td>
    </tr>
    <tr>
      <td>Ashton.1 -2</td>
      <td>
        <p>Technical Authorjkjkjk fdfd h gjjjhjhk
          <p>
      </td>
      <td>San Francisco</td>
      <td>66</td>
      <td>2009/01/12</td>
      <td>$4,800</td>
      <td>Tech author</td>
    </tr>
    </tr>
  </tbody>
</table>
</div>

关键说明 #

• 格式转换：通过format.body函数处理每个单元格的HTML内容，将各类标签转换为Excel可识别的文本格式，比如用• 代替<li>，用\n代替换行标签。
• 自动换行设置：在customize函数中修改Excel工作表的XML内容，为所有单元格设置自动换行样式（样式码55对应自动换行+垂直居中，可根据需求调整）。

内容的提问来源于stack exchange，提问作者newuser