CODEWITHSUNDEEP
asp.netmstestmemorystreamclosedxml
Luciano Umpierrez
Luciano Umpierrez

Reputation: 1

Why do I get the error "Cannot access a disposed object" when using a stream

I am trying to read an xlsx file using CloseXlm so I created the below ReadFile method. In order to test it, I've created a test method using MSTest and another method that creates an xlsx file and save it into a memory stream that is returned. That same memory stream is then used by the test to check if the functionality is working. However, I keep getting the error "Cannot access a disposed object" and I can't find the reason why.

ReadFile method:

public async Task<IEnumerable<string[]>> ReadFile(Stream fileReader)
    {
        using var workbook = new XLWorkbook(fileReader);
        var worksheets = workbook.Worksheets;
        return worksheets.SelectMany(worksheet => 
            worksheet.RowsUsed()
                .Skip(1)
                .Select(row => ExtractRowData(row)));
    }

    
    private string[] ExtractRowData(IXLRow row)
    {
        return row.Cells()
            .Where(cell => !string.IsNullOrWhiteSpace(cell.Value.ToString()))
            .Select(cell => cell.Value.ToString())
            .ToArray();
    }

Test class

[TestClass]
public class ReadXlsxFile
{
    private XlsxFileReaderStrategy _excelReader;

    [TestInitialize]
    public void SetUp()
    {
        _excelReader = new XlsxFileReaderStrategy();
    }

    [TestMethod]
    public async Task ReadExcelFile_MultipleSheets_ReturnsCorrectData()
    {
        // Arrange
        var rowDataSheet1 = new List<string[]>
        {
            new[] { "Header1", "Header2", "Header3" },
            new[] { "Data1", "Data2", "Data3" },
            new[] { "Data4", "Data5", "Data6" }
        };
        
        var rowDataSheet2 = new List<string[]>
        {
            new[] { "HeaderA", "HeaderB", "HeaderC" },
            new[] { "Info1", "Info2", "Info3" },
            new[] { "Info4", "Info5", "Info6" }
        };

        // Crear archivo Excel en MemoryStream
        using var memoryStream = CreateExcelFileWithData(rowDataSheet1);

        // Act
        memoryStream.Seek(0, SeekOrigin.Begin);
        var result = await _excelReader.ReadFile(memoryStream);

        // Assert
        Assert.AreEqual(3, result.Count()); // 3 filas en la primera hoja
        var row1 = result.ElementAt(0);
        Assert.AreEqual("Data1", row1[0]);
        Assert.AreEqual("Data2", row1[1]);
        Assert.AreEqual("Data3", row1[2]);
    }

    private MemoryStream CreateExcelFileWithData(List<string[]> data)
    {
        // Crear el archivo Excel en memoria
        var workbook = new XLWorkbook();
        
        // Crear una hoja llamada "Sheet1"
        var worksheet = workbook.AddWorksheet("Sheet1");

        // Escribir los datos en las celdas
        for (int row = 0; row < data.Count; row++)
        {
            for (int col = 0; col < data[row].Length; col++)
            {
                worksheet.Cell(row + 1, col + 1).Value = data[row][col];
            }
        }

        // Crear un MemoryStream para almacenar el archivo Excel en memoria
        var memoryStream = new MemoryStream();
        workbook.SaveAs(memoryStream); // Guardar en el MemoryStream
        memoryStream.Seek(0, SeekOrigin.Begin); // Reposicionar el flujo al principio

        // No se llama a Dispose() aquí, porque el flujo se gestionará en el test
        return memoryStream;
    }
}

Upvotes: 0

Views: 57

Answers (1)

Alexander Dimitrov
Alexander Dimitrov

Reputation: 131

SelectMany returns an IEnumerable, which is not "calculated" until you enumerate it. The first time this happens is when you call result.Count() in your assert statement. By then, the XLWorkbook is already disposed because of the using statement in ReadFile, resulting in an ObjectDisposedException. For a solution, you have two options. Either pass the XLWorkbook into the ReadFile method, thus avoiding it being disposed prematurely, or you materialize the enumeration before you leave ReadFile:

public async Task<IEnumerable<string[]>> ReadFile(Stream fileReader)
{
    using var workbook = new XLWorkbook(fileReader);
    var worksheets = workbook.Worksheets;
    return worksheets.SelectMany(worksheet => 
        worksheet.RowsUsed()
            .Skip(1)
            .Select(row => ExtractRowData(row)))
        .ToList(); // <---
}

The second solution seems to be the clearer solution in your case. You can also use other "materializing" methods like ToArray() as well.

A side note - your ReadFile method doesn't seem to have any asynchronous calls, so you should probably convert it to a regular method.

Upvotes: 0

Related Questions