製品版のみの機能
スプレッドシート - 条件付き書式
このサンプルでは、カスタム Excel ワークシートを生成し、各列に条件付き書式を適用する方法を紹介します。
このサンプルは CTP 機能を使用しています。製品版では、API や動作が変更される場合があります。
このサンプルは、より大きい画面サイズのためにデザインされました。
モバイル デバイスで画面を回転、フル サイズ表示、またはその他のデバイスにメールで送信します。
このサンプルでは、カスタム Excel ワークシートを生成し、各列に条件付き書式を適用する方法を紹介します。
コード ビュー
クリップボードへコピー
<!DOCTYPE html>
<html>
<head>
<title>igSpreadsheet Conditional Formatting</title>
<link href="http://cdn-na.infragistics.com/igniteui/2023.2/latest/css/themes/infragistics/infragistics.theme.css" rel="stylesheet" />
<link href="http://cdn-na.infragistics.com/igniteui/2023.2/latest/css/structure/modules/infragistics.ui.spreadsheet.css" rel="stylesheet" />
<script src="http://ajax.aspnetcdn.com/ajax/modernizr/modernizr-2.8.3.js"></script>
<script src="http://code.jquery.com/jquery-1.11.3.min.js"></script>
<script src="http://code.jquery.com/ui/1.11.1/jquery-ui.min.js"></script>
<script src="http://cdn-na.infragistics.com/igniteui/2023.2/latest/js/infragistics.core.js"></script>
<script src="http://cdn-na.infragistics.com/igniteui/2023.2/latest/js/infragistics.lob.js"></script>
<script src="http://cdn-na.infragistics.com/igniteui/2023.2/latest/js/infragistics.excel-bundled.js"></script>
<script src="http://cdn-na.infragistics.com/igniteui/2023.2/latest/js/infragistics.spreadsheet-bundled.js"></script>
</head>
<body>
<div>
<input class="spreadsheet-sample-btn" id="createWorkbookBtn" type="button" value="ワークブックを生成" onclick="createWorkbook()">
<div id="spreadsheet"></div>
</div>
<script>
$(function () {
var editingEnabled = true;
//Initializing igSpreadsheet
$("#spreadsheet").igSpreadsheet({
height: "600",
width: "100%",
isFormulaBarVisible: true,
editModeEntering: function (e, args) {
return editingEnabled;
}
});
});
//display the workbook via igSpreadsheet
function loadWorkbook(workbook) {
$("#spreadsheet").igSpreadsheet("option", "workbook", workbook);
}
function createWorkbook() {
var workbook = new $.ig.excel.Workbook($.ig.excel.WorkbookFormat.excel2007);
var sheet = workbook.worksheets().add('Sheet1');
var letters = ["A", "B", "C", "D", "E", "F", "G"]
var headers = ["SKU", "Name", "Category", "Supplier", "Back Order", "Unit Price", "Units in Stock"]
var skus = ["401043204-423","534041202-345","601041205-784","51041204-426","41041204-427","31041204-428","21041204-429","41041204-430","61022204-431","11043204-432","41041204-433","21041204-434","401043204-423","31041204-436","21041204-437"]
var names = ["Guaraná Fantástica","NuNuCa Nuß-Nougat-Creme","Gumbär Gummibärchen","Schoggi Schokolade","Rössle Sauerkraut","","Nord-Ost Matjeshering","Gorgonzola Telino","Mascarpone Fabioli","Geitost","Sasquatch Ale","","Inlagd Sill","Gravad lax"]
var categories = ["Grains/Cereals","Beverages","Confections","Confections","Confections","Produce","Meat/Poultry","Seafood","Dairy Products","Dairy Products","Dairy Products","Beverages","Beverages","Seafood","Seafood"]
var suppliers = ["PB Knäckebröd AB","Refrescos Americanas LTDA","Heli Süßwaren GmbH & Co. KG","Heli Süßwaren GmbH & Co. KG","Heli Süßwaren GmbH & Co. KG","Plutzer Lebensmittelgroßmärkte AG","Plutzer Lebensmittelgroßmärkte AG","Nord-Ost-Fisch Handelsgesellschaft mbH","Formaggi Fortini s.r.l.","Formaggi Fortini s.r.l.","Norske Meierier","Bigfoot Breweries","Bigfoot Breweries","Svensk Sjöföda AB","Svensk Sjöföda AB"]
for (var i = 0; i < letters.length; i++) {
for (var j = 1; j < skus.length + 1; j++) {
var str = letters[i] + (j).toString();
var cell = sheet.getCell(str);
if (j == 1) {
sheet.columns(i).width(6000);
cell.value(headers[i]);
}
else {
if (i == 0) {
cell.value(skus[j - 2]);
}
else if (i == 1) {
cell.value(names[j - 2]);
}
else if (i == 2) {
cell.value(categories[j - 2]);
}
else if (i == 3) {
cell.value(suppliers[j - 2]);
}
else if (i == 4) {
var x = getRandomBetween(1, 2);
if (x == 1) {
cell.value(true);
}
else {
cell.value(false);
}
}
else if (i == 5) {
var x = getRandomBetween(1, 100);
cell.value(x);
}
else if (i == 6) {
var x = getRandomBetween(1, 100);
cell.value(x);
}
}
}
}
var duplicateCondition = sheet.conditionalFormats().addDuplicateCondition("A2:A15");
duplicateCondition.cellFormat().font().colorInfo(new $.ig.excel.WorkbookColorInfo("red"));
var blanksCondition = sheet.conditionalFormats().addBlanksCondition("B2:B15");
blanksCondition.cellFormat().fill($.ig.excel.CellFill.createSolidFill("gray"));
var textCondition = sheet.conditionalFormats().addTextCondition("C2:C15", "Bev");
textCondition.cellFormat().font().colorInfo(new $.ig.excel.WorkbookColorInfo("blue"));
var uniqueCondition = sheet.conditionalFormats().addUniqueCondition("D2:D15");
uniqueCondition.cellFormat().font().colorInfo(new $.ig.excel.WorkbookColorInfo("orange"));
var operatorCondition1 = sheet.conditionalFormats().addOperatorCondition("E2:E15");
operatorCondition1.setOperand1("TRUE");
operatorCondition1.cellFormat().font().colorInfo(new $.ig.excel.WorkbookColorInfo("green"));
var operatorCondition2 = sheet.conditionalFormats().addOperatorCondition("E2:E15");
operatorCondition2.setOperand1("FALSE");
operatorCondition2.cellFormat().font().colorInfo(new $.ig.excel.WorkbookColorInfo("red"));
sheet.conditionalFormats().addDataBarCondition("F2:F15");
var avgCondition = sheet.conditionalFormats().addAverageCondition("G2:G15");
avgCondition.cellFormat().font().colorInfo(new $.ig.excel.WorkbookColorInfo("red"));
loadWorkbook(workbook);
}
function getRandomBetween(min, max){
return Math.floor(Math.random() * (max - min + 1)) + min;
}
</script>
</body>
</html>