自定义表 crud_在Microsoft Office文档“自定义属性”上执行CRUD的帮助程序类-无需担心

自定义表 crud

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. 

在业务应用程序中，处理Office文档是一个非常常见的要求，许多开发人员都使用OfficeInterOp来获取快捷方式-这也包括我在内（有时……）。 尽管它已经可靠地工作了很多年，但使用OfficeInterOp却是种种种痛苦。

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).

首先，它没有很好的记录。 它本来就很神秘，所以其他人无法做Office可以做的事情，或者衍生出可能危害数百万Office用户的恶意代码（请选择）。

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. 

无论如何，我一直在EE上看到很多问题，问如何使用Microsoft Office InterOp做到这一点，或者在其中一个问题中，我认为使用Office InterOp修改自定义文档确实非常快-进出属性。 事实证明并非如此。

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. 

我花了数小时进行搜索，故障排除，研究和敲打键盘（因为我很困；））我终于在Office InterOp上放弃了它，而且我认为在15分钟或更短的时间内，我就找到了解决问题的完整方案- 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: 

我过去已经使用过几次它来支持Dynamics 365方案-它对可以做什么和不能做什么进行了严格的设置-以及Office InterOp不适合该法案的地方。 因此，我从这里获取了一段示例代码：

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

https://docs.microsoft.com/zh-cn/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)

以下是使用它的步骤（假设您已经打开一个.Net项目）

1. Solution Explorer -> Right Click on Project. -> Manage NuGet Packages...
   解决方案资源管理器->右键单击项目。 ->管理NuGet包...
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.
   浏览...->在搜索框中输入openxml或DocumentFormat.OpenXML。 ->在搜索结果中，您将看到DocumentFormat.OpenXML（很可能是第一个）->请确保它来自Microsoft->安装。
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.
   它需要一些时间，并且在安装其他依赖项时可能会要求您在需要时接受EULA。 请接受所有。
4. Once you are done, just copy - paste the above code in a .cs file and save
   完成后，只需将以上代码复制粘贴到.cs文件中并保存
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.

让我知道您是否遇到任何问题，或者是否希望实现某些其他功能。

翻译自: https://www.experts-exchange.com/articles/33048/A-helper-class-to-perform-CRUD-on-Microsoft-Office-documents-Custom-Properties-without-headache.html

自定义表 crud