[]
DEMOS DOC DESIGNER API
DOWNLOAD
(Showing Draft Content)

Format String

In SpreadJS, users can set the values and formulas in a cell at the same time. You can also format the displayed text based on the formula in the cell. The format string in the formatter of Style is extended with formula or text value template which customizes the display value in a cell. The cell value acts as the parameter to the formatter which renders it in the cell, as an image or updated text.


The formatter is divided into three cases as below:

Types of Formatter

Syntax Format

Examples

Normal cell formatter

Standard formatter

"#,##0_);(#,##0)""h:mm AM/PM"

Format string - Formula

Standard formula

"=SUM(@, C1)""=AVERAGE(C4:C7)"

Format string - Text value Template

Text string where formula is contained in "{{" "}}"

"After {{=@}} approval, Total Sales Were: {{=SUM(Sales4)}}"“Hello, @”

'@' symbol represents the current cell reference and can be used directly in the formula or text value template.

Format String - Formulas

A formula can be set to the formatter of Style, apart from setting the cell value. The current cell can be referenced in the formula by using the '@' symbol.


For example, a cell value can be set as a sum of the range A1:A2:

style.formatter = '=SUM(@, A1:A2)';

Format String - Text Value Template

Text value templates are very useful for creating dashboards and summarizing data. With text value templates, text values and formulas can be combined in a single cell and can be used inside "{{" and "}}" to wrap the formula in a string.


For example, a cell value can be combined with a day value, sum formula and static text:

style.formatter = "On {{=TEXT(TODAY(),"DDDD")}}, Total Sales Were: ${{=SUM(A1:A5)}}.";

The format string is very useful and can be used commonly in scenarios like:

  • Formatting a long display string instead of using the concatenation formula

  • Displaying values in a different currency or unit without changing the underlying value

  • Displaying graphic representation of a value or range using barcode and sparkline functions

  • Displaying numeric values as words using the 'Number to Words' function

  • Displaying a KPI Range Template of a value in a dashboard or report

Usage Scenarios

Use Case 1


Numerals can be changed to words and their value is automatically updated whenever the cell's value is changed.




The following code sample implements a customized function to display numerals in words based on the cell value.

// initializing Spread
var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'), { sheetCount: 1 });
// Number to Words
var sheet1 = spread.getSheet(0);
sheet1.name('Number to Words');
spread.addCustomName('n_1', '{"";" One";" Two";" Three";" Four";" Five";" Six";" Seven";" Eight";" Nine";" Ten";" Eleven";" Twelve";" Thirteen";" Fourteen";" Fifteen";" Sixteen";" Seventeen";" Eighteen";" Nineteen"}');
spread.addCustomName('n_2', '{"";0;" Twenty";" Thirty";" Forty";" Fifty";" Sixty";" Seventy";" Eighty";" Ninety"}');
spread.addCustomName('n_3', '{"";"-One";"-Two";"-Three";"-Four";"-Five";"-Six";"-Seven";"-Eight";"-Nine"}');
var numberToWordsStyle = new GC.Spread.Sheets.Style();
numberToWordsStyle.formatter = '=TRIM(REPT(INDEX(n_1,1+INT(@/10^8))&" hundred",10^8<@)&IF(@-TRUNC(@,-8)<2*10^7,INDEX(n_1,1+MID(TEXT(@,"000000000"),2,2)),INDEX(n_2,1+MID(TEXT(@,"000000000"),2,2)/10)&INDEX(n_3,1+RIGHT(INT(@/10^6))))&REPT(" million",10^6<@)&IF(--RIGHT(INT(@/10^5)),INDEX(n_1,1+RIGHT(INT(@/10^5)))&" hundred","")&IF(@-TRUNC(@,-5)<2*10^4,INDEX(n_1,1+MID(TEXT(@,"000000000"),5,2)),INDEX(n_2,1+MID(TEXT(@,"000000000"),5,2)/10)&INDEX(n_3,1+RIGHT(INT(@/10^3))))&IF(--MID(TEXT(@,"000000000"),4,3)," thousand","")&IF(--RIGHT(INT(@/100)),INDEX(n_1,1+RIGHT(INT(@/100)))&" hundred","")&IF(MOD(@,100)<20,INDEX(n_1,1+RIGHT(@,2)),INDEX(n_2,1+RIGHT(@,2)/10)&INDEX(n_3,1+RIGHT(@))))';
numberToWordsStyle.name = 'NumberToWords';
numberToWordsStyle.backColor = "lightblue";
spread.addNamedStyle(numberToWordsStyle);
sheet1.setColumnWidth(1, 700);
sheet1.setStyleName(1, 1, 'NumberToWords');
sheet1.getCell(1, 1).value(765348921).hAlign(GC.Spread.Sheets.HorizontalAlign.center);
sheet1.setColumnWidth(1, 700);

Use Case 2


Consider another case where the monthly sales revenue can be represented in USD, CNY, JPY and EURO currencies by selecting the same from the dropdown. Based on the currency exchange rate, the revenue numbers are updated for each month. Also, whenever the currency exchange rate is updated, the revenue numbers are dynamically updated.




The following code sample implements the format string to update the sales revenue based on the exchange rate of different currencies.

window.onload = function () {
    var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'), {
        sheetCount: 1
    });
    initSpread(spread);
};

function initSpread(spread) {
    var gcns = GC.Spread.Sheets;
    spread.suspendPaint();
    var sheet = spread.getActiveSheet();
    var data = [
        [, "FY 2019"],
        [, "Sales"],
        [, "Monthly", "Cumulative"],
        ["Apr", 188897, 188897],
        ["May", 208146, 397043],
        ["Jun", 226196, 623239],
        ["Jul", 277318, 900557],
        ["Aug", 263273, 1163830],
        ["Sep", 259845, 1423675],
        ["Oct", 241047, 1664722],
        ["Nov", 256306, 1921028],
        ["Dec", 195845, 2116873],
        ["Jan", 204934, 2321808],
        ["Feb", 257852, 2579660],
        ["Mar", 227779, 2807439]
    ];
    sheet.setArray(3, 1, data);
    sheet.setColumnWidth(2, 110);
    sheet.setColumnWidth(3, 110);
    sheet.setRowCount(20);
    sheet.setColumnCount(9);
    sheet.options.gridline.showHorizontalGridline = false;
    sheet.options.gridline.showVerticalGridline = false;
    sheet.getRange(3, 1, 15, 3).setBorder(
        new gcns.LineBorder("black", gcns.LineStyle.medium),
        { all: true });
    sheet.addSpan(3, 2, 1, 2);
    sheet.addSpan(4, 2, 1, 2);
    sheet.getRange(3, 2, 3, 2).backColor('#CFE1F3').hAlign(gcns.HorizontalAlign.center);
    sheet.getRange(6, 1, 12, 1).backColor('#CCC1DA');

    var cMapSource = [
        { "Currency": "USD", "Value": 1, "Symbol": "$" },
        { "Currency": "CNY", "Value": 7.02, "Symbol": "¥" },
        { "Currency": "JPY", "Value": 108.8, "Symbol": "¥" },
        { "Currency": "EURO", "Value": 0.91, "Symbol": "€" },
    ];
    sheet.tables.addFromDataSource('cT', 3, 5, cMapSource);
    [5, 6, 7].forEach((col) => { sheet.setColumnWidth(col, 80); });

    sheet.getCell(1, 2).value("Unit:").hAlign(gcns.HorizontalAlign.right);
    sheet.getRange(1, 3, 1, 1).backColor("yellow").setBorder(
        new gcns.LineBorder("blue", gcns.LineStyle.medium),
        { all: true });

    var dv1 = gcns.DataValidation.createFormulaListValidator('=cT[[#Data], [Currency]]');
    sheet.setDataValidator(1, 3, dv1);
    sheet.getCell(1, 3).hAlign(gcns.HorizontalAlign.center).value("USD");

    sheet.getRange(6, 2, 12, 2)
        .hAlign(gcns.HorizontalAlign.center)
        .formatter('=VLOOKUP($D$2,cT[#Data],3,FALSE)&"  "&TEXT(@*VLOOKUP($D$2,cT[#Data],2,FALSE),"###,###")');
    spread.resumePaint();

Limitations

  • Excel doesn't support format string. Hence, excel I/O is unsupported.

  • Format string does not support cell state.