Verify.ClosedXml

Extends Verify to allow verification of Excel documents via ClosedXML.

Converts Excel documents (xlsx) to csv for verification.

See Milestones for release notes.

Sponsors

Entity Framework Extensions

Entity Framework Extensions is a major sponsor and is proud to contribute to the development this project.

Developed using JetBrains IDEs

NuGet

• https://nuget.org/packages/Verify.ClosedXml

Usage

Enable Verify.ClosedXml

[ModuleInitializer]
public static void Initialize() =>
    VerifyClosedXml.Initialize();

^{snippet source | anchor}

Choosing what is verified

A workbook is verified as the xlsx, an info file, and a csv of each sheet. What is left out is controlled by Verify's own settings, which every Verify plugin that splits a document shares: see Leaving out what was derived and Excluding targets. Anything left out is not produced at all (the csv is not built, the workbook is not saved), so these also save work.

ExcludeDerivedTargets("csv") leaves out the csv of each sheet, keeping the workbook and its info file:

[Test]
public Task ExcludeCsv() =>
    VerifyFile("sample.xlsx")
        .ExcludeDerivedTargets("csv");

^{snippet source | anchor}

ExcludeTargets("xlsx") leaves out the workbook, keeping the info file and the csv of each sheet:

[Test]
public Task ExcludeXlsx() =>
    VerifyFile("sample.xlsx")
        .ExcludeTargets("xlsx");

^{snippet source | anchor}

Each can also be set for every test, on VerifierSettings:

[ModuleInitializer]
public static void Initialize()
{
    VerifyClosedXml.Initialize();

    // For every test: no csv, so only the workbook and its info are verified
    VerifierSettings.ExcludeDerivedTargets("csv");
}

^{snippet source | anchor}

A workbook has no pages, so the settings for the pages of a document, PageText and PagesToInclude, have no effect on it.

Input

For a given input Excel file.

Verify a file

[Test]
public Task VerifyExcel() =>
    VerifyFile("sample.xlsx");

^{snippet source | anchor}

Snapshot Result

For a given Verify, the result is 3 (or more files)

Metadata

{
  SheetNames: [
    Sheet1
  ],
  Properties: {
    Title: The Title
  },
  WorksheetCount: 1,
  DefaultFont: Arial,
  CalculateMode: Default,
  Style: {
    Font: {
      Name: Arial
    }
  }
}

^{snippet source | anchor}

CSV

One per sheet, named for the sheet: #Sheet1.verified.csv. A workbook with one sheet is no exception, so that a second sheet adds a file rather than renaming the first. A hidden sheet has a csv as any other, and is named under HiddenSheets in the info file. A sheet is a page, numbered in tab order with hidden sheets counted, so PagesToInclude leaves out the csv of a sheet. The info file still names every sheet, and the xlsx is still the whole workbook.

0,First Name,Last Name,Gender,Country,Date,Age,Id,Formula
1,Dulce,Abril,Female,United States,DateTime_1,32,1562,1594 (G2+H2)
2,Mara,Hashimoto,Female,Great Britain,DateTime_2,25,1582,1607 (G3+H3)
3,Philip,Gent,Male,France,DateTime_3,36,2587,2623 (G4+H4)
4,Kathleen,Hanner,Female,United States,DateTime_1,25,3549,3574 (G5+H5)
5,Nereida,Magwood,Female,United States,DateTime_2,58,2468,2526 (G6+H6)
6,Gaston,Brumm,Male,United States,DateTime_3,24,2554,2578 (G7+H7)

^{snippet source | anchor}

Characters of a sheet name that a file name cannot hold are replaced with -: a sheet named Q2 <draft> is #Q2 -draft-.verified.csv. Sheets that are then named the same are told apart by an index: Q1 <draft> and Q1 |draft| are #Q1 -draft-.00.verified.csv and #Q1 -draft-.01.verified.csv.

Excel file

Verify a Stream

[Test]
public Task VerifyExcelStream()
{
    var stream = new MemoryStream(File.ReadAllBytes("sample.xlsx"));
    return Verify(stream, "xlsx");
}

^{snippet source | anchor}

Verify a ClosedXML SpreadsheetDocument

[Test]
public Task XLWorkbook()
{
    using var book = new XLWorkbook();

    var sheet = book.Worksheets.Add("Basic Data");

    sheet.Cell("A1").Value = "ID";
    sheet.Cell("B1").Value = "Name";

    sheet.Cell("A2").Value = 1;
    sheet.Cell("B2").Value = "John Doe";

    sheet.Cell("A3").Value = 2;
    sheet.Cell("B3").Value = "Jane Smith";

    return Verify(book);
}

^{snippet source | anchor}

Verify a named target

An xlsx can be a named target of a verification, the way an attachment of a mail message is:

[Test]
public Task NamedTarget()
{
    var stream = new MemoryStream(File.ReadAllBytes("sample.xlsx"));
    return Verify(new Target("xlsx", stream, "Attachment1"));
}

^{snippet source | anchor}

Verify names the files relative to the target that was converted, so they take its name: #Attachment1.verified.xlsx, #Attachment1.verified.txt and #Attachment1.Sheet1.verified.csv.

Binary output across .NET frameworks

When verifying binary package output (xlsx, docx, nupkg, etc.) across multiple target frameworks (e.g. net48 and net10.0), the binary output may differ due to Deflate compression implementation differences. The XML content within entries is identical — only the compressed bytes differ. Use UniqueForRuntime to generate framework-specific verified files:

await Verify(stream, extension: "xlsx")
    .UniqueForRuntime();

See Verify Naming docs for more details.

Reviewing changes

A change to a workbook is a change to several files: the xlsx, its info file, and the csv of every sheet. Verify tells the diff tool that the csv files and the info file were derived from the xlsx, and DiffEngineViewer, which reads and draws an xlsx itself, shows them as one row and accepts them together. Other diff tools are given each file, as before.

Migrating from 1.x

Version 2 moves to the source and derived targets of Verify 33.3. The outputs parameter of Initialize is gone, and ClosedXmlOutputs is obsolete as an error, so that code naming it is pointed here. What they chose is chosen with Verify's settings, which can be set for one verification as well as for every test:

1.x	2.x
Initialize(ClosedXmlOutputs.None)	Initialize() and VerifierSettings.ExcludeDerivedTargets("csv")
Initialize(ClosedXmlOutputs.Csv)	Initialize()
Initialize(ClosedXmlOutputs.All)	Initialize()

Verify's ExcludeTargets("xlsx") already left out the workbook. With it, the workbook is now not saved at all.

The snapshots of a workbook that is verified directly, as a file, a stream or an XLWorkbook, keep their names and their content. Nothing has to be accepted again.

An xlsx that is a named target is the exception. The plugin built the name of each csv from the name of the target, and left the workbook and its info file with no name. All three are now named by Verify, relative to the target. For a test Tests.Mail whose target is an xlsx named Attachment1:

1.x	2.x
Tests.Mail.verified.xlsx	Tests.Mail#Attachment1.verified.xlsx
Tests.Mail.verified.txt	Tests.Mail#Attachment1.verified.txt
Tests.Mail#Attachment1-Sheet1.verified.csv	Tests.Mail#Attachment1.Sheet1.verified.csv

Renamed snapshots show as a new file and a pending delete. Accepting both, or running once with AutoVerify, moves a test over, and since the content of the files is unchanged, source control shows each as a rename.