Forums Home / Spread / SpreadJS
Posted by: yes2us on 9 June 2018, 12:25 am EST
yes2us
Posted 9 June 2018, 12:25 am EST
Question1: I like to show a big number with separator every 4 digits. E,g, 123456789, should be 1,2345,6789 or 1,2345.6W, or 1,2Y. Because in china, people read number every four digits. Here, W means ten thousands, Y means a hundred millions. How can write the formatter string?
Qestion2: When a number is input, how can I convert it to percentage directly? Like in Excel, when we input 8, it shows 8%, but 800% in spreadjs.
yes2us
Posted 9 June 2018, 12:40 am EST
OK. I have solved Qestion2 with the help of the old post. What about the Question1?
grapecity
Posted 11 June 2018, 7:30 am EST
Hello,
You can create a function to format the value in cell as shown below:
function setcomma(num) {
var str = num.toString().split('.');
if (str[0].length >= 5) {
str[0] = str[0].replace(/(\d)(?=(\d{4})+$)/g, '$1,');
}
if (str[1] && str[1].length >= 5) {
str[1] = str[1].replace(/(\d{4})/g, '$1 ');
}
return str.join('.');
}
activeSheet.setValue(1, 2, setcomma(125678923.3456));
Thanks,
Deepaak Sharma
yes2us
Posted 11 June 2018, 8:04 am EST
Thanks. It seems the number becomes a string and cannot be directly used in formula any more.
yes2us
Posted 11 June 2018, 1:21 pm EST
Make it done with the following code.
function NumberInWan(){};
NumberInWan.prototype = new GC.Spread.Formatter.FormatterBase();
NumberInWan.prototype.format = function (obj, formattedData) {
if (typeof obj === “number”) {
return formatNumber(obj, formattedData);
} else if (typeof obj === “string”) {
if ($.isNumeric(obj)) {
return formatNumber(parseFloat(obj), formattedData);
}
}
return obj ? obj.toString() : “”;
};
function formatNumber(value, formattedData) {
var valuestr = value.toString();
if (isFinite(value)) {
valuestr = (value/10000).toFixed(1)
if(value<0)
{
formattedData.conditionalForeColor = “red”;
value = (-value)
valuestr = “(”+valuestr.substr(1,valuestr.length-1)+“)W”;
}
else if(value>0)
{
valuestr = valuestr+“W”;
}
else
{
valuestr = ‘-’;
}
}
return valuestr;
}
NumberInWan.prototype.parse = function (str) {
return new GC.Spread.Formatter.GeneralFormatter().parse(str);
};
sheet.getRange(0,0,100,100).formatter(new NumberInWan())
grapecity
Posted 18 June 2018, 1:21 am EST
Hello,
Thanks for sharing the code.
Regards,
Deepak Sharma
