Overview

In SpreadJS, you can customize the format of each cell so that the data is displayed however you like. This can be useful when you have raw data that comes from a database, and you want to show a user-friendly format of that data.

To set the formatter for a cell use the setFormatter method to set a formatter string to a cell, and use the getFormatter method to get the cell formatter. You can also get the cell, column, or row by using the formatter method to get and set the formatter for the cell. For example: You can set a general format for a cell. Create a GeneralFormatter and set it to the cell. For example:
var spreadNS = GC.Spread.Sheets; window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 4 }); initSpread(spread); }; var numValue = "1234.0123456789012345678901234567899"; var dateValue = "2011/9/2"; var eraDateValue = "2019/11/1"; var timeValue = "10:08:25"; var perValue = "1234.0123456789012345678901234567899%"; var sciValue = "1.012345678901234567890123456789e2"; var cusValue = "1001.1"; var cusDateValue = "2011/08/29"; var dateFormatterArray = [ //Date Formatter dateValue, "[$-411]dddd-mmmm;@","", dateValue, "[$-411]dddd-mmmm-yyyy;@", "", dateValue, "[$-411]mmm-yy;@", "", dateValue, "[$-411]mmmm d, yyyy;@", "", dateValue, "[$-411]m/d/yy h:mm AM/PM;@:", "", dateValue, "dddd-mmmm;@", "", dateValue, "dddd-mmmm-yyyy;@", "", dateValue, "mmm-yy;@", "", dateValue, "mmmm d, yyyy;@", "", dateValue, "m/d/yy h:mm AM/PM;@:", "", dateValue, "M/d/yyyy", "9/2/2011", dateValue, "[$-411]ggg ee/MMM/dd dddd", "", dateValue, "[$-F800]dddd, mmmm dd, yyyy", "Friday, September 02, 2011", dateValue, "M/d", "9/2", dateValue, "M/d/yy", "9/2/11", dateValue, "MM/dd/yy", "09/02/11", dateValue, "d-MMM", "2-Sep", dateValue, "d-MMM-yy", "2-Sep-11", dateValue, "dd-MMM-yy", "02-Sep-11", dateValue, "MMM-yy", "Sep-11", dateValue, "MMMM-yy", "September-11", dateValue, "d-MMM-yyyy", "2-Sep-2011", dateValue, "MMMM d, yyyy", "September 2, 2011", dateValue, "M/d/yy HH:mm tt", "9/2/11 12:00 AM", dateValue, "M/d/yy H:mm", "9/2/11 0:00", dateValue, "[$-409]mmmmm;@", "S", dateValue, "[$-409]mmmmm-yy;@", "S-11", //Time Formatter timeValue, "HH:mm:ss tt", "10:08:25 AM", timeValue, "HH:mm", "10:08", timeValue, "HH:mm tt", "10:08 AM", timeValue, "HH:mm:ss", "10:08:25", timeValue, "mm:ss.0", "08:25.0", timeValue, "m/d/yy HH:mm tt", "12/30/99 10:08 AM", timeValue, "d/m/yy HH:mm", "30/12/99 10:08", //Custom DateTime Formatter cusDateValue, "General", "40784", cusDateValue, "m/d/yyyy", "8/29/2011", cusDateValue, "d-mmm-yy", "29-Aug-11", cusDateValue, "d-mmm", "29-Aug", cusDateValue, "mmm-yy", "Aug-11", cusDateValue, "h:mm AM/PM", "0:00 AM", cusDateValue, "h:mm:ss AM/PM", "0:00:00 AM", cusDateValue, "h:mm", "0:00", cusDateValue, "h:mm:ss", "0:00:00", cusDateValue, "m/d/yyyy h:mm", "8/29/2011 0:00", cusDateValue, "mm:ss", "00:00", cusDateValue, "mm:ss.0", "00:00.0", cusDateValue, "@", "8/29/2011 12:00:00 AM", cusDateValue, "[h]:mm:ss", "978816:00:00" ]; var numberFormatterArray = [ //Number Formatter, "0.25", "# ?/?", "1/4", "1.35", "# ??/??", "1 7/20", "1.053", "# ???/???", "1 6/113", "0.33", "# ?/2", "1/2", "0.25", "# ?/4", "1/4", "1.35", "# ?/8", "1 3/8", "1.5", "# ??/16", " 1 8/16", "0.33", "# ?/10", "3/10", "0.25", "# ??/100", "25/100", "12345", "[DBNum1][$-411]#,##0", "一二,三四五", "12345", "[DBNum2][$-411]#,##0","壱弐,参四伍", "12345", "[DBNum3][$-411]#,##0","12,345", "12345", "[DBNum1][$-411]General","一万二千三百四十五", "12345", "[DBNum2][$-411]General","壱萬弐阡参百四拾伍", "12345", "[DBNum3][$-411]General", "12345", numValue, "0", "1234", numValue, "0.0", "1234.0", numValue, "0.00", "1234.01", numValue, "0.0000000000", "1234.0123456789", numValue, "0.00000000000000000000", "1234.01234567890000000000", numValue, "0.000000000000000000000000000000", "1234.012345678900000000000000000000", numValue, "#,##0", "1,234", numValue, "#,##0.00", "1,234.01", numValue, "#,##0.0000000000", "1,234.0123456789", numValue, "#,##0.00000000000000000000", "1,234.01234567890000000000", numValue, "#,##0.000000000000000000000000000000", "1,234.012345678900000000000000000000", numValue, "$#,##0", "$1,234", numValue, "$#,##0.00", "$1,234.01", numValue, "$#,##0.0000000000", "$1,234.0123456789", numValue, "$#,##0.00000000000000000000", "$1,234.01234567890000000000", numValue, "$#,##0.000000000000000000000000000000", "$1,234.012345678900000000000000000000", numValue, "#,##0.00", "1,234.01", numValue, "¥#,##0.00", "¥1,234.01", numValue, "Lek#,##0.00", "Lek1,234.01", numValue, "դր#,##0.00000", "դր1,234.01235", numValue, "$#,##0.00", "$1,234.01", numValue, "€#,##0.00", "€1,234.01", numValue, "$#,##0.00", "$1,234.01", numValue, "ман#,##0.00", "ман1,234.01", numValue, "€#,##0.00", "€1,234.01", numValue, "лв#,##0.000000", "лв1,234.012346", numValue, "$#,##0.00", "$1,234.01", numValue, "$b#,##0.00", "$b1,234.01", numValue, "R$#,##0.00", "R$1,234.01", numValue, "#,##0.00р.", "1,234.01р.", numValue, "BZ$#,##0.00", "BZ$1,234.01", numValue, "$#,##0.00", "$1,234.01", numValue, "CB#,##0.00", "CB1,234.01", numValue, "fr#,##0.00", "fr1,234.01", numValue, "$#,##0.00", "$1,234.01", numValue, "¥#,##0.00", "¥1,234.01", numValue, "$#,##0.00", "$1,234.01", numValue, "₡#,##0.00", "₡1,234.01", numValue, "Kč#,##0.00", "Kč1,234.01", numValue, "€#,##0.00", "€1,234.01", numValue, "kr#,##0.00", "kr1,234.01", numValue, "$#,##0.00", "$1,234.01", numValue, "kr#,##0.00", "kr1,234.01", numValue, "€#,##0.00", "€1,234.01", numValue, "£#,##0.00", "£1,234.01", numValue, "Lari#,##0.00", "Lari1,234.01", numValue, "Q#,##0.00", "Q1,234.01", numValue, "L#,##0.00", "L1,234.01", numValue, "kn#,##0.00", "kn1,234.01", numValue, "Ft#,##0.00", "Ft1,234.01", numValue, "Rp#,##0.00", "Rp1,234.01", numValue, "₪#,##0.00", "₪1,234.01", numValue, "रु#,##0.00", "रु1,234.01", numValue, "J$#,##0.00", "J$1,234.01", numValue, "¥#,##0.00", "¥1,234.01", numValue, "сом#,##0.00", "сом1,234.01", numValue, "₩#,##0.00", "₩1,234.01", numValue, "Т#,##0.00", "Т1,234.01", numValue, "Lt#,##0.00", "Lt1,234.01", numValue, "ден#,##0.00", "ден1,234.01", numValue, "₮#,##0.00", "₮1,234.01", numValue, "$#,##0.00", "$1,234.01", numValue, "C$#,##0.00", "C$1,234.01", numValue, "kr#,##0.00", "kr1,234.01", numValue, "zł#,##0.00", "zł1,234.01", numValue, "lei#,##0.00", "lei1,234.01", numValue, "р#,##0.00", "р1,234.01", numValue, "TA#,##0.00", "TA1,234.01", numValue, "฿#,##0.00", "฿1,234.01", numValue, "TL#,##0.00", "TL1,234.01", numValue, "TT$#,##0.00", "TT$1,234.01", numValue, "NT$#,##0.00", "NT$1,234.01", numValue, "₴#,##0.00", "₴1,234.01", numValue, "$U#,##0.00", "$U1,234.01", numValue, "сўм#,##0.00", "сўм1,234.01", numValue, "₫#,##0.00", "₫1,234.01", numValue, "R#,##0.00", "R1,234.01", numValue, "Z$#,##0.00", "Z$1,234.01", //Percentage Formatter perValue, "0%", "1234%", perValue, "0.00%", "1234.01%", perValue, "0.000000000000000%", "1234.012345678900000%", perValue, "0.000000000000000000000000000000%", "1234.012345678900000000000000000000%", //Scientific Formatter sciValue, "0E+00", "1E+02", sciValue, "0.00E+00", "1.01E+02", sciValue, "0.000000000000000E+00", "1.012345678901230E+02", sciValue, "0.000000000000000000000000000000E+00", "1.012345678901230000000000000000E+02", //Custom Number Formatter cusValue, "General", "1001.1", cusValue, "0", "1001", cusValue, "0.00", "1001.10", cusValue, "#,##0", "1,001", cusValue, "#,##0.00", "1,001.10", cusValue, "0%", "100110%", cusValue, "0.00%", "100110.00%", cusValue, "0.00E+00", "1.00E+03", cusValue, "##0.0E+0", "1.0E+3", cusValue, "# ?/?", "1001 1/9", cusValue, "# ??/??", "1001 1/10" ]; var eraFormatterArray = [ eraDateValue, '[$-ja-JP-x-gannen]ggg ee"年"mm"月"dd"日"', '', eraDateValue, '[$-ja-JP-x-gannen]ggg ee"年"m"月"d"日"', '', eraDateValue, '[$-411]ggg ee"年"mm"月"dd"日"', '', ]; var dbNum4FormatterArray = [ '1234567890000', '[DBNum4][$-412]General', '', '123456.789', '[DBNum4][$-412]0.00', '', '2020/07/16', '[DBNum4][$-412]YYYY-MM-DD', '' ]; function initSpread(spread) { spread.suspendPaint(); spread.options.tabStripRatio = 0.8; sheet = spread.getSheet(3); sheet.name("DBNum4 Formatter"); initFormatterUnitTest(sheet); startFormatterUnitTest(sheet, dbNum4FormatterArray); sheet = spread.getSheet(2); sheet.name("Era Formatter"); initFormatterUnitTest(sheet); startFormatterUnitTest(sheet, eraFormatterArray); var sheet = spread.getSheet(1); sheet.name("Date Formatter"); initFormatterUnitTest(sheet); startFormatterUnitTest(sheet, dateFormatterArray); sheet = spread.getSheet(0); sheet.name("Number Formatter"); initFormatterUnitTest(sheet); startFormatterUnitTest(sheet, numberFormatterArray); spread.resumePaint(); document.getElementById("btnSetFormat").addEventListener('click',function () { var sheet = spread.getActiveSheet(); try { var selections = sheet.getSelections(); for (var i = 0; i < selections.length; i++) { var range = getActualCellRange(selections[i], sheet.getRowCount(), sheet.getColumnCount()); for (var i = 0; i < range.rowCount; i++) { for (var j = 0; j < range.colCount; j++) { sheet.getCell(range.row + i, range.col + j).formatter(new GC.Spread.Formatter.GeneralFormatter(document.getElementById("format_text").value)); } } } } catch (ex) { alert(ex.message); } }); document.getElementById("btnClearFormat").addEventListener('click',function () { var sheet = spread.getActiveSheet(); var selections = sheet.getSelections(); for (var i = 0; i < selections.length; i++) { var range = getActualCellRange(selections[i], sheet.getRowCount(), sheet.getColumnCount()); for (var i = 0; i < range.rowCount; i++) { for (var j = 0; j < range.colCount; j++) { sheet.getCell(range.row + i, range.col + j).formatter(null); } } } }); }; function initFormatterUnitTest(sheet) { sheet.setColumnCount(4); sheet.getCell(0, 0, spreadNS.SheetArea.colHeader).value(" "); sheet.getCell(0, 1, spreadNS.SheetArea.colHeader).value("Value"); sheet.getCell(0, 2, spreadNS.SheetArea.colHeader).value("Formatter"); sheet.getCell(0, 3, spreadNS.SheetArea.colHeader).value("Display"); sheet.setColumnWidth(0, 10); sheet.setColumnWidth(1, 300); sheet.setColumnWidth(2, 300); sheet.setColumnWidth(3, 300); }; function startFormatterUnitTest(sheet, formatters) { for (var i = 0; i < formatters.length; i += 3) { //Mark the new added format in current version. if (sheet.name() === "Number Formatter") { if (i < 15 * 3) { sheet.getCell(i / 3, 0).value("*").font("20px arial bold"); } } if (sheet.name() === "Date Formatter") { if (i < 13 * 3) { sheet.getCell(i / 3, 0).value("*").font("20px arial bold"); } } sheet.setValue(i / 3, 1, formatters[i]); sheet.setValue(i / 3, 2, formatters[i + 1]); sheet.getCell(i / 3, 3).formatter(formatters[i + 1]); sheet.setText(i / 3, 3, formatters[i]); } }; function getActualCellRange(cellRange, rowCount, columnCount) { if (cellRange.row == -1 && cellRange.col == -1) { return new spreadNS.Range(0, 0, rowCount, columnCount); } else if (cellRange.row == -1) { return new spreadNS.Range(0, cellRange.col, rowCount, cellRange.colCount); } else if (cellRange.col == -1) { return new spreadNS.Range(cellRange.row, 0, cellRange.rowCount, columnCount); } return cellRange; };
<!doctype html> <html style="height:100%;font-size:14px;"> <head> <meta charset="utf-8" /> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> <link rel="stylesheet" type="text/css" href="$DEMOROOT$/en/purejs/node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/en/purejs/node_modules/@grapecity/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script> <script src="app.js" type="text/javascript"></script> <link rel="stylesheet" type="text/css" href="styles.css"> </head> <body> <div class="sample-tutorial"> <div id="ss" class="sample-spreadsheets"></div> <div class="options-container"> Select a cell in Spread, enter a format in this text box, and click the "SetFormat" button to set the format for that cell. The "ClearFormat" button can be used to clear the format in the selected cell in Spread. <div class="option-row"> <input type="text" id="format_text" value="##,##0" /> <input type="button" value="SetFormat" id="btnSetFormat" /> <input type="button" value="ClearFormat" id="btnClearFormat" /> </div> </div> </div> </body> </html>
.sample-tutorial { position: relative; height: 100%; overflow: hidden; } .sample-spreadsheets { width: calc(100% - 280px); height: 100%; overflow: hidden; float: left; } .options-container { float: right; width: 280px; padding: 12px; height: 100%; box-sizing: border-box; background: #fbfbfb; overflow: auto; } .option-row { font-size: 14px; padding: 5px; margin-top: 10px; } input { padding: 4px 8px; display: block; margin-top: 6px; } input[type=button] { display: inline-block; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; }