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

Did You Know!?: SharePoint Webparts and libraries

Hello again!

I decided to start a new blogging category named “Did You Know!?”. The idea behind this is that I write tips and tricks you might not think of or may not have heard but will help you and make you life easier.

TOC:

Content Query WebPart – Audience targeting

Custom views for document library or lists webparts

So here we go:

Content Query WebPart – Audience targeting:

If you did not know you can filter content of the CQWP(and also in the code if you are using CrossListQueryInfo and CrossListQueryCache) by enabling Audience Targeting to the webpart.

What do you need to do to make it to work:

– Enable the CQWP Audience targeting – from the webpart properties

cqwp audience

– Enable your content source to support audience targeting – Example: your library or list – from the list/library settings

Go to your library settings => Under “General Settings” => Select “Audience Targeting Settings” => Then enable Audience targeting by checking the checkbox.

After this you will get a new field to your list named as “Target Audiences”. Now when you create or edit items you will be able to chose an audience to that item. When a CQWP has audience targeting enabled what will happen is that the webpart will check the item audience targeting values and compare them to the user profile audiences. If the user is part of the items audience then the item will show up in the CQWP

– Configure your user profile service to support audience targeting

This is the tricky part. You need to configure a whole lot of places to get your audience targeting for a user to work so that webpart and other content related queries etc filter data properly.

I will do it in three parts the configuration:

1. Configure a Term Set in your Manage Metadata Service. Let’s say named “Location” that has 10 different countries your organization is interested in. This I will not explain in much detail. I hope you are familiar with MMS.

article mms

2. Go to your user profile service and create a new User Property(The picture below as an example). Also take notice(I do not remember exactly) but the privacy settings needs to set to everyone for this field to work properly.

new user property

3. Next assign to a user profile(s) a desired location to the newly created field.

4. Then lets create a new audience. In your user profile service go to “Manage Audiences”. Then Press the “New Audience” button. In the next view give the audience a name and an owner. And remember to select the rule strategy. I chose “Satisfy any of the rules so that I may include many rules to a single audience. This way I can aggregate two or more location to an audience and have any of them apply to an item and user.

5. In the next step select as an “Operand” property and select the location property we created and as the operator I would chose “Contains”. Next in the Value field select a value.

audience

6. Now all you have to do is compile the audience. Press “Compile Audience”. After the compilation is complete you should see how many members are apart of this audience. Next when you assign this audience to an item any user from is apart of this audience through his location value that corresponds to the audience location values are going to see the CQWP item.

Custom views for document library or lists webparts:

You are able to create custom Views for your library or list webparts that show you content from your library. You are not limited to what you get once you add a documents library to a page as a webpart. BUT there is a catch!!! If you do select and existing view and use it to display content in a certain manner in a page YOU WILL NOT have a reference to that view in you library BUT A COPY held in the webpart. So lets say that you have a view that lists the TOP 5 newest documents in your library. You decided to add the document library webpart to a page, you then select this TOP 5 view, what happens is that you will get a copy that is not related to the original view of that document library. SO if you make changes to the view in the document library view those changes will not be seen in your webpart because it is not a reference. So remember this when you use a view from a library or list into your webpart.