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)
            {

            }
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s