I've been working in the Office Development space for over 10 years now, and I finally decided it's time for a change. So, I'm leaving my current team and heading over to a new job in Xbox. I expect that will change the nature of my blog posts - the adventure continues. Thanks to all my readers and commentators for putting up with my rambling so far - it's been a ride.
HLSL is the High Level Shading Language for DirectX, documented here. I was wondering the other day how you might use shaders in Office, and this led me to build a little proof-of-concept that allows you to experiment with HLSL within the context of an Office application. I built a PowerPoint add-in that provides a custom task pane which displays a WPF usercontrol. The task pane allows the user to select an image file, type in arbitrary HLSL code to build a shader, and apply that shader to the image before inserting it into the slide. Here’s a screenshot:
The user can type in any HLSL they like, and hit the Run button to apply the resulting shader to the image. My prototype has 2 projects: a WPF UserControl, and a VSTO PowerPoint 2007 add-in. The UserControl visuals are pretty straightforward: as you can see from the screenshot, I have an Image control, a TextBox for the HLSL source-code, 3 Buttons, and another TextBox for any diagnostic output. Note that I wrap my Image control in a Border to workaround the offsetting issue described in Jaime Rodriguez’s post:
<Border x:Name="ImageBorder" Width="Auto" Height="Auto">
<Image Name="ImageSource" />
</Border>
My 3 Button Click handlers open an image file, compile the HLSL source into a shader, and insert the resulting image into the slide, respectively. The Open handler is very simple – it just displays an OpenFileDialog and then sets the user’s image selection into the Image control:
private void buttonOpen_Click(object sender, RoutedEventArgs e)
{
OpenFileDialog dlg = new OpenFileDialog();
dlg.Filter = "Images|*.jpg;*.png;*.bmp;*.gif|All Files|*.*";
if (dlg.ShowDialog() == true)
{
ImageSource.Source = new BitmapImage(new Uri(dlg.FileName));
}
}
The Run handler is a little more involved. First, I get the HLSL source-code, and write it out to a temporary file on disk:
private void buttonRun_Click(object sender, RoutedEventArgs e)
{
string codePath = System.IO.Path.GetTempFileName();
using (FileStream stream =
new FileStream(codePath, FileMode.Create))
{
byte[] data = Encoding.ASCII.GetBytes(HlslSource.Text);
stream.Write(data, 0, data.Length);
}
Then, I get the path to the DirectX effect compiler (fxc.exe). To figure out where this is on the filesystem, I have a couple of choices. I could get the InstallPath for the DirectX SDK from the registry, as a versioned sub-key of [HKLM\software\Microsoft\DirectX]. Alternatively (and more simply), I can fetch the DXSDK_DIR environment variable. With this path, I can set up a Process object to launch the compiler, and pass in the path to the new HLSL source file, again saving the output to a temporary file:
string fxcPath = System.IO.Path.Combine(
Environment.GetEnvironmentVariable("DXSDK_DIR"),
"Utilities\\bin\\x86\\fxc.exe");
ProcessStartInfo startInfo = new ProcessStartInfo(fxcPath);
startInfo.CreateNoWindow = true;
startInfo.UseShellExecute = false;
startInfo.RedirectStandardError = true;
string shaderPath = System.IO.Path.GetTempFileName();
startInfo.Arguments = String.Format(
"/T ps_2_0 /E main /Fo\"{0}\" \"{1}\"",
shaderPath, codePath);
When I start the effect compiler process, I’ll handle any error output by displaying it nicely in my diagnostic TextBox. If there were no errors, I can set up a new pixel shader from the compiled HLSL, and apply it to the selected image.
using (Process proc = Process.Start(startInfo))
{
proc.WaitForExit();
StreamReader reader = proc.StandardError;
string errors = reader.ReadToEnd();
if (!String.IsNullOrEmpty(errors))
{
string errorMessage =
errors.Replace(codePath, "Line ");
string[] messageParts = errorMessage.Split(':');
if (messageParts.Length == 3)
{
CompilerOutput.Text = String.Format(
"{1}{0}{2}{0}{3}",
Environment.NewLine,
messageParts[0].Trim(),
messageParts[1].Trim(),
messageParts[2].Trim());
}
else
{
CompilerOutput.Text = errorMessage;
}
}
else
{
PixelShader pixelShader = new PixelShader();
pixelShader.UriSource = new Uri(shaderPath);
CustomShaderEffect shaderEffect =
new CustomShaderEffect(pixelShader);
ImageSource.Effect = shaderEffect;
CompilerOutput.Text = "Effect applied successfully.";
}
}
}
Note that I’m using a custom ShaderEffect class. You can derive from the ShaderEffect class to implement a custom effect based on a single pixel shader, as documented here.
The Insert handler uses RenderTargetBitmap and PngBitmapEncoder to create a bitmap from the image and save it to a PNG-format temporary file on disk:
private void buttonInsert_Click(object sender, RoutedEventArgs e)
{
try
{
RenderTargetBitmap renderTarget = new RenderTargetBitmap(
(int)ImageBorder.ActualWidth,
(int)ImageBorder.ActualHeight,
96, 96, PixelFormats.Pbgra32);
renderTarget.Render(ImageBorder);
PngBitmapEncoder encoder = new PngBitmapEncoder();
encoder.Frames.Add(BitmapFrame.Create(renderTarget));
string imagePath = System.IO.Path.GetTempFileName();
using (FileStream stream =
new FileStream(imagePath, FileMode.Create))
{
encoder.Save(stream);
}
ImageEventArgs ie = new ImageEventArgs(imagePath);
if (ImageSavedEvent != null)
{
ImageSavedEvent(sender, ie);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
You can see from the foregoing that when I’ve saved the final image, I fire a custom event. This is because I’ll be using my WPF UserControl within the context of an Office custom taskpane, and I want my Office add-in code to be notified when the image has been successfully processed. So, I have a custom ImageEvent event type, and corresponding custom ImageEventArgs type. I declare an ImageEvent as an event field in my UserControl class. Then, at the end of the Insert handler above, I fire this event.
public delegate void ImageEvent(object source, ImageEventArgs e);
public class ImageEventArgs : EventArgs
{
public String ImagePath;
public ImageEventArgs(String name)
{
ImagePath = name;
}
}
public partial class HlslControl : UserControl
{
public event ImageEvent ImageSavedEvent;
// …etc as above.
Next, I create a VSTO PowerPoint add-in project, and add a reference to my WPF UserControl project. I also add a custom Ribbon with one Button: the Button handler simply toggles the visibility of my custom task pane. To create the custom task pane, I create a (Windows Forms) UserControl in my add-in project, add a Windows.Forms.Integration.ElementHost, fully-docked. I set the ElementHost.Child to a new instance of my WPF UserControl, and sink the custom ImageSavedEvent. In the event handler, I call out to a custom InsertImage method in my main add-in class:
public partial class HlslTaskPane : UserControl
{
WpfControls.HlslControl hlslControl;
public HlslTaskPane()
{
InitializeComponent();
this.hlslControl = new WpfControls.HlslControl();
this.hlslControl.ImageSavedEvent += new WpfControls.ImageEvent(hlslControl_ImageSavedEvent);
this.elementHost.Child = this.hlslControl;
}
void hlslControl_ImageSavedEvent(object source, WpfControls.ImageEventArgs e)
{
Globals.ThisAddIn.InsertImage(e.ImagePath);
}
}
The custom InsertImage method in my add-in class gets hold of the current presentation, and adds a new slide. Then, I insert the image into the slide:
public void InsertImage(string imagePath)
{
PowerPoint.Slides slides =
this.Application.ActivePresentation.Slides;
PowerPoint.Slide slide = slides.Add(slides.Count + 1,
PowerPoint.PpSlideLayout.ppLayoutBlank);
PowerPoint.Shapes shapes = slide.Shapes;
shapes.AddPicture(
imagePath,
Office.MsoTriState.msoFalse,
Office.MsoTriState.msoTrue,
10, 10, 240, 180);
slide.Select();
}
That’s it. Bear in mind this is a simple proof-of-concept exercise, not production code, and I’ve taken a number of short-cuts. For instance, I’m not too happy about saving temporary files to disk, but this approach is partly forced on me by my choice of technologies (WPF and Windows Forms, managed code and native Office) and tools (fxc.exe). The project also serves as a useful experimentation pad for learning HLSL. If you’re serious about shaders, you’ll probably want to look at the Shazzam tool, the WPF Pixel Shader Effects Library, and the Shader Effects BuildTask and Templates.
This continues my previous checklists here, here and here on getting started with Excel Services. In this post, I’ll append a walkthrough for using the Excel Web Services APIs.
6. Using Excel Web Services APIs
6.1 Create an publish a workbook (RegionalSales.xlsx)
a. We could use any of the workbooks we’ve already published, but for simplicity, we’ll create a new one. Using Excel client, create a simple spreadsheet to represent regional sales. In one column put labels for the sales regions (North, South, East, West), and in the next column put some arbitrary $ values. Define names for each of the value cells, using the default names offered by Excel (based on the labels in the adjoining cell).
|
|
A |
B |
|
1 |
Sales |
|
|
2 |
North |
$1,234.00 |
|
3 |
South |
$5,678.00 |
|
4 |
East |
$9,876.00 |
|
5 |
West |
$5,432.00 |
b. Publish the sheet to Excel Services, using an appropriate path, eg: http://MyServer/sites/Contoso/Spreadsheets/RegionalSales.xlsx
6.2 Manipulate the workbook via a web service proxy (ConsoleTestExcelServices.dll)
a. In Visual Studio, create a simple console application.
b. Add a traditional web reference. To do this, right-click on References in Solution Explorer, and select Add Service Reference. In the Add Service Reference dialog, click the Advanced button, and then the Add Web Reference button.
c. Type in the URL for your Excel Services server, eg: http://MyServer/sites/Contoso/_vti_bin/ExcelService.asmx
d. When the wizard finds the Excel Services service, specify a suitable web reference name, eg: “ES”, and click Add Reference.
e. Declare some variables for the service URL, the workbook URL, and the worksheet name.
private const String serviceUrl =
"http://MyServer/sites/Contoso/_vti_bin/ExcelService.asmx";
private const String workbookUrl =
"http://MyServer/sites/Contoso/Spreadsheets/RegionalSales.xlsx";
private const String sheetName = "Sheet1";
f. Create the proxy to Excel Services web service, and specify the SOAP 1.2 protocol so we get richer error information. Also make sure that our user credentials are used.
ES.ExcelService s = new ES.ExcelService();
s.SoapVersion =
System.Web.Services.Protocols.SoapProtocolVersion.Soap12;
s.Credentials =
System.Net.CredentialCache.DefaultCredentials;
s.Url = serviceUrl;
String sessionId = null;
g. Open the workbook from Excel Services, read a cell value, and write a cell value. Be sure to close the workbook when you’re done.
try
{
Console.WriteLine(
"Opening workbook {0} at {1}", workbookUrl, s.Url);
ES.Status[] status;
// Open the workbook from Excel Services.
sessionId = s.OpenWorkbook(
workbookUrl, String.Empty, String.Empty,
out status);
Console.WriteLine("Session ID: {0}", sessionId);
// Read a cell value.
object north = s.GetCellA1(
sessionId, sheetName, "North", true, out status);
Console.WriteLine("North = {0}", north);
// Write a cell value.
status = s.SetCellA1(sessionId, sheetName, "East", 999);
if (status == null)
{
object east = s.GetCellA1(
sessionId, sheetName, "East", true, out status);
Console.WriteLine("East = {0}", east);
}
}
catch (SoapException e)
{
Console.WriteLine(
"SoapException: {0}", e.SubCode.Code.Name);
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
finally
{
if (!String.IsNullOrEmpty(sessionId))
{
try
{
s.CloseWorkbook(sessionId);
}
catch { }
}
}
h. The runtime output should look something like this:
Opening workbook http://MyServer/sites/Contoso/Spreadsheets/RegionalSales.xlsx at http://MyServer/sites/Contoso/_vti_bin/ExcelService.asmx
Session ID: 64.7d0d5456-4b85-448d-a88e-ce9242deb7f9HtTBm7DThVZQoXsRZba94EiD6V8=117.21.4/Uqc302XUOqoMSBpn5z190.5.en-US5.en-US73.+0480#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-0060
North = $1,234.00
East = $999.00
i. Note that the changes made to the sheet cell values are not persisted in the original file on the server. Note also that the app.config is not required in this client.
6.3 Manipulate the workbook via a WCF proxy (ConsoleTestEwsWcf.dll)
a. Previously, we used a traditional web service proxy, but we can use a WCF proxy instead.
b. To do this, create a console application as before.
c. In Solution Explorer, right-click on References and select Add Service Reference.
d. Type in the URL for the Excel Services service, eg: http://MyServer/sites/Contoso/_vti_bin/ExcelService.asmx, and click Go.
e. When the wizard finds the Excel Services service, select the ExcelServiceSoap node, specify a suitable namespace name, eg: “ES”, and click OK.
f. As before, declare some variables for the the workbook URL, and the worksheet name. Note that the service URL was written into the app.config by the service reference wizard.
//private const String serviceUrl =
//"http://MyServer/sites/Contoso/_vti_bin/ExcelService.asmx";
private const String workbookUrl =
"http://MyServer/sites/Contoso/Spreadsheets/RegionalSales.xlsx";
private const String sheetName = "Sheet1";
g. Create the proxy to Excel Services web service. Security information will be provided in the app.config.
ES.ExcelServicesSoapClient s =
new ES. ExcelServicesSoapClient ();
//s.SoapVersion =
// System.Web.Services.Protocols.SoapProtocolVersion.Soap12;
//s.Credentials =
// System.Net.CredentialCache.DefaultCredentials;
//s.Url = serviceUrl;
String sessionId = null;
h. Open the workbook from Excel Services, read a cell value, and write a cell value. Be sure to close the workbook when you’re done. This code is the same for both the traditional web service proxy and the WCF proxy, except that we allow for catching FaultExceptions not SoapExceptions.
try
{
Console.WriteLine(
"Opening workbook {0} at {1}", workbookUrl, s.Url);
ES.Status[] status;
// Open the workbook from Excel Services.
sessionId = s.OpenWorkbook(
workbookUrl, String.Empty, String.Empty,
out status);
Console.WriteLine("Session ID: {0}", sessionId);
// Read a cell value.
object north = s.GetCellA1(
sessionId, sheetName, "North", true, out status);
Console.WriteLine("North = {0}", north);
// Write a cell value.
status = s.SetCellA1(sessionId, sheetName, "East", 999);
if (status == null)
{
object east = s.GetCellA1(
sessionId, sheetName, "East", true, out status);
Console.WriteLine("East = {0}", east);
}
}
//catch (SoapException e)
//{
// Console.WriteLine(
// "SoapException: {0}", e.SubCode.Code.Name);
//}
catch (System.ServiceModel.FaultException e)
{
Console.WriteLine("FaultException: {0}", e.ToString());
if (e.Code.SubCode != null)
{
Console.WriteLine(
"SubCode: {0}", e.Code.SubCode.Name);
}
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
finally
{
if (!String.IsNullOrEmpty(sessionId))
{
try
{
s.CloseWorkbook(sessionId);
}
catch { }
}
}
i. Note the additional changes we must make to the app.config to specify security. First, the service reference wizard gives us security mode None by default, but we want to specify NTLM credentials:
<!--<security mode="None">
<transport clientCredentialType="None"
proxyCredentialType="None"
realm="" />
<message clientCredentialType="UserName"
algorithmSuite="Default" />
</security>-->
<security mode="TransportCredentialOnly">
<transport clientCredentialType="Ntlm"
proxyCredentialType="None" realm="" />
<message clientCredentialType="UserName"
algorithmSuite="Default" />
</security>
j. We add an endpoint behavior to allow impersonation, so that the endpoint can act with the client’s credentials on the server.
<behaviors>
<endpointBehaviors>
<behavior name="AllowImpersonationBehavior">
<clientCredentials>
<windows allowedImpersonationLevel="Impersonation"/>
</clientCredentials>
</behavior>
</endpointBehaviors>
</behaviors>
k. Finally, apply this behavior to the endpoint.
<endpoint
address="http://MyServer/_vti_bin/ExcelService.asmx"
binding="basicHttpBinding"
bindingConfiguration="ExcelServiceSoap"
contract="ES.ExcelServiceSoap"
behaviorConfiguration="AllowImpersonationBehavior"
name="ExcelServiceSoap" />
l. The runtime output will be the same as before.
OK, this series of posts has built up a very simple checklist of tasks for setting up and configuring MOSS and Excel Services, publishing workbooks, building and publishing UDFs, and using Excel Web Access and Excel Web Services APIs. There’s obviously a lot more information on MSDN on these topics, and you’re encouraged to flesh out the information in this checklist with the much richer information in the published documentation.
This continues my previous checklists here and here on getting started with Excel Services. In this post, I’ll append a walkthrough for using Excel Web Access.
5. Using Excel Web Access
5.1 Make the EWA web part available in your sub-site
a. Note: this task is only required if you did not base your site on the Document Workspace template.
b. We want to create a new page for this sub-site, using the Excel Web Access web part. However, the EWA web part is not available by default for new sub-sites based off the Blank Site template. If we had based the site off the Document Workspace site template, the EWA web part would be available by default. Depending on which template you did use, the web part might be part of a feature that simply needs to be activated on your site.
c. If not, we can simply copy the EWA web part from the top-level site gallery into the sub-site gallery. To do this, navigate to the default home page for the SharePoint server, eg: http://MyServer/Pages/Default.aspx
d. Select the Site Actions drop-down in the top right-hand corner, and click Site Settings, then Modify All Site Settings.
e. Under Galleries, click Web Parts.
f. From the list of web parts, find the Microsoft.Office.Excel.WebUi.dwp, and click the Edit Document Properties link.
g. On the properties page, click the Export button, and save the dwp to some location on your machine.
h. Then, navigate to the sub-site, eg: http://MyServer/sites/Contoso
i. Select the Site Actions menu, Site Settings option.
j. Under Galleries, click Web Parts.
k. Click Upload, and find the Microsoft.Office.Excel.WebUi.dwp that you exported previously.
l. Edit the web part properties to match the properties in the top-level site gallery. Most of the properties are already set for you, and you can actually accept all the defaults. For consistency, however, you can type in “Business Data” for the group, and “Dashboard” for the Quick Add Groups.
m. Note that the .DWP is simply an XML file that identifies the DLL for the web part:
<WebPart xmlns="http://schemas.microsoft.com/WebPart/v2" >
<Assembly>Microsoft.Office.Excel.WebUI, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c</Assembly>
<TypeName>Microsoft.Office.Excel.WebUI.ExcelWebRenderer</TypeName>
<Title>Excel Web Access</Title>
<Description>Use the Excel Web Access to interact with an Excel workbook as a Web page.</Description>
<FrameType>TitleBarOnly</FrameType>
<PartImageSmall>/_layouts/images/ewr023.gif</PartImageSmall>
<PartImageLarge>/_layouts/images/ewr023.gif</PartImageLarge>
</WebPart>
5.2 Create a new Web Part page in MOSS (EwaTest.aspx)
a. Navigate to your sub-site, eg: http://MyServer/sites/Contoso.
b. Select the Site Actions menu, Create option. On the Create page, under Web Pages, select Web Part page.
c. On the Create Page page, type in a suitable title for the page, eg “EwaTest” – this will be an .ASPX page.
d. Select the simplest layout template to base the new page on: Full Page Vertical.
e. Select any of your site’s document libraries as the save location, eg Spreadsheets, and click Create.
f. SharePoint creates the new page and navigates to it. On the new page, click the Add Web Park link.
g. From the Add Web Parts menu, scroll down to the All Web Parts, Business Data section, select the Excel Web Access part, and click Add.
5.3 Associate the web part with a workbook
a. On the new web part page, under Select a Workbook, click the link to open the tool pane.
b. In the EWA properties pane, either type in a (relative or absolute) URL to the workbook you want to render in this EWA part, or click the Browse button to navigate to the workbook. You can choose any workbook, including any that you have uploaded to this site’s document library, eg: /sites/Contoso/Spreadsheets/ContosoSales.xlsx.
c. For the purposes of this exercise, you can leave all the other settings at their defaults. Click OK, and exit Edit Mode. This will take you to the new page. You can also get to this page by navigating independently to it, eg: http://MyServer/sites/Contoso/Spreadsheets/EwaTest.aspx
d. Done.
This continues my previous checklist on getting started with Excel Services. Here, I’ll append a walkthrough for creating, deploying and updating User-Defined Function assemblies with Excel Services.
4. Create and deploy a UDF assembly
4.1 Create a UDF assembly in Visual Studio (SampleUdf.dll)
a. I’m assuming here that your developer machine is not the same as your Excel Server machine. When you build UDFs, you do need to reference an assembly that is part of Excel Services, but which is unlikely to be available on your dev machine.
b. For simplicity, take a copy of the Microsoft.Office.Excel.Server.Udf.dll from the Excel Server machine, and put it onto your dev machine, so that you can reference it locally. On the Excel Server machine, this DLL is in the GAC, typically here: C:\Windows\assembly\GAC_MSIL\Microsoft.Office.Excel.Server.Udf\12.0.0.0__71e9bce111e9429c\. Copy this to a suitable location on your dev machine.
c. In Visual Studio, create a simple Class Library project. Add a reference to Microsoft.Office.Excel.Server.Udf.dll (and set the Copy Local property to false). You need this for the UdfClass and UdfMethod attributes. Add a suitable using statement for the Microsoft.Office.Excel.Server.Udf namespace.
d. Create a simple class with two methods, using the UdfClass and UdfMethod attributes, eg:
[UdfClass()]
public class MyUdfs
{
[UdfMethod]
public double GetDouble(double d)
{
return d * 2;
}
[UdfMethod(IsVolatile = false)]
public DateTime GetStaticTime()
{
return DateTime.Now;
}
[UdfMethod(IsVolatile = true)]
public DateTime GetVolatileTime()
{
return DateTime.Now;
}
}
e. In the example above, there are 2 non-volatile methods: GetDouble and GetStaticTime, and one volatile method, GetVolatileTime. GetStaticTime will often return the current time, but Excel Services makes performance optimizations by caching return values of non-volatile methods. So, sometimes Excel Services will return a cached value for GetStaticTime instead of calling the method again. On the other hand, GetVolatileTime will be called every time anything on the sheet is recalculated and re-rendered to the client.
f. Build the DLL.
4.2 Deploy the UDF assembly to Excel Services
a. Copy the UDF DLL from your dev machine to some suitable location on the server machine, eg: “C:\Data\UDFs”. You can deploy UDFs either to an arbitrary file location, or to the GAC. Note that normal probing rules apply, so any dependencies need to be either in the same folder tree as the primary UDF assembly or in the GAC.
b. On the SharePoint Server machine, in Central Administration, select the Application Management tab at the top.
c. Click the SSP link for your Excel Services SSP (by default, “SharedServices1”) to go to the administration page for that SSP.
d. On the Shared Services Administration page, under Excel Services Settings, select the User-defined function assemblies link.
e. Click the Add User Defined Function Assembly option.
f. In the Add User-Defined Function Assembly page, specify the path where you copied the UDF DLL to on the server, eg: “C:\Data\UDFs\SampleUdf.dll”.
g. Specify that this is a File path location, and ensure that the Assembly enabled option is checked. Click OK.
h. The UDF should now be listed on the Excel Services User-Defined Functions page for the SSP.
4.3 Test the UDF assembly (TestSampleUdf.xlsx)
a. Create a new Excel workbook. In Column A, enter labels for the 3 UDFs: Double, StaticTime and VolatileTime. In Column B, enter formulas that call each of these methods:
|
|
A |
B |
C |
|
1 |
Double |
=GetDouble(C1) |
5 |
|
2 |
StaticTime |
=GetStaticTime() |
|
|
3 |
VolatileTime |
=GetVolatileTime() |
|
b. Note that Excel client will return #NAME? for these UDF calls because it cannot find the UDFs on the client. This is expected.
c. Note also that the GetDouble call uses cell C1 as a parameter. Define a name for cell C1, eg: “DoubleParam”.
d. Publish the workbook to Excel Services as normal. Only publish Sheet1, and add a parameter for the DoubleParam cell.
e. When the workbook is published, it is rendered in the browser. You can enter new values for the DoubleParam, and when you click Apply, Excel Services will recalculate the sheet and re-render it.
f. Note that whenever GetDouble is recalculated, so is GetVolatileTime. On the other hand, Excel Services will often return a cached value for GetStaticTime.
4.4 Update the UDF assembly
a. Excel Services loads UDF assemblies when it needs to look for a UDF in a sheet that it is calculating. It only loads each assembly once, and never unloads any assemblies. This makes it difficult to develop iteratively.
b. To force Excel Services to release its lock on a UDF assembly, you can simply recycle the Excel Services application pool.
c. This simple command will recycle the pool:
cd %windir%\system32\inetsrv
appcmd recycle apppool "OfficeServerApplicationPool"
pause
d. You can find the name of the app pool used by Excel Services in IIS Manager, under Application Pools.
e. If necessary, you can also reset IIS altogether, although this is obviously pretty draconian:
net stop iisadmin
rem respond Y
net start w3svc
pause
I hope this continuation checklist is useful – as before, it simply collects together and distils information from a range of documentation on MSDN.
As part of an internal prototype I was working on recently, I had to set up a machine for Excel Services – and do this repeatedly as my prototype messed up the installation in various ways. I found lots of MSDN documentation that covered all aspects of setting up the base server OS, setting up MOSS, and configuring MOSS for Excel Services in a multitude of possible configurations – but what I wanted was a nice concise checklist so that I could reliably setup an E/S box whenever I needed to. I couldn’t find one, so I’ve created my own.
This is a brief step-by-step checklist for setting up Excel Services on MOSS 2007 from scratch, and publishing workbooks. Later on, I’ll look at how to publish UDFs, and use Excel Web Access and the Excel Web Services APIs. A word of warning: I used these instructions to set up a machine specifically for testing, demoing and prototyping – and explicitly not for real production use, so some of the configuration choices I made might not be suitable for real production use.
1. Set up Windows Server 2008
1.1 Install and configure Windows Server 2008
a. Install Windows Server 2008, and all SPs and updates. This simple installation will be for a standalone server, not a farm.
b. In Server Manager | Customize this server | Enable Remote Desktop – this step is just so I could continue setting up the server remotely (kicking off various parts of the configuration while sitting in some tedious meeting, perhaps).
c. Add the Web Server role: Start | Administrative Tools | Server Manager | Roles | Add Role | Web Server (IIS)
i. Under Role Services, add Application Development, all Health & Diagnostics, all Security, and IIS 6.0 Management Compatibility (in addition to the IIS 7.0 support provided by default).
d. Add the Application Server role, which will install .NET Fx 3.0. (Alternatively, install .NET Fx 3.0 as a feature: in Server Manager, select Action | Add features | .NET Framework 3.0 Features.)
e. Turn off IE Enhanced Security Configuration: Server Manager | Security Information | Configure IE ESC – this step is just a convenience for my test machine, so that I can navigate freely without security prompts. Obviously, not recommended for production use.
f. Turn on network discovery and file sharing: Control Panel | Network & Sharing.
1.2 Install Microsoft Office SharePoint Server 2007
a. Run setup for MOSS 2007, and all SPs and updates. Configure the server as Basic.
b. At the end of setup, run the SharePoint Configuration Wizard. This configures MOSS, and does not require user input.
2. Configure MOSS to support Excel Services
2.1 Make sure Excel Services is running
a. This step should only be required for a farm setup – in a standalone setup, Excel Services should already be started, but it is included here for completeness.
b. Select Start | Administrative Tools | SharePoint 3.0 Central Administration. This runs the SharePoint Central Administration web application.
c. In Central Administration, select the Operations tab at the top.
d. On the Operations page, under Topology and Services, click on “Services on server”.
e. On the Services on Server page, ensure that Excel Calculation Services is started.
2.2 Create a new SharePoint site
a. In SharePoint Central Administration, select the Application Management tab at the top.
b. On the Application Management tab, under SharePoint Site Management, select Create site collection. This creates a new site.
c. On the Create Site Collection page, make sure the base web application is set to your server name without a port (the web app with a port is your server admin app).
d. Type in a suitable Title (eg “Contoso”).
e. Also type in a suitable URL. You’ll be given the start of the URL based on your SharePoint Server address (eg: http://MyServer). Make the new site a sub-site of the top-level “sites”, so that your full URL will be something like http://MyServer/sites/Contoso.
f. For Template Selection, you can use any of the provided templates. However, it tends to be best to start with the Document Workspace template (on the Collaboration tab), because that gives you the maximum features, page templates, web parts, etc.
g. Specify the Primary and Secondary Site Collection Administrators. You can simply set the Primary Administrator to the existing SharePoint Server administrator username. The names must be valid domain usernames – make sure to check the name, using the lookup button at the end of the field.
h. Click OK. This will cause SharePoint to create and provision the new site, and the site collection for the site. When it’s done, SharePoint will display a “Site Successfully Created” page.
i. Navigate to your new site, eg: http://MyServer/sites/Contoso. Drop down the Site Actions menu, and select Site Settings. Under Site Administration, click Site features. On the Site features page, locate Office SharePoint Server Enterprise Site features (which includes Excel Services) and make sure it is activated.
2.3 Create a Document Library to hold the workbooks
a. Navigate to your new site, eg: http://MyServer/sites/Contoso.
b. From your new site page, drop down the Actions menu, and select the Create command. Note that if you did not base your site on the Document Workspace template, the command navigation to create a document library may be slightly different.
c. From the Create page, under Libraries, select Document Library.
d. On the New page, type in a name for the library, eg “Spreadsheets”.
e. Change the default document template to Excel Spreadsheet. This is only required if you’re going to allow users to create new documents within the library – as opposed to publishing documents to the library. For my testing, I was only going to publish documents to the library, not create new ones from there, so this step was really redundant in my case.
f. Click Create. This creates the new document library.
2.4 Add a trusted file location for uploading workbooks to Excel Services
a. In Central Administration, select the Application Management tab at the top.
b. Under Office SharePoint Server Shared Services, click on “Create or configure this farm’s shared services”.
c. You can use the default SSP, probably named “SharedServices1”.
d. Click the SSP link (either in the left-hand nav-pane, or in the drop-down on the page) to go to the administration page for that SSP.
e. On the Shared Services Administration page, under Excel Services Settings, select the Trusted file locations link.
f. On the Trusted File Locations page, click the Add Trusted File Location link.
g. On the Add/Edit Trusted File Location page, type in the address – use the URL of the document library you created for this purpose, eg: http://MyServer/sites/Contoso/Spreadsheets.
h. Make sure the location type is a Windows SharePoint Services location.
i. If you want to trust sub-folders of this location, check the Children Trusted option.
j. If you want to trust workbooks with with links to external data, you can check one of these options.
k. If you want to allow User-Defined Functions, check the User-Defined Functions Allowed option at the bottom of the page.
l. Click OK.
2.5 Configure Single Sign-On for Excel Services
a. Open the Services MMC: Start menu | Administrative Tools | Services.
b. Right-click on Microsoft Single Sign-on Service, and select Properties. In the Properties dialog, on the General tab, change the Startup Type to Automatic. On the Log On tab, select the Log on as This account option, and type in the domain\username and password that you used to install MOSS 2007. Click OK.
c. Start the service by clicking Start in the Services dialog, then close the Services dialog.
d. Open the SharePoint Central Administration web app, and select the Operations tab. Under Security Configuration, click Manage settings for single sign-on. In the Manage Settings for Single Sign-On page, click Manage server settings.
e. In the Manage Server Settings for Single Sign-On page, type in the domain\username for the Single Sign-On Administrator Account – this should be the same user that the SSO service is running as (and the same as the admin user account for MOSS). Enter the same domain\username for the Enterprise Application Definition Administrator Account. Leave the other settings as their defaults, and click OK.
f. Navigate back to the Manage Settings for Single Sign-On page, and click Manage settings for enterprise application definitions. Then click New Item.
g. In the Create Enterprise Application Definition page, type in a suitable Display name and Application name. You can set these both to the same value, for example “SSO”. Type in the email address that users can contact for this application.
h. Set the Account type to Group, and the Authentication type to Windows authentication. Set the Logon Account Information Display Name fields to suitable values – the defaults of “Username” and “Password” are fine. The username field should not be masked, but the password field should be masked. Click OK.
i. Navigate back to the Manage Settings for Single Sign-On page, and click Manage Account Information for enterprise application definitions.
j. On the Manage Account Information for an Enterprise Application Definition page, make sure the SSO application definition is selected in the drop-down listbox. Then enter a suitable domain\group name as the group account name, eg: “MYDOMAIN\Domain Users”, and click Set.
k. Enter the username and password of a user on your domain that has access to the data sources that you will be using, and click OK.
l. This puts you back on the previous Manage Account Information for an Enterprise Application Definition page. Click Done.
2.6 Configure Excel Services timeouts
a. The various timeout settings on Excel Services can be tuned for optimum performance and reliability. For testing or demo purposes, you might want to disable timeouts altogether.
b. To do this, open the Shared Services Admin page for your SSP (probably SharedServices1). Under Excel Services Settings, click Trusted File Locations. From the list of trusted locations, click the one you have set up for publishing worksheets, so that you can edit the properties.
c. On the Excel Services Edit Trusted File Location page, under Session Management, set the Session Timeout to -1. The default is 300, and setting it to -1 means that sessions will not timeout.
d. Under Session Management, set the Short Session Timeout to -1 to prevent sessions timing out (default is 75 seconds) - this is the maximum allowable time between opening the session and the first user interaction. Also set the Maximum Request Duration to -1 (default is 300) - this is the maximum duration of a single request in a session.
e. Under Calculation Behavior, set the Volatile Function Cache Lifetime to 0 (zero), to force Excel to recalculate the workbook for each new session. Note this will not result in recalculations within the current session. The only way to force recalculation during a session is to change input parameters or (using the Excel Services web service) by calling the CalculateWorkbook API. Refreshing the page in the browser will not cause a recalculation. Setting the VFCL to zero simply sets a zero initial cache for each session.
2.7 Configure the Unified Logging Service
a. Out of the box, MOSS uses ULS to sent trace information to the system event logs, performance counters, and to the MOSS-specific ULS logs. For testing purposes, you probably want to maximize the log information.
b. To do this, go to SharePoint Central Admininistration, click the Operations tab, find the Logging and Reporting section, and click Diagnostic Logging.
c. In the Event Throttling section, select the Category dropdown and, for each of the eight Excel options do the following:
i. Select the item (eg, Excel Calculation Services).
ii. In the Least Critical Event to Report to the Event Log dropdown, leave the default setting (Error).
iii. In the Least Critical Event to Report to the Trace Log dropdown, select Verbose.
iv. Click OK.
v. Go back to step (i).
d. By default, the logs are written to %ProgramFiles%\Common Files\Microsoft Shared\Web Server Extensions\12\LOGS.
e. The log files are tab-delimited text files, which you can view in Notepad or Excel.
3. Create and publish a workbook to Excel Services
3.1 Create an Excel workbook (ContosoSales.xlsx)
a. On the client machine, open the regular Excel desktop application, and create a new workbook with 2 worksheets.
b. In Sheet1, set up a simple set of cell values and labels. Define named ranges for the cells with the money values. For example, select the cell containing the Sales value (cell B1 in the example below, not the label). From the Formulas tab, select Define Name. By default, Excel will suggest the label to the left (that is, “Sales”) as the name of the named range. Accept this default. Repeat for the money cell for Costs (B2).
|
|
A |
B |
|
1 |
Sales |
$100,000.00 |
|
2 |
Costs |
$85,000.00 |
|
3 |
Profit |
|
c. On Sheet2, in any cell, enter a suitable formula for the Profit value, eg: =(Sales-Costs)/Sales. When we publish this workbook to Excel Services, we’re going to hide Sheet2.
d. Back in Sheet1, in the value cell for Profit (cell B3 in the example above), enter a formula to reference the formula in Sheet2, eg: =Sheet2!B2 (where B2 is the cell containing the profit formula in Sheet2), and format it as a percentage.
3.2 Publish the workbook to Excel Services
a. In Excel, click the Office button, and select Publish, Excel Services. This displays a modified Save As dialog.
b. In the Save As dialog, use the URL of the Spreadsheets document library you created on SharePoint Server as the path for your new workbook, eg: http://MyServer/sites/Contoso/Spreadsheets/ContosoSales.xlsx.
c. Then click the Excel Services Options button.
d. In the Excel Services Options dialog, on the Show tab, specify that you only want to publish Sheet1, not the entire workbook.
e. On the Parameters tab, click Add. In the Add Parameters dialog, select both the Sales and Costs named ranges as parameters. This will allow the browser client to feed data into the worksheet for these parameters.
f. Click Save. This will publish the workbook to Excel Services, and open a browser window with a rendering of the published spreadsheet. Note that you can’t edit the sheet directly, but you can enter values for the Costs and Sales parameters in the task pane, and apply them to update the sheet. This will cause the sheet to be recalculated on the server, and re-rendered on the client. Note also that any changes you make are not persisted to the workbook on the server.
3.3 Ensure users open the spreadsheet in the browser
a. Back on the SharePoint Server, if you navigate to the Spreadsheets document library, you should see your new workbook listed there.
b. By default, if a user navigates to this page, and clicks on a workbook, the workbook will open on the client in client Excel. To ensure that it opens in the client’s browser instead, drop down the Settings list, and select Document Library Settings.
c. Under General Settings, click Advanced settings.
d. In the Browser-enabled Documents section, select the Display as a Web page option, and click OK.
That’s it! I’ve basically collected together the necessary installation and configuration steps from a range of published sources, and distilled them down to a simple checklist. Next time I get a few moments, I’ll add to this checklist with the steps for creating and publishing UDFs, and using the Excel Web Services APIs.
In my last post, I looked briefly at MEF, and I’m wondering how this model can be applied to Office add-ins. The Office add-in model itself already achieves a level of dynamic composition, by virtue of the fact that the set of add-ins to be loaded is only discovered at runtime. However, even though the model is similar at a high level, Office itself does not use MEF.
Nonethless, MEF could perhaps be useful in an Office context. For example, suppose you want to build the add-in itself as a composite. That is, suppose your total add-in functionality is made up of components – and furthermore, you want to discover these components at runtime, instead of statically linking them at design-time.
In this post, I’ll translate the simple console MEF app from my previous post to an Office add-in.
This turned out to be remarkably easy. I used the same ‘Interface’ assembly, the same ‘Implementation’ assembly, and the same ‘Alternate’ implementation assembly. The only difference was that I built a VSTO add-in instead of a console app. This is the code in my add-in class:
[Import]
public Interface.ICalculate Calculate { get; set; }
private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
DirectoryCatalog catalog =
new DirectoryCatalog(
AppDomain.CurrentDomain.BaseDirectory);
CompositionContainer container =
new CompositionContainer(catalog);
CompositionBatch batch = new CompositionBatch();
batch.AddPart(this);
container.Compose(batch);
}
internal void GetCircumference()
{
double d = (double)this.Application.ActiveCell.Value2;
this.Application.ActiveCell.get_Offset(0, 1).Value2 =
Calculate.Circumference(d);
}
As you can see, the code is almost identical to what I had in my console app. The difference here is that I split up the composition from the method invocation. I set up the catalog and CompositionContainer and perform the composition on startup, but I defer actually invoking the imported method until the user clicks my custom Ribbon button:
public partial class RibbonX : OfficeRibbon
{
public RibbonX()
{
InitializeComponent();
}
private void RibbonX_Load(object sender, RibbonUIEventArgs e)
{
}
private void buttonCircumference_Click(
object sender, RibbonControlEventArgs e)
{
Globals.ThisAddIn.GetCircumference();
}
}
As before, this works with either my first implementation of the contract, or the alternate version.
One obvious question that springs to mind is, “can this model be used to mitigate the versioning problem in Office?”. Specifically, for example, could you use this model to build an add-in that dynamically composes components that implement either 2003-style CommandBar UI or 2007-style Ribbon/TaskPane UI? The answer is, No, probably not in any useful way. If you think about it, at the core of the MEF model is the contract: the model relies on the contract being the ‘fixed’ point in the moving constellation of composable parts. In the CommandBar vs Ribbon scenario, the contract is not fixed – you’re either using CommandBar interfaces or you’re using Ribbon/TaskPane interfaces.
You can perhaps envisage an imaginary IOfficeUi interface, which one component might implement to render commandbars while another component implements the same interface to render ribbons – but this is an imaginary scenario, and it does not map to the reality of Office versioning. Where the Office add-in scenario does map to the MEF model is in the composability of add-ins – because all add-ins must implement IDTExtensibility2, and they can implement it in arbitrarily different ways (within some constraints). However, back to what I said at the top of the post – this is conceptually similar to the MEF model, but does not use MEF.
All right, then, you’re thinking – why can’t we devise such an IOfficeUi interface for use between our add-in and composable parts that implement this differently? Well, of course, we could do this – but not in any useful way. Imagine what you’d need to build… Because of the way Office uses the new optional extensibility interfaces (IRibbonExtensibility, ICustomTaskPaneConsumer, etc), your add-in must advertise that it either does or does not implement these interfaces very early on in its lifetime – very soon after it is loaded. If you don’t tell Office up front that you implement these interfaces, you’ll never get called back on them. So, even if you build an Office 2003 add-in (which does not use these interfaces), you’d still be obliged to implement them in case you’re actually running in Office 2007 or later.
Moreover, the imaginary IOfficeUi interface would probably end up being very generic – plus you’d have the problem of how to communicate between the UI and the business logic in your solution. You’d end up with some hopelessly generic interfaces like this:
public interface IOfficeUi
{
// In Initialize, construct CommandBars or Ribbon/TaskPane.
void Initialize(object addIn);
IBusinessLogic BusinessLogic { get; set; }
}
public interface IBusinessLogic
{
// In InvokeMethod, invoke business logic from the UI event handlers.
object InvokeMethod(string methodID, params object[] args);
IOfficeUi OfficeUi { get; set; }
}
This is not really a workable model. So, for the UI versioning case, the only really workable approach is to componentize along the lines of my post here, using the lowest-common denominator but implementing later interfaces. MEF doesn’t help you in this scenario.
The Managed Extensibility Framework (MEF) is a new library, still under development, that provides support for composing applications dynamically. Many applications have a composite model, where the total functionality is provided by a number of component parts. Often, these components are all known at design-time, so the composition is static. However, it is sometimes useful to be able to build the composition dynamically – where the set of components to be loaded is only discovered at runtime. Office client apps are composite apps in the sense that they discover and load add-ins dynamically – although of course Office apps do not use MEF. A preview of the MEF code, documentation and samples are available on codeplex here.
Let’s consider a simple example. A console app wants to invoke functionality that can be implemented in components discovered at runtime. There must be no static reference to the components within the app. To provide this indirection, the app needs to communicate with these components via an interface that they both recognize. The component implements this interface, and is then deployed in such a way that the app can discover it at runtime. Here’s the interface:
public interface ICalculate
{
double Circumference(double radius);
}
The idea of using interfaces to communicate between components that can be built independently is not new, but the MEF framework simplifies the discovery and composition mechanics. I can create a simple class library project (which I shall call “Interface”) that contains just this interface. MEF components (or “composable parts”) do not directly depend on one another, instead they depend on a contract, which is a string identifier. It is reasonable to specify a contract via an interface, to ensure separation. So, I can implement this interface in another class library (which I’ll call “Implementation”):
[Export(typeof(Interface.ICalculate))]
public class Calculate : Interface.ICalculate
{
public double Circumference(double radius)
{
return 3.14159 * radius * 2;
}
}
Note the use of the Export attribute. This is defined in the assembly System.ComponentModel.Composition.dll, which is released as part of the preview of the MEF library on codeplex. Effectively, this specifies that I’m exporting the type identified by the string “Interface.ICalculate”.
Finally, I can create a console application project which specifies the contract it wants to use (via the Import attribute), uses MEF to discover and compose the available components, and then invokes the implemented contract:
class Program
{
[Import]
public Interface.ICalculate Calculate { get; set; }
static void Main(string[] args)
{
Program p = new Program();
p.Run();
Console.ReadKey();
}
private void Run()
{
//AssemblyCatalog catalog =
// new AssemblyCatalog(Assembly.Load(
// "Implementation, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null"));
DirectoryCatalog catalog =
new DirectoryCatalog(
AppDomain.CurrentDomain.BaseDirectory);
CompositionContainer container =
new CompositionContainer(catalog);
CompositionBatch batch = new CompositionBatch();
batch.AddPart(this);
container.Compose(batch);
Console.WriteLine(
String.Format("{0}", Calculate.Circumference(4)));
}
}
The app uses a MEF catalog to load the assembly that implements the interface (‘exports the contract type’) that it wants to use. There are several different kinds of catalog. I’ve deliberately chosen not to use a simple AssemblyCatalog – because (as you can see from the commented code) that would require me to specify the assembly name for the assembly to be loaded – and I explicitly want to have zero knowledge of the implementing assembly (ie, no static reference) within the consuming application.
For this reason, I’m using a DirectoryCatalog, and pointing it at the folder where the executing assembly is. This means that the catalog will be able to use any assemblies it finds in this folder when it comes time to build the composition.
Next, I create a CompositionContainer, and initialize it with the DirectoryCatalog (and therefore, with all the assemblies that the DirectoryCatalog finds). I also add the current application object to the container, so that its dependencies (Import requirements) are included in the composition operation. Then, I can invoke the Compose method – this matches up all the Imports and Exports that have been collected in the container. Finally, I can invoke the contract method (“Circumference”) on the imported type, and I’m done.
So far, my composite app has been made up of 3 assemblies: the app itself, the interface assembly, and the implementation assembly. To further illustrate how MEF dynamic composition works, let’s suppose I have an alternative version of the implementation assembly (let’s call it “Alternate.dll”). You can imagine that this is produced by a different team, or perhaps bought in from a 3rd party vendor. Instead of the simple constant value for Pi that I used in my first implementation, this alternative implementation uses Math.PI:
[Export(typeof(Interface.ICalculate))]
public class Calculate : Interface.ICalculate
{
public double Circumference(double radius)
{
return Math.PI * radius * 2;
}
}
Note that this is not simply an updated version of the same assembly – it is a completely independent assembly, with a different assembly name. I can simply deploy this alternate version to the same folder as the consuming application. I do NOT need to rebuild the app, because it did not have any static reference to the implementation assembly – it only referred to the interface assembly, which has not changed.
At runtime, the catalog will pick up the alternate assembly, and the CompositionContainer will compose the composite app such that the consuming app code can transparently use the new version. Note that I cannot deploy both versions of the implementation assemblies to the same location – because the DirectoryCatalog will find them both, and then the CompositionContainer will barf if it finds 2 implementations of the same Export. So, when I deploy the alternate implementation, I must be careful to remove the original one.
You can see that this mechanism would in fact also work in the case of updated versions of the same assembly – although in that scenario the ‘dynamic discovery and composition’ feature is less obviously useful, because you’re more likely to know the assembly name at design-time.
As you can see, MEF is pretty simple, yet it enables a quite powerful dynamic composition model.
The eagerly-awaited update to Eric Carter and Eric Lippert’s VSTO book has been released this week. This is the definitive guide to Visual Studio Tools for Office, and this edition targets development for Excel, Word and Outlook 2007 using Visual Studio 2008. Eric and Eric have done another excellent job, and the book weighs in at 1055 pages – jam-packed with in-depth insights into VSTO development. The ToC looks like this:
Part I – An Introduction to VSTO
1. An Introduction to Office Programming
2. Introduction to Office Solutions
Part II – Office Programming in .NET
3. Programming Excel
4. Working with Excel Events
5. Working with Excel Objects
6. Programming Word
7. Working with Word Events
8. Working with Word Objects
9. Programming Outlook
10. Working with Outlook Events
11. Working with Outlook Objects
Part III – Office Programming in VSTO
12. The VSTO Programming Model
13. Using Windows Forms and WPF in VSTO
14. Working with Document-Level Actions Panes
15. Working with Application-Level Custom Task Panes
16. Working with Outlook Form Regions
17. Working with the Ribbon in VSTO
18. Working with Smart Tags in VSTO
19. VSTO Data Programming
20. Server Data Scenarios
21. ClickOnce Deployment
…what are you waiting for – go get a copy!
Having looked at Silverlight in Office client, I thought I’d round out the exercise by looking at Silverlight in Office server. It turns out that lots of people have looked at this before, and I relied heavily on information from several other people’s blog posts (see below), the Silverlight Blueprint for Sharepoint, and Steve Fox and Paul Stubbs’s new book.
To host a Silverlight app in SharePoint, you’ll need the Silverlight Tools for Visual Studio 2008 sp1, which you can download for free here. You’ll also want the VS2008 Extensions for WSS 3.0 v1.3 – the latest Feb2009 CTP is available here. Make sure you read the release notes for this CTP: in particular, VSeWSS 1.3 now includes a web service that needs to run as a member of the local Administrators group – this is not done automatically on install (quite rightly), so you must set it up manually.
There are 3.5 main steps in this exercise:
· Create the Silverlight application.
· Put the Silverlight application somewhere on the server so that it is accessible from SharePoint.
· Create a web part to host the Silverlight application (this is in 2 sub-steps).
Step 1, create the Silverlight application in VS. I called my example SilverlightCoffee. Put some simple control on the page. In my example, I used a TextBlock, and I’ve set the initial content and Foreground color, plus an event handler for the MouseLeftButtonUp event.
<TextBlock x:Name="coffeeText"
FontFamily="Calibri"
FontSize="80"
FontWeight="Bold"
Foreground="#ff402010"
MouseLeftButtonUp="CoffeeText_MouseLeftButtonUp">
espresso
</TextBlock>
Add the implementation for the event handler in the Page.xaml.cs. I’ve set things up so that when you click the mouse over the app, the text and color change:
public partial class Page : UserControl
{
private bool isEspresso = true;
private SolidColorBrush latteBrush;
private SolidColorBrush espressoBrush;
public Page()
{
InitializeComponent();
latteBrush = new SolidColorBrush(
Color.FromArgb(0xff, 0xc0, 0x90, 0x50));
espressoBrush = new SolidColorBrush(
Color.FromArgb(0xff, 0x40, 0x20, 0x10));
}
private void CoffeeText_MouseLeftButtonUp(
object sender, MouseButtonEventArgs e)
{
if (isEspresso)
{
coffeeText.Text = "latte";
coffeeText.Foreground = latteBrush;
}
else
{
coffeeText.Text = "espresso";
coffeeText.Foreground = espressoBrush;
}
isEspresso = !isEspresso;
}
}
Build and test the Silverlight app. By default, VS will give you both a TestPage.html for testing the app in a client browser, and a SilverlightXXXTestPage.html/aspx for testing it in a web server/client browser mode.
Step 2, put the Silverlight app somewhere where SharePoint can find it. One way to do this is to create a document library on your SharePoint site to hold Silverlight applications. You can name this whatever you like, but from the code below you can see that I’ve named mine “XAPs”. Upload the Silverlight XAP to this library. You should create this document library at whatever site level makes sense on your server. For example, http://MyServer/sites/Contoso/XAPs. Note: this isn’t the only way to do this – as an alternative to using a document library, you could use IIS Manager to create a virtual directory under your SharePoint web site, map it to a physical directory, and put the XAP there instead. This approach is described in Phil Wicklund’s excellent series of posts here.
Step 3.1, create a SharePoint Web Part to host the Silverlight app. There are 2 ways to do this:
· Use one of the standard SharePoint web parts, and customize it.
· Create a completely new custom web part, using Visual Studio.
Let’s try the first approach – we’ll do the second later. Before this will work, you need to install the System.Web.Silverlight.dll from the Silverlight SDK\Libraries\Server folder to the GAC, so that SharePoint can find it. Then, go to your home page (or any page on your site), drop down the Site Actions listbox, and select Edit Page. When the page is in edit mode, click on any of the ‘Add a Web Part’ links. From the Add Web Parts dialog, find the Content Editor Web Part, check the box next to it, and click Add. Inside the CEWP, you’ll see a link to open the tool pane. Click this link, and then in the tool pane, click the Source Editor button.
Now copy the <div> for the Silverlight host control from the TestPage.html that VS generates for you for the Silverlight application project (you’ll find this in the bin\debug folder). Paste this into the Source Editor window. You need to make 3 changes to this HTML: specify explicit pixel values for the width and height of the Silverlight control; specify the same width and height for the Silverlight application within the control; and specify the full path to the XAP:
<div id="silverlightControlHost">
<object data="data:application/x-silverlight,"
type="application/x-silverlight-2" width="400px" height="120px">
<param name="source"
value="http://MyServer/sites/Contoso/XAPs/SilverlightCoffee.xap"
<param name="onerror" value="onSilverlightError" />
<param name="background" value="white" />
<param name="minRuntimeVersion" value="2.0.31005.0" />
<param name="autoUpgrade" value="true" />
<a href="http://go.microsoft.com/fwlink/?LinkID=124807"
style="text-decoration: none;">
<img src="http://go.microsoft.com/fwlink/?LinkId=108181"
alt="Get Microsoft Silverlight" style="border-style: none"/>
</a>
</object>
<iframe style='visibility:hidden;height:0;width:0;border:0px'>
</iframe>
</div>
Then exit edit mode, and click the Silverlight web part to make sure it is working, and you’re done.
Step 3.2, the second way to host the Silverlight app in a web part is to create a completely new custom web part in Visual Studio. To do this, create a SharePoint Web Part project in VS, adding it to the current solution. If you’re prompted for a trust level decision, select Partial Trust (deploy to Bin). When the project is created, rename the WebPart1 folder to something more meaningful (eg, ‘SlWebPart’) – renaming the folder should give you the option to rename all occurrences of WebPart1, including in filenames such as SlWebPart.cs. Add a reference to System.Web.Extensions and System.Web.Silverlight, and a using statement for System.Web.UI.SilverlightControls.
In the SlWebPart.cs, implement the CreateChildControls override to instantiate a Silverlight control, and set its Source property to your Silverlight XAP. Note that using SPContext requires security permissions to access the SharePoint OM, which involves additional configuration. I found some information about this on Ryan McIntyre’s blog here, but I’ve kept my example simple by specifying the fully-qualified absolute path to my Silverlight XAP.
protected override void CreateChildControls()
{
base.CreateChildControls();
Silverlight sl = new Silverlight();
sl.ID = "SlCoffee";
//sl.Source = SPContext.Current.Site.Url+"/XAPs/SilverlightCoffee.xap";
sl.Source = "http://MyServer/sites/Contoso/XAPs/SilverlightCoffee.xap";
sl.Width = new Unit(400);
sl.Height = new Unit(120);
Controls.Add(sl);
}
Also override the OnLoad method to make sure we have a ScriptManager: in a SharePoint context, there should be a ScriptManager by default, so we only need to create one if there isn’t one already attached to the page.
protected override void OnLoad(EventArgs e)
{
base.OnLoad(e);
ScriptManager sm = ScriptManager.GetCurrent(this.Page);
if (sm == null)
{
sm = new ScriptManager();
Controls.AddAt(0, sm);
}
}
Double-check the properties for the web part project: specifically, make sure the Debug Start Action is set to start the browser with an appropriate URL. By default, this is http://localhost/, and this is often ok – but you should set it to the URL for whatever site you want the feature available on, which is not necessarily your top-level site. For example, http://MyServer/sites/Contoso/. Build the project. Then go to the Build menu and select Deploy Solution. This will deploy the web part to your SharePoint server, and activate it for the specified site.
Next, you need to make a host of changes to the web.config for your site. I found a detailed list of these changes on Phil Jirsa’s blog here, and a related video on a post by Patrick Tisseghem (thanks and RIP) here.
Finally, use your new web part on a page on your SharePoint server. Again, this requires you to install the System.Web.Silverlight.dll from the Silverlight SDK to the GAC (if you haven’t already done so). Then, go to any page on the site where this feature was deployed/activated, drop down the Site Actions listbox, and select Edit Page. When the page is in edit mode, click on any of the ‘Add a Web Part’ links. From the Add Web Parts dialog, find your SlWebPart and check the box. Then exit edit mode, and click the Silverlight web part to make sure it is working.
The screenshot below shows the results of both web part hosting techniques:
I had some ‘free’ time today waiting to give a demo at an MVP conference session – the session over-ran, and I found myself sitting in the hallway for an hour. So I got to thinking about Silverlight and Office. If we assume that Silverlight is more or less a subset of WPF, then it makes little sense to build any Silverlight into an Office add-in: you’ve already got the regular CLR loaded, so why would you want to load a second CLR with a subset of the capabilities? Plus, there’s the performance consideration – Silverlight apps are browser-hosted, so communicating between the SL app and the rest of your solution incurs a perf penalty.
Well, I thought of a couple of possible arguments…
If you can do everything you need to do with Silverlight instead of WPF, then you might actually save some working set – since WPF is bigger than SL. Think about it: if you’re not using WPF, then you don’t have the WPF assemblies loaded. Silverlight’s coreclr + agcore + npctl = approx 2.5Mb. On the other hand, WPF’s PresentationCore + PresentationFramework + WindowsBase + WindowsFormsIntegration = approx 3.7Mb. Not a terribly convincing argument, but still.
Then there are some features that Silverlight supports that WPF doesn’t. Deep Zoom, for example. It might be difficult to imagine a use for Deep Zoom in the context of an Office add-in – but not impossible. Excel, for example, has a range of data visualization capabilities (charting, conditional formatting, heat-maps), but wouldn’t it be nice to use some Deep Zoom-like feature for pivot table drill-downs for instance? There are other features in Silverlight (and not currently supported in WPF) that might be more obviously useful in Office (specifically, the DataGrid control and the VisualStateManager), but remember this was a hallway exercise – and I like Deep Zoom.
So, I built a little proof-of-concept managed Office add-in with a custom task pane that hosts a WebBrowser control, which in turn hosts a Silverlight app. I used the Deep Zoom Composer (available for free download here) to generate the image pyramid. The Deep Zoom Composer is a very nice tool that saves an awful lot of effort in building image pyramids. It also has a very handy ‘Export to Silverlight project’ feature, which creates an entire Silverlight app solution complete with the Silverlight project itself plus an ASP.NET web app (in case you want to host the app on a server). It really couldn’t be any easier – all I had to do was build the Silverlight app – the tool even gave me a ‘TestPage.html’ which hosts the Silverlight app, so that I can test it in a browser.
Deep Zoom is documented here and here, and the Expression Blend team (that owns Deep Zoom) has a blog here. Note that the current version of the Composer tool has a maximum document size of 4 billion pixels – that’s the ‘document’ that includes all the layers of all the images in your final image set. You can easily hit this limit with just 3 or 4 images (one per layer) from a compact digital camera, depending on how much you zoom between layers. I haven’t attached a solution to this post because the image set adds up to about 30Mb.
I created a simple custom UserControl (called WebHostControl) with a fully-docked Windows Forms WebBrowser control as its only child. In my add-in class, I initialized this WebBrowser to load the TestPage.html generated by the Deep Zoom Composer. In accordance with Office UI guidelines, I set up a Ribbon customization with a ToggleButton to toggle the visibility of the task pane.
private WebHostControl whc;
private string silverlightHtml;
private CustomTaskPane slTaskPane;
private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
silverlightHtml = Path.Combine(
AppDomain.CurrentDomain.BaseDirectory, "TestPage.html");
}
internal void ToggleSLTaskPane(bool isVisible)
{
if (isVisible)
{
if (whc == null)
{
whc = new WebHostControl();
}
if (slTaskPane == null)
{
slTaskPane = CustomTaskPanes.Add(whc, "Silverlight Pane");
slTaskPane.Width = 300;
}
whc.webBrowser.Navigate(silverlightHtml);
}
slTaskPane.Visible = isVisible;
}
This gets a Silverlight app running in an Office add-in. That’s a start, but it’s obviously more useful if the add-in code can communicate with the Silverlight code. Superficially, you’d think this would be easy – after all, they’re both managed components in the same process, and one is loaded (through a couple of layers) by the other. Of course, it’s not so simple – don’t forget that even though they’re both in the same process, they’re running on 2 different CLRs. For my simple PoC, I wanted the user to be able to enter a value in a cell, and have that value be used as the zoom factor in a call to zoom the image in my Silverlight app in the task pane.
Fortunately, Silverlight provides an ‘Html Bridge’ capability, documented here, which allows you to integrate script and managed code in a Silverlight app, including the ability to invoke managed code from javascript on the HTML page. So, I can communicate between the add-in code and the Silverlight code via the HTML script. To use this feature, I first changed the modifier on the Zoom method in the Page.cs in my Silverlight project, to make it public. This method is normally invoked via the mouse handlers (which is obviously a more sensible way to manipulate the zoom than using cell values – but remember this was a science experiment conducted in a hallway (without a chair, I might add)).
public void Zoom(double newzoom, Point p)
{
if (newzoom < 0.5)
newzoom = 0.5;
msi.ZoomAboutLogicalPoint(newzoom / zoom, p.X, p.Y);
zoom = newzoom;
}
Next, I created a managed class with a method that invokes the Zoom method. I exposed this method for scripting via the ScriptableMember attribute:
public class ScriptableManagedType
{
private Page thisPage;
public ScriptableManagedType(Page p)
{
thisPage = p;
}
[ScriptableMember]
public void ExposedSilverlightMethod(int cellValue)
{
thisPage.Zoom(cellValue, new Point(0.5, 0.5));
}
}
I instantiated this class at the end of the Page constructor, and made it available for scripting using the RegisterScriptableObject method. This method takes an arbitrary string identifier for the scriptable object – in my case I used the string “ScriptableSilverlightObject”:
ScriptableManagedType smt = new ScriptableManagedType(this);
HtmlPage.RegisterScriptableObject("ScriptableSilverlightObject", smt);
On the HTML page, make sure the Silverlight object has an explicit ID:
<object id="SLP" data="data:application/x-silverlight," type="application/x-silverlight-2" width="100%" height="100%">
Then I can add some javascript to the HTML page, to get hold of the Silverlight plug-in and invoke the exposed method on the registered scriptable object. Note that the arbitrary identifier is dynamically accessible in the script from the Content property of the Silverlight plug-in:
function InvokeSilverlightMethod(cellValue)
{
var slp = document.getElementById("SLP");
slp.Content.ScriptableSilverlightObject.ExposedSilverlightMethod(
cellValue);
}
Then, in my add-in project, in my custom UserControl code, I can write a method to invoke this script:
public void InvokeScriptMethod(int cellValue)
{
this.webBrowser.Document.InvokeScript(
"InvokeSilverlightMethod", new object[] { cellValue });
}
Finally, in my ThisAddIn class, I can sink the SheetChange event and implement it to invoke the method in my UserControl.
private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
silverlightHtml = Path.Combine(
AppDomain.CurrentDomain.BaseDirectory, "TestPage.html");
this.Application.SheetChange +=
new Excel.AppEvents_SheetChangeEventHandler(
Application_SheetChange);
}
void Application_SheetChange(object Sh, Excel.Range Target)
{
int cellValue = Convert.ToInt32(Target.Value2);
whc.InvokeScriptMethod(cellValue);
}
So, the sequence of operations is:
- The user changes a value in a cell in the worksheet.
- In the SheetChange event handler, I call the InvokeScriptMethod method on the UserControl that’s hosting the WebBrowser control.
- The InvokeScriptMethod invokes my InvokeSilverlightMethod javascript on the HTML page.
- The javascript method InvokeSilverlightMethod gets hold of the Silverlight object and invokes the scriptable method ExposedSilverlightMethod.
- The ExposedSilverlightMethod calls Zoom to zoom the image.
That seems somewhat painful, but right now it’s the only way (apart from some other possibly even more painful methods involving remoting or WCF or somesuch) – and it’s very nice that there is actually a way to do this kind of communication.
My conclusion here is that if you want sophisticated presentation features in your Office add-in, you’re almost certainly better off using WPF than Silverlight. Even the Deep Zoom functionality could be replicated with WPF, albeit with a lot of effort. Of course, for Deep Zoom or any WPF equivalent to be really useful as a data visualization feature in Office, you’d want to be able to compose the images dynamically – perhaps dependent on the data current in the active document/workbook. Also, the real advantage of Deep Zoom is its intelligent use of network bandwidth – which is not likely to be an issue in an Office add-in if your images are based on data from the current workbook. So, you could build a much simpler image zoom feature with WPF, without the smart download capability, using ScaleTransform. That’s a bit more work than I had time for, but this little exercise pleasantly filled up the hour’s wait in the hallway – and provided some food for thought.
I posted a while back about exposing an automation object from an add-in that fires events. That post was couched in terms of VSTO add-ins. A customer asked recently how the same technique could be used in a non-VSTO add-in. So, that’s the topic of this post.
First, here’s my automation object and the interfaces it implements. IAddInUtilities is a regular incoming interface that defines one method, CreateCustomTaskPane. The AddInUtilities class implements this method: because the AddInUtilities class is little more than an API into the add-in’s functionality, this implementation simply invokes a corresponding method on the add-in class itself to do the actual work of creating the task pane.
IAddInEvents is the outgoing event interface, which defines one method, SomeEvent. The AddInUtilities class does not implement this interface – like any other event interface, this will be implemented by the client. Instead, the AddInUtilities class declares its support for this interface by using the ComSourceInterfaces attribute. The class then exposes a method, FireEvent, to allow the add-in class to cause the event to fire.
// The custom interface that our COMAddIn.Object implements.
[ComVisible(true)]
[InterfaceType(ComInterfaceType.InterfaceIsDual)]
[Guid("C2743EFC-AD90-47a6-B1DC-12E52C6E2FE7")]
public interface IAddInUtilities
{
void CreateCustomTaskPane();
}
// The delegate type for our custom event.
[ComVisible(false)]
public delegate void SomeEventHandler(object sender, EventArgs e);
// Outgoing (source/event) interface.
[ComVisible(true)]
[InterfaceType(ComInterfaceType.InterfaceIsIDispatch)]
public interface IAddInEvents
{
[DispId(1)]
void SomeEvent(object sender, EventArgs e);
}
// The object we will expose through the COMAddIns collection.
[ComVisible(true)]
[ClassInterface(ClassInterfaceType.None)]
[Guid("F743C9A0-DDEF-49d5-AEAA-2E6798814C23")]
[ComSourceInterfaces(typeof(IAddInEvents))]
public class AddInUtilities :
StandardOleMarshalObject,
IAddInUtilities
{
private Connect addInObject;
// Event field: this is what a COM client will hook up
// their sink to.
public event SomeEventHandler SomeEvent;
internal AddInUtilities(SharedAddInEvents.Connect o)
{
addInObject = o;
}
// This is the interface method we expose to potential clients.
public void CreateCustomTaskPane()
{
addInObject.CreateCustomTaskPane();
}
internal void FireEvent(object sender, EventArgs e)
{
if (SomeEvent != null)
{
SomeEvent(sender, e);
}
}
}
Next, we define a custom UserControl, which will form the basis of our custom task pane. As this is a shared add-in, not a VSTO add-in, we can’t take advantage of VSTO’s wrappers for custom task panes. So, we have to make our custom UserControl visible to COM, effectively registering it as an ActiveX control. At runtime, Office will instantiate this control via normal COM registry lookup and the CoCreateInstance mechanism. Our control will have just one button, and when the user clicks this button, we sink the Click event and fire the custom event exposed from our IAddInUtilities object.
[ComVisible(true)]
[Guid("6017B040-87CE-4bfc-88E8-18009A8EC403")]
public partial class SimpleUserControl : UserControl
{
public Connect AddInObject;
public SimpleUserControl()
{
InitializeComponent();
}
private void btnHello_Click(object sender, EventArgs e)
{
AddInObject.FireClickEvent(sender, e);
}
}
Finally, the main add-in class – which, in a shared add-in project, is typically named Connect. This implements IDTExtensibility2 as normal, and also ICustomTaskPaneConsumer – because we want to advertise to Office that we want to create custom task panes. In the OnConnection method, we instantiate our AddInUtilities object and expose it out through the COMAddIns collection.
[Guid("CA2575D4-E119-4257-B180-2121720CF773")]
[ProgId("SharedAddInEvents.Connect")]
public class Connect :
Object, IDTExtensibility2,
ICustomTaskPaneConsumer
{
private ICTPFactory factory;
private AddInUtilities addInUtilities;
public void OnDisconnection(
ext_DisconnectMode disconnectMode, ref System.Array custom) {}
public void OnAddInsUpdate(ref System.Array custom) {}
public void OnStartupComplete(ref System.Array custom) {}
public void OnBeginShutdown(ref System.Array custom) {}
public void OnConnection(
object application, ext_ConnectMode connectMode,
object addInInst, ref System.Array custom)
{
addInUtilities = new AddInUtilities(this);
COMAddIn comAddIn = (COMAddIn)addInInst;
comAddIn.Object = addInUtilities;
}
public void CTPFactoryAvailable(ICTPFactory CTPFactoryInst)
{
factory = CTPFactoryInst;
}
Recall that the AddInUtilities class makes a call to the CreateCustomTaskPane method in the add-in class. Here, we create a custom task pane based on our custom UserControl. When Office has created the task pane, we can extract the custom UserControl and set its add-in object property. We do this so that the control has a way to call back into the add-in class. When the user clicks the button in our task pane, the event is propagated from the UserControl to the add-in object, and we propagate it out to any clients that are listening for the event on the exposed COMAddIn.Object object (that is, in our case, the AddInUtilities object).
internal void CreateCustomTaskPane()
{
try
{
CustomTaskPane taskPane =
factory.CreateCTP(
"SharedAddInEvents.SimpleUserControl",
"My Caption", Type.Missing);
SimpleUserControl sc =
(SimpleUserControl)taskPane.ContentControl;
sc.AddInObject = this;
taskPane.Visible = true;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
internal void FireClickEvent(object sender, EventArgs e)
{
addInUtilities.FireEvent(sender, e);
}
}
When the add-in is built, and the host Office app is running, we can invoke the AddInUtilities method to create a custom task pane from any suitable automation client. When the user clicks the button in the task pane, we fire an event, which the client can sink. For example, this is how you could connect from VBA:
Public WithEvents addInUtils As SharedAddInEvents.AddinUtilities
Private Sub CommandButton1_Click()
Dim addin As Office.COMAddIn
Set addin = Application.COMAddIns("SharedAddInEvents.Connect")
Set addInUtils = addin.Object
addInUtils.CreateCustomTaskPane
End Sub
Private Sub addInUtils_SomeEvent( _
ByVal sender As Variant, ByVal e As mscorlib.EventArgs)
MsgBox "Got SomeEvent"
End Sub
Note, when you build the project, you should have the Register for COM Interop option checked. This registers all ComVisible types, as well as building and registering a typelib for these types. Unlike VSTO projects, the ComVisible types are not associated in the registry with the typelib, so you need to add this extra registry key for the typelib under the CLSID for the AddInUtilities object:
[HKEY_CLASSES_ROOT\<Wow6432Node>\CLSID\{F743C9A0-DDEF-49D5-AEAA-2E6798814C23}\TypeLib]
@="{45C1778F-EFB9-4d93-81EF-EE14011C133B}"
Note also that I’ve carefully added explicit GUIDs for both interfaces, the AddInUtilities class, the custom UserControl, the main add-in Connect class, and the assembly itself (in the assemblyinfo.cs). If you don’t do this, VS will give you arbitrary GUIDs when it builds the project – and you won’t have any easy way to figure out what they are.
A customer (VSP) was using the COM Shim and identified a scenario where a bug in the shim code could cause the host application to remain in memory indefinitely – thanks, VSP for finding this and bringing it to our attention! Misha did some ninja debugging and pinpointed the problem.
The shim is described here and here.
The problem is that the ManagedAggregator.CreateAggregatedInstance method is taking in an IComAggregator object (the CConnectProxy object, which is the outer object in the aggregation), but is not releasing it. This is a problem because the object was passed from the native shim code to the managed aggregator code and wrapped as an RCW. If we don’t release the RCW, the CLR will potentially hold on to it (and the underlying COM object) until the corresponding AppDomain is unloaded. However, in the current implementation the AppDomain is unloaded when COM object’s reference counter reaches zero. This essentially constitutes a circular reference problem thus preventing the host from terminating cleanly. The fix is simple: in the finally block, add a call to Marshal.ReleaseComObject on the outer object:
public void CreateAggregatedInstance(
string assemblyName, string typeName, IComAggregator outerObject)
{
IntPtr pOuter = IntPtr.Zero;
IntPtr pInner = IntPtr.Zero;
try
{
pOuter = Marshal.GetIUnknownForObject(outerObject);
object innerObject =
AppDomain.CurrentDomain.CreateInstanceAndUnwrap(
assemblyName, typeName);
pInner = Marshal.CreateAggregatedObject(pOuter, innerObject);
outerObject.SetInnerPointer(pInner);
}
finally
{
if (pOuter != IntPtr.Zero)
{
Marshal.Release(pOuter);
}
if (pInner != IntPtr.Zero)
{
Marshal.Release(pInner);
}
// FIX: Bug discovered after release of 2.3.1.0.
// We call ReleaseComObject on the outer object (ConnectProxy)
// to make sure we delete the RCW, and prevent the CLR from
// holding onto it indefinitely (and keeping the host alive).
Marshal.ReleaseComObject(outerObject);
}
}
While we were in the code, Misha identified another potential problem. There are scenarios where it is possible that the CConnectProxy::FinalRelease may not get called – for example, if you have some other add-in or automation client that connects to this shimmed add-in via the COMAddIns collection. Right now, the FinalRelease is implemented to clean up and unload the appdomain. To make sure this always happens, you can simply move all that code to the end of the OnDisconnection method instead:
HRESULT __stdcall CConnectProxy::OnDisconnection(
ext_DisconnectMode RemoveMode, SAFEARRAY **custom)
{
HRESULT hr = S_OK;
hr = m_pConnect->OnDisconnection(RemoveMode, custom);
if (SUCCEEDED(hr))
{
m_pConnect->Release();
m_pConnect = NULL;
}
// FIX: Bug discovered after release of 2.3.1.0.
// Move the code that releases the aggregated innner object
// from the CConnectProxy::FinalRelease to
// CConnectProxy::OnDisconnection.
// This ensures that the code gets called even if this add-in gets
// handed out via the COMAddIns collection to other consumers.
if (m_pUnknownInner)
{
m_pUnknownInner->Release();
}
if (m_pCLRLoader)
{
m_pCLRLoader->Unload();
delete m_pCLRLoader;
m_pCLRLoader = NULL;
}
return hr;
}
// FinalRelease will be the last thing called in the shim/add-in, after
// OnBeginShutdown and OnDisconnection.
void CConnectProxy::FinalRelease()
{
}
Thanks again to VSP for spotting the original problem, and to Misha for identifying the cause and the fix.
Just like my earlier post on message filters, this is an advanced scenario – so be warned: you almost certainly don’t want to do this. However, there are probably some extreme edge-case scenarios where this technique might be useful. For example, Office apps are notoriously parsimonious with their events. I’m sure you can all cite situations where it would be really nice if Office fired an event, but it just doesn’t. In this situation, it might be useful to intercept raw windows messages instead (and it might not – you’d have to think very carefully if this possibility is worth the potential pain of the technique I’m about to describe). Another scenario where this might be applicable is if you want to show modeless managed dialogs and not have the host app process all the keyboard messages.
You can use the SetWindowsHookEx native API to insert your custom method into the hook chain for a given category of windows messages. Note that inserting a hook obviously slows down the whole system slightly. Note also that if you don’t implement your hook methods in a careful and socially-responsible manner, you risk causing far-reaching damage across the whole system.
Windows maintains an independent hook chain for each type of hook. Each chain is a list of pointers to user-defined callback functions called hook procedures. When a message occurs that is associated with a particular type of hook, Windows passes the message to each hook procedure in the chain, one after the other. For some hooks, the hook procedure can only read messages; others can modify messages or prevent them proceeding on through the chain. Here’s a concrete example: suppose you want to prevent your users from invoking the VB Editor in a given Office app? One way you could do this is by inserting a keyboard hook that intercepts the Alt-F11 keystroke message and does not forward the message on down the chain.
Another scenario where a message hook might be useful is where you use modeless dialogs or custom propertysheets in your add-in. In this scenario, the main window (the Office window) still owns the message pump, so, all the keystroke messages are taken by the host application and not dispatched to the modeless dialog box or propertysheet window. (This doesn’t happen with modal dialogs, because these get their own message pump.)
The main MSDN documentation on hooks is here. The basic set of operations is as follows:
· Write a method with the prescribed signature that will handle the messages you’re interested in, including (most often) forwarding the message down the chain with CallNextHookEx.
· Insert your method into the hook chain with SetWindowsHookEx.
· When you’re done, remove your method from the chain, with UnhookWindowsHookEx.
Let’s look at this in more detail with a practical example. First, declare an int to hold the return from SetWindowsHookEx – this will be the global handle to your hook, and we’ll need this later when we want to call UnhookWindowsHookEx. Also declare a delegate type for your callback, and a variable to hold an instance of this type.
private int hookHandle = 0;
private delegate int HookProc(int nCode, IntPtr wParam, IntPtr lParam);
private HookProc messageHookProcedure;
Next, import all the Windows API methods you’ll need, including: SetWindowsHookEx to install the hook; UnhookWindowsHookEx to uninstall the hook; and CallNextHookEx to pass the hook information to the next hook procedure in the chain. Note that SetWindowsHookEx takes a pointer to a callback as one of its parameters – in managed code, of course, this translates to a delegate:
[DllImport("user32.dll", CharSet = CharSet.Auto)]
private static extern int SetWindowsHookEx(
int idHook, HookProc lpfn, IntPtr hInstance, int threadId);
[DllImport("user32.dll", CharSet = CharSet.Auto)]
private static extern bool UnhookWindowsHookEx(int idHook);
[DllImport("user32.dll", CharSet = CharSet.Auto,
CallingConvention = CallingConvention.StdCall)]
private static extern int CallNextHookEx(
int idHook, int nCode, IntPtr wParam, IntPtr lParam);
Also, define the managed equivalent of the MSG struct, and it’s nested POINT type:
[StructLayout(LayoutKind.Sequential)]
private struct POINT
{
public int x;
public int y;
}
[StructLayout(LayoutKind.Sequential)]
private struct MSG
{
internal int hwnd;
internal uint message;
internal uint wParam;
internal int lParam;
internal uint time;
internal POINT pt;
}
To set the hook, instantiate your delegate and call SetWindowsHookEx. You have to specify the type of hook you want to install. In this example, I’m setting a hook for generic windows messages, using the constant WH_GETMESSAGE, defined in winuser.h. When you set up this type of message hook, you have to pass the current thread ID. If this parameter is zero, the hook procedure is associated with all existing threads running in the same desktop as the calling thread, which is not usually what you want. Note that using System.AppDomain.GetCurrentThreadId procudes a compiler warning: “GetCurrentThreadId has been deprecated because it does not provide a stable Id when managed threads are running on fibers. To get a stable Id for a managed thread, use the Thread.ManagedThreadId property.” However, in the context of Office add-ins, we don’t care about fibers, and anyway, passing the Thread.ManagedThreadId to SetWindowsHookEx always fails. (I don’t know why this is.)
internal void SetMessageHook()
{
const int WH_GETMESSAGE = 3;
messageHookProcedure = new HookProc(MessageHookProc);
hookHandle =
SetWindowsHookEx(WH_GETMESSAGE, messageHookProcedure,
(IntPtr)0, AppDomain.GetCurrentThreadId());
}
When you instantiate your delegate, you initialize it to your custom callback method. Bear in mind that the types of operation you can legally perform in this callback depend on the type of the message hook. In this case, with a generic message hook, we have a fair amount of leeway, but there are still some restrictions. First, if the first parameter (nCode) < 0, the hook procedure must pass the message to CallNextHookEx without further processing, and should return the value returned by CallNextHookEx. Secondly, we only want to process the message if it has not already been removed from the queue, so we need to compare the wParam against PM_NOREMOVE. For this type of message hook callback, the lParam that Windows passes us is a pointer to a MSG struct, and we can retrieve this by using Marshal.PtrToStructure. In the following example, I’m simply extracting the message values and putting them into a ListBox in a custom taskpane. Finally, make sure we don’t break the hook chain, by calling CallNextHookEx:
private int MessageHookProc(int nCode, IntPtr wParam, IntPtr lParam)
{
if (nCode < 0)
{
return CallNextHookEx(hookHandle, nCode, wParam, lParam);
}
else
{
const int PM_NOREMOVE = 0;
if (wParam.ToInt32() == PM_NOREMOVE)
{
MSG msg =
(MSG)Marshal.PtrToStructure(lParam, typeof(MSG));
listBox.Items.Add(String.Format("{0}: {1},{2}",
(WM)msg.message, msg.pt.x, msg.pt.y));
listBox.SelectedIndex = listBox.Items.Count - 1;
}
return CallNextHookEx(hookHandle, nCode, wParam, lParam);
}
}
Note that I’m using a custom enum (WM) based on a listing I found on pinvoke.net:
public enum WM : uint
{
NULL = 0x0000,
CREATE = 0x0001,
DESTROY = 0x0002,
MOVE = 0x0003,
SIZE = 0x0005,
//... lots more message IDs
SYSTIMER = 0x118
}
Don’t forget to make sure you remove the hook whenever you don’t need it any more, and before you exit. You could implement a suitable method, and make sure to call it (for instance) in the ThisAddIn_Shutdown method:
internal void ClearHook()
{
if (hookHandle != 0)
{
bool ret = UnhookWindowsHookEx(hookHandle);
if (ret == false)
{
Debug.WriteLine("UnhookWindowsHookEx Failed");
return;
}
hookHandle = 0;
}
}
That’s pretty much it. The implementation would be very similar for all types of hook, with minor differences. For example, to hook only mouse messages, you’d declare a managed equivalent of the MOUSEHOOKSTRUCT defined in winuser.h – note that this also uses the POINT struct type. You can use the same HookProc delegate type, the same hook handle, and the same ClearHook method to call UnhookWindowsHookEx. To set up the hook, specify WH_MOUSE in the call to SetWindowsHookEx:
[StructLayout(LayoutKind.Sequential)]
private class MouseHookStruct
{
internal POINT pt;
internal int hwnd;
internal int wHitTestCode;
internal int dwExtraInfo;
}
internal void SetMouseHook()
{
const int WH_MOUSE = 7;
mouseHookProcedure = new HookProc(MouseHookProc);
hookHandle =
SetWindowsHookEx(WH_MOUSE, mouseHookProcedure,
(IntPtr)0, AppDomain.GetCurrentThreadId());
}
The mouse hook callback is very similar to the generic message callback, except that the lParam that Windows passes us in this case is a pointer to a MOUSEHOOKSTRUCT:
private int MouseHookProc(int nCode, IntPtr wParam, IntPtr lParam)
{
if (nCode < 0)
{
return CallNextHookEx(hookHandle, nCode, wParam, lParam);
}
else
{
MouseHookStruct mouseHookStruct =
(MouseHookStruct)
Marshal.PtrToStructure(lParam, typeof(MouseHookStruct));
// Compose a string that shows the current mouse message Id
// and coordinates.
string mouseMessage = string.Format("{0}: x={1},y={2}",
((WM)wParam.ToInt32()),
mouseHookStruct.pt.x, mouseHookStruct.pt.y);
// Put the mouse message values into the listbox in the
// taskpane, and scroll to the bottom.
listBox.Items.Add(mouseMessage);
listBox.SelectedIndex = listBox.Items.Count - 1;
// Ensure that we don't break the hook chain.
return CallNextHookEx(hookHandle, nCode, wParam, lParam);
}
}
To hook only keyboard messages, you’d declare a managed equivalent of the KBDLLHOOKSTRUCT defined in winuser.h. Again, you can use the same HookProc delegate type, the same hook handle, and the same ClearHook method to call UnhookWindowsHookEx. To set up the hook, specify WH_KEYBOARD in the call to SetWindowsHookEx. In this case, you pass the module handle for the current process, which you can get with the GetModuleHandle API:
[DllImport("kernel32.dll", CharSet = CharSet.Auto)]
private static extern IntPtr GetModuleHandle(string lpModuleName);
[StructLayout(LayoutKind.Sequential)]
private struct KbDllHookStruct
{
internal int vkCode;
internal int scanCode;
internal int flags;
internal int time;
internal int dwExtraInfo;
}
internal void SetKeyboardHook()
{
const int WH_KEYBOARD_LL = 13;
keyboardHookProcedure = new HookProc(KeyboardHookProc);
using (Process curProcess = Process.GetCurrentProcess())
using (ProcessModule curModule = curProcess.MainModule)
{
hookHandle =
SetWindowsHookEx(WH_KEYBOARD_LL, keyboardHookProcedure,
GetModuleHandle(curModule.ModuleName), 0);
}
}
The keyboard hook callback is very similar to the generic/mouse message callbacks, except that the lParam that Windows passes us in this case is a pointer to a KBDLLHOOKSTRUCT. In the following implementation, in addition to logging the message information in my ListBox, I’m also trapping specifically Alt-F11 keystrokes – when I get this message, I simply return 1, which prevents the message from being propagated further down the chain:
private int KeyboardHookProc(int nCode, IntPtr wParam, IntPtr lParam)
{
if (nCode < 0)
{
return CallNextHookEx(hookHandle, nCode, wParam, lParam);
}
else
{
if ((wParam == (IntPtr)WM.KEYDOWN)
|| (wParam == (IntPtr)WM.SYSKEYDOWN))
{
KbDllHookStruct kbDllHookStruct =
(KbDllHookStruct)
Marshal.PtrToStructure(lParam, typeof(KbDllHookStruct));
// Put the message values into the listbox in the
// taskpane, and scroll to the bottom.
listBox.Items.Add(String.Format(
"{0}: {1}", (WM)wParam, (Keys)kbDllHookStruct.vkCode));
listBox.SelectedIndex = listBox.Items.Count - 1;
// Supress Alt-F11.
if (wParam == (IntPtr)WM.SYSKEYDOWN
&& kbDllHookStruct.vkCode == (int)Keys.F11)
{
return 1;
}
}
return CallNextHookEx(hookHandle, nCode, wParam, lParam);
}
}
That’s it. The sample solution attached to this post includes all 3 hook types described. Drive safe now.
There are at least 9 different ways to start or connect to an Office app programmatically in managed code, as summarized in this table:
|
PIA Interop |
Using the Office PIAs is the most RAD approach, with the greatest level of design-time and compile-time support. This has to be the preferred approach for almost all scenarios. A variation of this is the use of embedded PIA types using ComImport, as described here and here. |
|
Process.Start |
The simplest fire-and-forget approach, useful if you want to launch any executable but don’t need to interact with the app’s OM afterwards. |
|
Activator.CreateInstance |
Internally, this uses reflection to find and execute an appropriate constructor for the specified object. This can be slow, but useful if you intend to continue using reflection to work with the app – which you might do if you want to eliminate use of the PIAs altogether. |
|
Marshal.BindToMoniker |
Internally, this p/invokes to the Win32 BindToMoniker API. Useful if you have a narrow interest in the app’s exposed object that deals with a particular file type. In other words, if you want to work with a particular doc/workbook/presentation/etc using only a limited subset of the OM. |
|
Marshal.GetActiveObject |
Internally, p/invokes to the Win32 GetActiveObject. This will throw an exception if the object’s server is not already running, as it looks up the target ProgID in the ROT. One of the classic uses of this API is to determine whether or not the target app is already running. |
|
VisualBasic.CreateObject |
A compatibility API, which internally calls Activator.CreateInstance. |
|
VisualBasic.GetObject |
A compatibility API, which internally calls either Activator.CreateInstance or Marshal.BindToMoniker. In other words, it will connect to an already-running instance of the app if it finds one, otherwise it will create a new instance. |
|
ActivateMicrosoftApp |
This is a method exposed from the Excel Application object (and only the Excel Application object), used for activating or starting other Office apps (specifically, Access, FoxPro, Outlook, PowerPoint, Project, or Word). This approach does not give you access to the target app’s OM, so its effect is very similar to Process.Start. |
|
AccessibleObjectFromWindow |
Given the HWND for the target app (which you can find using FindWindowEx), this gets you access to the app’s OM. This is useful if your starting point is an HWND, or if you’re specifically focused on the app’s IAccessible implementation, and only minimally interested in the rest of the OM. |
Here’s some code that illustrates each of these approaches – this is implemented in an Excel add-in, where each of the “launch/connect” methods is invoked via a Ribbon button click handler. The comments should make it clear enough what's going on, and you can see the full source-code in the attached solution zip:
public partial class ThisAddIn
{
private string pptxFile;
private PowerPoint.Application ppt;
private PowerPoint.Presentation pptx;
private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
Uri codeBaseUri =
new Uri(Assembly.GetExecutingAssembly().CodeBase);
pptxFile = Path.Combine(
Path.GetDirectoryName(codeBaseUri.AbsolutePath), "Test.pptx");
}
private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
{
// Make sure to release any references to COM objects.
ppt = null;
pptx = null;
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
// Use the PIAs to interop with PowerPoint.
internal void InteropCreate()
{
ppt = null;
pptx = null;
ppt = new PowerPoint.Application();
ppt.Visible = Office.MsoTriState.msoTrue;
pptx = ppt.Presentations.Open(
pptxFile,
Office.MsoTriState.msoFalse,
Office.MsoTriState.msoFalse,
Office.MsoTriState.msoTrue);
}
// Use Process.Start to start a PowerPoint process. This does not
// give you access to the target app's OM.
internal void ProcessStart()
{
ProcessStartInfo si = new ProcessStartInfo();
si.FileName =
@"C:\Program Files (x86)\Microsoft Office\Office12\Powerpnt.exe";
si.Arguments = pptxFile;
Process.Start(si);
}
// Internally, Activator.CreateInstance uses reflection to find and
// execute an appropriate constructor for the specified object.
internal void CreateInstance()
{
Type t = Type.GetTypeFromProgID("PowerPoint.Application");
object o = Activator.CreateInstance(t);
// Note: we could cast the return from Activator.CreateInstance
// to the PIA type that we expect, if we wanted to use the PIAs.
t.InvokeMember(
"Visible", BindingFlags.Public | BindingFlags.SetProperty,
null, o, new object[] { true }, null);
object p = t.InvokeMember(
"Presentations",
BindingFlags.Public | BindingFlags.GetProperty,
null, o, null, null);
Type t2 = p.GetType();
t2.InvokeMember("Open",
BindingFlags.Public | BindingFlags.InvokeMethod,
null, p, new object[] { pptxFile }, null);
}
// Internally, Marshal.BindToMoniker p/invokes to Win32 BindToMoniker.
internal void BindToMoniker()
{
ppt = null;
pptx = null;
pptx = (PowerPoint.Presentation)Marshal.BindToMoniker(pptxFile);
pptx.Application.Visible = Office.MsoTriState.msoTrue;
}
// Internally, Marshal.GetActiveObject p/invokes to Win32
// GetActiveObject. This will throw an exception if the object's
// server is not already running. Win32 GetActiveObject looks up the
// target ProgID in the Running Object Table.
internal void GetActiveObject()
{
ppt = null;
pptx = null;
try
{
ppt = (PowerPoint.Application)
Marshal.GetActiveObject("PowerPoint.Application");
ppt.Visible = Office.MsoTriState.msoTrue;
pptx = ppt.Presentations.Open(
pptxFile,
Office.MsoTriState.msoFalse,
Office.MsoTriState.msoFalse,
Office.MsoTriState.msoTrue);
}
catch (COMException cex)
{
// If the target app is not already running, GetActiveObject
// will throw a COMException (0x800401E3): Operation
// unavailable (Exception from HRESULT: 0x800401E3
// (MK_E_UNAVAILABLE)).
Debug.WriteLine(cex.ToString());
}
}
// Internally, CreateObject calls Activator.CreateInstance - that is,
// it creates a new instance of the target application (if the app is
// single-use).
internal void CreateObject()
{
ppt = null;
pptx = null;
// GetObject requires the object class name, plus optionally a
// machine server name.
ppt = (PowerPoint.Application)
Microsoft.VisualBasic.Interaction.CreateObject(
"PowerPoint.Application", "");
ppt.Visible = Office.MsoTriState.msoTrue;
pptx = ppt.Presentations.Open(
pptxFile,
Office.MsoTriState.msoFalse,
Office.MsoTriState.msoFalse,
Office.MsoTriState.msoTrue);
}
// Internally, GetObject calls either Marshal.BindToMoniker or
// Activator.CreateInstance - that is, it either uses an existing
// instance of the target app, or creates a new instance.
internal void GetObject()
{
ppt = null;
pptx = null;
// GetObject requires either the executable filename or the object
// class name.
ppt = (PowerPoint.Application)
Microsoft.VisualBasic.Interaction.GetObject(
"", "PowerPoint.Application");
ppt.Visible = Office.MsoTriState.msoTrue;
pptx = ppt.Presentations.Open(
pptxFile,
Office.MsoTriState.msoFalse,
Office.MsoTriState.msoFalse,
Office.MsoTriState.msoTrue);
}
// ActivateMicrosoftApp activates a Microsoft application if it is
// running or starts a new instance of it if it is not. Restricted
// to these apps: Access, FoxPro, Outlook, PowerPoint, Project, Word.
// Note that this does not give you access to the target app's OM.
internal void ActivateMicrosoftApp()
{
this.Application.ActivateMicrosoftApp(
Excel.XlMSApplication.xlMicrosoftPowerPoint);
}
[DllImport("User32")]
public static extern int GetClassName(
int hWnd, StringBuilder lpClassName, int nMaxCount);
// Callback passed to EnumChildWindows to find any window with the
// registered classname "paneClassDC" - this is the class name of
// PowerPoint's accessible document window.
public bool EnumChildProc(int hwnd, ref int lParam)
{
StringBuilder windowClass = new StringBuilder(128);
GetClassName(hwnd, windowClass, 128);
if (windowClass.ToString() == "paneClassDC")
{
lParam = hwnd;
}
return true;
}
public delegate bool EnumChildCallback(int hwnd, ref int lParam);
[DllImport("User32")]
public static extern bool EnumChildWindows(
int hWndParent, EnumChildCallback lpEnumFunc, ref int lParam);
[DllImport("User32")]
public static extern int FindWindowEx(
int hwndParent, int hwndChildAfter, string lpszClass,
int missing);
// AccessibleObjectFromWindow gets the IDispatch pointer of an object
// that supports IAccessible, which allows us to get to the native OM.
[DllImport("Oleacc.dll")]
private static extern int AccessibleObjectFromWindow(
int hwnd, uint dwObjectID,
byte[] riid,
ref PowerPoint.DocumentWindow ptr);
// Get the window handle for a running instance of PowerPoint.
internal void GetAccessibleObject()
{
ppt = null;
pptx = null;
try
{
// Walk the children of the desktop to find PowerPoint’s main
// window.
int hwnd = FindWindowEx(0, 0, "PP12FrameClass", 0);
if (hwnd != 0)
{
// Walk the children of this window to see if any are
// IAccessible.
int hWndChild = 0;
EnumChildCallback cb =
new EnumChildCallback(EnumChildProc);
EnumChildWindows(hwnd, cb, ref hWndChild);
if (hWndChild != 0)
{
// OBJID_NATIVEOM gets us a pointer to the native
// object model.
uint OBJID_NATIVEOM = 0xFFFFFFF0;
Guid IID_IDispatch =
new Guid("{00020400-0000-0000-C000-000000000046}");
PowerPoint.DocumentWindow ptr = null;
int hr = AccessibleObjectFromWindow(
hWndChild, OBJID_NATIVEOM,
IID_IDispatch.ToByteArray(), ref ptr);
if (hr >= 0)
{
ppt = ptr.Application;
ppt.Visible = Office.MsoTriState.msoTrue;
pptx = ppt.Presentations.Open(
pptxFile,
Office.MsoTriState.msoFalse,
Office.MsoTriState.msoFalse,
Office.MsoTriState.msoTrue);
}
}
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.ToString());
}
}
}
Now, wasn’t that exciting? Note that in almost all circumstances, you’ll want to use PIA interop (or the embedded types variation) rather than any of the other approaches. Note that the basic functionality of the embedded types approach is greatly enhanced in .NET CLR 4.0 as described by Misha here and here.
