Export in CSV/Excel - Set column type

willcode

Hi!
I have a problem with data export. One of my table columns need to be exported in csv/excel format and interpreted by excel/libreoffice as String. This column is a string formed by 44 integer numbers.
For example: 42190808924289000147550010000046151039279700
And when exported in csv ou excel format, libreoffice and excel are creating this column as a number and that makes this column information wrong. The value which appears in Excel/Libreoffice is 4,2190808924289E+043
Is there anyway to force some columns type to prevent this kind of behavior and make the excel/libreoffice identify it as string?

I've tried some options in datatables configuration using "columnDefs" but it doesn't work.

This is my dataTables config used to create the table:

config = {

                                    // ordernar a coluna com indice 0 em ordem decrescente
                                    order: [[ 0, "asc" ]],
                                
                                    //adicionar suporte para nagegação pelo teclado ao clicar em uma célula
                                    keys: false,
                                
                                    // habilitar reorganização de colunas com drag'n drop
                                    colReorder: false,
                                
                                    // tabela responsiva
                                    responsive: true,
                                
                                    fixedHeader: false,
                                    
                                    // Desabilitar paginação dos resultados
                                    paging: true,
                                
                                    pageLength: 50,
                                
                                    //define inicializador padrão como referencia para plugins
                                    dom: 'Bfrtip',

                                    // teste tipo de coluna
                                    columnDefs: [ 
                                                    { "type": "string", "targets": "chave-nf"},
                                                    { "type": "num", "targets": "column-number"},
                                                ],
                                
                                    // habilita botões para exportar a tabela
                                    buttons: [
                                        
                                        {
                                            // tipo para exportar
                                            extend: 'csv',
                                
                                            // texto do botao
                                            text:   'CSV',
                                
                                            // nome do arquivo
                                            title: nomeArquivpExportacao,
                                            className: 'btn-xs',
                                            exportOptions: {
                                                //columns: ':visible'
                                                columns: ':not(.notexport)'
                                            },
                                
                                        },
                                
                                        {
                                            extend: 'pdf',
                                            text: 'PDF',
                                            title: nomeArquivpExportacao,
                                            className: 'btn-xs',
                                            exportOptions: {
                                                //columns: ':visible'
                                                columns: ':not(.notexport)'
                                            },
                                        },
                                
                                        {
                                            extend: 'excel',
                                            text: 'Excel',
                                            title: nomeArquivpExportacao,
                                            className: 'btn-xs',
                                            exportOptions: {
                                                //columns: ':visible'
                                                columns: ':not(.notexport)'
                                            },
                                        },
                                
                                        {
                                            extend: 'copy',
                                            text: 'Copiar',
                                            className: 'btn-xs',
                                            title: nomeArquivpExportacao,
                                            exportOptions: {
                                                //columns: ':visible'
                                                columns: ':not(.notexport)'
                                            },
                                        },
                                
                                        {
                                            extend: 'print',
                                            className: 'btn-xs',
                                            exportOptions: {
                                                //columns: ':visible'
                                                columns: ':not(.notexport)'
                                            },
                                        },
                                
                                        {
                                            extend: 'colvis',
                                            text:  'Colunas Visíveis',
                                            className: 'btn-xs',
                                            exportOptions: {
                                                //columns: ':visible'
                                                columns: ':not(.notexport)'
                                            },
                                        }
                                
                                    ]

                                };

kthorngren

It has to do with Excel limits for the size of a number. Colin posted an example in this thread that should help:
https://datatables.net/forums/discussion/49957

Kevin