salesforce Dreamforce On-Demand
Contact Us 1-800-596-4880
  1. Homepage
  2. Microsoft Excel Online Connector (1.1)

  3. Microsoft Excel Online Connector Examples

All Connectors

Microsoft Excel Online Connector 1.1 Examples - Mule 4

This example shows you how to use Microsoft Excel Online Connector to perform the following actions:

Before You Begin

To run this example, you must have:

  • Anypoint Studio 7.3.5 or later

  • Microsoft Excel Online Connector 1.0 or later

  • A working instance of Microsoft Excel Online

Configure OAuth Authorization

Because this connector uses OAuth to secure connections, you must specify OAuth field values, such as client credentials, keys, and OAuth-related URLs. To do this:

  • Create a configuration file for the OAuth connection.

  • Configure the OAuth-related global elements.

  • Configure a global element for the properties file.

Create a Configuration File for the OAuth Connection

Create a configuration file for the OAuth connection that includes properties for the Authorization Code grant type:

  1. Create a file named mule-app.properties in the /src/main/resources/ folder.

  2. In the mule-app.properties file, create a set of properties for the Authorization Code grant type, similar to the ones that follow, replacing the bracketed text (including the brackets) with the correct values for your OAuth configuration:

    config.oauth.authorization.code.consumerKey=<consumerKey>
config.oauth.authorization.code.consumerSecret=<consumerSecret>
config.oauth.authorization.code.authorizationUrl=<authorizationUrl>
config.oauth.authorization.code.accessTokenUrl=<accessTokenUrl>

For more information about creating a properties file, see Configuring Property Placeholders.

Configure the OAuth Global Elements

Configure global elements for the OAuth_Authorization_Code_Config file:

  1. Create a new Mule project.

  2. In the Mule Palette view, click Search in Exchange and enter microsoft excel.

  3. Add Microsoft Excel Online Connector to the Selected modules section and click Finish.

  4. Click the Global Elements tab and click Create.

  5. Select Connector Configuration > Microsoft Excel Online Authorization Code Config and click OK.

  6. Enter the following values:

    Field Value

    Name

    OAuth_Authorization_Code_Config

    Consumer key

    ${config.oauth.authorization.code.consumerKey}

    Consumer secret

    ${config.oauth.authorization.code.consumerSecret}

    Authorization url

    ${config.oauth.authorization.code.authorizationUrl}

    Access token url

    ${config.oauth.authorization.code.accessTokenUrl}

    Scopes

    Leave the default: https://graph.microsoft.com/.default

    Listener config

    auth

    Callback path

    /callback

    Authorize path

    authorizePath

    External callback url

    http://localhost:8083/callback

  7. Click OK.

Configure a Global Element for the Properties File

Configure a global element for the mule-app.properties file so that Mule knows where to find it:

  1. Click the Global Elements tab and click Create.

  2. In the Choose Global Type dialog, select Configuration properties and click OK.

  3. In the File field, enter mule.app.properties.

  4. Click OK.

Add a Worksheet

The following screenshot shows the Studio app flow for adding a new worksheet:

Add Worksheet
Figure 1. Use a flow like this one to add a new worksheet.

Adding a new worksheet involves configuring an HTTP Listener component, Transform Message component, Add worksheet operation, and Logger component.

To create the flow:

  1. Create a new Mule project in Studio.

  2. In the Mule Palette view, search for HTTP and select the Listener operation.

  3. Drag the Listener operation onto the canvas.

  4. In the Listener configuration, click + next to the Connector configuration field to add a global element.

  5. Accept the defaults.

  6. In the HTTP properties window, set the Path field to /create.

Add the Transform Message Component

The first Transform Message component creates a template for the input used to create the team:

  1. In the Mule Palette view, search for transform message.

  2. Drag the Transform Message component onto the canvas, to the right of the Listener component.

  3. In the Transform Message configuration, overlay the brackets in the Output section with this XML:

    <ee:set-payload ><![CDATA[%dw 2.0
output application/json
---
{
	workbookId: attributes.queryParams.workbookID,
	worsheetname: attributes.queryParams.worksheetName
}]]></ee:set-payload>

Add the Add Worksheet Operation

The Add Worksheet operation creates a new worksheet based on user input:

  1. Drag the Add worksheet operation onto the canvas, to the right of the Transform Message component.

  2. In the Add worksheet configuration, click the Connector configuration dropdown and select Microsoft-Excel-Online-Authorization-Code-Config.

  3. Select Microsoft_Excel_Online_Authorization_Code_Config as the global element type and click OK.

  4. Configure the following fields in the Add worksheet properties window:

    Field Value

    Workbook Id

    payload.workbookId

    Worksheet Name

    payload.worsheetname

List a Worksheet

Create a second flow to list a worksheet. Use the List worksheet operation in this flow:

List Worksheet
Figure 2. Use a flow like this one to list a worksheet.

Update a Worksheet

Create a third flow to update information about a worksheet. Use the Update worksheet operation in this flow:

Update Worksheet
Figure 3. Use a flow like this one to update information about a worksheet.

Delete a Worksheet

Create a fourth flow to delete a worksheet. Use the Delete worksheet operation in this flow:

Delete Worksheet
Figure 4. Use a flow like this one to delete a worksheet.

Run the App

To run the app:

  1. Right-click the project in Package Explorer and select Run As > Mule Application.

  2. After the app deploys, open a web browser.

  3. Enter the following URL to start the OAuth dance: http://localhost:8081/authorize.

  4. In the login screen, enter the login information used to access Microsoft Excel Online and click Login.

  5. Click Allow.

  6. Initiate a flow by entering the associated URL, as shown in the following table:

    Flow URL Notes

    Add a worksheet

    http://localhost:8081/create?workbookID={workbookID}&worksheetName={worksheetName}

    List a worksheet

    http://localhost:8081/list?workbookID={workbookID}

    Update a worksheet

    http://localhost:8081/update?workbookID={workbookID}&worksheetName={worksheetName}&worksheetUpdateName={worksheetUpdateName}

    Delete a worksheet

    http://localhost:8081/delete?workbookID={workbookID}&worksheetName={worksheetName}

XML for the Examples

Paste this XML code into the Configuration XML tab in your project to experiment with the flows described in the previous sections. When you paste this code, click Yes on the Regenerate 'doc:id' Values dialog.

<?xml version="1.0" encoding="UTF-8"?>

<mule xmlns:ee="http://www.mulesoft.org/schema/mule/ee/core" xmlns:excel="http://www.mulesoft.org/schema/mule/excel"
	xmlns:http="http://www.mulesoft.org/schema/mule/http"
	xmlns="http://www.mulesoft.org/schema/mule/core" xmlns:doc="http://www.mulesoft.org/schema/mule/documentation" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.mulesoft.org/schema/mule/core http://www.mulesoft.org/schema/mule/core/current/mule.xsd
http://www.mulesoft.org/schema/mule/http http://www.mulesoft.org/schema/mule/http/current/mule-http.xsd
http://www.mulesoft.org/schema/mule/excel http://www.mulesoft.org/schema/mule/excel/current/mule-excel.xsd
http://www.mulesoft.org/schema/mule/ee/core http://www.mulesoft.org/schema/mule/ee/core/current/mule-ee.xsd">
	<http:listener-config name="HTTP_Listener_config" doc:name="HTTP Listener config" doc:id="b67a7f0e-9d8f-43ee-b3d8-0a2f86fe10c5" >
		<http:listener-connection host="0.0.0.0" port="8081" />
	</http:listener-config>
	<excel:authorization-code-config name="Microsoft_Excel_Online_Authorization_Code_Config" doc:name="Microsoft Excel Online Authorization Code Config" doc:id="a03ea31c-ab5c-4508-ae87-91485d0d9201" >
		<excel:oauth-authorization-code-connection >
			<excel:oauth-authorization-code consumerKey="${consumerKey}" consumerSecret="${consumerSecret}" authorizationUrl="${authorizationUrl}" accessTokenUrl="${accessTokenUrl}" scopes="${scopes}" resourceOwnerId="${resourceOwnerId}"/>
			<excel:oauth-callback-config listenerConfig="HTTP_Listener_config" callbackPath="/callback" authorizePath="/authorize" externalCallbackUrl="http://localhost:8081/callback" />
		</excel:oauth-authorization-code-connection>
	</excel:authorization-code-config>
	<configuration-properties doc:name="Configuration properties" doc:id="2820ecc1-e8be-4b5f-8d6e-5eb3afaf1732" file="mule-app.properties" />
	<flow name="create-flow" doc:id="fcdaa88d-173a-4213-aaef-65bdcf384f03" >
		<http:listener doc:name="Listener" doc:id="f6a51e00-2be8-4524-9e39-cd4e3387b5e0" config-ref="HTTP_Listener_config" path="/create"/>
		<ee:transform doc:name="Transform Message" doc:id="f277839a-9e59-4d2a-9d53-c4d2f012ae90" >
			<ee:message >
				<ee:set-payload ><![CDATA[%dw 2.0
output application/json
---
{
	workbookId: attributes.queryParams.workbookID,
	worsheetname: attributes.queryParams.worksheetName
}]]></ee:set-payload>
			</ee:message>
		</ee:transform>
		<excel:add-worksheet doc:name="Add worksheet" doc:id="071f5319-16ae-43dc-9420-bf47479eb3d1" config-ref="Microsoft_Excel_Online_Authorization_Code_Config" workbookId="#[payload.workbookId]" worksheetName="#[payload.worsheetname]"/>
		<logger level="INFO" doc:name="Logger" doc:id="9dd54e43-357e-454d-94ff-ccace4bcb580" message="Worksheet ID : #[payload.id]"/>
	</flow>
	<flow name="list-flow" doc:id="5b1b8836-f3a2-445e-b495-c80d93dcd82d" >
		<http:listener doc:name="Listener" doc:id="0ffa794c-37a4-4c57-a14d-da7009fa504f" config-ref="HTTP_Listener_config" path="/list" />
		<ee:transform doc:name="Transform Message" doc:id="f71f89a7-0f51-44ba-9e46-18e6f8dcc8c8" >
			<ee:message >
				<ee:set-payload ><![CDATA[%dw 2.0
output application/json
---
{
	workbookId: attributes.queryParams.workbookID
}]]></ee:set-payload>
			</ee:message>
		</ee:transform>
		<excel:list-worksheet doc:name="List worksheet" doc:id="5e576012-f3a1-428a-b20f-d9d5a3442398" config-ref="Microsoft_Excel_Online_Authorization_Code_Config" workbookId="#[payload.workbookId]"/>
	</flow>
	<flow name="update-flow" doc:id="0cfe6bad-85e8-4e61-9688-52c6b37a93a8" >
		<http:listener doc:name="Listener" doc:id="6c874287-c011-4b19-b3e8-55744003f0f6" config-ref="HTTP_Listener_config" path="/update"/>
		<ee:transform doc:name="Transform Message" doc:id="d0697ad3-e842-48cd-b0c4-016dd65a712e" >
			<ee:message >
				<ee:set-payload ><![CDATA[%dw 2.0
output application/json
---
{
		name : attributes.queryParams.worksheetUpdateName
}]]></ee:set-payload>
			</ee:message>
		</ee:transform>
		<excel:update-worksheet doc:name="Update worksheet" doc:id="983a65bc-584b-4199-867d-abfb9cf09bcb" config-ref="Microsoft_Excel_Online_Authorization_Code_Config" workbookId="#[attributes.queryParams.workbookID]" worksheetId="#[attributes.queryParams.worksheetName]">
		</excel:update-worksheet>
	</flow>
	<flow name="delete-flow" doc:id="c989f49f-809c-4593-95dc-f93f8634c609" >
		<http:listener doc:name="Listener" doc:id="f6e8d004-e264-48c1-85cf-b2ce5a0135d6" config-ref="HTTP_Listener_config" path="/delete"/>
		<ee:transform doc:name="Transform Message" doc:id="ea4da82b-cd40-4bf6-b8c7-04efe280d835" >
			<ee:message >
				<ee:set-payload ><![CDATA[%dw 2.0
output application/json
---
{
	workbookId: attributes.queryParams.workbookID,
	worksheetName: attributes.queryParams.worksheetName
}]]></ee:set-payload>
			</ee:message>
		</ee:transform>
		<excel:delete-worksheet doc:name="Delete worksheet" doc:id="183fb53f-0c3d-42c2-8676-df485f9a5f43" config-ref="Microsoft_Excel_Online_Authorization_Code_Config" workbookId="#[payload.workbookId]" worksheetId="#[payload.worksheetName]"/>
	</flow>
</mule>
Back to top
View on GitHub