基本应用

在 Spread Sheets 中,你可以为每一个单元格自定义格式,以便数据以你想要的方式展现。当你的原始数据是从一片数据生成出来的,而且你想展示一个对用户友好的数据格式,那么这个将非常有用。

要为一个单元格设置格式,需要通过调用 setFormatter 方法来为单元格设置一个格式字符串。调用 getFormatter 方法可以获得这个单元格的格式。你也可以单元格,列或者行上的格式方法来获取或者设置单元格上的格式。例如: 你可以为单元格设置一个常规的格式。创建一个 GeneralFormatter 然后设置给单元格。例如:
var spreadNS = GC.Spread.Sheets; window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 5 }); 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', '' ]; var colorFormatterArray = [ "1","[color 1]0.0","", "2","[color 2]0.0","", "3","[color 3]0.0","", "4","[color 4]0.0","", "5","[color 5]0.0","", "6","[color 6]0.0","", "7","[color 7]0.0","", "8","[color 8]0.0","", "9","[color 9]0.0","", "10","[color 10]0.0","", "11","[color 11]0.0","", "12","[color 12]0.0","", "13","[color 13]0.0","", "14","[color 14]0.0","", "15","[color 15]0.0","", "16","[color 16]0.0","", "17","[color 17]0.0","", "18","[color 18]0.0","", "19","[color 19]0.0","", "20","[color 20]0.0","", "21","[color 21]0.0","", "22","[color 22]0.0","", "23","[color 23]0.0","", "24","[color 24]0.0","", "25","[color 25]0.0","", "26","[color 26]0.0","", "27","[color 27]0.0","", "28","[color 28]0.0","", "29","[color 29]0.0","", "30","[color 30]0.0","", "31","[color 31]0.0","", "32","[color 32]0.0","", "33","[color 33]0.0","", "34","[color 34]0.0","", "35","[color 35]0.0","", "36","[color 36]0.0","", "37","[color 37]0.0","", "38","[color 38]0.0","", "39","[color 39]0.0","", "40","[color 40]0.0","", "41","[color 41]0.0","", "42","[color 42]0.0","", "43","[color 43]0.0","", "44","[color 44]0.0","", "45","[color 45]0.0","", "46","[color 46]0.0","", "47","[color 47]0.0","", "48","[color 48]0.0","", "49","[color 49]0.0","", "50","[color 50]0.0","", "51","[color 51]0.0","", "52","[color 52]0.0","", "53","[color 53]0.0","", "54","[color 54]0.0","", "55","[color 55]0.0","", "56","[color 56]0.0","", ] function initSpread(spread) { spread.suspendPaint(); spread.options.tabStripRatio = 0.8; sheet=spread.getSheet(4); sheet.name("Color Formatter"); initFormatterUnitTest(sheet); startFormatterUnitTest(sheet,colorFormatterArray); 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 name="spreadjs culture" content="zh-cn" /> <meta charset="utf-8" /> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> <link rel="stylesheet" type="text/css" href="$DEMOROOT$/zh/purejs/node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity/spread-sheets-resources-zh/dist/gc.spread.sheets.resources.zh.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; }