Verify.Sylvan.Data.Excel

Code provided by Cédric Luthi https://github.com/0xced

Extends Verify to allow verification of Excel documents via Sylvan.Data.Excel.

Converts Excel documents (xls, xlsb and xlsx) to csv for verification.

Verifying a workbook produces:

• The workbook itself, as .verified.xlsx, .verified.xlsb or .verified.xls. An xlsx or an xlsb is a zip package, so it is passed through DeterministicIoPackaging, which removes the timestamps and the entry order that differ from one save to the next. This can be omitted with ExcludeTargets.
• An info file, .verified.txt, with the names of its sheets.
• A csv of each sheet, named after the sheet: #Sheet1.verified.csv, #Sheet2.verified.csv, etc. A hidden sheet has a csv as any other, and is named under HiddenSheets in the info file. Options passed with ExcelDataReaderOptions are used as they are, so with those a hidden sheet is read only when ReadHiddenWorksheets is set. A reader that is passed in directly reads what it was created to read, and does not say which sheets are hidden. A sheet is a page, numbered by where it is among all the sheets of the workbook with hidden sheets counted, so PagesToInclude leaves out the csv of a sheet. A hidden sheet is counted whether or not the reader is asked to read it.

An ExcelDataReader passed to a verification has no workbook to hand, so only the info file and the csv files are produced for it.

The names of the csv files, and the setting that leaves them out, are Verify's: the same for every plugin that splits a document into derived targets.

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.Sylvan.Data.Excel

Usage

Enable Verify.Sylvan.Data.Excel

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

^{snippet source | anchor}

Excel

Verify a file

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

^{snippet source | anchor}

Verify a Stream

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

^{snippet source | anchor}

Verify a ExcelDataReader

[Test]
public Task VerifyExcelDataReader()
{
    using var stream = File.OpenRead("sample.xlsx");
    using var reader = ExcelDataReader.Create(stream, ExcelWorkbookType.ExcelXml);
    return Verify(reader);
}

^{snippet source | anchor}

Example snapshot

The test Samples.VerifyExcel verifies a workbook with one sheet, named Sheet1. The names of its sheets are in Samples.VerifyExcel.verified.txt:

{
  SheetNames: [
    Sheet1
  ]
}

^{snippet source | anchor}

And the sheet is in Samples.VerifyExcel#Sheet1.verified.csv:

0,First Name,Last Name,Gender,Country,Date,Age,Id,Formula
1,Dulce,Abril,Female,United States,2017-10-15,32,1562,1594
2,Mara,Hashimoto,Female,Great Britain,2016-08-16,25,1582,1607
3,Philip,Gent,Male,France,2015-05-21,36,2587,2623
4,Kathleen,Hanner,Female,United States,2017-10-15,25,3549,3574
5,Nereida,Magwood,Female,United States,2016-08-16,58,2468,2526
6,Gaston,Brumm,Male,United States,2015-05-21,24,2554,2578

^{snippet source | anchor}

The csv is named after its sheet even when the workbook has no other, so that a second sheet adds a file rather than renaming the first.

CsvDataWriterOptions

Used to configure options for writing CSV data.

[Test]
public Task CsvDataWriterOptions()
{
    using var stream = File.OpenRead("sample.xlsx");
    var options = new CsvDataWriterOptions
    {
        Delimiter = '\t',
        Quote = '"',
    };

    return Verify(stream)
        .CsvDataWriterOptions(options);
}

^{snippet source | anchor}

Choosing what is verified

The csv files are what was derived from the workbook, so Verify's ExcludeDerivedTargets leaves them out, and only the sheet names are verified. The sheets are then not read:

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

^{snippet source | anchor}

VerifierSettings.ExcludeDerivedTargets("csv") does the same for every test.

ExcludeTargets("xlsx") leaves out the workbook instead, keeping the info file and the csv files. It is then not built.

A workbook has no pages, so the other settings for paged documents, PageText and PagesToInclude, have no effect on it.

Reviewing changes

A change to a workbook is a change to several files: the workbook, the csv of each sheet that differs, and the info file when a sheet is added, removed or renamed. Verify tells the diff tool that the csv files and the info file were derived from the workbook, and DiffEngineViewer, which draws a workbook 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 API of this plugin is unchanged. What differs is that the workbook is now a snapshot too, and the name of a csv file, which Verify now decides, as it does for every plugin that has moved:

	1.x	2.x
The workbook	Not a snapshot	Tests.Report.verified.xlsx
A workbook with one sheet	Tests.Report.verified.csv	Tests.Report#Sheet1.verified.csv
A workbook with several sheets	Tests.Report#Sheet1.verified.csv	Unchanged
The info file	Tests.Report.verified.txt	Unchanged

VerifierSettings.ExcludeTargets("xlsx") keeps the workbook out of the snapshots, as it was in 1.x.

A renamed snapshot shows as a new file and a pending delete. Accepting both, or running once with AutoVerify, moves a test over. The content of the csv is unchanged, so source control shows it as a rename.

Two cases that are less common also differ:

• A workbook that is itself a named target has its csv files named relative to it: one named Attachment1 has #Attachment1.Sheet1.verified.csv, where 1.x did not use the name of the workbook. Passed to a verification as a Target, its info file takes the name as well: #Attachment1.verified.txt.
• An ExcelDataReader that has been moved past its last sheet has no csv. In 1.x it had one, holding only a header row.