概要と基本的な使い方

SpreadJSでは、各セルの書式をカスタマイズすることで、必要に応じた形式でデータを表示できます。この機能は、データベースから取得した未加工データを、ユーザーが分かりやすい形式で表示したい場合に便利です。

セルのフォーマッタを設定するには、setFormatterメソッドを使用して、セルのフォーマッタ文字列を設定します。セルのフォーマッタを取得するには、getFormatterメソッドを使用します。formatterメソッドを使用してセル、列、または行を取得して、セルのフォーマッタを取得および設定することもできます。次に、例を示します。 セルの標準的な書式を設定できます。これには、GeneralFormatterを作成してセルに設定します。次に、例を示します。
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 name="spreadjs culture" content="ja-jp" /> <meta charset="utf-8" /> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> <link rel="stylesheet" type="text/css" href="$DEMOROOT$/ja/purejs/node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/ja/purejs/node_modules/@grapecity/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/ja/purejs/node_modules/@grapecity/spread-sheets-resources-ja/dist/gc.spread.sheets.resources.ja.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"> Spreadのいずれかのセルを選択し、次のテキストボックスに書式を入力して、[書式を設定]ボタンをクリックします。このセルに、入力した書式が適用されます。[書式をクリア]ボタンをクリックすると、Spreadで選択したセルから書式が削除されます。 <div class="option-row"> <input type="text" id="format_text" value="##,##0" /> <input type="button" value="書式を設定" id="btnSetFormat" /> <input type="button" value="書式をクリア" 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; }