Create a spreadsheet from your web page, populate it with data, and embed your Excel add-in

Browse code

This sample accomplishes the following tasks.

• Creates a new Excel spreadsheet from a web page.
• Populates the spreadsheet with data from the web page.
• Embeds the Script Lab add-in into the Excel spreadsheet.
• Opens the spreadsheet on a new browser tab.

This sample implements the pattern described in Create an Excel spreadsheet from your web page, populate it with data, and embed your Office Add-in

Applies to

• Microsoft Excel

Prerequisites

• Visual Studio 2022 or later. Add the Office/SharePoint development workload when configuring Visual Studio.
• Visual Studio Code.
• A Microsoft 365 account. You can get one if you qualify for a Microsoft 365 E5 developer subscription through the Microsoft 365 Developer Program; for details, see the FAQ. Alternatively, you can sign up for a 1-month free trial or purchase a Microsoft 365 plan.
• At least a few files and folders stored on OneDrive for Business in your Microsoft 365 subscription.

Set up the sample

Step 1: Clone or download this repository

From your shell or command line:

git clone https://github.com/OfficeDev/Office-Add-in-samples.git

or download and extract the repository .zip file.

⚠️ To avoid path length limitations on Windows, we recommend cloning into a directory near the root of your drive.

Step 2: Install project dependencies

    cd <WebApplication-folder>
    npm install

Step 3: Register the sample application(s) in your tenant

Choose the Azure AD tenant where you want to create your applications

1. Sign in to the Azure portal.
2. If your account is present in more than one Azure AD tenant, select your profile at the top right corner in the menu on top of the page, and then switch directory to change your portal session to the desired Azure AD tenant.

Register the client app (contoso-addin-data-to-excel)

1. Go to the Azure portal and select the Azure Active Directory service.
2. Select the App Registrations blade on the left, then select New registration.
3. In the Register an application page that appears, enter your application's registration information:
   1. In the Name section, enter a meaningful application name that will be displayed to users of the app, for example contoso-addin-data-to-excel.
   2. Under Supported account types, select Accounts in this organizational directory only
   3. Select Register to create the application.
4. In the Overview blade, find and note the Application (client) ID. You use this value in your app's configuration file(s) later in your code.
5. In the app's registration screen, select the Authentication blade to the left.
6. If you don't have a platform added, select Add a platform and select the Single-page application option.
   1. In the Redirect URI section enter the following redirect URIs:
      1. http://localhost:3000
      2. http://localhost:3000/redirect
   2. Click Save to save your changes.
7. Since this app signs-in users, we will now proceed to select delegated permissions, which is is required by apps signing-in users.
   1. In the app's registration screen, select the API permissions blade in the left to open the page where we add access to the APIs that your application needs:
   2. Select the Add a permission button and then:
   3. Ensure that the Microsoft APIs tab is selected.
   4. In the Commonly used Microsoft APIs section, select Microsoft Graph
   5. In the Delegated permissions section, select User.Read, Contacts.Read, and Files.ReadWrite in the list. Use the search box if necessary.
   6. Select the Add permissions button at the bottom.

Configure Optional Claims

1. Still on the same app registration, select the Token configuration blade to the left.
2. Select Add optional claim:
   1. Select optional claim type, then choose ID.
   2. Select the optional claim acct.

   Provides user's account status in tenant. If the user is a member of the tenant, the value is 0. If they're a guest, the value is 1.

   1. Select the optional claim login_hint.

   An opaque, reliable login hint claim. This claim is the best value to use for the login_hint OAuth parameter in all flows to get SSO.See $optional claims for more details on this optional claim.

   1. Select Add to save your changes.

Configure the client app (contoso-addin-data-to-excel) to use your app registration

Open the project in your IDE (like Visual Studio or Visual Studio Code) to configure the code.

In the steps below, "ClientID" is the same as "Application ID" or "AppId".

1. Open the WebApplication/App/authConfig.js file.
2. Find the key Enter_the_Application_Id_Here and replace the existing value with the application ID (clientId) of contoso-addin-data-to-excel app copied from the Azure portal.
3. Find the key Enter_the_Tenant_Id_Here and replace the existing value with your Azure AD tenant/directory ID.

Run the sample

Start the Azure Functions project

1. Open FunctionCreateSpreadsheet.sln in Visual Studio.
2. Press F5 (or choose Debug > Start Debugging) to build and start the Azure function project. The function will run locally using the Azure Functions Core Tools. You should see the following output in a new console window.

Start the web application

1. From your shell or command line go to the WebApplication/ folder, then run the following command:

   npm start
   

2. In a browser, go to the URL http://localhost:3000/index.html.

3. Choose the Sign In button.

4. You will be prompted to sign in. Sign in with a user name and password from your Microsoft 365 account.

   Note: You may also be prompted to consent to the app permissions. You'll need to consent before the app can continue successfully.

   Once you sign in, the page will display a table of sales data.

5. Choose the Excel icon to open a new tab with a new spreadsheet.

When the spreadsheet opens, you will see the sales data. The embedded Script Lab add-in will be available on the ribbon.

Key parts of this sample

Authentication

This sample was built using the code from Vanilla JavaScript single-page application using MSAL.js to authenticate users to call Microsoft Graph. Please refer to the readme for more information on how the authentication works.

Implement the Excel button

The WebApplication/App/index.html page has an <img> tag that displays the Excel icon. The click handler calls openInExcel() which is in the WebApplication/App/authPopup.js file. The openInExcel function sends the sales data from WebApplication/App/tableData.js in a POST request to the FunctionCreateSpreadsheet Azure Functions app.

Construct the spreadsheet

The FunctionCreateSpreadsheet app uses Azure Functions to provide a function that constructs the spreadsheet. The function is triggered by an HTTP POST request. The body of the request contains JSON describing rows and columns of data to populate the spreadsheet. The function expects data in the format shown in ./WebApplication/App/tableData.js. The function returns the raw data of the new spreadsheet as a Base64 string.

The function uses the Open XML SDK to construct the spreadsheet in memory. The code that constructs the spreadsheet is in FunctionCreateSpreadsheet/SpreadsheetBuilder.cs.

• The InsertData method inserts the data values for the sales data table.
• The EmbedAddin method embeds the script lab add-in.
• Modify the GenerateWebExtensionPart1Content method to embed your add-in instead of the script lab add-in. Note that there is a CUSTOM MODIFICATION BEGIN/END section where you can specify custom properties that your add-in needs to load when it starts.

Upload the spreadsheet to OneDrive

Once the Base64 encoded string of the new spreadsheet is returned to the openInExcel function, it calls uploadFile. The uploadFile function uses the Microsoft Graph API to upload the spreadsheet to the OneDrive. It creates the URI 'https://graph.microsoft.com/v1.0/me/drive/root:/ for the Microsoft Graph API and adds the folder location and filename. It adds the Base64 string as the body, and calls the callGraph function to make the actual REST API call.

Modify the sample for your own web site

To repurpose the code in this sample for your own web site, you'll want to make the following changes.

Use your own data

The sample uses mock data described in WebApplication/App/tableData.js. You'll need to replace this code to use the actual data from your web site. If your data uses a different data model, you'll need to update the FunctionCreateSpreadsheet/Product.cs file. The FunctionCreateSpreadsheet/SpreadsheetBuilder.cs file contains an InsertData method that is bound to the product model data of this sample. You'll need to update it handle any changes you make to the data model.

Embed your add-in

The sample embeds the script lab add-in. You'll need to change the code to embed your own add-in. In the SpreadsheetBuilder.cs file, the GenerateWebExtensionPart1Content method sets the reference to Script Lab.

We.WebExtension webExtension1 = new We.WebExtension() { Id = "{635BF0CD-42CC-4174-B8D2-6D375C9A759E}" };
webExtension1.AddNamespaceDeclaration("we", "http://schemas.microsoft.com/office/webextensions/webextension/2010/11");
We.WebExtensionStoreReference webExtensionStoreReference1 = new We.WebExtensionStoreReference() { Id = "wa104380862", Version = "1.1.0.0", Store = "en-US", StoreType = "OMEX" };

In the previous code:

• The StoreType value is "OMEX", an alias for the Office Store.
• The Store value is "en-US" the culture section of the store where Script Lab is.
• The Id value is the Office Store's asset ID for Script Lab.

The GenerateWebExtensionPart1Content method contains commented code that shows how to set values for a centrally deployed add-in.

Note: For more information about alternative values for these attributes, see Automatically open a task pane with a document.

The GeneratePartContent method specifies the visibility of the task pane when the file opens.

Wetp.WebExtensionTaskpane webExtensionTaskpane1 = new Wetp.WebExtensionTaskpane() { DockState = "right", Visibility = true, Width = 350D, Row = (UInt32Value)4U };

In the previous code, the Visibility property of the WebExtensionTaskpane object is set to true. This ensures that the first time that the file is opened after the code is run, the task pane opens with Script Lab in it (after the user accepts the prompt to trust Script Lab). This is what we want for this sample. However, in most scenarios you will probably want this set to false. The effect of setting it to false is that the first time the file is opened, the user has to install the add-in, from the Add-in button on the ribbon. On every subsequent opening of the file, the task pane with the add-in opens automatically.

The advantage of setting this property to false is that you can use the Office.js to give users the ability to turn on and off the auto-opening of the add-in. Specifically, your script sets the Office.AutoShowTaskpaneWithDocument document setting to true or false. However, if WebExtensionTaskpane.Visibility is set to true, there is no way for Office.js or, hence, your users to turn off the auto-opening of the add-in. Only editing the OOXML of the document can change WebExtensionTaskpane.Visibility to false.

Note: For more information about task pane visibility at the level of the Open XML that these .NET APIs represent, see Automatically open a task pane with a document.

Security notes

• There may be security issues in packages used by this sample. Be sure to run npm audit to identify any security vulnerabilities.
• In the FunctionCreateSpreadsheet/FunctionCreateSpreadsheet/local.settings.json file, "CORS" is set to "*". This is only for development purposes. In production code, you should list the allowed domains and not leave this header open to all domains.

Questions and feedback

• Did you experience any problems with the sample? Create an issue and we'll help you out.
• We'd love to get your feedback about this sample. Go to our Office samples survey to give feedback and suggest improvements.
• For general questions about developing Office Add-ins, go to Microsoft Q&A using the office-js-dev tag.

Solution

Solution	Authors
Open data from your web page in a spreadsheet	Microsoft

Version history

Version	Date	Comments
1.0	January 31, 2023	Initial release
1.1	April 24, 2024	Update package versions

Copyright

Copyright (c) 2023 Microsoft Corporation. All rights reserved.

This project has adopted the Microsoft Open Source Code of Conduct. For more information, see the Code of Conduct FAQ or contact opencode@microsoft.com with any additional questions or comments.