SharePoint Excel Services Loading Excel files from code

If you ever wondered how you might load an Excel Sheet from an Excel file dynamically from a webpart then this is your lucky day :). Well below is the code how to do it.

The code below is rather simple. You have an excel file URL within SharePoint which is used to retrieve data to build a excel web services URL from where the actualy HTML based excel data is retrieved.

There are two points to be made:
1. During the development of this functionality I noticed that without the definition for the headers excel services would not always operate as expected. This was especially true when in anonymous mode, in other words a public site.
2. If you are going to ry to use excel services through code like in this example you also need to take into consideration access. Under a Windows Authentication model you have no problems but if you use anonymous access you are going to have problems. so if you encounter problem with public sites consider creating an AD account which is going to be used as the anonymous user in IIS for your SharePoint application. Then to allow access to the target excel file location simply add the minimum privileges of “Read” to the account. Add these rights either to the library where the file is located of the file itself.

private String excelWebServicesURL = "{0}/_vti_bin/ExcelRest.aspx/{1}/model/Ranges('''{2}''!{3}|{4}')?$format=html";

String composedExcelWebServicesURL = null;
            try
            {
                if (!String.IsNullOrEmpty(this.ExcelFileLocation) && !String.IsNullOrEmpty(this.ExcelSheetName) && !String.IsNullOrEmpty(this.ExcelSheetStartCell) && !String.IsNullOrEmpty(this.ExcelSheetEndCell))
                {
                    using (SPSite site = new SPSite(this.ExcelFileLocation))
                    {
                        using (SPWeb web = site.OpenWeb())
                        {
                            SPFile excelFile = web.GetFile(this.ExcelFileLocation);
                            if (excelFile != null)
                            {
                                String hostName = null;
                                if (!string.IsNullOrEmpty(Request.Url.Query))
                                {
                                    hostName = Request.Url.AbsoluteUri.Replace(Request.Url.Query, String.Empty);
                                    hostName = hostName.Replace(Request.Url.AbsolutePath, String.Empty);
                                }
                                else
                                {
                                    hostName = Request.Url.AbsoluteUri.Replace(Request.Url.AbsolutePath, String.Empty);
                                }
                                Uri webURI = new Uri(web.Url);
                                String presentWebReformedURL = hostName + webURI.AbsolutePath;
                                composedExcelWebServicesURL = String.Format(this.excelWebServicesURL, presentWebReformedURL, excelFile.Url, this.ExcelSheetName, this.ExcelSheetStartCell, this.ExcelSheetEndCell);

                                if (this.DebugMode == true)
                                {
                                    this.lDebugInfo.Text += composedExcelWebServicesURL != null ? composedExcelWebServicesURL : "URL data is empty";
                                }
                                using (var client = new WebClient())
                                {
                                    var newUri = new Uri(composedExcelWebServicesURL);
                                    var myProxy = new WebProxy();
                                    myProxy.Credentials = CredentialCache.DefaultCredentials;
                                    myProxy.Address = newUri;
                                    client.Proxy = myProxy;

                                    client.Encoding = System.Text.Encoding.UTF8;
                                    client.Headers.Add("User-Agent", @"Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; WOW64; Trident/5.0)");
                                    client.Headers.Add("Accept", @"text/html, application/xhtml+xml, */*");
                                    if (this.DebugMode == true)
                                    {
                                        this.lDebugInfo.Text += "</br> Before download string";
                                    }
                                    this.lExcelWebServiceHTML.Text = client.DownloadString(composedExcelWebServicesURL);
                                    if (this.DebugMode == true)
                                    {
                                        this.lDebugInfo.Text += "</br> After download string";
                                    }
                                }
                            }
                        }
                    }
                }
				}
            catch (Exception ex)
            {

            }

Creating ASPX-pages under SharePoint layouts-folder with and without authentication

On this post I will write a few words how you can create pages under the SharePoint layouts-folder. If you are wondering why would you want to do it, well it is simple. As an example you might want to create a web service (which has the same ideology behind it. In other words once you understand how to create a page you know how to do it for a web service). Other examples than web service might be a user control which you might want to use in somewhere in your project. Or you might want to create a page that streams certain data like charts. Well, the usage of this example is up to you but there are endless usages.

To make this work we will be needing the following few items:

A created ASPX file that you would copy under your desired layouts folder location.

  1. A code behind file in an assembly that you will deploy in GAC or your Sharepoint Bin directory or in any other place of your choice.
  2. Also we must take into consideration possible authentication issues.
  3. We need to tell the ASPX file what class it will use and where to find it. In this case in the compiled assembly in step two.
Step 1:
Go to your desired location under the layouts folder like below:
C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\TEMPLATE\LAYOUTS
Once there you can either create an empty ASPX file with notepad or you can use Visual Studio if you want. It’s up to you. Give the file a name like “Sample.aspx”.
Step 2:
Create a new project under which you can create a new class that is going to hold the logic for you page. Lets say that this project is named as “Sample” and is going to be compiled under the assembly named as “Sample.dll” and you class will reside under “Sample” namespace. Make sure you have referenced the SharePoint assembly in your project so you can use the necessary SharePoint logic for your page.
Now there are two classes which you can use on a page under the the layouts folder. It all depends on your needs.
Step 3:
The class Microsoft.SharePoint.WebControls.LayoutsPageBase should be used when you need the authentication and authorization of SharePoint. This means that if you have a site that is depending on user login information to access the site and/or functionality this should be your choice. On the other hand if you needs are such that you have a public site which should avoid authentication and authorization logic you should use Microsoft.SharePoint.WebControls.UnsecuredLayoutsPageBase.

This class will save your nerves if you want to have a page that for example generates a chart image and you want that data to be available to anyone. If you do not use this class you might run into SharePoint security issues like data won’t show or you are going to be asked for authentication information. NOTICE that this class will not solver all of your authentication issues like any data access to listings or alike locations. You must make sure yourself that proper right are given to the anonymous user or use the following delegate:

SPSecurity.RunWithElevatedPrivileges(delegate

    { 
        “Write your code here!!” 
    });

BUT BE CAREFUL. The code run under the delegate has many rights that you might not want to give.

Below is a code sample of how the page class looks like. Notice the overridden AllowAnonymousAccess property which when set to true tells that SharePoint to force no authentication logic on this page. Make sure this is to true if you want an anonymous page and make sure this is set to false if you want security logic to be taken into consideration.

    public class ClassNoAuth : Microsoft.SharePoint.WebControls.UnsecuredLayoutsPageBase

    {
        protected override bool AllowAnonymousAccess
        {
            get
            {   
                return true;
            }
        }
    }

Once you are done with your code. Build & Compile and then deploy your assembly so that your SharePoint application has access to it. Putting it into GAC is probably the easiest and this is how I’m going to show in this example. Also because this assembly is deployed into GAC it needs to be signed.

Step 4:

Now what we need to do next is to tell the ASPX file where to find it’s code logic. This is ofcourse to be found in the “Sample.dll” assembly which we spoke earlier under the “Sample” namespace in the class named as “ClassNoAuth”.

So here is the piece of ASPX syntax that you must insert into the ASPX file:

<%@ Page Language=”C#” ContentType=”image/png” Inherits=”Sample.ClassNoAuth,Sample, Version=1.0.0.0, Culture=neutral, PublicKeyToken=er3f31tf5g1fbed2″ AutoEventWireup=”true” %>

The parameters in this piece of code are as follows:
  • Language: This simply states your coding language used to create the logic for this page.
  • ContentType: In this case tells how this page is rendered to the client. In other words this is going to be a image. This is good if you plan to use this page to generate a chart image that is then streamed into a AJAX control.
  • Inherits: This is the thing that is the most important to us. The first parameter before the “,” char is the name of the class. Notice make an explicit definition the whole path to the class. This means take into consideration namespaces also. The next param is the assembly name. Notice that you do not need to specify the file extension. Only the name is sufficient. Next are the simple assembly specific params as version, Culture and your assembly signature publickeytoken. All these information must match with your compiled assembly for the ASPX page to function properly.
Save the ASPX file and your good to go.
If everything went alright you should be able to access your newly created ASPX page from you web browser as long as you write the proper URL pointing to the ASPX page under the layouts folder.
Hope this was a help to you.