How to create a collaborative Excel Services solution
Excel Services allows users to open workbooks and watch them using only a browser. The solution I will show here will allow you to use a simple set of UDFs (User Defined Functions) to enable users to collaborate by shunting information between users and workbooks.
The solution may not be right for everyone. Side effects may include nausea, dry mouth, insomnia and severe internal bleeding.
The idea behind the UDFs is simple - two functions will allow get and set values into a central repository. Because UDFs are shared across the server, data will be available for all workbooks/users. Our two UDFs will be GetGlobalValue() and SetGlobalValue() – the first method will be used by consumers of the shared data while the second would be used by producers of the data. As you can guess, the implementation of these two functions is incredibly simple:
public class CollaborationUdfs
{
static Dictionary<string, object> m_values = new Dictionary<string, object>();
[UdfMethod(IsVolatile = true)]
public static object SetGlobalValue(string id, object value)
{
lock (m_values)
{
m_values[id] = value;
}
return value;
}
[UdfMethod(IsVolatile = true)]
public static object GetGlobalValue(string id)
{
lock (m_values)
{
object result = "";
m_values.TryGetValue(id, out result);
return result;
}
}
}
All the functions do is save values into a static member – that way, the data is available for all calls.
Note that the SetGlobalValue() call returns an object - because UDFs must return values, we just return a dummy value back.
Usage
To use this functionality, we will create two workbooks – the first will be used to update a value and the second will be used to view the value.
In this case, the provider workbook has a parameter defined on it which allows the user to enter a value (the cell containing the value is called ShequelsInDollar). That value will then cause a recalculation of the workbook which in turn will cause a call to the SetGlobalValue() UDF using the “ShequelsInDollarKey” string to identify the value:
(This is how the workbook looks in Excel - note the call to the UDF)
(And this is how it looks in IE - note the parameters and the dummy result of the UDF in the C1 cell)
The second workbook – the consumer - will use the value set by the provider to do its calculation, in our case, it will use the value of how many Shequels are there to a Dollar to calculate how many Dollars a given Shequel amount is:
(This is the consumer workbook in Excel 2007 - note how we call the GetGlobalValue UDF to get the value set by the other workbook)
(And this is the workbook in IE - the consumer just puts in a value into the parameter pane and gets back the result)
Now, whenever someone will use the provider workbook (the first screenshot) to change the value of how many Shequels to a Dollar, everyone who is using the consumer workbook (the second one) will get the new value in their calculations.
Conclusion
This model is overly simplified and is given just another potential usage for UDFs in Excel Services. If my example was to be made a real-world scenario there are quite a few things that need to be taken into consideration:
1. Excel Services can be run in a farm scenario – in those cases, since there is no guarantee as to which machine in the farm will run the workbooks, one cannot really rely on such a simple mechanism to handle the requirements. Extra components would have to be introduced.
2. If Excel Services was to shut down (due to scheduled maintenance or due to a power failure, for example) the UDF gets “shuts down” as well and will forget all the data it contained.
(These are just examples of shortcomings, I am sure that you can find more if you just think about it a bit)
Furthermore, there need not always be a consumer and provider workbooks – this solution, as limited as it is, works just as well with just one workbook or with many workbooks.