CODEWITHSUNDEEP
c#exceldatareader
venkat
venkat

Reputation: 513

get excel sheet column names using exceldatareader

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?

Upvotes: 7

Views: 14317

Answers (2)

Galilo Galilo
Galilo Galilo

Reputation: 569

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

Upvotes: 4

Kevin Holditch
Kevin Holditch

Reputation: 5303

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.

Upvotes: 1

Related Questions