Microsoft KB Archive/928783

= You receive different results on an Office SharePoint Server 2007 server than on a client computer for an Office Excel 2007 workbook when you use a managed-code user-defined function in the workbook =

Article ID: 928783

Article Last Modified on 1/11/2007

-

APPLIES TO


 * Microsoft Office Excel 2007
 * Microsoft Office SharePoint Server 2007

-



SYMPTOMS
In a Microsoft Office Excel 2007 workbook, you use a managed-code user-defined function that is also a Microsoft Excel COM+ add-in. The workbook is published to a Microsoft Office SharePoint Server 2007 Web site. However, you receive different results on the server than on a client computer for the same workbook.

For example, the Excel 2007 client automatically converts a data type from a numeric data type to a string data type. However, this behavior does not occur on the server.



WORKAROUND
To work around this problem, test your managed-code user-defined function in a workbook that is on a client computer instead of a SharePoint Server 2007 server.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the &quot;Applies to&quot; section.



MORE INFORMATION
For more information about how to develop a managed-code user-defined function, visit the following Microsoft Developer Network (MSDN) Web sites:

http://msdn2.microsoft.com/en-us/library/ms572961.aspx

Create a managed-code DLL to use as an Excel COM+ add-in and as an Excel Services user-defined function
 On a computer that has SharePoint Server 2007 installed, start Microsoft Visual Studio 2005. On the File menu, click New, and then click Project. Click Visual C#, click Class Library, type DataTypes in the Name box, and then click OK. In Solution Explorer, right-click Class1.cs, and then click Rename. Type RoundTripping.cs, and then press ENTER. In the Microsoft Visual Studio dialog box, click Yes.</li>  Replace the code that is in the RoundTripping.cs file with the following code example. using System; using System.Collections.Generic; using System.Text; using Microsoft.Office.Excel.Server.Udf; using System.Runtime.InteropServices;  // This code is used for client compatibility. using Microsoft.Win32; // This code is used for client compatibility.

namespace DataTypes {   [Guid(RoundTripping.ClsId)] // This code is used for client compatibility. [ProgId(RoundTripping.ProgId)] // This code is used for client compatibility. [ClassInterface(ClassInterfaceType.AutoDual)]  // This code is used for client compatibility. [ComVisible(true)] // This code is used for client compatibility. [UdfClass] public class RoundTripping {       #region CLIENT COMPATIBILITY VARIABLES

const string ClsId = &quot;D08A3087-F858-4103-8C52-66B2D2890011&quot;;   // This code is used for client compatibility. const string ProgId = &quot;DataTypes.RoundTripping&quot;;   // This code is used for client compatibility.

#endregion #region CLIENT COMPATIBILITY

[ComRegisterFunction]  // This code is used for client compatibility. public static void RegistrationMethod(Type type)   // This code is used for client compatibility. {           // Only add data to the registration // if this class is the one that is being registered. if (typeof(RoundTripping) != type) {               return; }           // Add &quot;Programmable&quot; under our key. RegistryKey key = Registry.ClassesRoot.CreateSubKey(&quot;CLSID\\{&quot; + ClsId + &quot;}\\Programmable&quot;); key.Close; }

[ComUnregisterFunction] // This code is used for client compatibility. public static void UnregisterationMethod(Type type) // This code is used for client compatibility. {           // Only add data to the registration // if this class is the one that is being registered. if (typeof(RoundTripping) != type) {               return; }           // Add &quot;Programmable&quot; under our key. Registry.ClassesRoot.DeleteSubKey(&quot;CLSID\\{&quot; + ClsId + &quot;}\\Programmable&quot;); }

#endregion

[UdfMethod(IsVolatile = true)] public string ReturnStringAndString(string ArgIn) {           return (ArgIn + ArgIn); }

[UdfMethod(IsVolatile = true)] public bool ReturnNotBool(bool ArgIn) {           return !ArgIn; }

[UdfMethod(IsVolatile = true)] public double ReturnPIxDbl(double ArgIn) {           return (Math.PI * ArgIn); }

[UdfMethod(IsVolatile = true)] public Single ReturnPIxSgl(Single ArgIn) {           return (Single)((Single)Math.PI * ArgIn); }

[UdfMethod(IsVolatile = true)] public Int32 Return2xInt32(Int32 ArgIn) {           return (2 * ArgIn); }

[UdfMethod(IsVolatile = true)] public Int16 Return2xInt16(Int16 ArgIn) {           return (Int16)((Int16)2 * ArgIn); }

[UdfMethod(IsVolatile = true)] public UInt32 Return2xUInt32(UInt32 ArgIn) {           return (2 * ArgIn); }

[UdfMethod(IsVolatile = true)] public UInt16 Return2xUInt16(UInt16 ArgIn) {           return (UInt16)((UInt16)2 * ArgIn); }

[UdfMethod(IsVolatile = true)] public byte Return2xbyte(byte ArgIn) {           return (byte)((byte)2 * ArgIn); }

[UdfMethod(IsVolatile = true)] public sbyte Return2xsbyte(sbyte ArgIn) {           return (sbyte)((sbyte)2 * ArgIn); }

[UdfMethod(IsVolatile = true)] public Single ReturnSingleNaN {           return Single.NaN; }

[UdfMethod(IsVolatile = true)] public Single ReturnSinglePositiveInfinity {           return Single.PositiveInfinity; }

[UdfMethod(IsVolatile = true)] public Single ReturnSingleNegativeInfinity {           return Single.NegativeInfinity; }

[UdfMethod(IsVolatile = true)] public Double ReturnDoubleNaN {           return Double.NaN; }

[UdfMethod(IsVolatile = true)] public Double ReturnDoublePositiveInfinity {           return Double.PositiveInfinity; }

[UdfMethod(IsVolatile = true)] public Double ReturnDoubleNegativeInfinity {           return Double.NegativeInfinity; }   } } </li> In Solution Explorer, right-click References, and then click Add Reference.</li> Click the .NET tab, click Excel Services UDF Framework, and then click OK.</li> On the Build menu, click Rebuild Solution.</li></ol>

Deploy the user-defined function on the SharePoint Server 2007 server

 * 1) In the root folder of one of the drives on the SharePoint Server 2007 server, create a folder that is named UDFs.
 * 2) Copy the DataTypes.dll file to the newly created folder.
 * 3) Click Start, point to Programs, point to Microsoft Office Server, and then click SharePoint 3.0 Central Administration.
 * 4) Under Shared Services Administration, click   to view the Shared Services home page for that particular shared services provider.
 * 5) Under Excel Services Settings, click User-defined function assemblies.
 * 6) On the Excel Services User-Defined Functions Web page, click Add User-Defined Function. The Excel Services Add User-Defined Function Assembly Web page opens.
 * 7) In the Assembly box, type  :\UDFs\DataTypes.dll.
 * 8) Under Assembly Location, click File path.
 * 9) Under Enable Assembly, click to select the Assembly enabled check box, and then click OK.

Build and deploy an Excel 2007 workbook that uses the user-defined function
<ol> Start Excel 2007.</li>  Select cell A1, and then copy the following text to the workbook: BlankCell DoBlankCell FALSE MakeString FALSE ParameterValue 4 Signature  Parameter   Result      Sum public string ReturnStringAndString(string ArgIn)  =IF(MakeString=TRUE, TEXT(ParameterValue,&quot;GENERAL&quot;),ParameterValue) =ReturnStringAndString(IF(DoBlankCell=TRUE,BlankCell, ReturnStringAndStringArg))        =SUM(ReturnStringAndStringArg) public bool ReturnNotBool(bool ArgIn)  =IF(MakeString=TRUE, TEXT(ParameterValue,&quot;GENERAL&quot;),ParameterValue) =ReturnNotBool(IF(DoBlankCell=TRUE,BlankCell, ReturnNotBoolArg))        =SUM(ReturnStringAndStringArg) public double ReturnPIxDbl(double ArgIn)   =IF(MakeString=TRUE, TEXT(ParameterValue,&quot;GENERAL&quot;),ParameterValue) =ReturnPIxDbl(IF(DoBlankCell=TRUE,BlankCell, ReturnPIxDblArg))      =SUM(ReturnStringAndStringArg) public Single ReturnPIxSgl(Single ArgIn)   =IF(MakeString=TRUE, TEXT(ParameterValue,&quot;GENERAL&quot;),ParameterValue) =ReturnPIxSgl(IF(DoBlankCell=TRUE,BlankCell, ReturnPIxSglArg))      =SUM(ReturnStringAndStringArg) public Int32 Return2xInt32(Int32 ArgIn) =IF(MakeString=TRUE, TEXT(ParameterValue,&quot;GENERAL&quot;),ParameterValue) =Return2xInt32(IF(DoBlankCell=TRUE,BlankCell, Return2xInt32Arg))       =SUM(ReturnStringAndStringArg) public Int16 Return2xInt16(Int16 ArgIn) =IF(MakeString=TRUE, TEXT(ParameterValue,&quot;GENERAL&quot;),ParameterValue) =Return2xInt16(IF(DoBlankCell=TRUE,BlankCell, Return2xInt16Arg))       =SUM(ReturnStringAndStringArg) public UInt32 Return2xUInt32(UInt32 ArgIn) =IF(MakeString=TRUE, TEXT(ParameterValue,&quot;GENERAL&quot;),ParameterValue) =Return2xUInt32(IF(DoBlankCell=TRUE,BlankCell, Return2xUInt32Arg))      =SUM(ReturnStringAndStringArg) public UInt16 Return2xUInt16(UInt16 ArgIn) =IF(MakeString=TRUE, TEXT(ParameterValue,&quot;GENERAL&quot;),ParameterValue) =Return2xUInt16(IF(DoBlankCell=TRUE,BlankCell, Return2xUInt16Arg))      =SUM(ReturnStringAndStringArg) public byte Return2xbyte(byte ArgIn)   =IF(MakeString=TRUE, TEXT(ParameterValue,&quot;GENERAL&quot;),ParameterValue) =Return2xbyte(IF(DoBlankCell=TRUE,BlankCell, Return2xbyteArg))      =SUM(ReturnStringAndStringArg) public sbyte Return2xsbyte(sbyte ArgIn) =IF(MakeString=TRUE, TEXT(ParameterValue,&quot;GENERAL&quot;),ParameterValue) =Return2xsbyte(IF(DoBlankCell=TRUE,BlankCell, Return2xsbyteArg))       =SUM(ReturnStringAndStringArg) public Single ReturnSingleNaN    =IF(DoBlankCell=TRUE,ReturnSingleNaN,ReturnSingleNaN) public Single ReturnSinglePositiveInfinity       =IF(DoBlankCell=TRUE,ReturnSinglePositiveInfinity,ReturnSinglePositiveInfinity) public Single ReturnSingleNegativeInfinity       =IF(DoBlankCell=TRUE,ReturnSingleNegativeInfinity,ReturnSingleNegativeInfinity) public Double ReturnDoubleNaN    =IF(DoBlankCell=TRUE,ReturnDoubleNaN,ReturnDoubleNaN) public Double ReturnDoublePositiveInfinity       =IF(DoBlankCell=TRUE,ReturnDoublePositiveInfinity,ReturnDoublePositiveInfinity) public Double ReturnDoubleNegativeInfinity       =IF(DoBlankCell=TRUE,ReturnDoubleNegativeInfinity,ReturnDoubleNegativeInfinity)     </li> Right-click cell A2, and then click Name a Range.</li> In the Name box, type DoBlankCell, type =Sheet1!$B$2 in the Refers to box, and then click OK.</li> Right-click cell A3, and then click Name a Range.</li> In the Name box, type MakeString, type =Sheet1!$B$3 in the Refers to box, and then click OK.</li> Right-click cell A4, and then click Name a Range.</li> In the Name box, type ParameterValue, type =Sheet1!$B$4 in the Refers to box, and then click OK.</li> Click the Microsoft Office Button, click Publish, and then click Excel Services.</li> In the File name box, type http:// / /Share Documents/Book1.xlsx .</li> Click Excel Services Options, and then click the Parameters tab.</li> Click Add, click to select the check boxes for DoBlankCell, click to select the check boxes for MakeString, click to select the check boxes for ParameterValue, and then click OK.</li> <li>In the Excel Services Options dialog box, click OK.</li> <li>In the Save As dialog box, click Save.</li></ol>

Additional query words: XL2007 Excel2007 MOSS2007 XLServices ExcelServices ECS EWR

Keywords: kbtshoot kbcode kbexpertiseinter kbprb KB928783

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.