Using C# in Excel

[zhouxing]

Some sample code

Some examples of C# code http://book.greenwich2greenwich.com/Examples/

and relevant book http://book.greenwich2greenwich.com/CsInFrontOffice/

 

[zhouxing]

Unfortunately, C# is not "integrated" into office, something you no doubt found out already. I believe the next version of office allows this, but even so that will not help everyone, there are many spreadsheets out using copious VBA code there that cannot be retired, so they will lurch onward zombie-like through the years, eating the brains of those unfortunate enough to have to maintain them.

True and not true. Using C# class from VBA is in fact straight-forward. Almost all C# classes can be registered as COM easily (meaning you don't need to worry about the details). There is an example in my book.

As a matter of fact, we use VBA/Excel as front end to send jobs to a C# based pricing engine farm.

 

[zhouxing]

Looks like some interesting examples on how to utilize Excel object model from desktop applications, but from my experience most of the quants use Excel and .NET quite differently - Excel as a front end UI and .NET assemblies as a place to put calculation and other business logic, not the vice versa.

Certainly. Excel IS a front end. By putting the logic inside an assembly (instead of VBA), it is now possible to make multiple facades for your core logic library. In addition to Excel, it can also be shared by a regular C# application on either a local or a remote machine, C++ application and even Unix application (e.g. Perl) via webservice.

 

[alain]

As a matter of fact, we use VBA/Excel as front end to send jobs to a C# based pricing engine farm.

Hi zhouxing,

How big is the pricing engine farm you use?

We found that the standard facilities for multithread processing in C# are lacking and we have to dish that effort. Have you seen the same behavior? Also, the C# threadpool is shared among all the programs running on the CLR (and, from I have found, the OS ). We didn't like that part.

 

[zhouxing]

An engine controller typically controls 30+ "slave" machines, each of which run3 4 pricing engines (a 4-core machine). We have multiple farms for different teams. One nice thing is that it serves both Windows clients and Unix clients

Also, London Stock Exchange has a messaging based backbone transmission system is written exclusively in C#. I don't know its size now, but when I was there, it had hundreds of machines.

I am not saying C# is better than Java for distributed computing. They probably have similar capabilities, depending on the platform and, most importantly, architecture design. But it is possible to have reasonable scaled system written in C#.

To serve Microsoft products as client interface (e.g. Excel), C# is certainly much better than other technologies.

 

[zhouxing]

Hi zhouxing,

How big is the pricing engine farm you use?

We found that the standard facilities for multithread processing in C# are lacking and we have to dish that effort. Have you seen the same behavior? Also, the C# threadpool is shared among all the programs running on the CLR (and, from I have found, the OS ). We didn't like that part.

I haven't seen threading pool issues in the past (of course, you need to create your own thread, not use the default worker thread). But I do encounter problems in its garbage collection for large scaled messaging system (e.g. the one in London Stock Exchange). We actually got a Microsoft senior guy to do some investigation / customization for us at very lower level.

 

[alain]

We found that the standard facilities for Thread Pooling offered by C# are extremely lacking. Also there is cap for only 25 threads per CPU or core (I don't remember exactly). So you have you roll your own and still can get contention with the OS. Our experience with the CLR hasn't been that good so far

 

[zhouxing]

We found that the standard facilities for Thread Pooling offered by C# are extremely lacking. Also there is cap for only 25 threads per CPU or core (I don't remember exactly). So you have you roll your own and still can get contention with the OS. Our experience with the CLR hasn't been that good so far

25 concurrent threads is the default, though you can adjust by changing the machine.config.

Actually it comes back to a more basic question. Is it really a good strategy to run 25 CPU intensive threads SIMULTANEOUSLY per CPU/core? Probably not. Running 1 or 2 CPU intensive thread with bunch of others low-CPU threads should be OK.

 

[alain]

That discussion is for another thread. The limit is per CPU (not core which could be a problem since new multicore CPUs are popping everyday) and I can see plenty of reasons to run multiple threads (> 25) per multicore CPUs. It all depends on the application.

 

[zhouxing]

That discussion is for another thread. The limit is per CPU (not core which could be a problem since new multicore CPUs are popping everyday) and I can see plenty of reasons to run multiple threads (> 25) per multicore CPUs. It all depends on the application.

Of course, you almost for sure will have >25 threads. The point is whether all these threads are CPU heavy.

 

[Aditya Chitral]

I have a Windows Form application where I have created a form and a control DataGridView.

My C Sharp Code recognizes the DataGridView as a object since I inlcuded System.Windows.Forms

Now I am creating a .dll and want to pass the DataGridView in the .dll as a "DataGridView" Object.

I added the reference of windowsFormsIntegration in my project but it does not seem to work.

Any Ideas how can I do it?

 

[Aditya Chitral]

I have a Windows Form application where I have created a form and a control DataGridView.

My C Sharp Code recognizes the DataGridView as a object since I inlcuded System.Windows.Forms

Now I am creating a .dll and want to pass the DataGridView in the .dll as a "DataGridView" Object.

I added the reference of windowsFormsIntegration in my project but it does not seem to work.

Any Ideas how can I do it?

I figured out one way.

Make a normal windows form application and change the project output to class library and build the project again. A dll would be formed.

Any other way to do it?

 

[AlexesDad]

Using XLW to use C# in Excel:

http://www.youtube.com/watch?v=1yLodcb32sI

 

[zhouxing]

I figured out one way.

Make a normal windows form application and change the project output to class library and build the project again. A dll would be formed.

Any other way to do it?

You can only reference a library, not an .exe. Maybe this restriction is a bit strange, but unfortunately it is there until MS removes it.

On a positive side, this may somewhat enforce good architecture design. i.e. separating business logic from presentation. Business logic code should be in a library. Exe should reference the library and including some presentation code (being a Windows Form? ASP.NET? even Excel). In this way, you should unlike hit into a situation where you need to reference an .exe in the first instance.

 

[Excel Net]

A new version of Excel4Net - 2.0.7 is out today and from now on Excel4Net is absolutely free for everyone!

Download link: http://www.excel4net.com/Download.aspx
Quick overview page: http://www.excel4net.com/Overview.aspx
Feedback and support page: http://excel4net.uservoice.com

Develop your C# and Excel applications with pleasure!

 

[Aditya Chitral]

I built my dlls in Csharp and in the project properties checked the option for "Register for COM" Interop.

I also wrote wrote the register and unregister functions in my dll. When I compile the code and deploy it in the local machine, it works fine i.e. Using Excel Automation, it detects my dll and I can use the function.

However taking the excel on another machine does not find my dll.

Any insights?

 

[zhouxing]

I built my dlls in Csharp and in the project properties checked the option for "Register for COM" Interop.

I also wrote wrote the register and unregister functions in my dll. When I compile the code and deploy it in the local machine, it works fine i.e. Using Excel Automation, it detects my dll and I can use the function.

However taking the excel on another machine does not find my dll.

Any insights?

Assuming it is an automation add-in, do one of the following:

1] in Excel, Tools -> Add-Ins -> Automation -> Browser and pick up your DLL
2] run regasm on your new machine

 

[Aditya Chitral]

Assuming it is an automation add-in, do one of the following:

1] in Excel, Tools -> Add-Ins -> Automation -> Browser and pick up your DLL
2] run regasm on your new machine

So will I have to run regasm on every machine I want to use the dll? The users of my dll are persons who would not be courageous enough to run an exe from windows folder.

 

[zhouxing]

I usually write a very simple script (.bat?) for deployment which basically copies the DLL to a local directory and run regasm if this is not the first time deployment. Please note that as long as the DLL is registered once, you don't need to re-register even if there are massive change within the newer version of DLL.

Alternatively, if you don't like the script idea, instruct the user to use the (1) approach and tell them where the DLL is. Again, they only need to browse & pick up the DLL once. Afterward, it will automatically appear in the add-in list.

 

[AlexesDad]

..or you could just write a standard XLL in C# and give that to your user. No registering required. Just tell your user to double-click on the XLL and it will just open in Excel, end of story. Also your the function wizard will display descriptions of your functions and parameters, much more user-friendly.

Oh by the way, doesn't usage of RegAsm require Admin rights on the machine ??

This thread :
http://www.wilmott.com/messageview.cfm?catid=10&threadid=70576

goes into more detail, with a touch of entertainment thrown in for good measure :D

Here you can watch a demo ( there's no sound, might need to fullscreen it)
YouTube - Howto create a C# XLL addin using XLW Visual Studio



Hey zhouxing, how's my main man ??