galexyu

Hi,

I've created simple UDF following the post:

http://blogs.msdn.com/cumgranosalis/archive/2006/08/03/ServerClientUDFsCompat1.aspx

and I've got my UDF working on SharePoint. I can open workbook in a web browser, change parameters, recalculate and it works fine.

Then I¡¯ve decided to add COM part and implement IDTExtensibility2 interface, following the post:

http://blogs.msdn.com/cumgranosalis/archive/2007/03/07/ServerClientUDFsCompat3.aspx

and when I open same workbook in a web browser and do recalculate workbook I am getting #NAME instead of the result.

I am assuming that I have proper settings on Share Point Server because same workbook and same SampleUDF.dll are working without code for IDTExtensibility2.

Here is the code, when COM part is commented out it works. When I rebuild project including code that was commented out I have problem described above:

using System;

using System.Collections.Generic;

using System.Text;

using System.Runtime.InteropServices;

using Microsoft.Office.Excel.Server.Udf;

using Excel = Microsoft.Office.Interop.Excel;

using Microsoft.Win32;

using Extensibility;

namespace SampleUDF

{

[UdfClass]

[Guid(TestingUDFforF9.ClsId)]

[ProgId(TestingUDFforF9.ProgId)]

[ClassInterface(ClassInterfaceType.AutoDual)]

[ComVisible(true)]

public class TestingUDFforF9 : Object//, IDTExtensibility2

{

public TestingUDFforF9() { }

const string ClsId = "F0652C22-EE75-4651-B958-D1C9EC1C693E";

const string ProgId = "SampleUDF.TestingUDFforF9";

//private Excel.Application m_app = null;

//private object addInInstance;

#region IDTExtensibility2 Members

/*

public void OnAddInsUpdate(ref Array custom)

{

}

public void OnBeginShutdown(ref Array custom)

{

}

public void OnConnection(object Application, ext_ConnectMode ConnectMode, object AddInInst, ref Array custom)

{

m_app = (Excel.Application)Application;

addInInstance = AddInInst;

}

public void OnDisconnection(ext_DisconnectMode RemoveMode, ref Array custom)

{

}

public void OnStartupComplete(ref Array custom)

{

}

*/

#endregion

[UdfMethod(IsVolatile = true)]

public double MyDouble(double d)

{

// if (m_app != null)

// m_app.Volatile(Type.Missing);

return d * 9;

}

[UdfMethod(IsVolatile = true)]

public string CurrentTime()

{

//if (m_app != null)

// m_app.Volatile(Type.Missing);

return (DateTime.Now.ToLongTimeString());

}

[UdfMethod(IsVolatile = true)]

public string GetNthWord(string sentence, int index, string delimiter)

{

if (delimiter.Length > 1)

{

throw new InvalidOperationException();

}

string[] split = sentence.Split(delimiter[0]);

if (split.Length <= index)

{

throw new InvalidOperationException();

}

//if (m_app != null)

// m_app.Volatile(Type.Missing);

return split[index];

}

[ComRegisterFunction]

public static void RegistrationMethod(Type type)

{

if (typeof(TestingUDFforF9) != type)

return;

RegistryKey key = Registry.ClassesRoot.CreateSubKey

(@"CLSID\{" + ClsId + @"}\Programmable");

key.Close();

}

[ComUnregisterFunction]

public static void UnregistrationMethod(Type type)

{

if (typeof(TestingUDFforF9) != type)

return;

Registry.ClassesRoot.DeleteSubKey(@"CLSID\{" + ClsId + @"}\Programmable");

}

}

}

Thanks for your help.

Aleksandra



Re: SharePoint - Excel Services Can not get UDF to work with COM Addin

Shahar Prish - MSFT

Can you look at the event log after doing a full iisreset and a recalulate - can you check if there are any events that are related to Excel Services and seem to be related to UDFs




Re: SharePoint - Excel Services Can not get UDF to work with COM Addin

galexyu

Sorry for the delayed reply, we had to reinstall SharePoint Server.

There are absolutely no events after I do iisreset and recalculate workbook. Any other ideas

I was wondering if I had to have Excel 2007 installed on the SharePoint Server Maybe in OnConnection the following line is causing problems because it is trying to find an instance of Excel:

m_app = (Excel.Application)Application;

Thanks.

Aleksandra





Re: SharePoint - Excel Services Can not get UDF to work with COM Addin

Shahar Prish - MSFT

RE: The connection thing confusing Excel Services

That's probably not what's happening - Excel Services will never call that method and thus will never actually cause the code to run.

However, it does raise an interesting point - you may need to have the PIA's installed on the server since you are referencing that assembly from yours (the PIA and the Extensibility DLL as well).

If that's the case, I can come up with an alternative that will not rquire the PIA to be available.






Re: SharePoint - Excel Services Can not get UDF to work with COM Addin

galexyu

Thanks for your help.

As soon as I copied extensibility.dll and PIA on the SharePoint server it started working.

I have one more question.

I am trying to use existing dll (written in C) which already has UDFs.

I found an article ¡°Developing User-Defined Functions for MicrosoftR Office ExcelR 2007 and Excel Services¡± (http://officeblogs.net/excel/UDFs%20for%20Excel%20client%20and%20server.doc)

I was wondering if there is any way that my managed dll containing UDF functions can call existing unmanaged dll to get additional functionalities Or the only alternative I have is to create managed UDF wrapper for existing dll.

Thanks again.

Aleksandra





Re: SharePoint - Excel Services Can not get UDF to work with COM Addin

manasig

where did you copy the dlls to on sharepoint server



Re: SharePoint - Excel Services Can not get UDF to work with COM Addin

galexyu

In the same folder where is the dll with UDF, and folder is declared as Trusted File Locations.