Export Datatable to PDF, EXCEL

Export Datatable to PDF, EXCEL

Andreas S.Andreas S. Posts: 207Questions: 73Answers: 4

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

Answers

  • allanallan Posts: 61,438Questions: 1Answers: 10,052 Site admin

    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.Andreas S. Posts: 207Questions: 73Answers: 4

    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

  • allanallan Posts: 61,438Questions: 1Answers: 10,052 Site admin

    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.Andreas S. Posts: 207Questions: 73Answers: 4

    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

  • allanallan Posts: 61,438Questions: 1Answers: 10,052 Site admin

    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.Andreas S. Posts: 207Questions: 73Answers: 4

    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

  • allanallan Posts: 61,438Questions: 1Answers: 10,052 Site admin

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

    Thanks,
    Allan

  • Andreas S.Andreas S. Posts: 207Questions: 73Answers: 4
    edited April 2018

    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

  • allanallan Posts: 61,438Questions: 1Answers: 10,052 Site admin
    edited April 2018

    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.Andreas S. Posts: 207Questions: 73Answers: 4

    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

  • allanallan Posts: 61,438Questions: 1Answers: 10,052 Site admin

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

    Allan

  • Andreas S.Andreas S. Posts: 207Questions: 73Answers: 4

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

    Andreas

This discussion has been closed.