<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

A helper class to perform CRUD on Microsoft Office documents Custom Properties - without headache

Published on
4,109 Points
109 Views
Last Modified:
Editors:
Chinmay Patel
A Tech Start-up Owner 24x7x365 :) ... Geeky, Passionate, Matrix Fan, Love To Travel And Explore Exotic Places.
How many times have you tried using Office InterOp to manage various custom properties? If you have ever tried it then you will see the article image very appropriate. After spending hours trying to solve a question I finally gave up on Office InterOp and took an easier way out - OpenXML SDK.

It is a very common ask in business applications to process Office Documents and many developers take a shortcut using OfficeInterOp - and that includes me as well (well... sometimes). While it has worked reliably for many years, using OfficeInterOp is a pain in all sorts of manners. 


First and foremost, it is not well documented. It was meant to be cryptic so others could not do what Office can do OR derive malicious code that can harm millions of Office users (take your pick).


Anyways, I have been seeing a lot of questions on EE asking how to do this OR that with Microsoft Office InterOp and in one such question I thought it would be really quick - get in and get out - to use Office InterOp to modify custom document properties. It turned out, that is not the case. 


I spent hours searching, troubleshooting, researching and banging my head to the keyboard (as I was sleepy ;) ) I finally gave it up on Office InterOp and I think within 15 minutes or less I had a complete - working solution to the problem - OpenXML SDK. 


I have used it a couple of times in past to support Dynamics 365 scenarios - it has strict sets on what can and cannot be done - and where Office InterOp does not fit the bill. So I took a sample piece of code from here: 


https://docs.microsoft.com/en-us/office/open-xml/how-to-set-a-custom-property-in-a-word-processing-document 


Which was really great but did not give a complete picture, hence I had to play around a bit and that's how this helper code came to be. Please feel free to use it in your solution and let me know what do you think.


// <copyright file="CustomPropertyHelper.cs" company="OblakConsulting.com">
// Copyright (c) 2018 All Right Reserved, 
// </copyright>
// <author>Chinmay Patel</author>
// <email>OblakConsulting</email>
// <date>2018-08-09</date>
// <summary>A Helper class to perform CRUD on Custom Document Property of Office Document using OpenXML SDK</summary>

namespace OblakConsulting
{
using DocumentFormat.OpenXml.CustomProperties;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.VariantTypes;
using System;
using System.Collections.Generic;
using System.Globalization;
using System.IO;
using System.Linq;

/// <summary>
/// Custom Property Helper is a wrapper around OpenXML SDK function that let's you perform CRUD operations on Office Documents
/// </summary>
public static class CustomPropertyHelper
{
/// <summary>
/// Property Data Types
/// </summary>
public enum PropertyTypes : int
{
YesNo,
Text,
DateTime,
NumberInteger,
NumberDouble
}

/// <summary>
/// Delete a custom property
/// </summary>
/// <param name="fileName">Complete file path to the document</param>
/// <param name="propertyName">the name of the property to be deleted</param>
/// <returns></returns>
public static bool DeleteProperty(string fileName,
string propertyName
)
{
bool returnValue = false;

using (dynamic document = OpenDocument(fileName, true))
{
CustomFilePropertiesPart customProps = document.CustomFilePropertiesPart;
if (customProps == null)
{
returnValue = false;
}

Properties customProperties = customProps.Properties;
if (customProperties != null)
{
CustomDocumentProperty property = GetProperty(customProperties, propertyName);

// Delete the property.
if (property != null)
{
property.Remove();

int pid = 2;
foreach (CustomDocumentProperty item in customProperties)
{
item.PropertyId = pid++;
}
customProperties.Save();
returnValue = true;
}
}
}
return returnValue;
}

/// <summary>
/// Lists all custom document properties from the document
/// </summary>
/// <param name="fileName">Complete file path to the document</param>
/// <returns></returns>
public static List<CustomDocumentProperty> GetProperties(string fileName)
{
List<CustomDocumentProperty> properties = new List<CustomDocumentProperty>();
dynamic document;
using (document = OpenDocument(fileName, true))
{
var customProperties = document.CustomFilePropertiesPart;
if (customProperties != null)
{
foreach (CustomDocumentProperty property in customProperties.Properties)
{
properties.Add(property);
}
}
}
if (document != null)
{
document = null;
}
return properties;
}

/// <summary>
///
/// </summary>
/// <param name="fileName">Complete file path to the document</param>
/// <param name="propertyName">Name of the custom property to be set</param>
/// <param name="propertyType">DataType of the custom property to be set</param>
/// <param name="propertyValue">Value of the custom property to be set</param>
/// <returns></returns>
public static bool SetProperty(
string fileName,
string propertyName,
PropertyTypes propertyType,
object propertyValue)
{
bool returnValue = false;

// Get the property definition
var newProperty = BuildProperty(propertyName, propertyType, propertyValue);
dynamic document = null;
using (document = OpenDocument(fileName))
{
var customProps = document.CustomFilePropertiesPart;
if (customProps == null)
{
// No custom properties? Add the part, and the collection of properties now.
customProps = document.AddCustomFilePropertiesPart();
customProps.Properties = new DocumentFormat.OpenXml.CustomProperties.Properties();
}

var customProperties = customProps.Properties;
if (customProperties != null)
{
// This will trigger an exception if the property's Name property is null, but if
// that happens, the property is damaged, and probably should raise an exception.
var property = GetProperty(customProperties, propertyName);

// Delete the property.
if (property != null)
{
property.Remove();
}

// Append the new property, and fix up all the property ID values. The PropertyId
// value must start at 2.
customProperties.AppendChild(newProperty);
int pid = 2;
foreach (CustomDocumentProperty item in customProperties)
{
item.PropertyId = pid++;
}
customProperties.Save();
returnValue = true;
}
}

if (document != null)
{
document = null;
}
return returnValue;
}

/// <summary>
///
/// </summary>
/// <param name="propertyName">The name of the property</param>
/// <param name="propertyType">DataType of the property</param>
/// <param name="propertyValue">The value of the property</param>
/// <returns></returns>
private static CustomDocumentProperty BuildProperty(string propertyName, PropertyTypes propertyType, object propertyValue)
{
var newProperty = new CustomDocumentProperty();
bool isPropertySet = false;

// Calculate the correct type.
switch (propertyType)
{
case PropertyTypes.DateTime:

// Be sure you were passed a real date, and if so, format in the correct way. The
// date/time value passed in should represent a UTC date/time.
if ((propertyValue) is DateTime)
{
newProperty.VTFileTime =
new VTFileTime(string.Format(CultureInfo.InvariantCulture, "{0:s}Z",
Convert.ToDateTime(propertyValue, CultureInfo.InvariantCulture)));
isPropertySet = true;
}

break;

case PropertyTypes.NumberInteger:
if ((propertyValue) is int)
{
newProperty.VTInt32 = new VTInt32(propertyValue.ToString());
isPropertySet = true;
}

break;

case PropertyTypes.NumberDouble:
if (propertyValue is double)
{
newProperty.VTFloat = new VTFloat(propertyValue.ToString());
isPropertySet = true;
}

break;

case PropertyTypes.Text:
newProperty.VTLPWSTR = new VTLPWSTR(propertyValue.ToString());
isPropertySet = true;
break;

case PropertyTypes.YesNo:
if (propertyValue is bool)
{
// Must be lowercase.
newProperty.VTBool = new VTBool(
Convert.ToBoolean(propertyValue, CultureInfo.InvariantCulture).ToString().ToLower());
isPropertySet = true;
}
break;
}

if (!isPropertySet)
{
// If the code was not able to convert the property to a valid value, throw an exception.
throw new InvalidDataException("propertyValue");
}

newProperty.FormatId = "{D5CDD505-2E9C-101B-9397-08002B2CF9AE}";
newProperty.Name = propertyName;
return newProperty;
}

/// <summary>
/// Get the custom property from the document, if this method fails a null is returned
/// </summary>
/// <param name="properties">Cutom Document Properties</param>
/// <param name="propertyName">Name of the property to be retrieved</param>
/// <returns></returns>
private static CustomDocumentProperty GetProperty(DocumentFormat.OpenXml.CustomProperties.Properties properties, string propertyName)
{
CustomDocumentProperty returnValue = null;

returnValue = (CustomDocumentProperty)properties.Where(p => ((CustomDocumentProperty)p).Name.Value
== propertyName).FirstOrDefault();

return returnValue;
}

/// <summary>
/// A method to open three supported Office File Types - can be extended to support other file types as well
/// </summary>
/// <param name="fileName">The complete path to the file to be opened</param>
/// <param name="openForEdit">Whether you want to open the document in read-only mode or not. Please note by default it will open the document in Edit mode.</param>
/// <returns></returns>
private static OpenXmlPackage OpenDocument(string fileName, bool openForEdit = true)
{
OpenXmlPackage package = null;
string fileExtension = Path.GetExtension(fileName).ToUpperInvariant();

// Each office document type has its own .Open method hence choose - wisely
switch (fileExtension)
{
case ".DOCX":
package = WordprocessingDocument.Open(fileName, openForEdit);
break;

case ".PPTX":
package = PresentationDocument.Open(fileName, openForEdit);
break;

case ".XLSX":
package = SpreadsheetDocument.Open(fileName, openForEdit);
break;
}

return package;
}
}
}

Here are the steps to use it (Assuming you already have a .Net project open)


  1. Solution Explorer -> Right Click on Project. -> Manage NuGet Packages...
  2. Browse... -> type openxml in search box OR DocumentFormat.OpenXML. -> In Search result you will see DocumentFormat.OpenXML (most probably the first one)-> Please do verify it is from Microsoft -> Install.
  3. It needs some time and as it installs other dependencies it might ask you to accept EULA when needed. Please accept all of them.
  4. Once you are done, just copy - paste the above code in a .cs file and save
  5. You are ready to modify custom document properties. 
  6. All the methods are self-explanatory and are well-documented.


Let me know if you run into any issues or you would like some other functionality implemented.



0
Comment
0 Comments

Featured Post

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Join & Write a Comment

In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month