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
– mjwills, Commented Jul 12, 2018 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
– J.FromHeaven, Commented Jul 12, 2018 at 10:19

shA.t · Accepted Answer · 2025-01-27 16:51:28Z

3

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.

edited Jan 27 at 16:51

answered Aug 14, 2018 at 11:28

shA.t

17k5 gold badges59 silver badges121 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

J.FromHeaven Over a year ago

"filename" is used but never declared.. I cannot just add constructor for the ExcelDataContext method

shA.t Over a year ago

filename is path of your Excel file!, something like C:\Temp\myExcel.xlsx ;).

user8728340 · Accepted Answer · 2018-07-12 10:22:19Z

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, 2018 at 10:22

user8728340

6755 silver badges8 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related