Summary
The scenario
-
You have created a VSTO project, you have so a document (let's say ".xlsx") associated with it
-
For any reason, you don't want the VSTO project to be the startup project
-
You want the startup project to be a Console application
-
You want to open the VSTO document from command line
-
You want, via the Console Application, to manipulate any VSTO object (typically Globals.ThisWorkbook, ...)
Launching a VSTO document in command line
Let's first see how we can launch the document :
using System;
using System.Diagnostics;
using System.Globalization;
using System.IO;
using System.Reflection;
using System.Threading;
using Microsoft.Office.Tools.Excel.Extensions;
using IExcel = Microsoft.Office.Interop.Excel;
using TExcel = Microsoft.Office.Tools.Excel;
namespace ClientApplication
{
class Program
{
static int Main(string[] args)
{
Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
IExcel.Application application = new IExcel.Application();
application.Visible = true;
string basePath = @"D:\Projects\PDA - Blog\Blog Research\VSTO - WCF\ServerApplication";
string fileName = "ServerApplication.xlsx";
IExcel.Workbook book = application.Workbooks.Open(Path.Combine(basePath, fileName),
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);
TExcel.Workbook workbook = WorkbookExtensions.GetVstoObject(book);
Debug.Assert(workbook != null, "The conversion to VSTO did not work");
return 0;
}
}
}
Note also that when you work with assertion, you must update the config file as follows :
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<system.diagnostics>
<assert assertuienabled="true"
logfilename="D:\\Projects\\PDA - Blog\\Blog Research\\VSTO - WCF\assert.log" />
</system.diagnostics>
</configuration>
As a consequence, if the assertion fails, you will receive a prompt in Visual Studio giving the exception, and furthermore, you will get the error logged in the text file you have specified.
Why does this fail ?
Yes the conversion fails. Meaning that it is impossible to convert an interop object to a VSTO object when opening from command line. The main reason is that the VSTO objects are attached to a host (ie the excel application), host that is created at design time and that cannot be created at runtime.
In other words, we just cannot use the VSTO objects outside of the excel application. Does that mean that we cannot fulfill the scenario?
There are some possibilities to workaround this, and I will present one of them below. If we cannot use the VSTO objects outside of the excel application, we can (of course) manipulate them when we are inside of the VSTO project. So one solution would be to let the VSTO project working.
How can we achieve that ? We can imagine a client / server application, using .NET remoting. The VSTO project (ie the server) can launch a remoting server and the console application (ie the client) can connect to it to send the commands to be executed on the VSTO objects. Let's see that working.
.NET remoting inside of an excel application
Let's create a communication object
We'll want to exchange some information from the client to the server. To do that, we'll need to work with a communication structure that can be sent over the pipe. Not a lot of choice for that : we need a MarshalByRefObject. This structure will be used by both the client application and the server application, and this object will be responsible of manipulating the real VSTO objects. We'll so add it to the server application.
This mean that your client application will need to reference the server application. As we cannot reference the project directly, we'll reference the DLL that is generated.
-
Add a reference (in the client and server applications) to "System.Runtime.Remoting"
-
Add a reference (in the client application) to the server application
-
Be careful ! Be sure, when testing your client app, to build your server app before !
We are now ready to create our communication object.
public class RemoteApplication : MarshalByRefObject
{
private static RemoteApplication application = new RemoteApplication();
internal static RemoteApplication Instance
{
get { return application; }
}
private RemoteApplication() {}
public static RemoteApplication GetRemoteInstance()
{
return null;
}
public void DisplayInCell(int rowIndex, int columnIndex, string value)
{
}
}
We'll complete the methods later on, but the principle is that the server will be able to instanciate this object and to "publish" it over the pipe. The client will only have access to the public API and so to that static method that will allow him retreiving the remote instance.
Let's create a remoting server
We'll want to start our remoting server as soon as our excel application starts. To do that, you can simply add some code to the "ThisWorkbook_Startup", which is avaliable in the ThisWorkbook class. In the "ThisWorkbook_Shutdown" method, we'll simply stop it.
public partial class ThisWorkbook
{
private static TcpChannel channel;
private void ThisWorkbook_Startup(object sender, System.EventArgs e)
{
channel = new TcpChannel(9999);
ChannelServices.RegisterChannel(channel, false);
RemoteApplication application = RemoteApplication.Instance;
RemotingServices.Marshal(application, "RemoteApplication");
}
private void ThisWorkbook_Shutdown(object sender, System.EventArgs e)
{
ChannelServices.UnregisterChannel(channel);
}
}
Let's use our remoting server
First we'll update our communication object to have it fully operational.
public static RemoteApplication GetRemoteInstance()
{
object obj = Activator.GetObject(typeof(RemoteApplication),
"tcp://localhost:9999/RemoteApplication");
return obj as RemoteApplication;
}
public void DisplayInCell(int rowIndex, int columnIndex, string value)
{
Globals.Sheet1.Cells[rowIndex, columnIndex] = value;
}
Then we can simply use it in our main program
static int Main(string[] args)
{
Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
IExcel.Application application = new IExcel.Application();
application.Visible = true;
string basePath = @"D:\Projects\PDA - Blog\Blog Research\VSTO - WCF\ServerApplication\bin\debug";
string fileName = "ServerApplication.xlsx";
IExcel.Workbook book = application.Workbooks.Open(Path.Combine(basePath, fileName),
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);
RemoteApplication remoteApplication = RemoteApplication.GetRemoteInstance();
remoteApplication.DisplayInCell(1, 1, "Hello World !");
return 0;
}
Testing the solution
We are now ready ! We can build everything and run the client application.
This is working perfectly ! Of course we could have done that without remoting, as we are only setting a simple text. However just imagine we want to work with VSTO controls, this would be impossible to do using the COM object.
So the .NET remoting works nice to fulfil the scenario. However could we do it differently ? Using WCF for example ?
Porting the .NET remoting solution to WCF
Creating the service
Very simple task to start, let's create the service contract as follows :
[ServiceContract]
public interface IWorkbookService
{
[OperationContract]
void DisplayInCell(int rowIndex, int columnIndex, string value);
}
To get that code compiling, you will need to add a reference to System.ServiceModel to your server. You can also add it to your client app as you will need it to consume the service.
Then, let's define our service : we'll keep the same implementation as we have done using our remoting server.
internal class WorkbookService : IWorkbookService
{
public void DisplayInCell(int rowIndex, int columnIndex, string value)
{
Globals.Sheet1.Cells[rowIndex, columnIndex] = value;
}
}
Defining the server configuration file
Now our service is really to be exposed. We simply need to add the configuration file to our server application. For that, I will use the WCF Service Configuration Editor available under the Tools menu.
-
Launch the editor
-
Click on File / New Config
-
On the right tab, click "Create New Service"
-
To set the service type, browse to the DLL that contain your service (under your bin/debug folder), and then select your service. In this example, it is named "ServerApplication.WorkbookService"
-
Click on Next
-
The tool shall now let you select the contract (ie the interface) used by this service
-
Click on Next
-
For the communication mode, choose TCP (we can add other mode later on)
-
Click on Next
-
Specify the address on which you want to publish your service. This could be net.tcp://localhost:9999/WorkbookService
-
Click on Next, and Finish
Our web.config file is now ready for the consumption of our service. However, we'll need our client to discover the service so Visual Studio can create the proxy class by himself. We need so to allow this. (Note that this is not allowed by default, as you could also decide to publish / give to your client the proxy class you would have generated with another tool).
-
On the left tab, select Advanced / Service Behaviors
-
On the right tab, click on New Service Behavior Configuration
-
Name it (for instance ServiceBehavior)
-
Click on Add and select serviceMetadata, and click on Add
-
Double-click on the serviceMetadata item (or click on it, in the left tab)
-
Set True to the value HttpGetEnabled
-
Set http://localhost:9998 to the value HttpGetUrl (note that we specify here a different address than the one used for TCP)
Now we need to specify that our service is using this new configuration
-
On the left tab, click on our service (ServerApplication.WorkbookService in my case)
-
On the right tab, select our configuration for the BehaviorConfiguration node.
Your configuration (as seen in the editor) should look like this: (click on the image to enlarge)
You can now save the file and check that your configuration is OK.
-
Click on File / Save
-
Navigate thru you server application root path and click OK
-
Go back to Visual Studio, to your server project, click on "Show All Files"
-
Right-Click on the App.config file and click "Include"
Your app.config file should look like this :
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<system.serviceModel>
<behaviors>
<serviceBehaviors>
<behavior name="ServiceBehavior">
<serviceMetadata httpGetEnabled="true" httpGetUrl=http://localhost:9998 />
</behavior>
</serviceBehaviors>
</behaviors>
<services>
<service behaviorConfiguration="ServiceBehavior"
name="ServerApplication.WorkbookService">
<endpoint
address="net.tcp://localhost:9999/WorkbookService"
binding="netTcpBinding"
bindingConfiguration=""
contract="ServerApplication.IWorkbookService" />
</service>
</services>
</system.serviceModel>
</configuration>
Start the service when running the server application
We'll do the same as for our .NET remoting service : as soon as the excel application is started, we'll start our service. To do that, we'll modify the ThisWorkbook_Startup method as follows:
public partial class ThisWorkbook
{
ServiceHost host;
private void ThisWorkbook_Startup(object sender, System.EventArgs e)
{
host = new ServiceHost(typeof(WorkbookService));
try
{
host.Open();
}
catch ( CommunicationException )
{
host.Abort();
throw;
}
}
private void ThisWorkbook_Shutdown(object sender, System.EventArgs e)
{
host.Close();
}
}
Adding a service reference to your client application
There are many ways to a service reference to the client application. The simplest is probably to let Visual Studio do it for us.
-
Run your server application
-
Right-clic on the client application and choose Add Service Reference
-
Enter http://localhost:9998 as the Address and click Go
-
The service WorkbookService should be displayed
-
Select it and add a namespace, for instance MyServices and click OK
Notes:
-
You should no longer have a reference from the client application to the server application. If it's still the case, just delete it !
-
As you have now added the service reference, you could update the server configuration file, so the HttpGet is no longer possible
-
When importing the service reference, Visual Studio will automatically update your config file to add all the information. It adds much more information than needed, we'll simplfy that !
Defining the client configuration file
Almost ready to use our service. Let's now configure our project to use it. Once again, we could use the WCF Service Configuration Editor tool to update it. However, as Visual Studio as already updated our config file, we'll just delete all what we do not need to get a minimal config file :
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<system.diagnostics>
<assert assertuienabled="true"
logfilename="D:\\Projects\\PDA - Blog\\Blog Research\\VSTO - WCF\assert.log" />
</system.diagnostics>
<system.serviceModel>
<client>
<endpoint
address="net.tcp://localhost:9999/WorkbookService"
binding="netTcpBinding"
contract="ClientApplication.MyServices.IWorkbookService"
name="NetTcpBinding_IWorkbookService" />
</client>
</system.serviceModel>
</configuration>
Note however that if you right-clic on your service reference to update it, then Visual Studio will also update your configuration file to reset it to the default content.
Testing the solution
Let's add a using statement to our program:
using ClientApplication.MyServices;
and then call the service in our main method:
WorkbookServiceClient service = new WorkbookServiceClient();
service.DisplayInCell(1, 1, "Hello world, from WCF !");
We can now test the solution :
Conclusion
We have succeeded to fulfil the scenario, either using .NET remoting, or by doing a WCF service. Using WCF provides us with a more elegant and extensible solution (we could migrate to WS or something else very easily) and everything is by default configurable : less code to do more !
Of course you should definitely ask yourself if you need such a solution or if manipulating the excel application thru the interop API is possible.