How to pass multiple Excel sheets as data table when using ExcelDataReader?

Question

I would like to know how I can pass more than just one "sheet" in below code?
I am filling in some data within a web app using Selenium C# where when information on "Sheet1" are filled up, I have to move on to fill in information from "Sheet2". below code just passes in "Sheet 1". How can I enhance it so I am able to get multiple sheets right in there?

public DataTable ExcelToDataTable(string filename)    
{
    FileStream stream = File.Open(filename, FileMode.Open, FileAccess.Read);
    IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);

    DataSet result = excelReader.AsDataSet(new ExcelDataSetConfiguration()
    {
        ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
        {
            UseHeaderRow = true
        }
    });

    DataTableCollection table = result.Tables;
    DataTable resultTable = table["sheet1"]; //here I'd like to have more than just one "sheet"

    return resultTable;    
}

Answer 1

When you have one Excel workbook with many worksheets that you want use each sheet in separate time, I can suggest you to use a Singleton design pattern to store information of Excel workbook - with a proper data/query model - then read data from that singleton instance.

e.g:

// singleton object of 
public class ExcelDataContext
{
    // creating an object of ExcelDataContext
    private static ExcelDataContext instance = new ExcelDataContext();

    // no instantiated available
    private ExcelDataContext()
    {
        FileStream stream = File.Open(filename, FileMode.Open, FileAccess.Read);
        IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
    
        DataSet result = excelReader.AsDataSet(new ExcelDataSetConfiguration()
        {
            ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
            {
                UseHeaderRow = true
            }
        });

        this.Sheets = result.Tables;
    }

    // accessing to ExcelDataContext singleton
    public static ExcelDataContext GetInstance()
    {
        return instance;
    }

    // the dataset of Excel
    public DataTableCollection Sheets { get; private set; }
}

Then you can use Excel worksheets like this:

DataTable sheet1 = ExcelDataContext.GetInstance().Sheets["sheet1"];

and when you need to read data of another sheet:

DataTable otherSheet = ExcelDataContext.GetInstance().Sheets["otherSheet"];

That will not read the Excel workbook again.