Export excel format error - Rebuild Table after colvis filter

leonardoRochaLima

I have a problem with colvis when exporting a formated table to excel after filtering with colvis.

I tryied to get the column by number, so after this don't work, I tryied to get target name of column to format, but the problem happens when I filter with colvis.

when I remove a column for example I want to redraw the table, to format correctly the column by target name.

follow the code ..

$(function() { var table = $(".datatables").DataTable({ "dom": "<'row'<'col-sm-12 col-md-6'B>>" + "<'marginButtons'>" + "<'row'<'col-sm-12 col-md-6'l><'col-sm-12 col-md-6'f>>" + "<'row'<'col-sm-12'tr>>" + "<'row'<'col-sm-12 col-md-5'i><'col-sm-12 col-md-7'p>>", "lengthMenu": [5, 10, 25, 50, 75, 100], "order": [0, 'desc'], columnDefs: [{ targets: 1, className: 'noVis' }], columns: [ { name: 'id'}, { name: 'razaoSocial'}, { name: 'email'}, { name: 'contrato'}, { name: 'servico'}, { name: 'acoes'}, ], buttons: [{ extend: 'copyHtml5', text: '', titleAttr: 'Copiar', title: 'Listagem de Clientes', exportOptions: { columns: ':visible :not(.noVis)', }, }, { extend: 'excelHtml5', text: '', titleAttr: 'Exportar Excel', title: 'Listagem de Clientes', exportOptions: { format: { body: function(data, row, column, node) { console.log(table.column('id:name').index()) //entrando na verificação da terceira coluna if (column === table.column('razaoSocial:name').index()){ var firstStr = data.indexOf('>')+1; var lastStr = data.lastIndexOf('<'); data = data.substring(firstStr, lastStr); } if (column === table.column('contrato:name').index()) { //declarando o objeto de contratos var output = [ {"bol": false, "name": "Locação (Desk/Note)"}, {"bol": false, "name": "Locação Servidor"}, {"bol": false, "name": "Prestação de Serviço"}, {"bol": false, "name": "Outsourcing"}, {"bol": false, "name": "Backup Cloud"}, {"bol": false, "name": "Server Cloud"}, {"bol": false, "name": "IAAS"}, {"bol": false, "name": "Colocation"}, {"bol": false, "name": "Drive"}, {"bol": false, "name": "Nenhum contrato ativo."} ] //anotando contratos ativos if (data.includes('laptop')) {output[0].bol = true} if (data.includes('server')) {output[1].bol = true} if (data.includes('servicestack')) {output[2].bol = true} if (data.includes('globe')) {output[3].bol = true} if (data.includes('cloud')) {output[4].bol = true} if (data.includes('cloudversify')) {output[5].bol = true} if (data.includes('atlas')) {output[6].bol = true} if (data.includes('golf')) {output[7].bol = true} if (data.includes('download')) {output[8].bol = true} if (data.includes('Nenhum')) {output[9].bol = true} //zerando a data depois de anotar os contratos data = "" //contador para preencher o conteúdo da coluna for (var i = 0; i < output.length; i++) { if (data === "" && output[i].bol == true) { data = output[i].name } else if (output[i].bol == true) { data += ", " data += output[i].name } } } if (column === table.column('servico:name').index()) { //declarando o objeto de contratos var output = [ {"bol": false, "name": "Avulso"}, {"bol": false, "name": "Atendimento N1"}, {"bol": false, "name": "Atendimento N2"}, {"bol": false, "name": "SPLA - Microsoft"}, {"bol": false, "name": "Bitdefender"}, {"bol": false, "name": "Trend Micro"}, {"bol": false, "name": "Locação e Suporte Firewall"}, {"bol": false, "name": "Suporte - Servidor"}, {"bol": false, "name": "Suporte - Desktop"}, {"bol": false, "name": "Nenhum serviço contratado."}, {"bol": false, "name": "Nenhum contrato ativo."} ] //anotando contratos ativos if (data.includes('helping')) {output[0].bol = true} if (data.includes('ambulance')) {output[1].bol = true} if (data.includes('anchor')) {output[2].bol = true} if (data.includes('windows')) {output[3].bol = true} if (data.includes('scale')) {output[4].bol = true} if (data.includes('bacteria')) {output[5].bol = true} if (data.includes('fire')) {output[6].bol = true} if (data.includes('volume')) {output[7].bol = true} if (data.includes('alt')) {output[8].bol = true} if (data.includes('serviço')) {output[9].bol = true} if (data.includes('contrato')) {output[10].bol = true} //zerando a data depois de anotar os contratos data = "" //contador para preencher o conteúdo da coluna for (var i = 0; i < output.length; i++) { if (data === "" && output[i].bol == true) { data = output[i].name } else if (output[i].bol == true) { data += ", " data += output[i].name } } } if(column === table.column('acoes:name').index()){ data="" } return data } }, columns: ':visible' }, }, { extend: 'print', text: '<i class="fas fa-print">', titleAttr: 'Imprimir/PDF', title: 'Listagem de Clientes', exportOptions: { columns: ':visible :not(.noVis)', }, }, { extend: 'colvis', text: '', titleAttr: 'Filtrar Colunas', columns: ':not(.noVis)' }, ], "language": { "url": "//cdn.datatables.net/plug-ins/1.11.4/i18n/pt_br.json" }, }); });

colin

Duplicate of this thread : https://datatables.net/forums/discussion/72059/export-excel-format-error-rebuild-table-after-colvis-filter/p1?new=1

Colin