Getting Excel data over HTTP has never been so easy.  The idea behind REST web services is that you can use simple web requests to get and set data that you need.  Instead of having to have complex SOAP headers and lots of code which makes consuming web services, particularly in AJAX solutions difficult, is eliminated in RESTful web services.   I am a bit picky when it comes to exact specifications of interfaces, which is easy to miss in RESTful services, but we’ll save that for another blog post because right now we are just focusing on the benefits.

REST BASICS

Nouns

RESTful web services use “Nouns” to describe how data is organized.  These nouns are included right a URI (URL).

RESTFul nouns based on basket ball teams

http://mysite/Players.html returns a list of all players as an HTML table

http://mysite/Players/Willie-Veasley returns details about a particular player.

(My girlfriend is mildly obsessed with Butler Basketball so I’ll use their players as examples).

The idea behind this is that it is easier to find information if it is intuitively categorized, maybe even using a taxonomy.  This hierarchy “Players” followed by player name are each nouns in a RESTful web service.

With RESTful web services are are exposing data based on a hierarchy. This is a full fledged taxonomy for the data in your system.

Verbs

The information is only so great until you can do something with it.  This is where verbs come in.   There are four verbs – based off of the 4 types of HTTP requests GET, POST, PUT, DELETE.  These, on the back end, map to the CRUD functions of an ADO object.  (Read, Create, Update, Delete).

The Point is to Keep it Simple

RESTful web services can return data in multiple formats meaning that it can be both human and computer readable.  It doesn’t have to be pretty, it just has to be easy and GET THE DATA OUT THERE.  The web is designed to share information, so use it to share the data so other people can use it (another blog on web data mining is coming).

Another idea is to use RESTful web services with AJAX controls.  REST is stateless and designed to retrieve and set data, kind of like RPC but over HTTP.   To retrieve data, a HTTP request sent using a javascript object like HttpRequest, using “GET” to the specific URL, pick the right url for the right format (xml, html, json) and you have your data.  There is no constructing complex XML requests to pass and just a quick hit to get and retrieve your data that is ready to be bound to your favorite AJAX control.

RESTful Excel Services in 2010

Excel REST can be used to get and set cells or ranges in an excel workbook that has been published to SharePoint.  It can also return charts and graphs as images that can be imbedded in any web site with a simple <img src=”URL”> tag.

The Excel REST web service is located

http://myserver/_vti_bin/ExcelRest.aspx/

To see an Rss feed of all of your excel objects just go to

http://myserver/_vti_bin/ExcelRest.aspx/model

Click around and check out the url.

The following url is used to both get and set the data.

Ranges(‘[Sheet]![TOPLEFT]%7C[BOTTOMRIGHT]’)?Ranage(‘[SHEET]![CELL]’)=[VALUE])

Range would be appended after the ExcelRest.aspx/model

Charts

Getting and setting data is really cool, but what blows my mind is the ability to use the Excel Web Services to get graphs as images.  The images are cached which makes the REST request very efficient and suitable to be included in public facing web sites.  The images are updated automatically when the data in the excel file.

/model/Charts(‘<CHART NAME>’)

This is so powerful because it enables excel to serve as a back end for user driven business intelligence.   One thing just about every businessperson loves and knows how to use is excel.  Excel Services allows the business user to make the charts and slice and dice the data the way they know how to and then share it by publishing the data on the server.  IT can use this data in their application development as a way to do more complex computations and graphic that is otherwise annoying to do with complex and costly APIs or SSRS.

Slide Deck

More Resources

http://www.techbubbles.com/softwarearchitecture/rest-overview/

http://msdn.microsoft.com/en-us/magazine/dd943053.aspx

http://msdn.microsoft.com/en-us/library/ee556820(office.14).aspx

Excel Services, nSPIN, REST, SharePoint

Access Services, Indianapolis, SharePoint 2010, SharePoint Saturday

The past few weeks I have been on and off working on a SharePoint Visual Studio workflow that routes documents for approval and sets permissions on the document and tasks to approve so that only the user approving that document can read.

In the workflow, I created a “CreateTaskWithContentType” activity and off I went, using the SpecializedPermissions Property to set the permissions on the task that was created, and then a custom function to modify the security on the document getting approved.

This code is pretty straight forward. You add a new dictionary to for the SpecializedPermission and then add the user you want as the key (yes it was a valid user stored in originator) and then the value to be the SPRoleType you want to grand the user.

Every blog out there say that this should work, and it did. Sort off. While technically the permissions were getting set and no impact seemed to be happening to functionality, the SharePoint Logs said differently.

The following error kept showing up and it took me weeks to track it down.

After doing some digging on the internet I discovered a Blog by Hristo Pavlov about AllowUnsafeUpdates.

In his blog he showed that when permissions are changed AllowUnsafeUpdates in some cases is reset to false. This was the cause of the above error. When the workflow created the task, SpecializedPermissions called the ResetRoleInheritance() method which reset allow unsafe updates (As best as I can tell).

That being said I still do not know a way to fix the problem using SpecialPermissions. Instead, I manually set the permissions on the task using the TaskCreated activity in the workflow.

This same error comes up when you reset role inheritance on any item in a workflow – paying particular attention to set AllowUnsafeUpdates to true after just about any permission operation.

For example:

I anyone else has a better solution, please comment and let me know. Likewise I’m happy to field any question

UPDATE:

I learned you don’t need to call “item.update()” after you set or break role inheritance, or add a role association. This will cause an exception. You DO still need to re-set the allowunsafeupdates to be true.

MOSS, MOSS 2007, Security, SharePoint, SpecializedPermissions, Workflow

Its easy to change the SharePoint maximum file size from Central Administrator in MOSS 2007. Go to Central Admin->Applications Tab -> General Application Settings and set the maximum size.

This works up to a certain point but if the size you set is greater than the maximum file size allowed by the web application an error exception will be thrown that looks like this when you are developing with the SharePoint Object Model:

This is because the web application from IIS running SharePoint has a maximum form size. You can either make sure you are using logic to chunk the file to be less than this maximum (ie: load up the first 50 MB then append every additional 50 to the back of the file once it is created) or change the Web.Config.

Change this line:

and set the 51200 to be greater than the SharePoint web app maximum. Remember there are 1024 k in 1 MB (not 1000 like the disk manufacturers want us to believe).

Maximum File Size, MOSS, MOSS 2007, SharePoint

I was working on a project recently where I needed to be able to have a two way hash table.  I need to be able to have standard hash functionality – looking up items by keys with O(n) time, but also needed to be able to determine if the item was associated with more than one key.  There was a “MultiMap” class in C++ STL but I was unable to find it for C# and who really wants to deal with interop.  Per the advice of one of my co-workers I wrote my own.  Here it is.

The two Dictionaries provide forward and backward lookup (key to item and item to key). Linked Lists are stored for each key so if a collisions exists it will still be inserted anyway.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace AH.Collections
{
    public class TwoWayDictionary
    {
        private Dictionary> m_forwardLooking = new Dictionary>();
        private Dictionary> m_backwardLooking = new Dictionary>();

        private void ForwardLookingInsert(string key, T item) {

            if( !m_forwardLooking.Keys.Contains(key) )
            {
                LinkedList t = new LinkedList();
                m_forwardLooking.Add(key, t);
            }

            m_forwardLooking[key].AddFirst(item);

        }

        private void BackwardLookingInsert(string key, T item) {

            if( !m_backwardLooking.Keys.Contains(item) ) {
                LinkedList t = new LinkedList();
                m_backwardLooking.Add(item, t);
            }

            m_backwardLooking[item].AddFirst(key);

        }

        public void AddItem(string key, T item)
        {
            ForwardLookingInsert(key, item);
            BackwardLookingInsert(key, item);
        }

        public void RemoveItem(T item)
        {
            LinkedList keys = GetKeyList(item);
            LinkedList itemList;
            foreach (string s in keys)
            {
                m_forwardLooking[s].Remove(item);

            }

            if( m_backwardLooking.Keys.Contains(item) )
               m_backwardLooking.Remove(item);

        }

        public void RemoveKey(string key)
        {
            LinkedList items = GetItemList(key);

            foreach (T item in items)
            {
                m_backwardLooking[item].Remove(key);
            }

            if (m_forwardLooking.Keys.Contains(key))
                m_forwardLooking.Remove(key);

        }

        public T GetItem(string key)
        {
            if (m_forwardLooking.Keys.Contains(key) &&
                m_forwardLooking[key].Count > 0)
                return m_forwardLooking[key].First.Value;

            return default(T);

        }

        public LinkedList GetItemList(string key)
        {
            if (m_forwardLooking.Keys.Contains(key))
                return m_forwardLooking[key];
            else
                return null;
        }

        public string GetKey(T item)
        {
            if (m_backwardLooking.Keys.Contains(item) &&
                m_backwardLooking[item].Count > 0)
                return m_backwardLooking[item].First.Value;

            return null;
        }

        public LinkedList GetKeyList(T item)
        {
           if( m_backwardLooking.Keys.Contains(item))
               return m_backwardLooking[item];
           else
               return new LinkedList();

        }

    }
}

code example, Hashtable, reverse, two way