How to convert numbers to Text in export excel of Jqery datatable plug in.

How to convert numbers to Text in export excel of Jqery datatable plug in.

gnsriharignsrihari Posts: 2Questions: 1Answers: 0

In my exported excel sheet, all the values starts with 0 are rendered as Text and with out 0 are rendered as Numbers. How to change all values to Text irrespective of zeros.

This question has an accepted answers - jump to answer

Answers

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736

    Are you using the latest version of Buttons (1.3.1)?

    I seem to remember reading a recent thread that suggested this issue is resolved in the latest version. I may be wrong though.

    Kevin

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Yes, a plain 0 should work okay with the latest version. There is still an issue with 0% which I'm going to look at today.

    Allan

  • gnsriharignsrihari Posts: 2Questions: 1Answers: 0

    I tried adding the latest version., but still issue exists. Please check the attached image. Where if the loan number starts with 0 it displayed as text and left aligned, if it is started with a number it is rendered as numeric and right aligned. I want all to be rendered as text.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Happy to take a look at a test case showing the issue.

    Allan

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736
    Answer ✓

    Sorry, I misread your question. Most people want number columns to be numbers not text and ask why 0 is text. You can use one of the built in excelHtml5 styles to format the cells. In this case you probably will use style 50 (Left aligned text). You can use this example to learn how:
    https://datatables.net/extensions/buttons/examples/html5/excelTextBold.html

    Just change this line to match the column and style you want:
    $('row c[r^="C"]', sheet).attr( 's', '2' );

    This changes the whole column including headers and footers. Also this sets the cell to General not Text. May or may not be what you want.

    I'm not aware of a built in style that will set the cells to Text. You can add styles to the built in styles. It takes a bit of work (extracting and reviewing the Excel XML for the styles and coding the appending of the style to the built in styles). There are a couple recent threads covering this. Please let us know if you need help finding them.

    Kevin

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Sorry - I misunderstood as well. Thanks for the correction and fine answer Kevin.

    Allan

  • ooklamooklam Posts: 3Questions: 0Answers: 0
    edited September 2017

    How can I apply those changes when having build the DataTables like this:

    var table = $('#ClassificationTable').DataTable();
    table.page.len(25).draw();
    new $.fn.dataTable.Buttons( table, {
        buttons: [
            'copy', 
            'excel', 
            'csv'
        ]
    } );
    table.buttons( 0, 0 ).containers().appendTo( '.inner' );
    

    I had to extent the table with buttons like this, because the normal way $('#example').DataTable( { ... }) does not work with my search.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Use the customize method which is documented in the excel reference documentation.

    Allan

  • ooklamooklam Posts: 3Questions: 0Answers: 0

    Hey Allan, I dont know how. This is how I would call that method table.buttons([0]).customize(); but I dont know how to specify it.

    Doing something like this does not work:

    new $.fn.dataTable.Buttons( table, {
        buttons: [
            { extend: 'copy', className: 'copyButton' },
            { extend: 'excel', className: 'excelButton' }, 
            { extend: 'csv', className: 'csvButton' },
            customize: function( xlsx ) {
                    var sheet = xlsx.xl.worksheets['sheet1.xml'];
                    $('row:first c', sheet).attr( 's', '42' );
                }
        ]
    } );
    

    SyntaxError: missing ] after element list

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    This example shows how you are use the customize callback.

    Allan

  • ooklamooklam Posts: 3Questions: 0Answers: 0
    edited September 2017

    Thanks alot, that works. However I got a last question. How I can apply the style to the whole sheet? With this I only get the C columns, I dont want to apply it not column by column but for the whole sheet

    $('row c[r^="C"]', sheet).each( function () {
                        $(this).attr( 's', '50' );
                    });
    
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Change the selector:

    $('row c', sheet)
    

    Allan

  • tnthieutnthieu Posts: 26Questions: 9Answers: 0
    edited September 2019

    Hi Allan,

    I would like to indicate multi columns (F and G and X). Could you please tell me what I should do?

    buttons: [{
                    extend: 'excelHtml5',
                    text: 'Excel',
                    className: 'btn-export-xsl',
                    exportOptions: {
                        columns: [0, 1, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 31, 32]
                    },
                    init: function (api, node, config) {
                        $(node).removeClass('dt-button')
                    },
                    customize: function (xlsx) {
                        var sheet = xlsx.xl.worksheets['sheet1.xml'];
                        $('c[r^="F"]', sheet).attr('s', '50');
                    }
                }],
    

    Furthermore, in my table, I have 33 columns and 6000 records. When I export it with
    $('row c', sheet).attr('s', '50'); I takes a lot of time 40 seconds and the browser is freezed before the file is download. Is there a problem with my code or it takes a lot of time to format the excel file. Do you have any suggestion for the performance improvement?

    Thank you

    Edited by Colin - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    Hi @tnthieu ,

    You would just need to repeat it for the three columns.

    There was a performance issue with the recent releases - see thread here. You can find the nightly releases here, they should be working better.

    Cheers,

    Colin

This discussion has been closed.