概要と基本的な使い方

シートに数千行規模のデータが含まれているとしたら、未加工の情報を検証するだけでは、パターンや傾向を読み取ることは至難の業でしょう。スパークライン同様、条件付き書式は、データを視覚化し、理解しやすいシートを構築するための1つの手段となります。

Spread.Sheetsに条件付き書式を追加するには、まずconditionalFormatsを使用して、シートの条件付き書式を取得します。次に、任意の条件付きルールを作成して、このルールをaddRuleメソッドを使用して追加します。また、addSpecificTextRule、addCellValueRuleなど、指定のルールのaddメソッドを使用することもできます。次に、例を示します。 シートにルールを追加した後は、getRuleメソッドを使用して、ルールインデックスを指定して特定のルールを返すことも、すべての条件付きルールを取得することもできます。次に、例を示します。 条件付きルールを削除するには、removeRule、removeRuleByRange、またはclearRuleメソッドを使用します。次に、例を示します。
var spreadNS = GC.Spread.Sheets; window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 2 }); initSpread(spread); }; function initSpread(spread) { var sheet = spread.getSheet(0); sheet.suspendPaint(); var cfs = sheet.conditionalFormats ; // sample title sheet.addSpan(1, 1, 1, 7); sheet.setValue(1, 1, "Conditional Format Samples"); sheet.getCell(1, 1).font("24px sans-serif"); sheet.getCell(1, 1).hAlign(spreadNS.HorizontalAlign.center); sheet.setRowHeight(1, 35); // cell value rule var r = 2; var c = 1; var w = 3; var h = 4; sheet.addSpan(r, c, 1, w); sheet.setValue(r, c, "Displays green background: value > 50:"); sheet.getCell(r, c).wordWrap(true); sheet.setRowHeight(r, 40); var increase = 0; for (var row = 0; row < h - 1; row++) { for (var col = 0; col < w; col++) { sheet.setValue(row + 3, col + 1, increase); increase += 10; } } sheet.getRange(r, c, h, w).setBorder(new spreadNS.LineBorder("Black", spreadNS.LineStyle.dashed), {all: true}); var style = new spreadNS.Style(); style.backColor = "#CCFFCC"; cfs.addCellValueRule(spreadNS.ConditionalFormatting.ComparisonOperators.greaterThan, 50, 0, style, [new spreadNS.Range(r + 1, c, h - 1, w)]); r = 2; c = 5; sheet.addSpan(r, c, 1, w); sheet.setValue(r, c, "Displays red background: value >= 60 and value <= 70:"); sheet.getCell(r, c).wordWrap(true); sheet.setRowHeight(r, 40); increase = 0; for (var row = 0; row < h - 1; row++) { for (var col = 0; col < w; col++) { sheet.setValue(row + r + 1, col + c, increase); increase += 10; } } sheet.getRange(r, c, h, w).setBorder(new spreadNS.LineBorder("Black", spreadNS.LineStyle.dashed), {all: true}); style = new spreadNS.Style(); style.backColor = "#FFCCCC"; cfs.addCellValueRule(spreadNS.ConditionalFormatting.ComparisonOperators.between, 60, 70, style, [ new spreadNS.Range(r + 1, c, h - 1, w) ]); // 2 color scale rule r = 7; c = 1; sheet.addSpan(r, c, 1, w); sheet.setValue(r, c, "Displays two color gradient represents cell value:"); sheet.getCell(r, c).wordWrap(true); sheet.setRowHeight(r, 40); increase = 0; for (var row = 0; row < h - 1; row++) { for (var col = 0; col < w; col++) { sheet.setValue(row + r + 1, col + c, increase); increase += 10; } } sheet.getRange(r, c, h, w).setBorder(new spreadNS.LineBorder("Black", spreadNS.LineStyle.dashed), {all: true}); cfs.add2ScaleRule(spreadNS.ConditionalFormatting.ScaleValueType.lowestValue, null, "#FF9999", spreadNS.ConditionalFormatting.ScaleValueType.highestValue, null, "#9999FF", [ new spreadNS.Range(r + 1, c, h - 1, w) ]); // 3 color scale rule r = 7; c = 5; sheet.addSpan(r, c, 1, w); sheet.setValue(r, c, "Displays three color gradient represents cell value:"); sheet.getCell(r, c).wordWrap(true); sheet.setRowHeight(r, 40); increase = 0; for (var row = 0; row < h - 1; row++) { for (var col = 0; col < w; col++) { sheet.setValue(row + r + 1, col + c, increase); increase += 10; } } sheet.getRange(r, c, h, w).setBorder(new spreadNS.LineBorder("Black", spreadNS.LineStyle.dashed), {all: true}); cfs.add3ScaleRule(spreadNS.ConditionalFormatting.ScaleValueType.lowestValue, null, "#FF9999", spreadNS.ConditionalFormatting.ScaleValueType.number, 100, "#99FF99", spreadNS.ConditionalFormatting.ScaleValueType.highestValue, null, "#9999FF", [new spreadNS.Range(r + 1, c, h - 1, w) ]); // date occurring rule addDays = function (date, days) { var dt = new Date(date.getFullYear(), date.getMonth(), date.getDate() + days); if (days) { if (dt.getDate() === date.getDate()) { dt = new Date(date.getFullYear(), date.getMonth(), date.getDate()); dt.setTime(dt.getTime() + (days * 24 * 3600 * 1000)); } } return dt; }; r = 12; c = 1; sheet.addSpan(r, c, 1, w); sheet.setValue(r, c, "Display blue background if cell value is in next week:"); sheet.getCell(r, c).wordWrap(true); sheet.setRowHeight(r, 40); increase = 0; for (var row = 0; row < h - 1; row++) { for (var col = 0; col < w; col++) { sheet.setValue(row + r + 1, col + c, addDays(new Date(), increase)); sheet.setFormatter(row + r + 1, col + c, "yyyy/mm/dd", spreadNS.SheetArea.viewport); increase += 1; } } sheet.getRange(r, c, h, w).setBorder(new spreadNS.LineBorder("Black", spreadNS.LineStyle.dashed), {all: true}); style = new spreadNS.Style(); style.backColor = "#CCCCFF"; cfs.addDateOccurringRule(spreadNS.ConditionalFormatting.DateOccurringType.nextWeek, style, [ new spreadNS.Range(r + 1, c, h - 1, w) ]); // specific text rule r = 12; c = 5; sheet.addSpan(r, c, 1, w); sheet.setValue(r, c, "Display red foreground if cell value contains \"o\":"); sheet.getCell(r, c).wordWrap(true); sheet.setRowHeight(r, 40); var data = ["The", "quick", "brown", "fox", "jumps", "over", "the", "lazy", "dog", "The", "quick", "brown", "fox", "jumps", "over", "the", "lazy", "dog"]; increase = 0; for (var row = 0; row < h - 1; row++) { for (var col = 0; col < w; col++) { sheet.setValue(row + r + 1, col + c, data[increase]); increase += 1; } } sheet.getRange(r, c, h, w).setBorder(new spreadNS.LineBorder("Black", spreadNS.LineStyle.dashed), {all: true}); var style = new spreadNS.Style(); style.foreColor = "Red"; style.font = "bold 12px sans-serif"; cfs.addSpecificTextRule(spreadNS.ConditionalFormatting.TextComparisonOperators.contains, "o", style, [ new spreadNS.Range(r + 1, c, h - 1, w) ]); // data bar rule r = 17; c = 1; sheet.addSpan(r, c, 1, w); sheet.setValue(r, c, "Display a colored data bar represents cell value:"); sheet.getCell(r, c).wordWrap(true); sheet.setRowHeight(r, 40); increase = 0; for (var row = 0; row < h - 1; row++) { for (var col = 0; col < w; col++) { sheet.setValue(row + r + 1, col + c, increase); increase += 10; } } sheet.getRange(r, c, h, w).setBorder(new spreadNS.LineBorder("Black", spreadNS.LineStyle.dashed), { all: true }); var dataBarRule = cfs.addDataBarRule(spreadNS.ConditionalFormatting.ScaleValueType.lowestValue, null, spreadNS.ConditionalFormatting.ScaleValueType.highestValue, null, "green", [new spreadNS.Range(r + 1, c, h - 1, w)]); dataBarRule.gradient(true); dataBarRule.showBorder(false); dataBarRule.showBarOnly(false); // icon set rule r = 17; c = 5; sheet.addSpan(r, c, 1, w); sheet.setValue(r, c, "Displays an icon represents cell value:"); sheet.getCell(r, c).wordWrap(true); sheet.setRowHeight(r, 40); increase = 0; for (var row = 0; row < h - 1; row++) { for (var col = 0; col < w; col++) { sheet.setValue(row + r + 1, col + c, increase); increase += 10; } } sheet.getRange(r, c, h, w).setBorder(new spreadNS.LineBorder("Black", spreadNS.LineStyle.dashed), { all: true }); var iconSetRule = cfs.addIconSetRule(spreadNS.ConditionalFormatting.IconSetType.fiveArrowsColored, [new spreadNS.Range(r + 1, c, h - 1, w)]); iconSetRule.iconSetType(); iconSetRule.reverseIconOrder(false); iconSetRule.showIconOnly(false); sheet.resumePaint(); updateRuleCount(); document.getElementById("removeRule").addEventListener('click',function() { var sels = sheet.getSelections(); if (sels && sels.length > 0) { var sel = sels[0]; cfs.removeRuleByRange(sel.row, sel.col, sel.rowCount, sel.colCount); updateRuleCount(); } }); document.getElementById("clearRule").addEventListener('click',function() { cfs.clearRule(); updateRuleCount(); }); function updateRuleCount() { document.getElementById("ruleCount").innerHTML="ルールの数: " + cfs.count(); } }; function getActualRange(range, maxRowCount, maxColCount) { var row = range.row < 0 ? 0 : range.row; var col = range.col < 0 ? 0 : range.col; var rowCount = range.rowCount < 0 ? maxRowCount : range.rowCount; var colCount = range.colCount < 0 ? maxColCount : range.colCount; return new spreadNS.Range(row, col, rowCount, colCount); }
<!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"> <p>ルールの設定されたセルを選択してください。</p> <div class="option-row"> <label id="ruleCount"></label> </div> <div class="option-row"> <input type="button" id="removeRule" value="選択したセル範囲のルールを削除" /> <input type="button" id="clearRule" value="すべてのルールを削除"> </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; overflow: auto; padding: 12px; height: 100%; box-sizing: border-box; background: #fbfbfb; } .option-row { padding-top: 6px; } input { padding: 4px 8px; margin-bottom: 6px; box-sizing: border-box; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; }