How to format xlsx numbers in jsreports ₹ #,##0;₹ -#,##0



  • How to format numbers in excel sheet... Like number format = "₹ #,##0;₹ -#,##0"
    numFormatId= ?



  • I have following snippet in my code

    {{#if sheetData.length}}
        {{#each sheetData}}
            {{#setRow}}{{/setRow}}
            {{#xlsxAdd ../sheetNumber "worksheet.sheetData[0].row"}}
                <row>
                    {{#createDataCells this}}{{/createDataCells}}
                </row>
            {{/xlsxAdd}}
        {{/each}}
    {{/if}}
    

    and
    function createDataCells() {
    var formula = ***;
    '<c t="n"><f>' + formula + '</f></c>'
    }

    I want ot apply custom formatting to the number cell like z="₹ #,##0;₹ -#,##0"



  • now I am not sure if you don't know how to do this in excel xml or if don't know how to do it with handlebars helper.

    One hint is that you should use {{{#createDataCells this}}{{/createDataCells}}} if you want to return xml from helper, because otherwise it gets escaped.



  • Hi jan_blaha I am a newbie here w.r.t js-report and handlebars
    I did spend couple of hours figuring out how to get access to number formatting
    "cellStyleXfs": [{
    "$": {
    "count": "165"
    },
    "xf": [{
    "$": {
    "numFmtId": "0",
    "fontId": "0",
    "fillId": "0",
    "borderId": "0"
    }
    }]
    }],

    <c t="n" formatCode16="_ € * #,##0_ ;_ € * -#,##0_ ;_ € *"><v>' + 5123.56+ '</v></c>';

    I want to control number formatting in excel

    following options <numFmts count="1">
    <numFmt numFmtId="5" formatCode=""₹"\ #,##0;"₹"\ -#,##0"/>
    </numFmts>

    this xml which I extracted from workbook...



  •             <row>
                    <c></c><c></c>
                    <c t="inlineStr" s="10"><is><t><%=test[i]['...']%></t></is></c>
                    <c t="inlineStr" s="10"><is><t><%=test[i]['...']%></t></is></c>
                    <c t="inlineStr" s="10"><is><t><%=test[x]['...']%></t></is></c>
                    <c t="inlineStr" s="10"><is><t><%=test[i]['...']%></t></is></c>
                </row> 
    

    I have to format numbers like this
    <row>
    <c></c><c></c>
    <c t="n" formatCode16="_ € * #,##0_ ;_ € * -#,##0_ ;_ € *"s="10"><is><v><%=test[i]['...']%></v></is></c>
    <c t="n" formatCode16="_ € * #,##0_ ;_ € * -#,##0_ ;_ € *"s="10"><is><v><%=test[i]['...']%></v></is></c>
    <c t="n" formatCode16="_ € * #,##0_ ;_ € * -#,##0_ ;_ € *" s="10"><is><v><%=test[x]['...']%></v></is></c>
    <c t="n" formatCode16="_ € * #,##0_ ;_ € * -#,##0_ ;_ € *" s="10"><is><v><%=test[i]['...']%></v></is></c>
    </row>



  • Hi Finally did it by editing content.txt file which has got style sheet and json data

    Added applyNumberFormat like this
    "cellStyleXfs":[{"$":{"count":"1"},"xf":[{"$":{"numFmtId":"5","fontId":"0","fillId":"0","borderId":"0","applyNumberFormat":"1"}}]}]
    and
    "cellXfs":[{"$":{"count":"2"},"xf":[{"$":{"numFmtId":"5","fontId":"0","fillId":"0","borderId":"0","xfId":"0","applyNumberFormat":"1"}}

    also added style sheet element
    "numFmts":[{"$":{"count":"1"},"numFmt":[{"$":{"numFmtId":"5","formatCode":"$#.###,00"}}]}],

    Which works perfectly after generating workbook this format will be added in as customFormat and new entry is made in the custm number format

    Thanks for all the help !!! hurry !!!


Log in to reply
 

Looks like your connection to jsreport forum was lost, please wait while we try to reconnect.