none
How to add a CustomProperty to an Excel worksheet? RRS feed

  • Question

  • How can I add a CustomProperty using the OpenXML 2.0 SDK interface from C# ?  I've gotten as far as this:

    CustomProperties customProperties = sheet1.AppendChild<CustomProperties>(new CustomProperties());
    CustomProperty customProperty = customProperties.AppendChild<CustomProperty>(new CustomProperty());
    customProperty.Name =
    "mycustompropertyname";

    ...but I can't figure out how to assign a VALUE to the custom property.  The code above does indeed add a custom property to the generated .xlsx file, but then Excel complains about unreadable content when opening the file, presumably because the custom property has no value assigned.

    That is to say, I'm looking for the C# equivalent of the VB code:

    sheet1.CustomProperties.Add "mycustompropertyname", "mystringvalue"

    Help?

    Thursday, April 14, 2011 11:31 PM

Answers

  • Answering my own question, this seems to do the trick:

    CustomPropertyPart customPropertyPart = _worksheetPart.AddCustomPropertyPart(CustomPropertyPartType.Spreadsheet, "rIdProp1");
    System.IO.
    Stream data = new System.IO.MemoryStream(System.Convert.FromBase64String("dAByAHUAZQA="));
    customPropertyPart.FeedData(data);
    data.Close();
    CustomProperties customProperties = worksheet.AppendChild<CustomProperties>(new CustomProperties());
    CustomProperty customProperty = customProperties.AppendChild<CustomProperty>(new CustomProperty());
    customProperty.Id =
    "rIdProp1";
    customProperty.Name =
    "mycustompropertyname";

    ...where that wacky Base64 string encodes "true".  Is this actually documented anywhere that I was supposed to find it on my own?

    • Marked as answer by Bruce Song Friday, April 15, 2011 5:20 AM
    Friday, April 15, 2011 12:15 AM

All replies

  • Answering my own question, this seems to do the trick:

    CustomPropertyPart customPropertyPart = _worksheetPart.AddCustomPropertyPart(CustomPropertyPartType.Spreadsheet, "rIdProp1");
    System.IO.
    Stream data = new System.IO.MemoryStream(System.Convert.FromBase64String("dAByAHUAZQA="));
    customPropertyPart.FeedData(data);
    data.Close();
    CustomProperties customProperties = worksheet.AppendChild<CustomProperties>(new CustomProperties());
    CustomProperty customProperty = customProperties.AppendChild<CustomProperty>(new CustomProperty());
    customProperty.Id =
    "rIdProp1";
    customProperty.Name =
    "mycustompropertyname";

    ...where that wacky Base64 string encodes "true".  Is this actually documented anywhere that I was supposed to find it on my own?

    • Marked as answer by Bruce Song Friday, April 15, 2011 5:20 AM
    Friday, April 15, 2011 12:15 AM
  • Hi Jonathanbrecher,

    I am glad to hear that you have resolved your problem and thank you for sharing with us.

    It seems that the string should be converted, which encodes binary data as base-64 digits, to an equivalent 8-bit unsigned integer array. For more details, please take a look at this API method:http://msdn.microsoft.com/en-us/library/system.convert.frombase64string.aspx

    We can view the file in Open XML SDK tool, and can find the generated code also use FromBase64String method.

    Hope this helps.

    Best Regards,

     


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    Friday, April 15, 2011 5:33 AM