How to read only columns from Excel using ExcelDataReader?

Question

I am using below code to read column names from Excel sheet. It's working fine. But it loads entire sheet data into datatable. It takes a lot of time. Is there any way we can read only column names and not load whole sheet?

   using (var stream = File.Open(strDoc, FileMode.Open, FileAccess.Read))
   { 

        using (var reader = ExcelReaderFactory.CreateReader(stream))
        {
            dt = reader.AsDataSet(new ExcelDataSetConfiguration()
            {
                ConfigureDataTable = (tablereader) => new ExcelDataTableConfiguration() { UseHeaderRow = true }
            }).Tables["Data"];
        }
    }

    foreach (var col in dt.Columns)
    {
        columnNamesList.Add(col.ToString().Trim());
    }

'Data' is the sheet name.

user8728340 · Accepted Answer · 2023-03-17 08:24:44Z

1

The fastest is to read the first row without the overhead of AsDataSet:

for (int i = 0; i < reader.FieldCount; i++) {
    columnNamesList.Add(reader.GetString(i));
}

answered Mar 17, 2023 at 8:24

user8728340

6755 silver badges8 bronze badges

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

2 Comments

SAT Over a year ago

How to use this code to read particular sheet in the excel? I am reading sheet named 'Data'.

user8728340 Over a year ago

@SAT The reader object has Name property and NextResult() method to navigate sheets. Could do something like: while (reader.Name != "Data" || reader.NextResult()) {} (untested!)

Chen · Accepted Answer · 2023-03-14 03:09:46Z

0

You can use FilterRow to skip rows. For example:

dt = reader.AsDataSet(new ExcelDataSetConfiguration()
{
     ConfigureDataTable = (tablereader) => new ExcelDataTableConfiguration() { UseHeaderRow = true, FilterRow = rowReader => rowReader.Depth < 1 }
}).Tables["Data"];

At this point dt.Rows.Count will be 0.

Not sure if this will speed up the response, but it prevents rows from the sheet from being inserted into the DataTable.

Helpful links:

Using ExcelDataReader to read Excel data starting from a particular cell.

How skip rows and cells to create the dataset?.

answered Mar 14, 2023 at 3:09

Chen

5,2721 gold badge4 silver badges12 bronze badges

1 Comment

SAT Over a year ago

Thanks. Unfortunately this doesn't improve speed.

Collectives™ on Stack Overflow

How to read only columns from Excel using ExcelDataReader?

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related