Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

I've written some C# User Defined Functions for Excel that I'm trying to run in Excel Services. One of these UDFs is working in my local environment, but failing in the QA environment. Instead of returning actual data, any cells using the UDF are displaying the #VALUE! error. What's particularly interesting is that all of my method code is wrapped in a try catch which should write out an error message to the workbook.

There are other functions in this assembly that run fine, so it's an error with this specific function, but I can't figure out where since none of my error handling code seems to be getting invoked. Here's the method in question.

[UdfMethod(IsVolatile = true, ReturnsPersonalInformation = true)]
    public object[,] getSharepointView(string serverName,
                                       string siteName,
                                       string listName,
                                       string viewName,
                                       bool getTitles)
    {
        object[,] toExcel = null;
        // Impersonate current user viewing Excel Web Access.                

                SPSite site = null;
                try
                {
                    // Get Sharepoint objects.
                    site = new SPSite(serverName);
                    SPList list = site.AllWebs[siteName].Lists[listName];
                    SPView view = list.Views[viewName];

                    // Grab collection of Sharepoint list values.
                    SPListItemCollection values =
                                            list.GetItems(new SPQuery(view));

                    // Add a row for the titles if requested.
                    int titleRow = getTitles ? 1 : 0;
                    int totalRows = values.Count + titleRow;
                    // Avoid two-dimensional arrays with only 
                    // one-dimensional of data 
                    // by adding an empty row if needed.
                    if (totalRows == 1)
                        totalRows++;
                    // Create an object array to return to Excel.
                    toExcel = new object[totalRows, view.ViewFields.Count];
                    // Add column titles to array if needed.
                    if (getTitles)
                    {
                        for (int i = 0; i < view.ViewFields.Count; i++)
                        {
                            toExcel[0, i] = list.Fields.
                              GetFieldByInternalName(view.ViewFields[i]).Title;
                        }
                    }
                    int j = titleRow;
                    // Iterate through SPListItems in the view.
                    foreach (SPListItem currVal in values)
                    {
                        // Add every field value of current item to array.
                        for (int i = 0; i < view.ViewFields.Count; i++)
                        {
                            // Get current field.
                            SPField field = currVal.Fields.
                                    GetFieldByInternalName(view.ViewFields[i]);
                            try
                            {
                                // Get field value as a text string.
                                toExcel[j, i] = field.GetFieldValueAsText(
                                                                currVal[field.Id]);
                            }
                            catch (Exception ex)
                            {
                                toExcel[j, i] = "Error loading field";
                            }
                        }
                        j++;
                    }
                }
                catch (System.Exception ex)
                {
                    object[,] error = new object[1, 1];
                    error[0, 0] = "An error has occurred.";
                    toExcel = error;
                }
                finally
                {
                    // Dispose of SharePoint objects instead of 
                    // relying on the common language runtime.
                    if (site != null)
                        site.Dispose();
                }               
        return toExcel;
    }
share|improve this question
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.