ExcelDocumentPropertiesTests.cs

using DocumentFormat.OpenXml.CustomProperties;

[TestFixture]
public class ExcelDocumentPropertiesTests
{
    const string formatId = "{D5CDD505-2E9C-101B-9397-08002B2CF9AE}";

    [Test]
    public Task DocumentProperties()
    {
        using var document = CreateDocument();
        return Verify(document);
    }

    static SpreadsheetDocument CreateDocument()
    {
        var document = SpreadsheetDocument.Create(new MemoryStream(), SpreadsheetDocumentType.Workbook);

        var wbPart = document.AddWorkbookPart();
        wbPart.Workbook = new(new Sheets());

        var wsPart = wbPart.AddNewPart<WorksheetPart>();
        wsPart.Worksheet = new(
            new SheetData(
                new Row(
                    new Cell
                    {
                        DataType = CellValues.InlineString,
                        InlineString = new(new Text("Header"))
                    })
                {
                    RowIndex = 1u
                }));

        var sheets = wbPart.Workbook.GetFirstChild<Sheets>()!;
        sheets.Append(new Sheet
        {
            Id = wbPart.GetIdOfPart(wsPart),
            SheetId = 1,
            Name = "Sheet1"
        });

        // Core properties (docProps/core.xml). Written as an explicit part rather than via
        // PackageProperties so the values survive the clone into the deterministic xlsx target —
        // the intrinsic package core-property store does not.
        var corePart = document.AddCoreFilePropertiesPart();
        using (var coreStream = corePart.GetStream(FileMode.Create))
        using (var writer = new StreamWriter(coreStream))
        {
            writer.Write(
                """
                <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
                <cp:coreProperties xmlns:cp="http://schemas.openxmlformats.org/package/2006/metadata/core-properties" xmlns:dc="http://purl.org/dc/elements/1.1/">
                  <dc:title>Q1 staff snapshot</dc:title>
                  <dc:subject>Headcount</dc:subject>
                  <cp:keywords>staff, q1</cp:keywords>
                  <dc:description>Generated by Excelsior</dc:description>
                  <cp:category>HR</cp:category>
                  <cp:contentStatus>Final</cp:contentStatus>
                </cp:coreProperties>
                """);
        }

        // Extended properties (docProps/app.xml)
        var extendedPart = document.AddExtendedFilePropertiesPart();
        extendedPart.Properties = new()
        {
            Company = new("Papyrine"),
            Manager = new("Jane")
        };

        // Custom properties (docProps/custom.xml)
        var customPart = document.AddCustomFilePropertiesPart();
        customPart.Properties = new(
            new CustomDocumentProperty
            {
                FormatId = formatId,
                PropertyId = 2,
                Name = "Project",
                VTLPWSTR = new("Excelsior")
            },
            new CustomDocumentProperty
            {
                FormatId = formatId,
                PropertyId = 3,
                Name = "Reviewed",
                VTBool = new("true")
            },
            new CustomDocumentProperty
            {
                FormatId = formatId,
                PropertyId = 4,
                Name = "Revision",
                VTInt32 = new("3")
            });

        return document;
    }
}