セルの書式を設定するには、setFormatterメソッドを使用して、セルの書式文字列を設定します。セルの書式を取得するには、getFormatterメソッドを使用します。formatterメソッドを使用してセル、列、または行を取得して、セルの書式を取得および設定することもできます。次に、例を示します。
セルの標準的な書式を設定できます。これには、GeneralFormatterを作成してセルに設定します。次に、例を示します。
色の書式では、color1からcolor56までのカラーインデックスもサポートしています。次に、例を示します。
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","１２,３４５",
"12345", "[DBNum1][$-411]General","一万二千三百四十五",
"12345", "[DBNum2][$-411]General","壱萬弐阡参百四拾伍",
"12345", "[DBNum3][$-411]General", "１２３４５",
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="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/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/ja/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/ja/purejs/node_modules/@mescius/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;
}
