Created
May 10, 2017 06:31
-
-
Save TristanD-MSFT/80920d57bf587bd859ff62afb2b7c673 to your computer and use it in GitHub Desktop.
Apply conditional formatting to ranges - Shared with Script Lab
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| name: Conditional Formatting in Excel Javascript API | |
| description: Apply conditional formatting to ranges | |
| author: TristanD-MSFT | |
| host: EXCEL | |
| api_set: {} | |
| script: | |
| content: "$(\"#setup\").click(setup);\n$(\"#list-conditional-formats\").click(listConditionalFormats);\n$(\"#apply-color-scale-format\").click(applyColorScaleFormat);\n$(\"#clear-all-conditional-formats\").click(clearAllConditionalFormats);\n\nasync function listConditionalFormats() {\n try {\n await Excel.run(async (context) => {\n const sheet = context.workbook.worksheets.getItem(\"Sample\");\n sheet.load(\"name\");\n const worksheetRange = sheet.getRange();\n worksheetRange.load(\"conditionalFormats/type\");\n await context.sync();\n\n $(\"#conditional-formats li\").remove();\n\n if (worksheetRange.conditionalFormats.items.length > 0) {\n for (let i = 0; i < worksheetRange.conditionalFormats.items.length; i++) {\n let $a = $(`<a href=''>${worksheetRange.conditionalFormats.items[i].type}</a>`).click({ worksheet: sheet.name, index: i }, async (e: any) => {\n e.preventDefault();\n const sheet = context.workbook.worksheets\n .getItem(e.data.worksheet);\n const conditionalFormat = sheet.getRange()\n .conditionalFormats.getItemAt(e.data.index);\n const range = conditionalFormat.getRange();\n range.select();\n await context.sync();\n });\n\n let $li = $(`<li></li>`);\n $li.append($a);\n\n $(\"#conditional-formats\").append($li);\n $(\".conditional-formats\").show();\n }\n} else\n {\n showNotification(\"No conditional formats in workbook\");\n }\n });\n }\n catch (error) {\n OfficeHelpers.Utilities.log(error);\n }\n}\n\nasync function applyColorScaleFormat() {\n try {\n await Excel.run(async (context) => {\n const sheet = context.workbook.worksheets.getItem(\"Sample\");\n const range = sheet.getRange(\"B2:M5\");\n range.conditionalFormats\n .add(Excel.ConditionalFormatType.colorScale);\n const conditionalFormatCount = range.conditionalFormats.getCount();\n await context.sync();\n\n const conditionalFormat = range.conditionalFormats\n .getItemAt(conditionalFormatCount.value - 1);\n context.load(conditionalFormat);\n conditionalFormat.priority = 0;\n await context.sync();\n\n const criteria = {\n\tminimum: { formula: null, type: Excel.ConditionalFormatColorCriterionType.lowestValue, color: \"blue\" },\n\tmidpoint: { formula: \"50\", type: Excel.ConditionalFormatColorCriterionType.percent, color: \"yellow\" },\n\tmaximum: { formula: null, type: Excel.ConditionalFormatColorCriterionType.highestValue, color: \"red\" }\n }; \n\n conditionalFormat.colorScale.criteria = criteria;\n await context.sync();\n });\n }\n catch (error) {\n OfficeHelpers.Utilities.log(error);\n }\n}\n\nasync function clearAllConditionalFormats() {\n try {\n await Excel.run(async (context) => {\n\n const sheet = context.workbook.worksheets.getItem(\"Sample\");\n const range = sheet.getRange();\n\n range.conditionalFormats.clearAll();\n \n await context.sync();\n\n $(\".conditional-formats\").hide();\n });\n }\n catch (error) {\n OfficeHelpers.Utilities.log(error);\n }\n}\n\nasync function setup() {\n\ttry {\n\t\tawait Excel.run(async (context) => {\n const sheet = await OfficeHelpers.ExcelUtilities\n .forceCreateSheet(context.workbook, \"Sample\");\n\n const data = [\n [\"\", \"Jan\", \"Feb\", \"Mar\", \"Apr\", \"May\", \"Jun\", \"Jul\", \"Aug\", \"Sep\", \"Oct\", \"Nov\", \"Dec\"],\n [\"Avg High\", 40, 38, 44, 45, 51, 56, 67, 72, 79, 59, 45, 41],\n [\"Avg Low\", 34, 33, 38, 41, 45, 48, 51, 55, 54, 45, 41, 38],\n [\"Record High\", 61, 69, 79, 83, 95, 97, 100, 101, 94, 87, 72, 66],\n [\"Record Low\", 0, 2, 9, 24, 28, 32, 36,39, 35, 21, 12, 4]\n ];\n\n const range = sheet.getRange(\"A1\")\n .getResizedRange(data.length - 1, data[0].length - 1);\n range.values = data;\n \n const header = sheet.getRange(\"B1:M1\");\n header.format.font.bold = true;\n\n range.format.autofitColumns();\n\n sheet.activate();\n\t\t await context.sync();\n\t });\n }\n\tcatch (error) {\n\t\tOfficeHelpers.Utilities.log(error);\n\t}\n}\n\n/**\n * Display the notification having synced the changes.\n */\nfunction showNotification(message: string) {\n const messageBanner = $('.ms-MessageBanner');\n $('.ms-MessageBanner-clipper').text(message);\n $('.ms-MessageBanner-close').click(() => {\n messageBanner.hide();\n messageBanner.off('click');\n });\n messageBanner.show();\n}\n" | |
| language: typescript | |
| template: | |
| content: | | |
| <!-- Fabric Message Banner template --> | |
| <!-- https://dev.office.com/fabric-js/Components/MessageBanner/MessageBanner.html --> | |
| <div class="ms-MessageBanner"> | |
| <div class="ms-MessageBanner-content"> | |
| <div class="ms-MessageBanner-text"> | |
| <div class="ms-MessageBanner-clipper"></div> | |
| </div> | |
| </div> | |
| <button class="ms-MessageBanner-close"> | |
| <i class="ms-Icon ms-Icon--Clear"></i> | |
| </button> | |
| </div> | |
| <section id="main" class="ms-font-m"> | |
| <p>This sample shows how to apply conditional formatting to ranges using the Excel JavaScript API.</p> | |
| </section> | |
| <section id="main" class="setup ms-font-m"> | |
| <h3>Set up</h3> | |
| <button id="setup" class="ms-Button"> | |
| <span class="ms-Button-label">Add sample data</span> | |
| </button> | |
| </section> | |
| <section id="main" class="samples ms-font-m"> | |
| <h3>Try it out</h3> | |
| <button id="list-conditional-formats" class="ms-Button"> | |
| <span class="ms-Button-label">List conditional formats</span> | |
| </button> | |
| <button id="apply-color-scale-format" class="ms-Button"> | |
| <span class="ms-Button-label">Apply color scale format</span> | |
| </button> | |
| <button id="clear-all-conditional-formats" class="ms-Button"> | |
| <span class="ms-Button-label">Clear all conditional formats</span> | |
| </button> | |
| </section> | |
| <section id="main" class="conditional-formats ms-font-m" hidden=true> | |
| <h3>Conditional Formats</h3> | |
| <ul id="conditional-formats" class="ms-font-m"> | |
| </ul> | |
| </section> | |
| language: html | |
| style: | |
| content: "section.samples {\r\n margin-top: 20px;\r\n}\r\n\r\nsection.samples .ms-Button, section.setup .ms-Button {\r\n display: block;\r\n margin-bottom: 5px;\r\n margin-left: 20px;\r\n min-width: 80px;\r\n}\r\n\r\nbody {\r\n margin: 0;\r\n padding: 0;\r\n}\r\n\r\n#main {\r\n margin: 10px;\r\n}\r\n\r\n.ms-MessageBanner {\r\n display: none;\r\n}" | |
| language: css | |
| libraries: | | |
| # Office.js | |
| https://appsforoffice.microsoft.com/lib/1/hosted/office.js | |
| # CSS Libraries | |
| office-ui-fabric-js@1.4.0/dist/css/fabric.min.css | |
| office-ui-fabric-js@1.4.0/dist/css/fabric.components.min.css | |
| # NPM libraries | |
| core-js@2.4.1/client/core.min.js | |
| @microsoft/office-js-helpers@0.6.0/dist/office.helpers.min.js | |
| jquery@3.1.1 | |
| # IntelliSense: @types/library or node_modules paths or URL to d.ts files | |
| @types/office-js | |
| @types/core-js | |
| @microsoft/office-js-helpers/dist/office.helpers.d.ts | |
| @types/jquery |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment