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?

Cami

What do you mean with column names? Like A, B, C as shown in Excel or? – Replacement

@venkat: Have you found the solution for this? – Shortage

Answer 1

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

Tolyl

If you use CreateReader(stream) instead CreateOpenXmlReader(stream), you will get better compatibility with the different Excel formats. By using it, you are letting the ExcelReaderFactory object select the proper underneath method (either CreateOpenXmlReader or CreateBinaryReader). – Donatelli

Answer 2

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.

Weltanschauung