製品版のみの機能

スプレッドシート - 条件付き書式

このサンプルでは、カスタム Excel ワークシートを生成し、各列に条件付き書式を適用する方法を紹介します。
Wj
Sheet1
Sheet1

このサンプルは、より大きい画面サイズのためにデザインされました。

モバイル デバイスで画面を回転、フル サイズ表示、またはその他のデバイスにメールで送信します。

このサンプルでは、カスタム Excel ワークシートを生成し、各列に条件付き書式を適用する方法を紹介します。

コード ビュー

クリップボードへコピー 
<!DOCTYPE html>
<html>    
    <head>    

        <title>igSpreadsheet Conditional Formatting</title>

        <link href="http://cdn-na.infragistics.com/igniteui/2024.2/latest/css/themes/infragistics/infragistics.theme.css" rel="stylesheet" />        
        <link href="http://cdn-na.infragistics.com/igniteui/2024.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/2024.2/latest/js/infragistics.core.js"></script>
        <script src="http://cdn-na.infragistics.com/igniteui/2024.2/latest/js/infragistics.lob.js"></script>            
        <script src="http://cdn-na.infragistics.com/igniteui/2024.2/latest/js/infragistics.excel-bundled.js"></script>
        <script src="http://cdn-na.infragistics.com/igniteui/2024.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>