Write hidden information (like software name & version) in Excel file using OpenXML

0
votes

My c# software exports to Excel files using OpenXML. I am reading and editing these files in the software even after user edits them in Excel. I want to add the software name and version somewhere, so, when the software opens the file, recognize it. Is there any property in the Workbook as document information, comments, or only visible to OpenXML not Excel?

For now the best workaround is to save this information in an excel sheet and hide the sheet. The point is this information should not be accidentally deleted by the user.

2
c#excelopenxmlopenxml-sdk
Put them in the document's Custom Properties? See here – canton7

2 Answers

1
votes

I'd use a Custom XML Part.

The content of a Custom XML Part is only accessible through the object model or by unzipping the file "package". So the information can't be altered accidentally or even viewed without extra effort. And if you're processing the file via Open XML, accessing a Custom XML Part is reasonably straight-forward.

(The user can see Document Properties (suggested in a comment) via the UI, in the File/Info tab.)

0
votes

Set a custom property for excel document via Openxml

Find method SetCustomProperty() at the bottom of page. This function is written for Word document so change open-file line to below one for Excel document

using (var document = SpreadsheetDocument.Open(fileName, true))

And you are good to add any property to your file.

How to Hide properties

The properties will be visible in Excel through File-> Info -> Properties -> Advanced Properties window. Users will be able to delete them there. If this is not desired, a property won't be visible in excel if instead of this unique id

newProp.FormatId = "{D5CDD505-2E9C-101B-9397-08002B2CF9AE}";

another one is used:

newProp.FormatId = Guid.NewGuid().ToString("B");

Note: for saving string use VTLPWSTR type. Do not use type VTBString along with the unique ID given above as Excel automatically deletes your property when you edit it in there (just by experience, I don't know why!).

How to read the properties?

You saved your file. Then, open it again and loop over all properties

foreach (CustomDocumentProperty property in document.CustomFilePropertiesPart.Properties)
{
    if (property.Name.Value == nameof(Product) &&
        property.VTBString.Text == Product)
        return true;
}

where Product is string property holds the name of software, and VTBString is used to save value of Product. As many properties as desired can be saved and read with this method.