Excel Export: format cells as text

Excel Export: format cells as text

cmdControlcmdControl Posts: 11Questions: 3Answers: 0

When I export the data into the excel file, it will import data ( I am forcing the data to type "string" via columnDefs: [ {type:'string', targets: '_all'}, [...] } ) correctly so if there are numbers with leading zeroes, it seems to keep them as strings; however, if there is a number of type string with no leading zeroes, excel will read display the value in a cell of format "Number".

My question is if there is a way, when extending button 'excelHtml5', to force format the excel cells to type "text"?

Thank you

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 61,436Questions: 1Answers: 10,049 Site admin

    Yes, this example shows how you can set a style for the cells in the spreadsheet. The built in styles are shown here. Use style 0 for plain text.

    Allan

  • cmdControlcmdControl Posts: 11Questions: 3Answers: 0
    edited July 2018

    Am I to understand that if I want to format all rows as text, I can just run this?

    $('row', sheet).each( function () { $(this).attr( 's', '0' ); });

    Because it still sets the cell as a "general" rather than forcing it to text

  • cmdControlcmdControl Posts: 11Questions: 3Answers: 0

    I'm just trying to format rows 3 and greater to have the cells formatted to type "Text"

  • allanallan Posts: 61,436Questions: 1Answers: 10,049 Site admin

    You need to apply the style to the c tags (cell), so you would use:

    $('row c', sheet).attr( 's', '0' );
    

    Allan

  • cmdControlcmdControl Posts: 11Questions: 3Answers: 0

    @allan

    I used this but the excel cells becomes "General" format which then continues to adapt depending on the values in each cell ( "0001" as text; "100" as number) and I need these all forced to be text format.

  • allanallan Posts: 61,436Questions: 1Answers: 10,049 Site admin
    Answer ✓

    Style 50 perhaps then?

    Allan

  • cmdControlcmdControl Posts: 11Questions: 3Answers: 0

    That worked perfectly! Thank you!

This discussion has been closed.