数据表删除导出到 pdf 和 excel 的列 [英] datatable remove column on export to pdf and excel

查看：20
本文介绍了数据表删除导出到 pdf 和 excel 的列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

在导出为 pdf、excel 之前删除列时遇到问题.第二个问题，由于此列，列的反向无法正常工作这是我使用的代码

$(document).ready(function(){var arrayCol = new Array();var table = $('#example').DataTable({dom: 'B<"top"iflp<"clear">>rt<"bottom"ip<"clear">>',initComplete: 函数 ( ) {var len = this.api().columns().count();var array = Array.from(Array(len).keys());arrayCol = array.reverse();},纽扣: [{扩展:'pdf'，文本:'到PDF'，导出选项:{行:函数(idx，数据，节点){返回 data.reverse();},格式: {列:':visible:not(.not-export-col)',头:函数(数据，idx，节点){var headers = $('#example').DataTable().table().header();var reversedHeaders = headers.innerText.split('	').reverse();返回 reversedHeaders[idx];}}}},{扩展:'优秀'，文本:'exel'，导出选项:{列:':visible:not(.not-export-col)',行:函数(idx，数据，节点){返回 data.reverse();},格式: {头:函数(数据，idx，节点){var headers = $('#example').DataTable().table().header();var reversedHeaders = headers.innerText.split('	').reverse();返回 reversedHeaders[idx];}}}}]});});

和:

像这样导出为PDF:

最后说明:

  1. 我只在上面的代码中实现了PDF按钮.Excel 按钮代码也需要添加，但应该是相同的.

  2. 我在代码中注释掉了 HTML 表的 <tfoot> 部分.我认为这意味着您还需要添加一个 footer: function() { ... } 到您的实现中，以匹配 header: function() { ... } 代码.我假设如下(但我没有对此进行测试):

格式:{头:函数(数据，idx，节点){返回 reversedHeaders[idx];},页脚:函数(数据，idx，节点){返回 reversedHeaders[idx];}}

更新

我看到了烦人的警报.这是由我的代码引起的，它改变了数据行的长度(当它拼接出一个元素时).

第二个问题是，一旦行被反转，它仍然是反转的 - 因此，在下一次导出时，导出的数据顺序错误.

解决这两个问题很麻烦，我提出的解决方案也很有限:

我的解决方案假设您只想隐藏最后一列.

它还依赖于您能够检测到行何时被反转 - 在我的情况下，这是通过检查行数据的最终位置中的特定值来完成的 - 同样，这不是一种非常可靠的方法 - 但它这是我现在唯一能想到的方法.

这是更新的代码:

$(document).ready(function() {var exportPositions = [];//不导出的数据的列索引var ignorePositions = [];//不导出的数据的列索引var reversedHeaders = [];//使用不导出"标题已删除var table = $('#example').DataTable( {dom: 'B<top"iflp<clear"><bottom"ip<clear">>',initComplete: 函数 ( ) {var thead = $( '#example' ).DataTable().table().header();var tds = $( thead ).find( 'th' ).each(function( index ) {if ( ! $( this ).hasClass('not-export-col') ) {reversedHeaders.push( $( this ).text() );exportPositions.push(index);} 别的 {ignorePositions.push(index);}});reversedHeaders.reverse();//给我们我们想要的导出顺序reversedHeaders.push('');ignorePositions.reverse();//当我们 splice() 时反转 - 见下文},纽扣: [{扩展:'pdf'，文本:'到PDF'，导出选项:{行:函数(idx，数据，节点){if (data[data.length - 1] === '~') {data.reverse();//取消反转已经反转的记录data.push(data.splice(0, 1)[0]);//将第一个元素移动到数组的末尾}//我们拼接删除那些我们不想导出的数据字段:ignorePositions.forEach(idx => {数据拼接(idx，1)；});data.reverse();ignorePositions.forEach(idx => {data.push('~');//将数组填充回其原始长度});返回数据；},columns: exportPositions,//这里我们使用我们之前构建的数组格式: {头:函数(数据，idx，节点){返回 reversedHeaders[idx];}}}}]});});

如果您的实际实现需要隐藏多个列，那么这种方法可能不适合您.如果我对更强大的解决方案有任何想法，我会更新答案.

i have a problem when remove column before export to pdf,excel. and second problem that because of this column the reverse of column doesn't work right here is the code i use

$(document).ready(function(){
            var arrayCol = new Array();
            var table = $('#example').DataTable({
                dom: 'B<"top"iflp<"clear">>rt<"bottom"ip<"clear">>',
                initComplete:function (  ) {
                    var len = this.api().columns().count();
                    var array =  Array.from(Array(len).keys());
                    arrayCol = array.reverse();
                  },
            buttons: [
      { 
        extend: 'pdf',
        text: 'To PDF',
        exportOptions: {
          rows: function ( idx, data, node ) {
            return data.reverse();
          },
          format: {
          columns: ':visible:not(.not-export-col)', 
            header: function ( data, idx, node ) {
              var headers = $('#example').DataTable().table().header();
              var reversedHeaders = headers.innerText.split('	').reverse();
              return reversedHeaders[idx];
            }
          }
        }
      },
               { 
        extend: 'excel',
        text: 'exel',
        exportOptions: {
        columns: ':visible:not(.not-export-col)', 
          rows: function ( idx, data, node ) {
            return data.reverse();
          },
          format: {
            header: function ( data, idx, node ) {
              var headers = $('#example').DataTable().table().header();
              var reversedHeaders = headers.innerText.split('	').reverse();
              return reversedHeaders[idx];
            }
          }
        }
      }
    ]
                });
        });

and here is live example

解决方案

You can extend the approach used here to handle the extra requirement for suppressing one or more columns that you do not want to export.

Your use of the columns selector works well:

columns: ':visible:not(.not-export-col)

For building a list of the headers we want to export, the initComplete function can be used (so we only perform this process once).

We can also use the initComplete function to build an array of column indexes that we do not want to export:

var ignorePositions = []; // column indexes of data NOT to be exported
var reversedHeaders = []; // with "not-export" headings removed

The function:

initComplete:function (  ) {
  var thead = $( '#example' ).DataTable().table().header();
  var tds = $( thead ).find( 'th' ).each(function( index ) {
    if ( ! $( this ).hasClass('not-export-col') ) {
      reversedHeaders.push( $( this ).text() );
    } else {
      ignorePositions.push(index);
    }
  });
  reversedHeaders.reverse(); // to give us the export order we want
  ignorePositions.reverse(); // reversed for when we splice() - see below
}

The above code populates two arrays:

  • reversedHeaders - containing a list (in reverse) of the headings for those columns we will export.
  • ignorePositions - containing a list of column indexes to be ignored. In our example, the only such column is the final one (index 6).

Then we can use the above arrays in our modified exportOptions code:

exportOptions: {
  rows: function ( idx, data, node ) {
    var keepRowData = [];
    // we splice to remove those data fields we do not want to export:
    ignorePositions.forEach(idx => data.splice(idx, 1) );
      return data.reverse();
  },
  columns: ':visible:not(.not-export-col)',
  format: { 
    header: function ( data, idx, node ) {
      return reversedHeaders[idx];
    }
  }
}

The only tricky part is the need to use splice to directly alter the original data array. This cuts out each unwanted element from the original data array, without creating a new data array.

Putting it all together:

<!doctype html>
<html>
<head>
  <meta charset="UTF-8">
  <title>Demo</title>
  <script src="https://code.jquery.com/jquery-3.5.1.js"></script>
  <script src="https://cdn.datatables.net/1.10.22/js/jquery.dataTables.js"></script>
  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.22/css/jquery.dataTables.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.5/css/buttons.dataTables.min.css"/> 
  <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jszip/2.5.0/jszip.min.js"></script>
  <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.36/pdfmake.min.js"></script>
  <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/buttons/1.6.5/js/dataTables.buttons.min.js"></script>
  <script type="text/javascript" src="https://cdn.datatables.net/buttons/1.6.5/js/buttons.colVis.min.js"></script>
  <script type="text/javascript" src="https://cdn.datatables.net/buttons/1.6.5/js/buttons.flash.min.js"></script>
  <script type="text/javascript" src="https://cdn.datatables.net/buttons/1.6.5/js/buttons.html5.min.js"></script>
  <script type="text/javascript" src="https://cdn.datatables.net/buttons/1.6.5/js/buttons.print.min.js"></script>

</head>

<body>

<div class="container">
      <table id="example" class="display nowrap" width="100%">
        <thead>
          <tr>
            <th>Name</th>
            <th>Position</th>
            <th>Office</th>
            <th>Age</th>
            <th>Start date</th>
            <th>Salary</th>
            <th class="not-export-col">opr</th>
          </tr>
        </thead>
<!--
        <tfoot>
          <tr>
            <th>Name</th>
            <th>Position</th>
            <th>Office</th>
            <th>Age</th>
            <th>Start date</th>
            <th>Salary</th>
            <th class="not-export-col">opr</th>
          </tr>
        </tfoot>
-->
        <tbody>
          <tr>
            <td>Tiger Nixon</td>
            <td>System Architect</td>
            <td>Edinburgh</td>
            <td>61</td>
            <td>2011/04/25</td>
            <td>$3,120</td>
            <td><a href="www.google.com"><i class="wb-edit"></i></a></td>
          </tr>
<!--
          <tr>
            <td>Garrett Winters</td>
            <td>Director</td>
            <td>Edinburgh</td>
            <td>63</td>
            <td>2011/07/25</td>
            <td>$5,300</td>
            <td><a href="www.google.com"><span class="iconify" data-icon="fa:edit"></span></a></td>
          </tr>
          <tr>
            <td>Ashton Cox</td>
            <td>Technical Author</td>
            <td>San Francisco</td>
            <td>66</td>
            <td>2009/01/12</td>
            <td>$4,800</td>
            <td><a href="www.google.com"><span class="iconify" data-icon="fa:edit"></span></a></td>
          </tr>
          <tr>
            <td>Cedric Kelly</td>
            <td>Javascript Developer</td>
            <td>Edinburgh</td>
            <td>22</td>
            <td>2012/03/29</td>
            <td>$3,600</td>
            <td><a href="www.google.com"><span class="iconify" data-icon="fa:edit"></span></a></td>
          </tr>
          <tr>
            <td>Jenna Elliott</td>
            <td>Financial Controller</td>
            <td>Edinburgh</td>
            <td>33</td>
            <td>2008/11/28</td>
            <td>$5,300</td>
            <td><a href="www.google.com"><span class="iconify" data-icon="fa:edit"></span></a></td>
          </tr>
          <tr>
            <td>Donna Snider</td>
            <td>System Architect</td>
            <td>New York</td>
            <td>27</td>
            <td>2011/01/25</td>
            <td>$3,120</td>
            <td><a href="www.google.com"><span class="iconify" data-icon="fa:edit"></span></a></td>
          </tr>
-->
        </tbody>
      </table>
    </div>
<script>

$(document).ready(function() {
  var ignorePositions = []; // column indexes of data NOT to be exported
  var reversedHeaders = []; // with "not-export" headings removed

  var table = $('#example').DataTable( {
    dom: 'B<"top"iflp<"clear">>rt<"bottom"ip<"clear">>',
    initComplete:function (  ) {
      var thead = $( '#example' ).DataTable().table().header();
      var tds = $( thead ).find( 'th' ).each(function( index ) {
        if ( ! $( this ).hasClass('not-export-col') ) {
          reversedHeaders.push( $( this ).text() );
        } else {
          ignorePositions.push(index);
        }
      });
      reversedHeaders.reverse(); // to give us the export order we want
      ignorePositions.reverse(); // reversed for when we splice() - see below
    },
    buttons: [
      { 
        extend: 'pdf',
        text: 'To PDF',
        exportOptions: {
          rows: function ( idx, data, node ) {
            var keepRowData = [];
            // we splice to remove those data fields we do not want to export:
            ignorePositions.forEach(idx => data.splice(idx, 1) );
            return data.reverse();
          },
          columns: ':visible:not(.not-export-col)',
          format: { 
            header: function ( data, idx, node ) {
              return reversedHeaders[idx];
            }
          }
        }
      }
    ]
  } );
} );

</script>

</body>
</html>

The table data, which looks like this:

Is exported to PDF like this:

Final notes:

  1. I only implemented the PDF button in the above code. The Excel button code also needs to be added, but should be identical.

  2. I commented out the <tfoot> section of the HTML table in my code. I think this means you will also need to add a footer: function() { ... } to your implementation, to match the header: function() { ... } code. I assume that would be as follows (but I did not test this):

format: { 
  header: function ( data, idx, node ) {
    return reversedHeaders[idx];
  },
  footer: function ( data, idx, node ) {
    return reversedHeaders[idx];
  }
}

Update

I see the annoying alert. This is caused by my code which changes the length of the data row (when it splices out an element).

A second issue is that once a row has been reversed, it remains reversed - so, on the next export, the exported data is in the wrong order.

To address both of these is messy, and my proposed solution is limited:

My solution assumes that you only want to hide the final column.

It also relies on you being able to detect when a row is reversed - and in my case this is done by checking for a specific value in the final location of the row data - again, not a very robust approach - but it is the only approach I can think of right now.

Here is the updated code:

$(document).ready(function() {
  var exportPositions = []; // column indexes of data NOT to be exported
  var ignorePositions = []; // column indexes of data NOT to be exported
  var reversedHeaders = []; // with "not-export" headings removed

  var table = $('#example').DataTable( {
    dom: 'B<"top"iflp<"clear">>rt<"bottom"ip<"clear">>',
    initComplete:function (  ) {
      var thead = $( '#example' ).DataTable().table().header();
      var tds = $( thead ).find( 'th' ).each(function( index ) {
        if ( ! $( this ).hasClass('not-export-col') ) {
          reversedHeaders.push( $( this ).text() );
          exportPositions.push(index);
        } else {
          ignorePositions.push(index);
        }
      });
      reversedHeaders.reverse(); // to give us the export order we want
      reversedHeaders.push('');
      ignorePositions.reverse(); // reversed for when we splice() - see below
    },
    buttons: [
      { 
        extend: 'pdf',
        text: 'To PDF',
        exportOptions: {
          rows: function ( idx, data, node ) {
            if (data[data.length - 1] === '~') {
              data.reverse(); // un-reverse an already reversed record
              data.push(data.splice(0, 1)[0]); // move first element to end of the array
            }
            // we splice to remove those data fields we do not want to export:
            ignorePositions.forEach(idx => {
              data.splice(idx, 1);
            } );
            data.reverse();
            ignorePositions.forEach(idx => {
              data.push('~'); // pad the array back to its original length
            } );
            return data;
          },
          columns: exportPositions, // here we use the array we built earlier
          format: { 
            header: function ( data, idx, node ) {
              return reversedHeaders[idx];
            }
          }
        }
      }
    ]
  } );
} );

If your actual implementation needs to hide multiple columns then this approach will probably not work for you. If I have any thoughts on a more robust solution, I will update the answer.

这篇关于数据表删除导出到 pdf 和 excel 的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
前端开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆