Month: June 2014

Programmatically Create and Export SharePoint Views

I had a requirement in one of my projects to create custom views on-demand programmatically in my application. This article provides a guide on how to create SharePoint List Views using the SharePoint Object Model and then export its contents to excel.

Generating the CAML Query

To be frank, I’m not a master in the dark art of writing CAML queries. So, before we go ahead and create the view using code, we will have to create a sample view first to extract the CAML query. For my project, I had to be able to generate separate views dynamically based on a specific column which was Survey ID.

All Items View



Items where Survey ID is 4


Now, that we have a sample view which will act as guide/template for future views which will be generated dynamically using code. To extract this views CAML query, we will use the CAML Generator. The tool is pretty straight forward so I will not get into the details of its usage. Open up the view you just created and copy the highlighted part of the schema.



Creating and exporting the view

	string query = "4";
	SPList surveyResponseList = SPContext.Current.Web.Lists["Custom List"];
	System.Collections.Specialized.StringCollection viewFields = new System.Collections.Specialized.StringCollection();
	SPView view = surveyResponseList.Views.Add("View_Name", viewFields, "query", 100, true, false);

	SPListItemCollection exportListItems = surveyResponseList.GetItems(view);
	DataTable dt = new DataTable();
	dt = exportListItems.GetDataTable();
	Export(dt, view.Title);

private void Export(DataTable dt, string viewName)
    if (dt.Rows.Count > 0)

	//excel file name
	string filename = String.Format("{0}.xls", viewName);

	DataGrid dgGrid = new DataGrid();
	dgGrid.DataSource = dt;

	System.IO.StringWriter tw = new System.IO.StringWriter();
	System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
	HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", filename));
	HttpContext.Current.Response.ContentType = "application/ms-excel";
	//render the htmlwriter into the response  

So in my case I just had to replace the value 4 in the query with the new Survey ID value each time a view had to be generated. There are other more “neat” ways to export to excel using third party libraries but I leave that to the reader to explore.


Enable app sideloading in SharePoint Online

To be able to deploy solutions from Visual Studio to SharePoint Online, the SideLoading feature must be enabled on your site.

Install and configure SharePoint Online Management Shell

If you haven’t already done so, you can download SharePoint Online Management Shell and install it. If you are running this a client machine like Windows 8, you might have to change the script execution policy. To be sure, type the following command

Get-ExecutionPolicy -List

If the ExecutionPolicy of CurrentUser scope is shown as Undefined then this means your policy is set as Restricted and you will not be able to execute scripts. To read more about the various types of execution policies, visit Usually the RemoteSigned policy is good enough so type the following command

Set-ExecutionPolicy RemoteSigned -Scope CurrentUser

Enabling Siteloading on your site

When you are prompted for your site URL, remember to use HTTPS prefix otherwise you will get a 403 Forbidden error.

$programFiles = [environment]::getfolderpath("programfiles")
add-type -Path $programFiles'\SharePoint Online Management Shell\Microsoft.Online.SharePoint.PowerShell\Microsoft.SharePoint.Client.dll'
Write-Host 'Ready to enable Sideloading'
$siteurl = Read-Host 'Site Url'
$username = Read-Host "User Name"
$password = Read-Host -AsSecureString 'Password'

$outfilepath = $siteurl -replace ':', '_' -replace '/', '_'

[Microsoft.SharePoint.Client.ClientContext]$cc = New-Object Microsoft.SharePoint.Client.ClientContext($siteurl)
[Microsoft.SharePoint.Client.SharePointOnlineCredentials]$spocreds = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($username, $password)
$cc.Credentials = $spocreds
$site = $cc.Site;

$sideLoadingGuid = new-object System.Guid "AE3A1339-61F5-4f8f-81A7-ABD2DA956A7D"
$site.Features.Add($sideLoadingGuid, $true, [Microsoft.SharePoint.Client.FeatureDefinitionScope]::None);


Write-Host -ForegroundColor Green 'SideLoading feature enabled on site' $siteurl
#Activate the Developer Site feature
Write-Host -ForegroundColor Red 'Error encountered when trying to enable SideLoading feature' $siteurl, ':' $Error[0].ToString();


That’s it. You will now be able to deploy your solutions from Visual Studio.