with JavaScript


Create Project Folder

Open Visual Studio and create a New Project.
Find "Excel Web Add-in" (with C#) and press Next.
Change the Project Name to "ExcelVisualStudio"
Change the Location by clicking on the Browse button
Tick the "Place solution and project in the same directory" checkbox.
The Create Office Add-in wizard will appear.
Select "Add new functionalities to Excel" and press Finish.

This will create a solution that contains two projects:
ExcelVisualStudio - This contains the XML manifest file.
ExcelVisualStudioWeb - This contains the corresponding HTML pages.


ExcelVisualStudio.xml

<?xml version="1.0" encoding="UTF-8"?> <OfficeApp 
          xmlns="http://schemas.microsoft.com/office/appforoffice/1.1"
          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
          xmlns:bt="http://schemas.microsoft.com/office/officeappbasictypes/1.0"
          xmlns:ov="http://schemas.microsoft.com/office/taskpaneappversionoverrides"
          xsi:type="TaskPaneApp">

  <Id> Unique GUID </Id>
  <Version>1.0.0.0</Version>
  <ProviderName>Better Solutions Limited</ProviderName>
  <DefaultLocale>en-US</DefaultLocale>
  <DisplayName DefaultValue="ExcelVisualStudio" />
  <Description DefaultValue="ExcelVisualStudio"/>
  <IconUrl DefaultValue="~remoteAppUrl/Images/Button32x32.png" />
  <SupportUrl DefaultValue="http://bettersolutions.com" />
  <AppDomains>
    <AppDomain>AppDomain1</AppDomain>
  </AppDomains>
  <Hosts>
    <Host Name="Workbook" />
  </Hosts>
  <DefaultSettings>
    <SourceLocation DefaultValue="~remoteAppUrl/Home.html" />
  </DefaultSettings>
  <Permissions>ReadWriteDocument</Permissions>
  <VersionOverrides xmlns="http://schemas.microsoft.com/office/taskpaneappversionoverrides" xsi:type="VersionOverridesV1_0">
    <Hosts>
      <Host xsi:type="Workbook">
        <DesktopFormFactor>
          <GetStarted>
            <Title resid="Contoso.GetStarted.Title"/>
            <Description resid="Contoso.GetStarted.Description"/>
            <LearnMoreUrl resid="Contoso.GetStarted.LearnMoreUrl"/>
          </GetStarted>
          <FunctionFile resid="Contoso.DesktopFunctionFile.Url" />
          <ExtensionPoint xsi:type="PrimaryCommandSurface">
            <OfficeTab id="TabHome">
              <Group id="Contoso.Group1">
                <Label resid="Contoso.Group1Label" />
                <Icon>
                  <bt:Image size="16" resid="Contoso.tpicon_16x16" />
                  <bt:Image size="32" resid="Contoso.tpicon_32x32" />
                  <bt:Image size="80" resid="Contoso.tpicon_80x80" />
                </Icon>
                <Control xsi:type="Button" id="Contoso.TaskpaneButton">
                  <Label resid="Contoso.TaskpaneButton.Label" />
                  <Supertip>
                    <Title resid="Contoso.TaskpaneButton.Label" />
                    <Description resid="Contoso.TaskpaneButton.Tooltip" />
                  </Supertip>
                  <Icon>
                    <bt:Image size="16" resid="Contoso.tpicon_16x16" />
                    <bt:Image size="32" resid="Contoso.tpicon_32x32" />
                    <bt:Image size="80" resid="Contoso.tpicon_80x80" />
                  </Icon>
                  <Action xsi:type="ShowTaskpane">
                    <TaskpaneId>ButtonId1</TaskpaneId>
                    <SourceLocation resid="Contoso.Taskpane.Url" />
                  </Action>
                </Control>
              </Group>
            </OfficeTab>
          </ExtensionPoint>
        </DesktopFormFactor>
      </Host>
    </Hosts>
    <Resources>
      <bt:Images>
        <bt:Image id="Contoso.tpicon_16x16" DefaultValue="~remoteAppUrl/Images/Button16x16.png" />
        <bt:Image id="Contoso.tpicon_32x32" DefaultValue="~remoteAppUrl/Images/Button32x32.png" />
        <bt:Image id="Contoso.tpicon_80x80" DefaultValue="~remoteAppUrl/Images/Button80x80.png" />
      </bt:Images>
      <bt:Urls>
        <bt:Url id="Contoso.DesktopFunctionFile.Url" DefaultValue="~remoteAppUrl/Functions/FunctionFile.html" />
        <bt:Url id="Contoso.Taskpane.Url" DefaultValue="~remoteAppUrl/Home.html" />
        <bt:Url id="Contoso.GetStarted.LearnMoreUrl" DefaultValue="https://go.microsoft.com/fwlink/?LinkId=276812" />
      </bt:Urls>
      <bt:ShortStrings>
        <bt:String id="Contoso.TaskpaneButton.Label" DefaultValue="Show Taskpane" />
        <bt:String id="Contoso.Group1Label" DefaultValue="Commands Group" />
        <bt:String id="Contoso.GetStarted.Title" DefaultValue="Get started with your sample add-in!" />
      </bt:ShortStrings>
      <bt:LongStrings>
        <bt:String id="Contoso.TaskpaneButton.Tooltip" DefaultValue="Click to Show a Taskpane" />
        <bt:String id="Contoso.GetStarted.Description" DefaultValue="Your sample add-in loaded succesfully" />
      </bt:LongStrings>
    </Resources>
  </VersionOverrides>
</OfficeApp>

Home.html

This html file must include a reference to office.js
This html file must call its corresponding javascript file (Home.js).

<!DOCTYPE html> 
<html>
<head>
   <meta charset="UTF-8"/>
   <meta http-equiv="X-UA-Compatible" content="IE=Edge"/>
   <title>Excel Add-In with Commands Sample</title>

   <script src="https://appsforoffice.microsoft.com/lib/1/hosted/office.js" type="text/javascript"></script>
   <script src="Scripts/jquery-3.4.1.js" type="text/javascript"></script>
   <script src="Scripts/MessageBanner.js" type="text/javascript"></script>
   <script src="Home.js" type="text/javascript"></script>

   <link href="Home.css" type="text/css" rel="stylesheet"/>
   <link href="https://static2.sharepointonline.com/files/fabric/office-ui-fabric-core/9.6.0/css/fabric.min.css" />
   <link href="../Contents/Button.css" type="text/css" rel="stylesheet"/>
   <link href="../Contents/MessageBanner.css" type="text/css" rel="stylesheet"/>
</head>
<body>
    <div id="content-main">
        <div class="padding">
            <br />
            <div class="ms-font-xxl ms-fontColor-neutralSecondary ms-fontWeight-semilight">Sample</div>
            <br /><br />
            <div class="ms-font-xl ms-fontColor-neutralTertiary">Getting Started</div>
            <p class="ms-font-m-plus ms-fontColor-neutralTertiary" id="template-description"></p>

            <div class="ms-font-m"><a target="_blank" class="ms-Link ms-Link--hero"
            href="https://go.microsoft.com/fwlink/?LinkId=276812">Find more samples online</a></div>
            <br /><br />
            <button class="ms-Button ms-Button--primary" id="highlight-button">
                <span class="ms-Button-icon"><i class="ms-Icon ms-Icon--plus"></i></span>
                <span class="ms-Button-label" id="button-text"></span>
                <span class="ms-Button-description" id="button-desc"></span>
            </button>
        </div>
    </div>
    <div class="footer">
        <div class="ms-Grid ms-bgColor-themeSecondary">
            <div class="ms-Grid-row">
                <div class="ms-Grid-col ms-u-sm12 ms-u-md12 ms-u-lg12">
                   <div class="ms-font-xl ms-fontColor-white">Contoso</div></div>
            </div>
        </div>
    </div>

    <div class="ms-MessageBanner" id="notification-popup">
        <div class="ms-MessageBanner-content">
            <div class="ms-MessageBanner-text">
                <div class="ms-MessageBanner-clipper">
                    <div class="ms-font-m-plus ms-fontWeight-semibold" id="notification-header"></div>
                    <div class="ms-font-m ms-fontWeight-semilight" id="notification-body"></div>
                </div>
            </div>
            <button class="ms-MessageBanner-expand" style="display:none">
                <i class="ms-Icon ms-Icon--chevronsDown"></i>
            </button>
            <div class="ms-MessageBanner-action"&ge</div>
        </div>
        <button class="ms-MessageBanner-close">
           <i class="ms-Icon ms-Icon--x"> </i>
        </button>
    </div>
</body>
</html>

Home.js

The JQuery messageBanner control is part of the Office UI Fabric JavaScript components.
This control is used to display any errors at the bottom of the task pane.

(function () { 
    "use strict";

    var cellToHighlight;
    var messageBanner;

    Office.initialize = function (reason) {
        $(document).ready(function () {
            // Initialize the notification mechanism and hide it
            var element = document.querySelector('.MessageBanner');
            messageBanner = new components.MessageBanner(element);
            messageBanner.hideBanner();
            
            if (!Office.context.requirements.isSetSupported('ExcelApi', '1.1')) {
                $("#template-description").text("This sample will display the value of the cells that you have selected in the spreadsheet.");
                $('#button-text').text("Display!");
                $('#button-desc').text("Display the selection");
                $('#highlight-button').click(displaySelectedCells);
                return;
            }

            $("#template-description").text("This sample highlights the highest value from the cells you have selected in the spreadsheet.");
            $('#button-text').text("Highlight!");
            $('#button-desc').text("Highlights the largest number.");
                
            loadSampleData();

            $('#highlight-button').click(hightlightHighestValue);
        });
    };

    function loadSampleData() {
        var values = [
            [Math.floor(Math.random() * 1000), Math.floor(Math.random() * 1000), Math.floor(Math.random() * 1000)],
            [Math.floor(Math.random() * 1000), Math.floor(Math.random() * 1000), Math.floor(Math.random() * 1000)],
            [Math.floor(Math.random() * 1000), Math.floor(Math.random() * 1000), Math.floor(Math.random() * 1000)]
        ];

        Excel.run(function (ctx) {
            var sheet = ctx.workbook.worksheets.getActiveWorksheet();
            sheet.getRange("B3:D5").values = values;
            return ctx.sync();
        })
        .catch(errorHandler);
    }

    function hightlightHighestValue() {
        Excel.run(function (ctx) {
            var sourceRange = ctx.workbook.getSelectedRange().load("values, rowCount, columnCount");

            return ctx.sync()
                .then(function () {
                    var highestRow = 0;
                    var highestCol = 0;
                    var highestValue = sourceRange.values[0][0];

                    for (var i = 0; i < sourceRange.rowCount; i++) {
                        for (var j = 0; j < sourceRange.columnCount; j++) {
                            if (!isNaN(sourceRange.values[i][j]) && sourceRange.values[i][j] > highestValue) {
                                highestRow = i;
                                highestCol = j;
                                highestValue = sourceRange.values[i][j];
                            }
                        }
                    }

                    cellToHighlight = sourceRange.getCell(highestRow, highestCol);
                    sourceRange.worksheet.getUsedRange().format.fill.clear();
                    sourceRange.worksheet.getUsedRange().format.font.bold = false;

                    cellToHighlight.format.fill.color = "orange";
                    cellToHighlight.format.font.bold = true;
                })
                .then(ctx.sync);
        })
        .catch(errorHandler);
    }

    function displaySelectedCells() {
        Office.context.document.getSelectedDataAsync(Office.CoercionType.Text,
            function (result) {
                if (result.status === Office.AsyncResultStatus.Succeeded) {
                    showNotification('The selected text is:', '"' + result.value + '"');
                } else {
                    showNotification('Error', result.error.message);
                }
            });
    }

    function errorHandler(error) {
        showNotification("Error", error);
        console.log("Error: " + error);
        if (error instanceof OfficeExtension.Error) {
            console.log("Debug info: " + JSON.stringify(error.debugInfo));
        }
    }

    function showNotification(header, content) {
        $("#notification-header").text(header);
        $("#notification-body").text(content);
        messageBanner.showBanner();
        messageBanner.toggleExpansion();
    }
})();

Build and Run

Select (Debug > Start Debugging).
This will launch Excel with an additional button added to the Home tab.
A "Getting Started" message will be displayed.

Press the Got It button to dismiss the popup message.
Press the "Show Taskpane" button to display the task pane.
Select the sample data, cell range "B3:D5" and press the "Highlight" button.
The largest number in the current selection will be shaded orange.


© 2021 Better Solutions Limited. All Rights Reserved. © 2021 Better Solutions Limited TopPrevNext