Custom Field and export to Excel

Question

I have a custom field that inherits the Number field (I change the UI of the field, but not the storage).

When I export to Excel a list that contains an instance of this field, I get different behaviors:

• With Excel 2013, the field is available
• With Excel 2003, the field is not available

Is there any restriction with Excel 2003 and custom fields?

Is there a workaround?

I've tried to create a calculated column that just replicate the value of the column (with a number output). The column shows the correct value in a SharePoint view. The column is even visible in Excel 2003, but with an error: Invalid cell reference (I guess it's because Excel cannot find the column).

My farm runs SP 2010.

[Edit]. As requested, a bit more information:

As I said, the purpose of the custom field is to customize the UI of the native number field (providing a DropDown list to be more precise instead of a TextBox).

What I've done:

1. The field definition:

   [Guid("3E27F5E8-348D-40C6-AFCC-87306B2BB3AC")]
   public class MyCustomField : SPFieldNumber
   {
       public MyCustomField(SPFieldCollection fields, string fieldName)
           : base(fields, fieldName) { }
   
       public MyCustomField(SPFieldCollection fields, string typeName, string displayName)
           : base(fields, typeName, displayName) { }
   
       public override Microsoft.SharePoint.WebControls.BaseFieldControl FieldRenderingControl
       {
           get
           {
               return new MyCustomFieldControl
               {
                   FieldName = this.InternalName,
               };
           }
       }
   }
   

2. The field declaration:

   <FieldTypes>
     <FieldType>
       <Field Name="TypeName">MyCustomField</Field>
       <Field Name="ParentType">Number</Field>
       <Field Name="TypeDisplayName">xxxxx</Field>
       <Field Name="TypeShortDescription">yyyyyy</Field>
       <Field Name="AllowBaseTypeRendering">TRUE</Field>
       <Field Name="UserCreatable">FALSE</Field>
       <Field Name="FieldTypeClass">$SharePoint.Type.3e27f5e8-348d-40c6-afcc-87306b2bb3ac.AssemblyQualifiedName$</Field>
       <Field Name="Sortable">TRUE</Field>
       <Field Name="Filterable">TRUE</Field>
     </FieldType>
   </FieldTypes>
   

3. The control's behavior (especially the Value property:

   public class MyCustomFieldControl : BaseFieldControl, IDesignTimeHtmlProvider
   {
       protected DropDownList ddlNumberOfPlaces;
       protected Label lblNumberOfPlaces;
   
       /* business rules removed from clarity */
   
       protected override void CreateChildControls()
       {
           if (this.Field != null)
           {
               base.CreateChildControls();
   
               ddlNumberOfPlaces = (DropDownList)this.TemplateContainer.FindControl("ddlNumberOfPlaces");
               lblNumberOfPlaces = (Label)this.TemplateContainer.FindControl("lblNumberOfPlaces");
   
               if (ControlMode == SPControlMode.Edit || ControlMode == SPControlMode.New) {
                   var maxNumberOfPlaces = MaximumNumberOfPlaces; // From my business rules
                   for (int i = 1; i <= maxNumberOfPlaces; i++) {
                       ddlNumberOfPlaces.Items.Add(i.ToString(CultureInfo.InvariantCulture));
                   }
                   lblNumberOfPlaces.Visible = false;
                   ddlNumberOfPlaces.Visible = true;
               }
               else
               {
                   lblNumberOfPlaces.Text = this.ItemFieldValue.ToString();
                   lblNumberOfPlaces.Visible = true;
                   ddlNumberOfPlaces.Visible = false;
               }
           }
       }
   
   
       public override object Value
       {
           get
           {
               int result;
               if (int.TryParse(ddlNumberOfPlaces.SelectedValue, out result))
               {
                   return result;
               }
               else
               {
                   return null;
               }
           }
           set
           {
               ddlNumberOfPlaces.SelectedValue = value.ToString();
               lblNumberOfPlaces.Text = value.ToString();
           }
       }
   }
   

4. And my simple control template

   <SharePoint:RenderingTemplate ID="MyCustomFieldControl" runat="server">
       <Template>
           <asp:DropDownList runat="server" ID="ddlNumberOfPlaces" EnableViewState="false" />
           <asp:Label runat="server" ID="lblNumberOfPlaces" EnableViewState="false" />
       </Template>
   </SharePoint:RenderingTemplate>
   

Answer 1

It's a poor solution, but I ended to duplicate all fields that have issues:

• Content Type name
• my custom field

In a receiver (fortunately, I already have a receiver on my content type), I duplicate the value from my fields to their "text-compatible-with-old-excel" counterparts.

Tweaking a bit the show in display/edit/new forms, and views, it's nearly transparent for the users.

I put this in my yet-full box of hack in SharePoint projects