none
update a custom property in an xlsx document RRS feed

  • Question

  • I want to use the OpenXML library to update the value of a custom document property.

    I can read and see the custom properties, and maybe even update the value of one of them, but can't quite see how to get it to save back.

                    using (var xl = SpreadsheetDocument.Open(filePath, true))
                    {
                        Console.WriteLine(xl.CustomFilePropertiesPart.Properties.OuterXml);
                        var property = xl.CustomFilePropertiesPart.Properties.GetFirstChild<Property>();
                        Console.WriteLine("Property {0} = {1}", property.Name, property.VTLPWSTR.Text);
                        property.VTLPWSTR.Text = "Updated " + DateTime.Now.ToString();
                        xl.Close();
                    }

    The intellisense for the Close method says it saves and closes the document and all parts, but the file is not updated.
    What am I missing here?

    Thanks


    roger reynolds
    Wednesday, July 7, 2010 3:56 PM

Answers

  • The trick was to add a call to

       xl.CustomFilePropertiesPart.Properties.Save();

    to the end of my using block.


    roger reynolds
    Wednesday, July 7, 2010 9:31 PM
  • Helle Roger,

    Based on my understanding,

    1.SpreadsheetDocument.CustomFilePropertiesPart.Properties.Save() functions saves the changes you make in high level APIs to the DOM stream.

    2.SpreadsheetDocument.Close() saves the stream back to the local file.

    So to make the changes saved, we must call Save() function manually as you found.

    Actually, we do not need to call Close() method if we use the using block to do resource management. If we open the Reflector tool to observe the OpenXML SDK's implementation, we can see that SpreadsheetDocument.Close is inherited from OpenXmlPackage.Close,

    public void Close()
    {
     this.ThrowIfObjectDisposed();
     this.Dispose();
    }
    
     

     The Close function calls into Dispose. If we put the SpreadsheetDocument instance in using block, the Dispose method is supposed to be call after the block executed. Dispose function is called to save the stream to local file, and close file handle, or say, clean-up jobs.

    Hope this clarifies!

    Best regards,
    Ji Zhou - MSFT
    MSDN Subscriber Support in Forum
    If you have any feedback of our support, please contact msdnmg@microsoft.com

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Wednesday, July 14, 2010 9:32 AM
    Moderator

All replies

  • I'm having issues posting the code here -- not sure why but these input boxes do not agree with me. I'm not sure the approach you're taking to this, but the way I achieved doing this was by modifying the code snippets found here for working with custom properties in Word. The only real difference is changing CustomFilePropertiesPart.Properties.Property to CustomFilePropertiesPart.Properties.CustomProperty.
    Wednesday, July 7, 2010 9:02 PM
  • The trick was to add a call to

       xl.CustomFilePropertiesPart.Properties.Save();

    to the end of my using block.


    roger reynolds
    Wednesday, July 7, 2010 9:31 PM
  • Helle Roger,

    Based on my understanding,

    1.SpreadsheetDocument.CustomFilePropertiesPart.Properties.Save() functions saves the changes you make in high level APIs to the DOM stream.

    2.SpreadsheetDocument.Close() saves the stream back to the local file.

    So to make the changes saved, we must call Save() function manually as you found.

    Actually, we do not need to call Close() method if we use the using block to do resource management. If we open the Reflector tool to observe the OpenXML SDK's implementation, we can see that SpreadsheetDocument.Close is inherited from OpenXmlPackage.Close,

    public void Close()
    {
     this.ThrowIfObjectDisposed();
     this.Dispose();
    }
    
     

     The Close function calls into Dispose. If we put the SpreadsheetDocument instance in using block, the Dispose method is supposed to be call after the block executed. Dispose function is called to save the stream to local file, and close file handle, or say, clean-up jobs.

    Hope this clarifies!

    Best regards,
    Ji Zhou - MSFT
    MSDN Subscriber Support in Forum
    If you have any feedback of our support, please contact msdnmg@microsoft.com

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Wednesday, July 14, 2010 9:32 AM
    Moderator