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

edgeineer

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

edgeineer

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"

jan_blaha

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.

edgeineer

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...

edgeineer

            <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>

edgeineer

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 !!!