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: 1,407Questions: 17Answers: 283

    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: 42,648Questions: 1Answers: 5,493 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: 42,648Questions: 1Answers: 5,493 Site admin

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

    Allan

  • kthorngrenkthorngren Posts: 1,407Questions: 17Answers: 283
    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: 42,648Questions: 1Answers: 5,493 Site admin

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

    Allan

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

    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: 42,648Questions: 1Answers: 5,493 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: 42,648Questions: 1Answers: 5,493 Site admin

    This example shows how you are use the customize callback.

    Allan

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

    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: 42,648Questions: 1Answers: 5,493 Site admin

    Change the selector:

    $('row c', sheet)
    

    Allan

Sign In or Register to comment.