Remove metadata from Excel file using C#?

Question

I'm currently using C# to set the custom attributes of multiple excel files. I'm using an imported library from Microsoft known as DSOFile to write to the CustomProperties property. One issue that I'm running into is whenever the code attempts to write to an excel file that already has custom properties written to it, such as the Company and Year, a COMException exception is thrown to indicate the custom properties of the file already has a field with that name. Exact Message: "An item by that name already exists in the collection". I would like to be able to delete that item in the collection so that I can rewrite to the file. For example, if I accidentally added the wrong year to the year attribute in the file, I would like the ability to clear that field and write a new value to it. I was unable to find a method in the DSOFile class that removes metadata. Is there anyway to "programmatically" clear metadata from a file without doing it through the file properties window?

Sample Code:

DSOFILE.OleDocumentProperties dso = new DSOFile.OleDocumentProperties();
dso.Open(@"c\temp\test.xls", false, DSOFile.dsoFileOpenOptions.dsoOptionDefault);

//add metadata
dso.CustomProperties.Add("Company", "Sony");
dso.Save();
dso.Close(false);

Is it possible to change the value of an existing CustomProperty? — StingyJack, Mar 25 '16 at 18:56
change this line `dso.Open("c\temp\test.xls", false, DSOFile.dsoFileOpenOptions.dsoOptionDefault);` to the following `dso.Open(@"c\temp\test.xls", false, DSOFile.dsoFileOpenOptions.dsoOptionDefault);` — MethodMan, Mar 25 '16 at 19:18
@MethodMan I meant to put the "@" symbol" in my code example above. The exception persists even with the @ symbol. — Dwayne, Mar 25 '16 at 19:57
@syntonix - your question is "Can I clear or remove the metadata" — StingyJack, Mar 28 '16 at 19:52

NineBerry · Accepted Answer · 2016-03-25T21:16:49.730

If you want to change the default properties used by Office like Company or Author, you can just update them via the SummaryProperties object:

    OleDocumentProperties dso = new DSOFile.OleDocumentProperties();
    dso.Open(@"c:\temp\test.xls", false, DSOFile.dsoFileOpenOptions.dsoOptionDefault);

    //Update Company 
    dso.SummaryProperties.Company = "Hello World!";

    dso.Save();
    dso.Close(false);

Note, that you cannot change the default properties of documents that you can access via the SummaryProperties object via the CustomProperties object in dso. The CustomProperties are meant for additional properties used by the user, not the ones already introduced by Microsoft Office.

In order to change the custom properties, you have to be aware that CustomProperties is a collection that you can iterate over via foreach. So you can use the following two methods:

private static void DeleteProperty(CustomProperties properties, string propertyName)
{
    foreach(CustomProperty property in properties)
    {
        if (string.Equals(property.Name, propertyName, StringComparison.InvariantCultureIgnoreCase))
        {
            property.Remove();
            break;
        }
    }
}

private static void UpdateProperty(CustomProperties properties, string propertyName, string newValue)
{
    bool propertyFound = false;

    foreach (CustomProperty property in properties)
    {
        if (string.Equals(property.Name, propertyName, StringComparison.InvariantCultureIgnoreCase))
        {
            // Property found, change it
            property.set_Value(newValue);
            propertyFound = true;
            break;
        }
    }

    if(!propertyFound)
    {
        // The property with the given name was not found, so we have to add it 
        properties.Add(propertyName, newValue);
    }
}

Here is an example on how to use UpdateProperty:

static void Main(string[] args)
{
    OleDocumentProperties dso = new DSOFile.OleDocumentProperties();
    dso.Open(@"c:\temp\test.xls", false, DSOFile.dsoFileOpenOptions.dsoOptionDefault);

    UpdateProperty(dso.CustomProperties, "Year", "2017");

    dso.Save();
    dso.Close(false);
}

You are a freaking genius and lifesaver. That worked! Thank you so much! — Dwayne, Mar 26 '16 at 03:06

Remove metadata from Excel file using C#?

1 Answers1