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;    
}

Could you run the code multiple times (in a loop), once per sheet? — mjwills, Jul 12 '18 at 10:11
Yes, I can run the code- What I am doing right after the above code, is to get data into a c# collection, then I am able to read information from Sheet 1, which passes in some user information like name and password. Then from My Web app, I have to move to a new page (window) then assign complementary information to the same user, and those information are available on sheet 2.Not sure, whether I have answered your question? — J.FromHeaven, Jul 12 '18 at 10:19

score 2 · Answer 1 · answered Jul 12 '18 at 10:22

2

Suppose the last three lines of code could be swapped with this:

for (int i = 1; i < result.Tables.Count - 1; i++) 
    result.Tables[i].Merge(result.Tables[0]);
return result.Tables[0];

Source: How to combine two DataSet Tables into one

answered Jul 12 '18 at 10:22

user8728340

646
5
7

score 2 · Accepted Answer · answered Aug 14 '18 at 11:28

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.

"filename" is used but never declared.. I cannot just add constructor for the ExcelDataContext method — J.FromHeaven, Aug 20 '18 at 02:23
`filename` is path of your Excel file!, something like `C:\Temp\myExcel.xlsx` ;). — shA.t, Aug 20 '18 at 04:01

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

2 Answers2