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.

score 1 · Accepted Answer · answered Mar 17 '23 at 08:24

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 '23 at 08:24

user8728340

646
5
7

How to use this code to read particular sheet in the excel? I am reading sheet named 'Data'. – SAT Mar 20 '23 at 13:53
@SAT The reader object has `Name` property and `NextResult()` method to navigate sheets. Could do something like: `while (reader.Name != "Data" || reader.NextResult()) {}` (untested!) – user8728340 Mar 21 '23 at 04:55

score 0 · Answer 2 · answered Mar 14 '23 at 03:09

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?.

Thanks. Unfortunately this doesn't improve speed. – SAT Mar 20 '23 at 13:52 — SAT, Mar 20 '23 at 13:52

How to read only columns from Excel using ExcelDataReader?

2 Answers2