Exposing .net methods as Excel functions?

Question!

I have a set of calculation methods sitting in a .Net DLL. I would like to make those methods available to Excel (2003+) users so they can use them in their spreadsheets.

For example, my .net method:

public double CalculateSomethingReallyComplex(double a, double b) {...}

I would like enable them to call this method just by typing a formula in a random cell:

=CalculateSomethingReallyComplex(A1, B1)

What would be the best way to accomplish this?



Answers

You should also have a look at ExcelDna (http://www.codeplex.com/exceldna). ExcelDna is an open-source project (also free for commercial use) that allows you to create native .xll add-ins using .Net. Both user-defined functions (UDFs) and macros can be created. Your add-in code can be in text-based script files containing VB, C# or F# code, or in managed .dlls.

Since the native Excel SDK interfaces are used, rather than COM-based automation, add-ins based on ExcelDna can be easily deployed and require no registration. ExcelDna supports Excel versions from Excel '97 to Excel 2007, and includes support for the Excel 2007 data types (large sheet and Unicode strings), as well as multi-threaded recalculation under Excel 2007.

By : Govert


There are two methods - you can used Visual Studio Tools for Office (VSTO):

http://blogs.msdn.com/pstubbs/archive/2004/12/31/344964.aspx

or you can use COM:

http://blogs.msdn.com/eric_carter/archive/2004/12/01/273127.aspx

I'm not sure if the VSTO method would work in older versions of Excel, but the COM method should work fine.

By : samjudson


Well I found the "Server Logs" mentioned in the error above.

You need to turn on tracing in the web.config file by adding the following tags:

    <system.diagnostics>
      <sources>
		<source name="System.ServiceModel.MessageLogging" switchValue="Warning, ActivityTracing" >
			<listeners>
				<add name="ServiceModelTraceListener"/>
			</listeners>
		</source>

		<source name="System.ServiceModel" switchValue="Verbose,ActivityTracing"		>
			<listeners>
				<add name="ServiceModelTraceListener"/>
			</listeners>
		</source>
		<source name="System.Runtime.Serialization" switchValue="Verbose,ActivityTracing">
			<listeners>
				<add name="ServiceModelTraceListener"/>
			</listeners>
		</source>
	</sources>
	<sharedListeners>
		<add initializeData="App_tracelog.svclog" 	
						type="System.Diagnostics.XmlWriterTraceListener, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
						name="ServiceModelTraceListener" traceOutputOptions="Timestamp"/>
	</sharedListeners>
</system.diagnostics>

This will create a file called app_tracelog.svclog in your website directory.

You then use the SvcTraceViewer.exe utility to view this file. The viewer does a good job of highlighting the errors (along with lots of other information about the communications).

Beware: The log file created with the above parameters grows very quickly. Only turn it on during debuging!

In this particular case, the problem ended up being the incorrect version of OraDirect.Net, our Oracle Data Provider. The version we were using did not support 3.5 SP1.



This video can help you solving your question :)
By: admin