Sometime back, I wrote a full trust code to export selected items to excel. It was written in the form of custom action in ribbon and a layouts application page which handles the actual excel file creation and providing it as response. The layouts application page actually uses server side APIs in order to read the list item values. It works perfectly fine for the SharePoint 2010 and 2013 on-premises instances, now it has become kind of old-school method.
Office 365 or SharePoint/Office 365-D model does not support any full trust code customization, so I decided to re-write this in a addin-model utilizing the client side REST APIs. When I started thinking about the addin-model, I found two available options for replacing the layouts application page. First, replace with a traditional way of using ASPX page residing in a provider hosted addin, the only difference between the earlier version and this model will be the server side APIs will be replaced by the REST APIs. I know its quiet achievable, and no big deal. Second option is to use NodeJS instead of IIS server, and use the JavaScript with the REST APIs. So this post is about the second option.
Download
ExportExcelAddin.zip
If you are Git person, directly fork or contribute from here.
Lets directly get into action and see how it works,
Configurations
1. Custom action
Use the below PowerShell CSOM script to add the custom action to your list, watch out for the hard coded url used in the form submit, you need replace it with your address of your nodeJS server.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
Add-Type -Path "c:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "c:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
Add-Type -Path "c:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Publishing.dll"
#Authenticate with the SharePoint site.
#
$siteUrl = Read-Host -Prompt "Enter site url"
$username = Read-Host -Prompt "Enter Username"
$password = Read-Host -Prompt "Enter password" -AsSecureString
$ctx = New-Object Microsoft.SharePoint.Client.ClientContext($siteUrl)
# SharePoint Online
$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($username, $password)
$ctx.Credentials = $credentials
$rootWeb = $ctx.Web
$ctx.Load($rootWeb)
$caColl = $rootWeb.get_userCustomActions()
$ctx.Load($caColl)
$ctx.ExecuteQuery()
$cUIExtn = "<CommandUIExtension><CommandUIDefinitions><CommandUIDefinition Location=""Ribbon.List.Share.Controls._children"">
<Button Id=""Ribbon.List.Share.ExportToExcelButton"" Alt=""Export to Excel"" Sequence=""11"" Command=""Invoke_ExportToExcelButtonRequest"" LabelText=""Export to Excel"" TemplateAlias=""o1"" Image32by32=""_layouts/15/images/placeholder32x32.png"" Image16by16=""_layouts/15/images/placeholder16x16.png"" />
</CommandUIDefinition></CommandUIDefinitions><CommandUIHandlers>
<CommandUIHandler Command=""Invoke_ExportToExcelButtonRequest"" CommandAction=""javascript: function exportSchema() { var field = function (Name, Type, ID, rFieldName) {this.DisplayName = Name; this.FieldType = Type; this.ID = ID; this.RealFieldName = rFieldName; };
var listFields = []; for (var i=0; i<ctx.ListSchema.Field.length; i++) {listFields.push(new field(ctx.ListSchema.Field[i].DisplayName, ctx.ListSchema.Field[i].FieldType, ctx.ListSchema.Field[i].ID, ctx.ListSchema.Field[i].RealFieldName));}
var form = document.createElement('form'); form.setAttribute('method', 'post'); var hField = document.createElement('input'); hField.setAttribute('type', 'hidden');
hField.setAttribute('name', 'ListFields'); hField.setAttribute('value', JSON.stringify(listFields)); form.appendChild(hField);
var hViewID = document.createElement('input'); hViewID.setAttribute('type', 'hidden'); hViewID.setAttribute('name', 'ViewID'); hViewID.setAttribute('value', ctx.view.replace('{','').replace('}','')); form.appendChild(hViewID);
var hListID = document.createElement('input'); hListID.setAttribute('type', 'hidden'); hListID.setAttribute('name', 'ListID'); hListID.setAttribute('value', ctx.listName.replace('{','').replace('}','')); form.appendChild(hListID);
var hlistName = document.createElement('input'); hlistName.setAttribute('type', 'hidden'); hlistName.setAttribute('name', 'ListName'); hlistName.setAttribute('value', ctx.ListTitle); form.appendChild(hlistName);
var hItemIDs = document.createElement('input'); hItemIDs.setAttribute('type', 'hidden'); hItemIDs.setAttribute('name', 'ItemIDs'); hItemIDs.setAttribute('value', '{SelectedItemId}'); form.appendChild(hItemIDs);
form.setAttribute('action', 'http://localhost:3000/export');
document.body.appendChild(form); form.submit(); } exportSchema();""
EnabledScript=""javascript: function exporttoexcelenable() { return (true);} exporttoexcelenable();""/></CommandUIHandlers></CommandUIExtension>"
$newUCA = $caColl.Add()
$newUCA.set_registrationId("100")
$newUCA.set_registrationType("List")
$newUCA.set_location('CommandUI.Ribbon')
$newUCA.set_title('Invoke ExportToExcel Action')
$newUCA.set_commandUIExtension($cUIExtn)
$newUCA.update()
$ctx.ExecuteQuery()
2. Addin Registration
In Azure portal, register your application and give read access on SharePoint sites. Note down the client ID and client secret. For more details on registering the application in Azure AD, refer this article.
NodeJS configurations
Update the below details in the parameters.json
1
2
3
4
5
6
7
{
"tenant": "yourtenant.onmicrosoft.com",
"authorityHostUrl": "https://login.windows.net",
"clientId": "89bedb2a-3c1e-4df6-b544-8f1f14392ebd",
"clientSecret": "vM2XycJD8lf29qfckwGC604ATqUBYTFcIsxvdnZuNFo=",
"resource": "https://yourtenant.sharepoint.com"
}
3. Install node dependencies
- “adal-node”: “>= 0.1.17”
- “express”: “4.13”
- “connect-logger”: “0.x”
- “cookie-parser”: “1.4”
- “cookie-session”: “2.x”
- “msexcel-builder”: “0.0.2”
- “unirest”: “2.14.x”
- “body-parser”: “2.14.x”
4. Execution
- Run the main.js in node to start the server.
- Your node instance is ready and starts listening the port configured.
- Go to your SharePoint Online site where the custom action has been added, and select few items in the list and select “export to excel’ from the ribbon
- An excel file will be downloaded with the file name same as your list name with the selected items as rows
Peace! Happy Coding.