Microsoft Excel Online Connector 1.1 Examples | MuleSoft Documentation
# Microsoft Excel Online Connector 1.1 Examples
This example shows you how to use Microsoft Excel Online Connector to perform the following actions:
  * Add a worksheet
  * List a worksheet
  * Update a worksheet
  * Delete a worksheet


## 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:
`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}`  
Leave the default: `https://graph.microsoft.com/.default`  
Listener config  
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:
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:
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:
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:
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:
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:
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>
```