I talked about how to bind data to Excel worksheet in this post, by comparing three methods.
Now, you should have known which method you’ll use. However, you still experience poor performance and long UI blocking when trying to load data by calling remote WCF service. Not because of the data binding, but simply because there’s needed time and resources needed for calling the service itself.
Then, you think, why don’t I create a separate thread for calling WCF service and hopefully UI thread will not be blocked. It seems like a good solution. Turn out, it’s not as easy as it seems.
There are several discussions on the net about this multi-threading issue in VSTO application. There’s post from Alvin Bruney in this discussion (http://www.dotnetmonster.com/Uwe/Forum.aspx/vs-net-office/1123/Multithreading-vsto-2003 ), that said “Excel is not built to support multiple concurrent users. As such, there is no graceful means to handle concurrency except from exception triggers…”.
There’s also post from Geoff Darst – MSFT here (http://social.msdn.microsoft.com/Forums/en/vsto/thread/f38edd9b-34dd-4cc0-bad2-71d42b667477), commenting about how to do multi-threading in Excel. He said,
“It sounds like you might benefit from reviewing the COM threading documentation which can be found here: http://windowssdk.msdn.microsoft.com/en-us/library/ms693344.aspx. Hopefully, that will fill in any of the gaps in your reading of other threads.
It is possible to do multi-threading in VSTO solutions regardless of whether your threads interact with the Office OM. However, in the latter case, you need to implement IMessageFilter (the COM version, not System.Windows.Forms version) and you need to understand that all calls into the Office OM will be serialized so while one thread is calling into Office, the remaining threads would be blocked from doing so.
All of this is because the Office object model isn't thread safe. Since COM can't control the fact that a client might try to call a non-thread safe server with multiple threads, it does the next best thing which is to serialize the calls. On the other hand, if you are calling into a component that is thread-safe, then COM doesn't need to get in the way and concurrency is allowed.
As I said, it is always possible to use multiple threads in VSTO. The issue that you need to understand is how much time your threads will spend being blocked and whether that will invalidate the performance gain you are hoping for. If your tasks are truly self-contained (or call into Free Threaded components) then you won't blocked by COM if you run them on background threads.“
Turn out, it’s not that easy to create multi-threaded VSTO-based Excel application. And then do we give up using Excel and VSTO just because of that? Well, there’s a will, there’s a way.
I’ve found a solution for that issue. Even it’s not an elegant solution, probably not correct one, it works for me. From previous post, I’ve create an app that has this architecture:
Then, I change the architecture to something like this.
Hmm…there’s a block called Loader. What the heck is that?
Loader
Loader is our guy that calls WCF service to get data, and then bind the data to Excel worksheet. It can be console app or better be a Windows service. Here’s inside of Loader.
Loader actually just hosts a WCF service called DataLoaderService. DataLoaderService exposes a service called LoadToExcel, that can be called by Excel to initiate WCF call and data binding. Here’s the DataLoaderService.cs code:
1: using System;
2: using System.Linq;
3: using Excel = Microsoft.Office.Interop.Excel;
4: using System.Threading.Tasks;
5: using ILoaderService;
6: using System.Runtime.InteropServices;
7: using System.Diagnostics;
8: namespace Loader
9: { 10: public class DataLoaderService : IDataLoaderService
11: { 12: public void LoadToExcel(String filename)
13: { 14: var taskA = new Task(() =>
15: { 16: Stopwatch sw = new Stopwatch();
17: sw.Start();
18:
19: Excel.Application excelApp = null;
20: try
21: { 22: //Get the currently running Excel application by BindToMoniker the workbook file.
23: //This is useful to ancipate if there're more than one instances of Excel app
24: Excel.Workbook wb = (Excel.Workbook)Marshal.BindToMoniker(filename);
25: excelApp = wb.Application;
26: }
27: catch
28: { 29: //If BindToMoniker failed, then just get any instance of Excel app
30: excelApp = (Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application"); 31: }
32:
33: if (excelApp == null)
34: return;
35:
36: Excel.Workbook workBook = excelApp.ActiveWorkbook;
37: Excel.Worksheet activeSheet = (Excel.Worksheet)workBook.ActiveSheet;
38: activeSheet.Range["A1", "A1"].Value2 = "Loading....";
39:
40: //Call the WCF data service
41: Services.NorthwindEntities ctx = new Services.NorthwindEntities(new Uri("http://localhost:3270/Northwind.svc/")); 42: //Take a nap a moment
43: System.Threading.Thread.Sleep(500);
44:
45: Console.WriteLine("Elapsed: {0} second", sw.ElapsedMilliseconds / 1000); 46:
47: var count = ctx.Orders.Count();
48: Object[,] data = new Object[count, 11];
49:
50: var orders = from o in ctx.Orders select o;
51: int i = 0;
52: foreach (var o in orders)
53: { 54: data[i, 0] = o.OrderID;
55: data[i, 1] = o.OrderDate.HasValue ? o.OrderDate.Value.ToString() : "";
56: data[i, 2] = o.CustomerID;
57: data[i, 3] = o.ShipName;
58: data[i, 4] = o.ShippedDate.HasValue ? o.ShippedDate.Value.ToString() : "";
59: data[i, 5] = o.ShipAddress;
60: data[i, 6] = o.ShipCity;
61: data[i, 7] = o.ShipCountry;
62: data[i, 8] = o.ShipRegion;
63: data[i, 9] = o.ShipPostalCode;
64: data[i, 10] = o.ShipVia;
65: i++;
66: }
67:
68: Console.WriteLine("Elapsed: {0} second", sw.ElapsedMilliseconds / 1000); 69:
70: String[] columnNames = new String[] {"Order ID", "Order Date", "Customer", "Ship Name", "Ship Date", 71: "Ship Address", "Ship City", "Ship Country", "Ship Region", "Ship Postal", "Ship Via"};
72:
73: activeSheet.Range["A1"].get_Resize(1, 11).Value = columnNames;
74: activeSheet.Range["A2"].get_Resize(RowSize: count, ColumnSize: 11).Value = data;
75:
76: activeSheet.Range["A1", "K" + count].Columns.AutoFit();
77:
78: sw.Stop();
79: Console.WriteLine("Elapsed: {0} second", sw.ElapsedMilliseconds / 1000); 80:
81: Marshal.ReleaseComObject(excelApp);
82: });
83: // Start the task.
84: taskA.Start();
85: }
86: }
87: }
In above code, I use Task Parallel Library to simplify multi-threading code. It’s new in .NET 4.0.
You see that DataLoaderService implement an interface IDataLoaderService. It is just an interface that defines WCF contract.
1: using System;
2: using System.ServiceModel;
3:
4: namespace ILoaderService
5: { 6: [ServiceContract(Namespace = "http://www.dycode.com/DataLoaderService")]
7: public interface IDataLoaderService
8: { 9: [OperationContract]
10: void LoadToExcel(String filename);
11: }
12: }
Program.cs of Loader.exe is like this:
1: using System;
2: using System.ServiceModel;
3:
4: namespace Loader
5: { 6: class Program
7: { 8: static void Main(string[] args)
9: { 10: using (ServiceHost host = new ServiceHost(typeof(DataLoaderService)))
11: { 12: NetTcpBinding binding = new NetTcpBinding();
13: host.AddServiceEndpoint(typeof(ILoaderService.IDataLoaderService),
14: binding,
15: "net.tcp://localhost:9000/DataLoaderService");
16:
17: host.Open();
18: Console.WriteLine("Press any key to terminate service"); 19: Console.ReadLine();
20: }
21: }
22: }
23: }
Program.cs is just a host for WCF service that listen at port 9000.
Excel
On Excel side, I create a method in ThisWorkbook.cs. That method will call LoadToExcel() method of DataLoaderService (that is hosted by Loader.exe), by supplying parameter of currently opened Workbook’s name. The code is:
1: public void LoadDataFromLoader()
2: { 3: EndpointAddress addr = new EndpointAddress("net.tcp://localhost:9000/DataLoaderService"); 4: NetTcpBinding binding = new NetTcpBinding();
5: IChannelFactory<IDataLoaderService> channelFactory = new ChannelFactory<IDataLoaderService>(binding);
6: IDataLoaderService proxy = channelFactory.CreateChannel(addr);
7:
8: String fileName = this.FullName; //get workbook full path
9: proxy.LoadToExcel(fileName);
10: }
Method LoadDataFromLoader() will be called by a button in my custom Ribbon.
To test this scenario:
1. Run the Loader.exe, so that the service is up
2. Run Excel application. Click the button in Ribbon (that will call LoadDataFromLoader() method) and walah!, the UI is not blocking. After waiting 2 seconds, the data will be loaded to Excel worksheet. It works.
This video proves my solution.
A Simple, Working (Not Elegant) Solution for Multi-Threading Issue in VSTO App from Andri on Vimeo.
Come on…a little applause may be?
Just download the code and play around with it. Don’t expect it’s perfect, I code it during my free time, and just for fun. Here's the file: