C# 以编程方式访问 Excel 自定义文档属性

Accessing Excel Custom Document Properties programmatically

提问by Dan McClain

I'm trying to add custom properties to a workbook I have created programmatically. I have a method in place for getting and setting properties, but the problem is the workbook is returning null for the CustomDocumentProperties property. I cannot figure out how to initialize this property so that I can add and retrieve properties from the workbook. Microsoft.Office.Core.DocumentProperties is an interface, so I cant go and do the following

我正在尝试将自定义属性添加到我以编程方式创建的工作簿中。我有一种用于获取和设置属性的方法，但问题是工作簿为 CustomDocumentProperties 属性返回 null。我不知道如何初始化这个属性，以便我可以从工作簿中添加和检索属性。Microsoft.Office.Core.DocumentProperties 是一个接口，所以我不能去执行以下操作

if(workbook.CustomDocumentProperties == null)
    workbook.CustomDocumentProperties = new DocumentProperties;

Here is the code I have to get and set the properties:

这是我必须获取和设置属性的代码：

     private object GetDocumentProperty(string propertyName, MsoDocProperties type)
    {
        object returnVal = null;

        Microsoft.Office.Core.DocumentProperties properties;
        properties = (Microsoft.Office.Core.DocumentProperties)workBk.CustomDocumentProperties;

        foreach (Microsoft.Office.Core.DocumentProperty property in properties)
        {
            if (property.Name == propertyName && property.Type == type)
            {
                returnVal = property.Value;
            }
            DisposeComObject(property);
        }

        DisposeComObject(properties);

        return returnVal;
    }

    protected void SetDocumentProperty(string propertyName, string propertyValue)
    {
        DocumentProperties properties;
        properties = workBk.CustomDocumentProperties as DocumentProperties;

        bool propertyExists = false;
        foreach (DocumentProperty prop in properties)
        {
            if (prop.Name == propertyName)
            {
                prop.Value = propertyValue;
                propertyExists = true;
            }
            DisposeComObject(prop);

            if(propertyExists) break;
        }

        if (!propertyExists)
        {
            properties.Add(propertyName, false, MsoDocProperties.msoPropertyTypeString, propertyValue, Type.Missing);
        }

        DisposeComObject(propertyExists);

    }

The line properties = workBk.CustomDocumentProperties as DocumentProperties; always set properties to null.

行属性 = workBk.CustomDocumentProperties as DocumentProperties; 始终将属性设置为 null。

This is using Microsoft.Office.Core v12.0.0.0 and Microsoft.Office.Interop.Excell v12.0.0.0 (Office 2007)

这是使用 Microsoft.Office.Core v12.0.0.0 和 Microsoft.Office.Interop.Excell v12.0.0.0 (Office 2007)

采纳答案by Gary McGill

I looked at my own code and can see that I access the properties using late binding. I can't remember why, but I'll post some code in case it helps.

我查看了自己的代码，可以看到我使用后期绑定访问属性。我不记得为什么，但我会发布一些代码以防万一。

object properties = workBk.GetType().InvokeMember("CustomDocumentProperties", BindingFlags.Default | BindingFlags.GetProperty, null, workBk, null);

object property = properties.GetType().InvokeMember("Item", BindingFlags.Default | BindingFlags.GetProperty, null, properties, new object[] { propertyIndex });

object propertyValue = property.GetType().InvokeMember("Value", BindingFlags.Default | BindingFlags.GetProperty, null, propertyWrapper.Object, null);

EDIT: ah, now I remember why. :-)

编辑：啊，现在我记得为什么了。:-)

EDIT 2: Jimbojones' answer - to use the dynamic keyword - is a better solution (if you value ease-of-use over the performance overhead of using dynamic).

编辑 2：Jimbojones 的回答 - 使用 dynamic 关键字 - 是一个更好的解决方案（如果您重视易用性而不是 using 的性能开销dynamic）。

回答by Dan McClain

I found the solution here.

我在这里找到了解决方案。

Here is the code I ended up with:

这是我最终得到的代码：

    public void SetDocumentProperty(string propertyName, string propertyValue)
    {
        object oDocCustomProps = workBk.CustomDocumentProperties;
        Type typeDocCustomProps = oDocCustomProps.GetType();

        object[] oArgs = {propertyName,false,
                 MsoDocProperties.msoPropertyTypeString,
                 propertyValue};

        typeDocCustomProps.InvokeMember("Add", BindingFlags.Default |
                                   BindingFlags.InvokeMethod, null,
                                   oDocCustomProps, oArgs);

    }

    private object GetDocumentProperty(string propertyName, MsoDocProperties type)
    {
        object returnVal = null;

        object oDocCustomProps = workBk.CustomDocumentProperties;
        Type typeDocCustomProps = oDocCustomProps.GetType();


        object returned = typeDocCustomProps.InvokeMember("Item", 
                                    BindingFlags.Default |
                                   BindingFlags.GetProperty, null,
                                   oDocCustomProps, new object[] { propertyName });

        Type typeDocAuthorProp = returned.GetType();
        returnVal = typeDocAuthorProp.InvokeMember("Value",
                                   BindingFlags.Default |
                                   BindingFlags.GetProperty,
                                   null, returned,
                                   new object[] { }).ToString();

        return returnVal;
    }

Some exception handling is necessary to hand if the property doesnt exist when retrieved

如果检索时属性不存在，则需要进行一些异常处理

回答by cori

Late answer to this question, but I worked out a simpler method for adding custom DocumentProperties that might be of use to someone in the future.

这个问题的答案很晚，但我找到了一种更简单的方法来添加将来可能对某人有用的自定义 DocumentProperties。

My problem was that calling the Add() method with the System type supplied by System.String.GetType() triggered a COMException: Type mismatch. Referring to the link in the previous answers it's clear that this method expects an Office-specific type, so the code that ended up working for me was:

我的问题是使用 System.String.GetType() 提供的 System 类型调用 Add() 方法会触发 COMException: Type mismatch。参考先前答案中的链接，很明显该方法需要特定于 Office 的类型，因此最终为我工作的代码是：

var custProps = (Office.DocumentProperties)this.CustomDocumentProperties;
custProps.Add( "AProperty", false, MsoDocProperties.msoPropertyTypeString, "AStringProperty" );

Because it's a CustomDocumentProperty Office will add the custom property without difficulty, but if you need to check for existence or validate the value when the CustomDocumentProperty might not exist you'll have to catch a System.ArgumentException.

因为它是一个 CustomDocumentProperty Office 将毫无困难地添加自定义属性，但是如果您需要检查是否存在或在 CustomDocumentProperty 可能不存在时验证值，则必须捕获 System.ArgumentException。

EDIT

编辑

As pointed out in Oliver Bock's comment, this is an Office 2007 and up only solution, as far as I know.

正如 Oliver Bock 的评论中指出的那样，据我所知，这是 Office 2007 及更高版本的唯一解决方案。

回答by jimbojones

If you are targetting .NET 4.0, you can use the dynamickey word for late binding

如果您的目标是 .NET 4.0，则可以使用dynamic关键字进行后期绑定

 Document doc = GetActiveDocument();
 if ( doc != null )
 {
     dynamic properties = doc.CustomDocumentProperties;
     foreach (dynamic p in properties)
     {
         Console.WriteLine( p.Name + " " + p.Value);
     }
 }