Export Datatable to PDF, EXCEL

Andreas S.

I have a Problem with the Header of the Datatables if I export to PDF or EXCEL. If the table Header have an font icon these are replaced with "-->". How can I remove this? I have attached an example.

Andreas

allan

You would need to use an export formatter function. There are formatters for the body, header and footer - that example shows a formatter for the body, but one for the header would be very similar.

Allan

Andreas S.

I tried with this code:

            dtButtons.push( {
                extend: "excelHtml5",
                text: '<i class="fas fa-file-excel text-green fa-fw"></i>',
                titleAttr: button_txt[4],
                className: 'btn btn-default',
                filename: 'athlete-list',
                exportOptions: {
                    columns: [ 0, 1, 2, 3, 4, 5, 6, 7, 8, 12 ],
                    orthogonal: 'export',
                    format: {
                        body: function( data, row, column, node ) {
                            return row === 0 ? data.replace( '-->', '' ) : data;
                        }
                    }
                },
                title: pdfTitle + showClubName,
                customize: function( xlsx ) {
                    var sheet = xlsx.worksheets[ 'sheet1.xml' ];
                    $( 'row:first c', sheet ).attr( 's', '30' );
                    
                }
            } );

I get an error, data did not support replace() function. Where is my fault here?

Andreas

allan

If the value is 0 (which you are checking for) then it is a number and doesn't have a string replace function.

Allan

Andreas S.

I have found a Problem with fontawesome 5. The fontawesome replace the icons to a SVG TAG. If I remove the <-- the export give now following output:

<svg xmlns="http://www.w3.org/2000/svg" class="svg-inline--fa fa-venus-mars fa-w-18 fa-fw" role="img" aria-hidden="true" viewBox="0 0 576 512" data-icon="venus-mars" data-fa-i2svg="" data-prefix="fas"><path fill="currentColor" d="M 564 0 h -79 c -10.7 0 -16 12.9 -8.5 20.5 l 16.9 16.9 l -48.7 48.7 C 422.5 72.1 396.2 64 368 64 c -33.7 0 -64.6 11.6 -89.2 30.9 c 14 16.7 25 36 32.1 57.1 c 14.5 -14.8 34.7 -24 57.1 -24 c 44.1 0 80 35.9 80 80 s -35.9 80 -80 80 c -22.3 0 -42.6 -9.2 -57.1 -24 c -7.1 21.1 -18 40.4 -32.1 57.1 c 24.5 19.4 55.5 30.9 89.2 30.9 c 79.5 0 144 -64.5 144 -144 c 0 -28.2 -8.1 -54.5 -22.1 -76.7 l 48.7 -48.7 l 16.9 16.9 c 2.4 2.4 5.4 3.5 8.4 3.5 c 6.2 0 12.1 -4.8 12.1 -12 V 12 c 0 -6.6 -5.4 -12 -12 -12 Z M 144 64 C 64.5 64 0 128.5 0 208 c 0 68.5 47.9 125.9 112 140.4 V 400 H 76 c -6.6 0 -12 5.4 -12 12 v 40 c 0 6.6 5.4 12 12 12 h 36 v 36 c 0 6.6 5.4 12 12 12 h 40 c 6.6 0 12 -5.4 12 -12 v -36 h 36 c 6.6 0 12 -5.4 12 -12 v -40 c 0 -6.6 -5.4 -12 -12 -12 h -36 v -51.6 c 64.1 -14.6 112 -71.9 112 -140.4 c 0 -79.5 -64.5 -144 -144 -144 Z m 0 224 c -44.1 0 -80 -35.9 -80 -80 s 35.9 -80 80 -80 s 80 35.9 80 80 s -35.9 80 -80 80 Z" /></svg><!-- <i class="fas fa-venus-mars fa-fw"></i> -->

I have no idea how can I remove this in the header, because the header have two different icons. And also the <abbr> TAG would be in the output <abbr title="Agegroup">AG</abbr>

I did not know why the output are so crazy. I have attached an excel output, that you see what I see.

Andreas

allan

It looks like you'll need to use the header formatter function as well as a body one. That will let you extract just the information you want and return that.

Allan

Andreas S.

I have changed it to the header, but the result is the same.

header: function( data, row, column, node ) {
  return ( 0 === row ) ? data.replace( '-->', '' ) : data;
}

This is the code that i used. Did you have some more hints, how can i solve this problem?

Andreas

allan

Can you give me a link to the page showing the issue please so I can help to debug it.

Thanks,
Allan

Andreas S.

I have solved the problem with this code:

                    format: {
                        header: function( data, column, node ) {
                            if( 2 === column ) { return data = button_txt[8]; }
                            else if( 3 === column ) { return data = button_txt[6]; }
                            else if( 5 === column ) { return data = button_txt[7]; }
                            else { return data; }
                        },

That's works with pdf and excel export.
I have one more questions about the export. If I want to set a column alignment to center I must set it for pdfmake like this code:

 body: [
          [ 'First', 'Second', 'Third', 'The last one' ],
          [ 'Value 1', 'Value 2', 'Value 3', 'Value 4' ],
          [ { text: 'Bold value', alignment: true }, 'Val 2', 'Val 3', 'Val 4' ]
        ]

I tried it with this:

body: function( data, row, column, node ) {
                            if( 3 === column ) { return { text: data, alignment: 'center' } }
                        }

the result see you in the images. How did I set the alignment for this column?

Andreas

allan

Text alignment is set using the text-align property in CSS. You could assign a class to the column (columns.className) that you want to change the text alignment of.

Allan

Andreas S.

I do this, but this did not work

{
                    data: 'nationality', width: '5%', render: function( data, type, row ) {
                        if( 'display' === type ) {
                            var $lng = ( 'de' == i18n_lng ) ? data.nameGerman : data.nameEnglish;
                            if( '' == data.ioc && '' == data.iso3166_1_alpha2 ) {
                                return '';
                            } else {
                                if( '' != data.ioc ) {
                                    return '<span class="flag-icon flag-icon-' + data.iso3166_1_alpha2.toLowerCase() + '" title="' + $lng + '"></span>';
                                }else if( '' == data.ioc && '' == data.iso3166_1_alpha2 ) {
                                    return '<span class="flag-icon flag-icon-' + data.iso3166_1_alpha2.toLowerCase() + '" title="' + $lng + '"></span>';
                                }
                            }
                        }else{
                            return data.ioc;
                        }
                    }, className: 'text-center'
                }

Andreas

allan

Did you add td.text-center { text-align: center; } to your CSS?

Allan

Andreas S.

Yes, On the screen The dt shows the Text as centered. Only with the export the css is not taken.

Andreas