C# Creating Custom Attributes in Excel, Word, and PowerPoint

Post Views: 675

Table of Contents

C# Creating Custom Attributes in Excel, Word, and PowerPoint

With the latest version of office products, they all have the ability to create custom attributes within the document. I have always wanted to create a small documents PLM and this would be my mechanism to store the attributes. I’m going to demonstrate this within a visual studio console application. I have not used generics at this moment and maybe this will be a follow-up post.

References

Within this project we will need to add the COM References for Excel, PowerPoint and Word.

ICustomApplicationAttributes Interface

Let start of with the interface, which will be used to define how applications will be required to manage the custom attributes via the CRUD Create, Replace, Update and Delete of the custom attributes.

	public interface ICustomApplicationAttributes
    {
        bool CreateStringAttribute(string name, string value);
        bool CreateBooleanAttribute(string name, bool value);
        bool CreateNumberAttribute(string name, double value);
        bool CreateDateAttribute(string name, DateTime value);

        bool DeleteAttribute(string name);

        string? GetStringAttribute(string name);
        bool? GetBooleanAttribute(string name);
        double? GetNumberAttribute(string name);
        DateTime? GetDateAttribute(string name);

        bool UpdateStringAttribute(string name, string value, string newName = "");
        bool UpdateBooleanAttribute(string name, bool value, string newName = "");
        bool UpdateNumberAttribute(string name, double value, string newName = "");
        bool UpdateDateAttribute(string name, DateTime value, string newName = "");
    }

OfficeCustomAttributes Abstract Class

Since we want to handle several applications and the mechanism is basically the same other than how they handle the active document, presentation and sheet. I wanted to create an abstract class that would provide the bulk of the methods based on the interfaces requirements.

Note the dynamic property for Property’s due to the different ways in which the Office products define this via the Active Document, Sheet and Presentation this will ahve to be defined within the constructor for each product implementation.

using Serilog;

namespace OfficeConnectTest
{
    public abstract class OfficeCustomAttributes : ICustomApplicationAttributes
    {
        private enum MsoDocProperties
        {
            msoPropertyTypeNumber = 1,
            msoPropertyTypeBoolean,
            msoPropertyTypeDate,
            msoPropertyTypeString
        }

        private ILogger _logger;

        public dynamic? Propertys { get; set; }
        public OfficeCustomAttributes(ILogger logger)
        {
            _logger = logger;
            logger.Information(string.Format("{0} - OfficeCustomAttributes - Constructor",DateTime.Now));
        }
        
        public virtual bool CreateBooleanAttribute(string name, bool value)
        {
            try
            {
                if (Propertys != null)
                {
                    Propertys.Add(name, false, MsoDocProperties.msoPropertyTypeBoolean, value);
                    _logger.Information(string.Format("{0} - OfficeCustomAttributes - CreateBooleanAttribute : {1}: {2}", DateTime.Now,name,value));
                    return true;
                }
                else
                {
                    _logger.Warning(string.Format("{0} - OfficeCustomAttributes - CreateBooleanAttribute : Properys Was Null", DateTime.Now));
                    return false;
                }
            }
            catch(Exception ex)
            {
                _logger.Error(ex.ToString());
                return false;
            }
        }
        
        public virtual bool CreateDateAttribute(string name, DateTime value)
        {
            try
            {
                if (Propertys != null)
                {
                    Propertys.Add(name, false, MsoDocProperties.msoPropertyTypeDate, value);
                    _logger.Information(string.Format("{0} - OfficeCustomAttributes - CreateDateAttribute : {1}: {2}", DateTime.Now, name, value));
                    return true;
                }
                else
                {
                    _logger.Warning(string.Format("{0} - OfficeCustomAttributes - CreateDateAttribute : Properys Was Null", DateTime.Now));
                    return false;
                }
            }
            catch (Exception ex)
            {
                _logger.Error(string.Format("{0} - OfficeCustomAttributes - CreateDateAttribute - Error : {1}",DateTime.Now,ex.ToString()));
                return false;
            }
        }
        
        public virtual bool CreateNumberAttribute(string name, double value)
        {
            try
            {
                if (Propertys != null)
                {
                    Propertys.Add(name, false, MsoDocProperties.msoPropertyTypeNumber, value);
                    _logger.Information(string.Format("{0} - OfficeCustomAttributes - CreateNumberAttribute : {1}: {2}", DateTime.Now, name, value));
                    return true;
                }
                else
                {
                    _logger.Warning(string.Format("{0} - OfficeCustomAttributes - CreateNumberAttribute : Properys Was Null", DateTime.Now));
                    return false;
                }
            }
            catch (Exception ex)
            {
                _logger.Error(string.Format("{0} - OfficeCustomAttributes - CreateNumberAttribute - Error : {1}", DateTime.Now, ex.ToString()));
                return false;
            }
        }
        
        public virtual bool CreateStringAttribute(string name, string value)
        {
            try
            {
                if (Propertys != null)
                {
                    Propertys.Add(name, false, MsoDocProperties.msoPropertyTypeString, value);
                    _logger.Information(string.Format("{0} - OfficeCustomAttributes - CreateStringAttribute : {1}: {2}", DateTime.Now, name, value));
                    return true;
                }
                else
                {
                    _logger.Warning(string.Format("{0} - OfficeCustomAttributes - CreateStringAttribute : Properys Was Null", DateTime.Now));
                    return false;
                }
            }
            catch (Exception ex)
            {
                _logger.Error(string.Format("{0} - OfficeCustomAttributes - CreateStringAttribute - Error : {1}", DateTime.Now, ex.ToString()));
                return false;
            }
        }

        public virtual bool DeleteAttribute(string name)
        {
            try
            {
                if (Propertys != null)
                {
                    DeleteCustomProperty(name);
                    _logger.Information(string.Format("{0} - OfficeCustomAttributes - DeleteAttribute : {1}", DateTime.Now, name));
                    return true;
                }
                else
                {
                    _logger.Warning(string.Format("{0} - OfficeCustomAttributes - DeleteAttribute : Properys Was Null", DateTime.Now));
                    return false;
                }
            }
            catch(Exception ex)
            {
                _logger.Error(string.Format("{0} - OfficeCustomAttributes - DeleteAttribute - Error : {1}", DateTime.Now, ex.ToString()));
                return false;
            }
        }

        public virtual bool? GetBooleanAttribute(string name)
        {
            try
            {
                if (Propertys != null)
                {
                    _logger.Information(string.Format("{0} - OfficeCustomAttributes - GetBooleanAttribute : {1}", DateTime.Now, name));
                    return GetCustomPropertyValue(name);
                }
                else
                {
                    _logger.Warning(string.Format("{0} - OfficeCustomAttributes - GetBooleanAttribute : Properys Was Null", DateTime.Now));
                    return false;
                }
            }
            catch(Exception ex)
            {
                _logger.Error(string.Format("{0} - OfficeCustomAttributes - GetBooleanAttribute - Error : {1}", DateTime.Now, ex.ToString()));
                return null;
            }
        }
        
        public virtual DateTime? GetDateAttribute(string name)
        {
            try
            {
                if (Propertys != null)
                {
                    _logger.Information(string.Format("{0} - OfficeCustomAttributes - GetDateAttribute : {1}", DateTime.Now, name));
                    return GetCustomPropertyValue(name);
                }
                else
                {
                    _logger.Warning(string.Format("{0} - OfficeCustomAttributes - GetDateAttribute : Properys Was Null", DateTime.Now));
                    return null;
                }
            }
            catch (Exception ex)
            {
                _logger.Error(string.Format("{0} - OfficeCustomAttributes - GetDateAttribute - Error : {1}", DateTime.Now, ex.ToString()));
                return null;
            }
        }
        
        public virtual double? GetNumberAttribute(string name)
        {
            try
            {
                if (Propertys != null)
                {
                    _logger.Information(string.Format("{0} - OfficeCustomAttributes - GetNumberAttribute : {1}", DateTime.Now, name));
                    return GetCustomPropertyValue(name);
                }
                else
                {
                    _logger.Warning(string.Format("{0} - OfficeCustomAttributes - GetNumberAttribute : Properys Was Null", DateTime.Now));
                    return null;
                }
            }
            catch (Exception ex)
            {
                _logger.Error(string.Format("{0} - OfficeCustomAttributes - GetNumberAttribute - Error : {1}", DateTime.Now, ex.ToString()));
                return null;
            }
        }
        
        public virtual string? GetStringAttribute(string name)
        {
            try
            {
                if (Propertys != null)
                {
                    _logger.Information(string.Format("{0} - OfficeCustomAttributes - GetStringAttribute : {1}", DateTime.Now, name));
                    return GetCustomPropertyValue(name);
                }
                else
                {
                    _logger.Warning(string.Format("{0} - OfficeCustomAttributes - GetStringAttribute : Properys Was Null", DateTime.Now));
                    return null;
                }
            }
            catch (Exception ex)
            {
                _logger.Error(string.Format("{0} - OfficeCustomAttributes - GetStringAttribute - Error : {1}", DateTime.Now, ex.ToString()));
                return null;
            }
        }

        public virtual bool UpdateBooleanAttribute(string name, bool value, string newName = "")
        {
            try
            {
                if (Propertys != null)
                {
                    try
                    {
                        _logger.Information(string.Format("{0} - OfficeCustomAttributes - UpdateBooleanAttribute : {1} : {2} : {3}", DateTime.Now, name,value,newName));
                        DeleteCustomProperty(name);
                    }
                    catch { /* Attribute Does Not Exist Dont Do Anything*/ }
                    CreateBooleanAttribute((newName == string.Empty) ? name : newName, value);
                    return true;
                }
                else
                {
                    _logger.Warning(string.Format("{0} - OfficeCustomAttributes - UpdateBooleanAttribute : Properys Was Null", DateTime.Now));
                    return false;
                }
            }
            catch (Exception ex)
            {
                _logger.Error(string.Format("{0} - OfficeCustomAttributes - UpdateBooleanAttribute - Error : {1}", DateTime.Now, ex.ToString()));
                return false;
            }
        }
        
        public virtual bool UpdateDateAttribute(string name, DateTime value, string newName = "")
        {
            try
            {
                if (Propertys != null)
                {
                    try
                    {
                        _logger.Information(string.Format("{0} - OfficeCustomAttributes - UpdateDateAttribute : {1} : {2} : {3}", DateTime.Now, name, value, newName));
                        DeleteCustomProperty(name);
                    }
                    catch { /* Attribute Does Not Exist Dont Do Anything*/ }
                    CreateDateAttribute((newName == string.Empty) ? name : newName, value);
                    return true;
                }
                else
                {
                    _logger.Warning(string.Format("{0} - OfficeCustomAttributes - UpdateDateAttribute : Properys Was Null", DateTime.Now));
                    return false;
                }
            }
            catch (Exception ex)
            {
                _logger.Error(string.Format("{0} - OfficeCustomAttributes - UpdateDateAttribute - Error : {1}", DateTime.Now, ex.ToString()));
                return false;
            }
        }
        
        public virtual bool UpdateNumberAttribute(string name, double value, string newName = "")
        {
            try
            {
                if (Propertys != null)
                {
                    try
                    {
                        _logger.Information(string.Format("{0} - OfficeCustomAttributes - UpdateNumberAttribute : {1} : {2} : {3}", DateTime.Now, name, value, newName));
                        DeleteCustomProperty(name);
                    }
                    catch { /* Attribute Does Not Exist Dont Do Anything*/ }
                    CreateNumberAttribute((newName == string.Empty) ? name : newName, value);
                    return true;
                }
                else
                {
                    _logger.Warning(string.Format("{0} - OfficeCustomAttributes - UpdateNumberAttribute : Properys Was Null", DateTime.Now));
                    return false;
                }
            }
            catch (Exception ex)
            {
                _logger.Error(string.Format("{0} - OfficeCustomAttributes - UpdateNumberAttribute - Error : {1}", DateTime.Now, ex.ToString()));
                return false;
            }
        }
        
        public virtual bool UpdateStringAttribute(string name, string value, string newName = "")
        {
            try
            {
                if (Propertys != null)
                {
                    try
                    {
                        _logger.Information(string.Format("{0} - OfficeCustomAttributes - UpdateStringAttribute : {1} : {2} : {3}", DateTime.Now, name, value, newName));
                        DeleteCustomProperty(name);
                    }
                    catch { /* Attribute Does Not Exist Dont Do Anything*/ }
                    CreateStringAttribute((newName == string.Empty) ? name : newName, value);
                    return true;
                }
                else
                {
                    _logger.Warning(string.Format("{0} - OfficeCustomAttributes - UpdateStringAttribute : Properys Was Null", DateTime.Now));
                    return false;
                }
            }
            catch (Exception ex)
            {
                _logger.Error(string.Format("{0} - OfficeCustomAttributes - UpdateStringAttribute - Error : {1}", DateTime.Now, ex.ToString()));
                return false;
            }
        }

        private void DeleteCustomProperty(string propName)
        {
            if (Propertys != null)
            {
                dynamic? customProperty = GetCustomProperty( propName);
                if (customProperty != null)
                {
                    customProperty.Delete();
                }
                else
                {
                    throw new Exception("Custom Attribute Does Not Exist.");
                }
            }
        }
        
        private dynamic? GetCustomProperty(string propName)
        {
            dynamic? returnProperty = null;
            if (Propertys != null)
            {
                for (int ioIndex = 1; ioIndex <= Propertys.Count; ioIndex++)
                {
                    var customProperty = Propertys.Item(ioIndex);
                    if (customProperty.Name == propName)
                    {
                        return customProperty;
                    }
                }
            }
            return returnProperty;
        }
        
        private dynamic? GetCustomPropertyValue(string propName)
        {
            if (Propertys != null)
            {
                dynamic? customProperty = GetCustomProperty( propName);
                if (customProperty != null)
                {
                    return customProperty.Value;
                }
                else
                {
                    throw new Exception("Custom Attribute Does Not Exist.");
                }
            }
            return null;
        }
        
        private bool CustomAttributeExists(string propName)
        {
            if (Propertys != null)
            {
                for (int ioIndex = 1; ioIndex <= Propertys.Count; ioIndex++)
                {
                    var customProperty = Propertys.Item(ioIndex);
                    if (customProperty.Name == propName)
                    {
                        return true;
                    }
                }
            }
            return false;
        }
    }
}

Excel Implementation

Within all of the Office implementations we have to define the Property’s object since they are all defined differently, but due to to inheriting the abstract object we get all the required methods.

using Serilog;

using ExcelApplication = Microsoft.Office.Interop.Excel.Application;

namespace OfficeConnectTest
{
    public class ExcelCustomAttributes : OfficeCustomAttributes
    {
        private ExcelApplication _application;
        public ExcelCustomAttributes(ILogger logger, ExcelApplication excelApplication) : base(logger)
        {
            _application = excelApplication;
            Propertys = excelApplication.ActiveWorkbook.CustomDocumentProperties;
        }
    }
}

PowerPoint Implementation

The PowerPoint implementation is almost identical except for the Property’s definition again.

using Serilog;

using PowerPointApplication = Microsoft.Office.Interop.PowerPoint.Application;

namespace OfficeConnectTest
{
    public class PowerPointCustomAttributes : OfficeCustomAttributes
    {
        private PowerPointApplication _application;
        public PowerPointCustomAttributes(ILogger logger, PowerPointApplication powerPointApplication) : base(logger)
        {
            _application = powerPointApplication;
            Propertys = powerPointApplication.ActivePresentation.CustomDocumentProperties;
        }
    }
}

Word Implementation

And the word implementation follows the same pattern.

using Serilog;

using WordApplication = Microsoft.Office.Interop.Word.Application;

namespace OfficeConnectTest
{
    public class WordCustomAttributes : OfficeCustomAttributes
    {
        private WordApplication _application;
        public WordCustomAttributes(ILogger logger, WordApplication wordApplication) : base(logger)
        {
            _application = wordApplication;
            Propertys = wordApplication.ActiveDocument.CustomDocumentProperties;
        }
    }
}

Console Program

To test this we will need to create a Serilog object for logging, and we will reuse the Custom Marshaling class we previous used in other posts.

Marshalling

To get the Office application were going to reuse the custom marshalling class that we have used in previous posts.

using System.Runtime.InteropServices;
using System.Runtime.Versioning;
using System.Security;

namespace OfficeConnectTest
{
    public static class CustomMarshal
    {
        internal const String OLEAUT32 = "oleaut32.dll";
        internal const String OLE32 = "ole32.dll";

        [System.Security.SecurityCritical]  // auto-generated_required
        public static Object GetActiveObject(String progID)
        {
            Object? obj = null;
            Guid clsid;

            // Call CLSIDFromProgIDEx first then fall back on CLSIDFromProgID if
            // CLSIDFromProgIDEx doesn't exist.
            try
            {
                CLSIDFromProgIDEx(progID, out clsid);
            }
            //            catch
            catch (Exception)
            {
                CLSIDFromProgID(progID, out clsid);
            }

            GetActiveObject(ref clsid, IntPtr.Zero, out obj);
            return obj;
        }

        //[DllImport(Microsoft.Win32.Win32Native.OLE32, PreserveSig = false)]
        [DllImport(OLE32, PreserveSig = false)]
        [ResourceExposure(ResourceScope.None)]
        [SuppressUnmanagedCodeSecurity]
        [System.Security.SecurityCritical]  // auto-generated
        private static extern void CLSIDFromProgIDEx([MarshalAs(UnmanagedType.LPWStr)] String progId, out Guid clsid);

        //[DllImport(Microsoft.Win32.Win32Native.OLE32, PreserveSig = false)]
        [DllImport(OLE32, PreserveSig = false)]
        [ResourceExposure(ResourceScope.None)]
        [SuppressUnmanagedCodeSecurity]
        [System.Security.SecurityCritical]  // auto-generated
        private static extern void CLSIDFromProgID([MarshalAs(UnmanagedType.LPWStr)] String progId, out Guid clsid);

        //[DllImport(Microsoft.Win32.Win32Native.OLEAUT32, PreserveSig = false)]
        [DllImport(OLEAUT32, PreserveSig = false)]
        [ResourceExposure(ResourceScope.None)]
        [SuppressUnmanagedCodeSecurity]
        [System.Security.SecurityCritical]  // auto-generated
        private static extern void GetActiveObject(ref Guid rclsid, IntPtr reserved, [MarshalAs(UnmanagedType.Interface)] out Object ppunk);
    }
}

Logging Helper

We need to create a small log helper to read the appsettigns.json file where we will configure Serilog.

using Microsoft.Extensions.Configuration;

namespace OfficeConnectTest
{
    public static class LogHelper
    {
        public static void BuildConfig(IConfigurationBuilder builder)
        {
            builder.SetBasePath(Directory.GetCurrentDirectory())
                .AddJsonFile("appsettings.json", optional: false, reloadOnChange: true)
                .AddJsonFile($"appsettings.{Environment.GetEnvironmentVariable("ASPNETCORE_ENVIRONMENT") ?? "Production"}.json", optional: true)
                .AddEnvironmentVariables();
        }
    }
}

Appsettings Json

The Appsettings.json file will configure the Serilog log files and where they are created.

{
  "Serilog": {
    "Using": [ "Serilog.Sinks.Console"],
    "MinimumLevel": {
      "Default": "Debug",
      "Override": {
        "Default": "Information",
        "Microsoft": "Warning",
        "Microsoft.Hosting.Lifetime": "Information"
      }
    },
    "WriteTo": [
      {
        "Name": "Logger",
        "Args": {
          "configureLogger": {
            "Filter": [
              {
                "Name": "ByIncludingOnly",
                "Args": {
                  "expression": "(@l='Error' or @l='Fatal' or @l='Warning')"
                }
              }
            ],
            "WriteTo": [
              {
                "Name": "File",
                "Args": {
                  "path": "C:\\CatiaWidgets\\Logs\\WarnErrFatal_.log",
                  "outputTemplate": "{Timestamp:o} [{Level:u3}] ({SourceContext}) {Message}{NewLine}{Exception}",
                  "rollingInterval": "Day",
                  "retainedFileCountLimit": 7
                }
              }
            ]
          }
        }
      },
      {
        "Name": "Logger",
        "Args": {
          "configureLogger": {
            "Filter": [
              {
                "Name": "ByIncludingOnly",
                "Args": {
                  "expression": "(@l='Information' or @l='Debug')"
                }
              }
            ],
            "WriteTo": [
              {
                "Name": "File",
                "Args": {
                  "path": "C:\\CatiaWidgets\\Logs\\DebugInfo_.log",
                  "outputTemplate": "{Timestamp:o} [{Level:u3}] ({SourceContext}) {Message}{NewLine}{Exception}",
                  "rollingInterval": "Day",
                  "retainedFileCountLimit": 7
                }
              }
            ]
          }
        }
      }
    ],
    "Enrich": [
      "FromLogContext",
      "WithMachineName"
    ],
    "Properties": {
      "Application": "MultipleLogFilesSample"
    }
  }
}

Console Application

Within the Console Application we will use configuration to read the appsettings json file and then create the Serilog logging object which will be used for logging within the abstract object. Then i have 4 examples of the creating, updating, getting and deleting the custom attributes for word.

using ExcelApplication = Microsoft.Office.Interop.Excel.Application;
using PowerPointApplication = Microsoft.Office.Interop.PowerPoint.Application;
using WordApplication = Microsoft.Office.Interop.Word.Application;

using OfficeConnectTest;
using Serilog;
using Microsoft.Extensions.Configuration;

IConfigurationBuilder builder = new ConfigurationBuilder();
LogHelper.BuildConfig(builder);

ILogger? _logger = new LoggerConfiguration()
                .ReadFrom.Configuration(builder.Build())
                .Enrich.FromLogContext()
                .CreateLogger();

WordApplication? WordApplication = (WordApplication)CustomMarshal.GetActiveObject("Word.Application") ?? null;

if(WordApplication != null)
{
    WordCustomAttributes wordCustomAttributes = new WordCustomAttributes(_logger, WordApplication);
    bool boolean = wordCustomAttributes.CreateStringAttribute("StringMarcs", "Hello World.");
    boolean = wordCustomAttributes.UpdateStringAttribute("StringMarcs", "Bugger","String");
    Console.Write(wordCustomAttributes.GetStringAttribute("String"));
    boolean = wordCustomAttributes.DeleteAttribute("String");
}

Console.WriteLine("");