Blog

How to implement a BenchmarkDotNet XLSX exporter

April 15, 2020 | 8 Minute Read

Let's implement a BenchmarkDotNet exporter, that writes a xlsx file with the benchmark results.


The BenchmarkDotNetXlsxExporter is open source and published as nuget package.

Requirements & Information gathering

Usually before I start writing any code, there should be an added value of the purposed solution (feature). So, what is the main use case/feature/user story or however you name it? The purpose of the library is simple:

  • A BenchmarkDotNet exporter that writes the results into a xlsx file

Why is that useful - isn’t the built-in csv exporter not enough? Yes and no. The xlsx format has some benefits:

  • It can hold many spreadsheets and store more information in a convenient way, instead of multiple csv files
  • Compatible with Open Office and Office 365
  • Further validation and virtualization possibilities (e.g. charts)

When the use case is identified and considered as real value, it’s common to validate if and how it is feasible to be implemented. In this case, the interface to BenchmarkDotNet is given. All exporters in BenchmarkDotNet implementing the following interface:

public interface IExporter
{
    string Name { get; }

    void ExportToLog(Summary summary, ILogger logger);

    IEnumerable<string> ExportToFiles(Summary summary, ILogger consoleLogger);
}

As you can see, it isn’t complicated to implement a valid exporter that integrates seemlessly into the BenchmarkDotNet ecosystem. The second part is writing the data as xlsx file - for this, I use the official OpenXML nuget package.

Keep in mind that, this “process” is most of the time a lot harder and takes more time as it is for this small library. But as usual, I connect simplicity and usfulness when writing blog posts.

Basic project setup

Let’s start Visual Studio 2019 and create a class library project targeting netstandard2.0. Additionaly, a test project for unit tests seems to be a good idea. Further, I add a console app as show case how the exporter could be used.

Let’s write some unit tests!

Why on earth do I start with writing a unit test, there isn’t any code to test?! Yes, it’s right. There isn’t any code. Probably you heard about TDD (Test Driven Development). In a nutshell, it’s writing tests for your (core) requirements and implementing the code until the test is successful (green). I’m using this concept when I see it fits for the project. Beside that, I’m using unit tests as playground to explore third party APIs and find out how my solution could work.

The core requirement of this library is to export the BenchmarkDotNet summary as xlsx file. That is why, it’s a good idea to start having this tested and working:

[Fact]
public void ExportToFilesTest()
{
    string file = null;
    try
    {
        var summary = TestBenchmarkRunner.GetSummary();
        var xlsxReporter = new XlsxExporter();
        var dateTime = DateTime.Now;
        var files = xlsxReporter.ExportToFiles(summary, NullLogger.Instance);
        Assert.True(files.Any());
        file = files.First();
        Assert.True(File.Exists(file));
        Assert.True(File.GetLastWriteTime(file) > dateTime);
    }
    finally
    {
        if (!(file is null))
            File.Delete(file);
    }
}

This test is more like an integration test - it runs a real benchmark and uses the output of it. Unfortunaelty I did not find a good way of “mock” the Summary class.

The method of the interface IExporter.ExportToFiles(Summary summary, ILogger consoleLogger) requires a physical file on the disk. For unit testing purpose, this isn’t optimal - imagine, there is for example an access authorization issue when the file should be written. The test would fail, even there is nothing wrong with code itself (false-positive) and this should be avoided - the test should reveal issues with the code under test, not with any of it’s dependencies. Additionally, the test execution time could be negatively influenced when the file system is used.

Further the file system brings another issue: was the file really written or is it two days old? Probably you noticed the last assertion with the last write time of the file and the File.Delete(file) within the finally block. This is to ensure, the file was really written. I know, this is still not 100% reliable but fair enough. Let me know how you solve that kind of issue.

It would make sense to add a method to the XlsxExporter class, that uses an output stream as input and is called internally by the method IEnumerable<string> ExportToFiles(Summary summary, ILogger consoleLogger):

public void CreateSpreadsheetWorkbook(Stream stream, Summary summary, ILogger consoleLogger) => { }

This solves at least the file system dependency:

[Fact]
public void CreateSpreadsheetWorkbookTest()
{
    using (var filePath = TestHelper.GetTemporaryFilePath("CreateSpreadsheetWorkbookTest.xlsx"))
    {
        var summary = BenchmarkRunnerForTests.GetSummary();

        var xlsxReporter = new XlsxExporter();
        xlsxReporter.CreateSpreadsheetWorkbook(filePath, summary, NullLogger.Instance);

        Assert.True(File.Exists(filePath));
    }
}

Note: This doesn’t replace the previous test. The interface method IExporter.ExportToFiles(...) should be tested at least to make sure there isn’t an exception thrown when calling it with valid parameters.

Details of the implementation

Let’s dive into some details… When you ever used the Open XML SDK, you probably noticed, that it is low level abstraction over the OpenXML file format. More specifically, the SpreadsheetML is relevant for this implementation. That is why, I decided to use facades to simplify the complexity within the exporter.

In general, I follow the SOLID principles as much as I can. I created an interface IXlsxExporterHandler to handle the data flow into the xlsx file. The exporter has a list of those and executes them (see code below). For instance, this is the Open-Close principle - you can always add/remove a handler of type IXlsxExporterHandler without changing the core of the XlsxExporter. This encourages also the Single-responsibility principle, one handler has exactly one purpose. Further, when you can use a class easily outside of it’s normal scope, this indicates loose/low coupling. Most of the time, loose/low coupling is a good thing to have because it increases the overall maintainability and testability of a library/product. The interface IXlsxExporterHandler also meant to be as an extensibility point for other developers to extend the xlsx output.

public void CreateSpreadsheetWorkbook(Stream stream, Summary summary, ILogger consoleLogger)
{
    // parameter checks omitted.

    using (var spreadsheetDocument = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))
    {
        var spreadsheet = new XlsxSpreadsheetDocument(spreadsheetDocument);
        spreadsheet.InitializeWorkbook();

        // "handlers" is our list to be executed:
        var handlers = _benchmarkXlsxHandlers.Any() ? _benchmarkXlsxHandlers : MinimalXlsxHandlers;
        foreach (var handler in handlers)
        {
            try
            {
                handler.Handle(spreadsheet, summary);
            }
            catch (Exception ex)
            {
                consoleLogger.WriteLineError($"Cannot execute {handler.GetType()}: {ex.ToString()}.");
            }
        }
        spreadsheet.Save();
    }
}

Summary

Hopefully, you got some (mini) insights of how I develop. It was quite fun to work on this small project.

As always, feel free to do pull requests for any kind of improvements, ask questions or just star the repository when you find it useful!

© 2020