get excel sheet column names using exceldatareader

Question

I'm writing the C# library to read the Excel Files without any other dependencies like OLDEB(AccessDatabaseEngine) library.

So I have chosen the ExcelDataReader Library for Reading the .XLS and .XLSX files.

ExcelDataReader is perfectly working with both file formats with my local and deployment server environment.

I'm facing issue, how to get all the columns names from given Excel files?

What do you mean with column names? Like A, B, C as shown in Excel or? — Johan Appelgren, Aug 19 '17 at 12:16

score 3 · Answer 1 · answered Feb 24 '20 at 10:17

The same answer of @Kevin, but need to set ExcelDataReader to use header row as column titles. the code will look like the following:

var stream = File.Open(@"C:\temp\Book1.xlsx", FileMode.Open, FileAccess.Read);

var excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);

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

var tables = result.Tables
                   .Cast<DataTable>()
                   .Select(t => new {
                                     TableName = t.TableName,
                                     Columns = t.Columns
                                                .Cast<DataColumn>()
                                                .Select(x => x.ColumnName)
                                                .ToList()
                          });

score 1 · Answer 2 · answered Jan 12 '17 at 09:24

The easiest way to do this is to cast both the DataTableCollection and DataColumnCollection so you can use normal Linq queries over them.

For example:

var stream = File.Open(@"C:\temp\Book1.xlsx", FileMode.Open, FileAccess.Read);

var excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);

var result = excelReader.AsDataSet();

var tables = result.Tables
                   .Cast<DataTable>()
                   .Select(t => new {
                                     TableName = t.TableName,
                                     Columns = t.Columns
                                                .Cast<DataColumn>()
                                                .Select(x => x.ColumnName)
                                                .ToList()
                          });

The reason the cast is necessary is because both DataTableCollection and DataColumnCollection only implement IEnumerable and not IEnumerable<T> as they date back to the days before generics.

For this code to work your spreadsheet has to actually contain defined tables not just have data in it.

get excel sheet column names using exceldatareader

2 Answers2