Unit test by constructing Excel content in memory without reading physical file

[PingPongSet]

I need to unit test the method below that reads Excel content using ExcelReader.

I wonder if it is possible to construct Excel content in MemoryStreamwithout reading physical file, and pass it to the method?

public void ReadExcelContent(Stream content)
        {
            IExcelDataReader excelReader = ExcelReaderFactory.CreateReader(content);

            while (excelReader.Read())
            {
                 var data = Convert.ToInt32(excelReader.GetValue(0));
               //..
              }

            excelReader.NextResult();

            while (excelReader.Read())
            {
                 var data = Convert.ToInt32(excelReader.GetValue(0));
               //..
              }

        }

[appel1]

Afaik it should work. I think the only requirement is that for .xls files the stream must be seekable.

[PingPongSet]

my question is that how to construct the excel file in memory without reading file. Any code example or link would be appreciated.

[andersnm]

@PingPongSet Hi,

You can either embed your excel file as a resource in the project, or you can use another library like ClosedXML or EPPlus to create excel files programmatically. I'm certain both ClosedXML and EPPlus allow you to save into a (Memory)Stream which you can pass to ExcelDataReader.

Another approach is to not run ExcelDataReader in your tests, but instead populate and test the DataSet that ExcelDataReader would have returned, or even mock the IExcelDataReader interface and implement to return the data you want to test if using the reader interface.

However, exactly how to do either of these is outside the scope of ExcelDataReader (it would take some effort to put together)

[PingPongSet]

Hi @andersnm,

Custom class that references IExcelDataReader has been abstracted.

Good to know ClosedXML or EPPlus. This allows me to perform unit test. Alternatively, I might go for integration test reading the file as a final option.