Using C# in Excel
- Thread starter kean
- Start date
- Joined
- 5/2/06
- Messages
- 12,965
- Points
- 273
It has its pro/con. You don't want your users to install rootkit tools or any malicious things that can wreck havoc on the network. But it's besides the point of this thread.
I haven't found a robust (read fool-proof) way to deploy my C# apps, be it VSTO or win aps. There are always Excel, .NET version incompatibilities and a thousand of little things out there. Last I checked, they made great stride with ClickOne but all the press from MS made it sound so easy. In reality, it's a whole different story.
Anyone knows anything more recently regarding deployment of C# apps?
I haven't found a robust (read fool-proof) way to deploy my C# apps, be it VSTO or win aps. There are always Excel, .NET version incompatibilities and a thousand of little things out there. Last I checked, they made great stride with ClickOne but all the press from MS made it sound so easy. In reality, it's a whole different story.
Anyone knows anything more recently regarding deployment of C# apps?
doug reich
Some guy
- Joined
- 4/23/08
- Messages
- 684
- Points
- 28
Why doesn't MS understand that extensibility is the way of the future? Fielding retail software that is completely closed or practically so (when you can't actually distribute your software in a way people will understand, that's not "open"), is just giving people more reasons to get out!
First, regasm does not require admin right in a typical setup. If you still don't believe it, as I have suggested many times to you, try to compile a COM yourself (or take example from here), go to a computer which you don't have admin right and have a try yourself. It just take minutes to test, so please have a try before continue making misleading assertions / "hints"
---------------------------
In general, C# deployment mirrors Java's approach (where CLASSPATH is somewhat equivalent to GAC) which I think is a good direction. That is, you only need to do a x-copy. So regularly C# app should not be a big problem.
The complication comes with the "legacy" Windows registry (which I think is a very bad design from the beginning). This means that anythings that use registry as the "bulletin board" to make it publicly available must register itself. This include all the COM based servers.
Actually I don't know whether installing an application on Windows is necessarily more difficult than on Unix. e.g. installing application such as Oracle etc. Even for some small applications, e.g. printer, flash players etc, I always find more issues when installing them (mainly drivers) on either Mac or Linux than on Windows. (I have all the three OS in my home.)
I think it is a debatable question ... or maybe a perception issue
I think it is a debatable question ... or maybe a perception issue
First, regasm does not require admin right in a typical setup. If you still don't believe it, as I have suggested many times to you, try to compile a COM yourself (or take example from here), go to a computer which you don't have admin right and have a try yourself. It just take minutes to test, so please have a try before continue making misleading assertions / "hints"
I tried it.
Screenshot attached.
Attachments
AlexesDad - I guess you are using the built-in super restrictive guest account on your home PC? Try it on a typical PC in your working environment.
To be very frank, I am really tired of sticking on this point endlessly. Firstly, this will be an issue only if it cause practical problem (e.g. your traders/sales cannot load the DLL). Secondly, even if it indeed requires admin right during first-time install, is it really a show-stop?
For sure, no people will stop using Excel simply because installing Excel will for sure require admin right. (Otherwise people probably cannot use any application, even Windows/Unix/MacOS whatever) People likes Excel because of its functionality. Similarly, the reason I personally like the C# approach is because of its simplicity (~10 lines of code) and powerfulness. You have already seen this list below on Wilmott. I copied here for the benefit of other readers who haven't seen this because that Wilmott thread has become far too long.
I will love to have the following convenience at the cost of register once. Tell me if you really believe the cost of register over-weights these functional benefits ... even if register indeed is a one-time hassle.
-------------------------------------------------------------------------------------------------------------------------
1. input cell reference (as you haven't answered this question, i assume xlw cannot do --- though as I said, it's really a surprise to me)
why it is useful:
a] EWB asked this question initially, so presumably it is useful to him
b] When writing an array UDF, it is helpful to raise exception if the given input is too small to hold the output (because people may miss some results which is serious). At the same time, if the given cell range is too large, it is better to clean up them instead of showing #N/A. This is not only because of nice looking, but #N/A is a headache for VBA code to handle
c] In a complicated sheet, it is extremely helpful to indicate the exact location (i.e. row and col) of the original offending cell, if there is any error
2. auto-data type recognition
From user friendliness and convenience, I want to allow a same UDF to take a hard date (e.g. 2009/7/20), an offset against today (e.g. 91) or a symbolic date (e.g. IMM, 3M etc) in the same date input parameter. So for example, given a function MyValuationFunction(ValueDate, .....), the following should all be valid:
=MyValuationFunction(#2009/07/20#, ....)
=MyValuationFunction(1, ...) // value as of tomorrow
=MyValuationFunction("IMM", ...) // value as of next IMM date
...
3. The possibility of delivering a single component which contains UDF and other functionality.
For many other components, e.g. a RTD component, it usually will requires some utility functions (ideally exposed as UDFs) to pre- / post- process the data and maybe inquiry the internal status. So it will be very useful to include some UDFs in that component, instead of supplying two separately components.
4. I want to make sure the breakpoint I set can always be reached during a debug session, no matter how crap input data is.
The list can go very long. And you can tell whether they are practically useful.
-----------------------------------------------
Here is another example of practical headache (I met it again today!) which the native C# approach can handle much easier and more intuitively - date format (and by extension number format, unicode string etc). US and UK have different date formats, continental Europe has different number formats, let alone those string encoding used in Far East etc. Of course, we can enforce a standard and blame users for errors. But in reality, different machines have different configurations and different people may have strong natural habits (acquired from his daily life outside these pricing sheets). When there is error, whatever the reason is, it may cost money, wast time and bring frustration. How nice it will be if all these difference can be handled automatically for us, and very importantly, for free. This is exactly what a native C# approach can offer.
To be very frank, I am really tired of sticking on this point endlessly. Firstly, this will be an issue only if it cause practical problem (e.g. your traders/sales cannot load the DLL). Secondly, even if it indeed requires admin right during first-time install, is it really a show-stop?
For sure, no people will stop using Excel simply because installing Excel will for sure require admin right. (Otherwise people probably cannot use any application, even Windows/Unix/MacOS whatever) People likes Excel because of its functionality. Similarly, the reason I personally like the C# approach is because of its simplicity (~10 lines of code) and powerfulness. You have already seen this list below on Wilmott. I copied here for the benefit of other readers who haven't seen this because that Wilmott thread has become far too long.
I will love to have the following convenience at the cost of register once. Tell me if you really believe the cost of register over-weights these functional benefits ... even if register indeed is a one-time hassle.
-------------------------------------------------------------------------------------------------------------------------
1. input cell reference (as you haven't answered this question, i assume xlw cannot do --- though as I said, it's really a surprise to me)
why it is useful:
a] EWB asked this question initially, so presumably it is useful to him
b] When writing an array UDF, it is helpful to raise exception if the given input is too small to hold the output (because people may miss some results which is serious). At the same time, if the given cell range is too large, it is better to clean up them instead of showing #N/A. This is not only because of nice looking, but #N/A is a headache for VBA code to handle
c] In a complicated sheet, it is extremely helpful to indicate the exact location (i.e. row and col) of the original offending cell, if there is any error
2. auto-data type recognition
From user friendliness and convenience, I want to allow a same UDF to take a hard date (e.g. 2009/7/20), an offset against today (e.g. 91) or a symbolic date (e.g. IMM, 3M etc) in the same date input parameter. So for example, given a function MyValuationFunction(ValueDate, .....), the following should all be valid:
=MyValuationFunction(#2009/07/20#, ....)
=MyValuationFunction(1, ...) // value as of tomorrow
=MyValuationFunction("IMM", ...) // value as of next IMM date
...
3. The possibility of delivering a single component which contains UDF and other functionality.
For many other components, e.g. a RTD component, it usually will requires some utility functions (ideally exposed as UDFs) to pre- / post- process the data and maybe inquiry the internal status. So it will be very useful to include some UDFs in that component, instead of supplying two separately components.
4. I want to make sure the breakpoint I set can always be reached during a debug session, no matter how crap input data is.
The list can go very long. And you can tell whether they are practically useful.
-----------------------------------------------
Here is another example of practical headache (I met it again today!) which the native C# approach can handle much easier and more intuitively - date format (and by extension number format, unicode string etc). US and UK have different date formats, continental Europe has different number formats, let alone those string encoding used in Far East etc. Of course, we can enforce a standard and blame users for errors. But in reality, different machines have different configurations and different people may have strong natural habits (acquired from his daily life outside these pricing sheets). When there is error, whatever the reason is, it may cost money, wast time and bring frustration. How nice it will be if all these difference can be handled automatically for us, and very importantly, for free. This is exactly what a native C# approach can offer.
You should also have a look at ExcelDna - http://www.codeplex.com/exceldna and the newsgroup at http://groups.google.com/group/exceldna. ExcelDna allows managed assemblies to expose user-defined functions (UDFs) and macros to Excel through the native .xll interface. The project is open-source and freely allows commercial use.
Your user-defined functions can be written in C#, Visual Basic, F#, Java (using IKVM.NET), and can be compiled to a .dll or exposed directly from a text-based script file. Excel versions from Excel 97 to Excel 2007 are supported. No C++ compiler is needed to create your ExcelDna add-ins, and there is no dependency on the C++ runtime redistribution. Users only need the .Net 2.0 framework installed.
Some advantages of using Excel's native .xll interface rather than making automation add-ins include:
Your user-defined functions can be written in C#, Visual Basic, F#, Java (using IKVM.NET), and can be compiled to a .dll or exposed directly from a text-based script file. Excel versions from Excel 97 to Excel 2007 are supported. No C++ compiler is needed to create your ExcelDna add-ins, and there is no dependency on the C++ runtime redistribution. Users only need the .Net 2.0 framework installed.
Some advantages of using Excel's native .xll interface rather than making automation add-ins include:
- older versions of Excel are supported,
- function and argument help strings are easy to add using attributes on your methods
- the performance of UDF functions exposed through ExcelDna is excellent,
- deployment is easier since COM registration is not required and references to user-defined functions in worksheet formulae do not bind to the location of the add-in,
- no Excel.exe.config file is needed to load the correct .Net runtime, and clear messages are displayed if .Net 2.0 is not present or an older version is already loaded,
- multi-threaded recalculation in Excel 2007 is supported,
- ExcelDna is also designed to support the use of managed Excel Services UDF add-ins in the Excel client.
- Joined
- 5/2/06
- Messages
- 12,965
- Points
- 273
Hey Govert,
Are you involved in the ExcelDNA project? I used it once and actually liked it. It just works though I haven't look into more advanced features to see it has what I need.
I hope it improves a bit since the last time I looked at it (see post #3 in How to create UDF in Excel using C# and Visual Studio 2008)
Are you involved in the ExcelDNA project? I used it once and actually liked it. It just works though I haven't look into more advanced features to see it has what I need.
I hope it improves a bit since the last time I looked at it (see post #3 in How to create UDF in Excel using C# and Visual Studio 2008)
Hi Andy,
Yes, I develop ExcelDna.
I try to make a significant improvement every year - last year I changed the whole unmanaged part to be much cleaner and friendly when there are problems like .Net not being installed. And in December I added support for all the extra Excel 2007 stuff like long Unicode strings and multi-threaded recalc.
There is a lot of functionality I'd still like to add - advanced features like async function calls, object handles and advanced marshaling support. Over time I'd love to add these, but it will take some years, I guess. But most of these are more at the level of a framework for advanced add-ins. The basic integration of .Net to Excel is in place with ExcelDna, and you can implement anything a C++ add-in can do much more easily in C#, or with C# to do the interface to Excel for a C++ library that does the heavy lifting but no Excel API stuff.
I'm just not sure what Microsoft's direction is with .Net in the Excel client - at least they made a significant update to the .xll interface in Excel 2007. But they don't talk about the long-term strategy much. For Excel Services they support managed UDF add-ins similar to the .dlls ExcelDna exports (and not the COM-based automation add-ins). But I have not heard of anyone using Excel Services yet. Maybe with the upcoming version...
So have a look again and let us know what you think.
Yes, I develop ExcelDna.
I try to make a significant improvement every year - last year I changed the whole unmanaged part to be much cleaner and friendly when there are problems like .Net not being installed. And in December I added support for all the extra Excel 2007 stuff like long Unicode strings and multi-threaded recalc.
There is a lot of functionality I'd still like to add - advanced features like async function calls, object handles and advanced marshaling support. Over time I'd love to add these, but it will take some years, I guess. But most of these are more at the level of a framework for advanced add-ins. The basic integration of .Net to Excel is in place with ExcelDna, and you can implement anything a C++ add-in can do much more easily in C#, or with C# to do the interface to Excel for a C++ library that does the heavy lifting but no Excel API stuff.
I'm just not sure what Microsoft's direction is with .Net in the Excel client - at least they made a significant update to the .xll interface in Excel 2007. But they don't talk about the long-term strategy much. For Excel Services they support managed UDF add-ins similar to the .dlls ExcelDna exports (and not the COM-based automation add-ins). But I have not heard of anyone using Excel Services yet. Maybe with the upcoming version...
So have a look again and let us know what you think.
- Joined
- 5/2/06
- Messages
- 12,965
- Points
- 273
Thanks for the info, Govert.
I think Excel/VBA/C#/Addin/VSTO is just an important topic because most of us here, at one point or another will just have to work with and I'm glad there are options out there.
As for the Microsoft direction regarding to the Excel/NET platform, maybe you can give the guys over at MS some of your input
Dialogue with Microsoft VBA Excel team - QuantNetwork - Financial Engineering Forum
I think Excel/VBA/C#/Addin/VSTO is just an important topic because most of us here, at one point or another will just have to work with and I'm glad there are options out there.
As for the Microsoft direction regarding to the Excel/NET platform, maybe you can give the guys over at MS some of your input
Dialogue with Microsoft VBA Excel team - QuantNetwork - Financial Engineering Forum
Govert,
Good to hear your thoughts. I think in general .NET is the direction for providing value-added features to Excel (and other MS product). And I also agree with you that it is not crystal clear about MS' long-term strategy. I guess Excel Service fits its grand view of SOA and Web strategy, but clearly MS has a too big legacy base to ignore.
Just my two cents.
Good to hear your thoughts. I think in general .NET is the direction for providing value-added features to Excel (and other MS product). And I also agree with you that it is not crystal clear about MS' long-term strategy. I guess Excel Service fits its grand view of SOA and Web strategy, but clearly MS has a too big legacy base to ignore.
Just my two cents.
Actually I am also interested to know what kind of "support" or user interface you plan to provide in ExcelDna for these techniques? I know these are very useful in some cases and in fact I am using some of these features in the native add-in approach. So i am very interested in what additional convenience you plan to provide to developers in your mind.
Thanks.
I imagine the ultimate tool for Excel add-ins with .Net would have to combine:
- The extensive functionality that ManagedXll provides for making advanced add-ins in .Net - an overview and examples of this works is here: http://www.stochastix.de/solutions/excel/managedxll/latest/features.
- The low barrier to entry and great integration that VBA provides - press Alt+F11 in Excel, add a module and your are writing your UDF. With debugger, intellisense, immediate window etc. all right there!
- Some VSTO features like Ribbon UI customization and higher-level objects.
I think ManagedXll really got the style right. For example, adding an [AsyncWorksheetFunction] attribute makes your function asynchronous. Currently in ExcelDna you would have to implement that kind of functionality explicitly inside your own add-in. It can be done (like with a C++ or an automation add-in), but with the reflection and code generation capabilities of .Net, the add-in host could generate the glue needed on the fly.
There have been attempts at my second point too. A company called Trelliswerk had something called ".NET Scripting Excel Edition" which was an integrated IDE for Excel, implementing the Boo language. They nearly got it right - it had a command shell, debugging, intellisense etc. But they couldn't integrate the UDF story well, partly because they tried to do it with the Automation Add-In interface. So they ended up with the 'one-function' UDF style, where all the user-defined functions are exposed through a single function to Excel, taking the function name as a parameter - not great. And maybe Boo was a bit strange - it was before IronPython - and even Python is not so mainstream under people who might like to use .Net in Excel. It seemed promising, but I don't think it's around anymore.
So I think ManagedXll, the Trelliswerk stuff and VSTO provide enough proof-of-concept to show that an awesome .Net add-in framework can be developed on top of the current Excel, even without any extra help from inside Excel. Even if Microsoft dramatically changes direction for the next-next version of Excel (i.e. properly integrates VSTA into Excel), I think the requirement to have something like this for existing versions (and including Excel 2010) will exist for many years. I'm slowly, slowly working my way there with ExcelDna...
- The extensive functionality that ManagedXll provides for making advanced add-ins in .Net - an overview and examples of this works is here: http://www.stochastix.de/solutions/excel/managedxll/latest/features.
- The low barrier to entry and great integration that VBA provides - press Alt+F11 in Excel, add a module and your are writing your UDF. With debugger, intellisense, immediate window etc. all right there!
- Some VSTO features like Ribbon UI customization and higher-level objects.
I think ManagedXll really got the style right. For example, adding an [AsyncWorksheetFunction] attribute makes your function asynchronous. Currently in ExcelDna you would have to implement that kind of functionality explicitly inside your own add-in. It can be done (like with a C++ or an automation add-in), but with the reflection and code generation capabilities of .Net, the add-in host could generate the glue needed on the fly.
There have been attempts at my second point too. A company called Trelliswerk had something called ".NET Scripting Excel Edition" which was an integrated IDE for Excel, implementing the Boo language. They nearly got it right - it had a command shell, debugging, intellisense etc. But they couldn't integrate the UDF story well, partly because they tried to do it with the Automation Add-In interface. So they ended up with the 'one-function' UDF style, where all the user-defined functions are exposed through a single function to Excel, taking the function name as a parameter - not great. And maybe Boo was a bit strange - it was before IronPython - and even Python is not so mainstream under people who might like to use .Net in Excel. It seemed promising, but I don't think it's around anymore.
So I think ManagedXll, the Trelliswerk stuff and VSTO provide enough proof-of-concept to show that an awesome .Net add-in framework can be developed on top of the current Excel, even without any extra help from inside Excel. Even if Microsoft dramatically changes direction for the next-next version of Excel (i.e. properly integrates VSTA into Excel), I think the requirement to have something like this for existing versions (and including Excel 2010) will exist for many years. I'm slowly, slowly working my way there with ExcelDna...
zhouxing,
I'm pleasantly surprised by the interest you have shown in the XLL approach to writing C# UDFs, especially given your insistence on its inferiority compared to the COM Automation approach.
Good on you lad for finally being so open minded. I am really genuinely happy for you. Lets celebrate over a pint of beer when you're next in London.
Best Regards
A'sD
I'm pleasantly surprised by the interest you have shown in the XLL approach to writing C# UDFs, especially given your insistence on its inferiority compared to the COM Automation approach.
Good on you lad for finally being so open minded. I am really genuinely happy for you. Lets celebrate over a pint of beer when you're next in London.
Best Regards
A'sD
zhouxing,
I'm pleasantly surprised by the interest you have shown in the XLL approach to writing C# UDFs, especially given your insistence on its inferiority compared to the COM Automation approach.
Good on you lad for finally being so open minded. I am really genuinely happy for you. Lets celebrate over a pint of beer when you're next in London.
Best Regards
A'sD
I don't know where did you get the impression that I don't like XLL. Is this simply because I personally prefer the native ~10 lines of code over xlw? XLL != xlw.
The reason why I am not that favor xlw is because the benefit of xlw/XLL over add-in does not justify the loss of many useful functionality, especially given the context that I need a big external dependency. Is it fair to say all the advantages xlw has over the ~10 lines of code, at the cost of thousands lines, is simply XLL over automation, not xlw specific?
Technology is to help solve practical issues. If you ask a trader what's the difference or his preference b/w XLL and add-in? Very likely he will tell you "I don't care, as long as it works and solve my issue."
If you can claim that xlw does not loss many useful features (some of what are listed previous) and bring some useful new (xlw specific) features at reasonable cost, I will reconsider my view. Either way, I wasn't and am not against XLL.
I like your ideas. Some of these features (e.g. integrated editor/debugger etc) are great and very useful.
A bit sensitive my friend ... chill !:dance:
I didn't say you don't like XLL, I said that you considered it inferior to COM automation addins for Excel UDFs.
BTW: you claimed that by using a few attribute classes you were able to provide documentation in the Excel function wizard and as I recall you were then asked by someone, other than me, to supply an example .... did you ever get around to it .. it would be VERY useful. Of course you're under no obligation and if you don't, don't worry I'll still believe you
Again it is your imagination. I wrote XLL myself. What I view as inferior is a solution that has thousands of lines without obvious functional benefits (and may even lose some free features). This applies to whatever technology.
BTW: you claimed that by using a few attribute classes you were able to provide documentation in the Excel function wizard and as I recall you were then asked by someone, other than me, to supply an example .... did you ever get around to it .. it would be VERY useful. Of course you're under no obligation and if you don't, don't worry I'll still believe you
I said very clearly before that "it is a complete hack and I will feel shame to promote it". A copy of the code may survive from my continuous international relocations, but not in my actively maintained code base. If I someday find it in a dusty directory on one of my 8 PCs, I will email you a copy just to satisfy your curiosity.
