C# ExcelDataReader read from specific columns only

Question

i'm trying to get data from my excel sheet to add it into database which works perfectly but i only want the data under specific headers. Here is my config code:

var headers = new List<string>;
DataSet result = excelDataReader.AsDataSet(new ExcelDataSetConfiguration()
        {
            ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
            {
                UseHeaderRow = true,
                ReadHeaderRow = rowReader =>
                {
                    for (var i = 0; i < rowReader.FieldCount; i++)
                        headers.Add(Convert.ToString(rowReader.GetValue(i)));
                },
                FilterColumn = (columnReader, columnIndex) =>
                    headers.IndexOf("LOCATION") == columnIndex
                    || headers.IndexOf("PARENT") == columnIndex
                    || headers.IndexOf("DESCRIPTION") == columnIndex
            }
        });

LOCATION,PARENT and DESCRIPTION are the columns header names. and here is the code i'm using to add the data to database

foreach (DataTable table in result.Tables)
        {
            foreach (DataRow row in table.Rows)
            {

                if (!existedLocations.Any(l => l.ShortCode?.Replace(" ", String.Empty) == row[0].ToString().Replace(" ", String.Empty)))
                {
                    addtable.Name = addtable.NameAr = row[2].ToString().Substring(row[2].ToString().LastIndexOf(',') + 1);
                    addtable.ParentLocation = connection.Locations.FirstOrDefault(l => l.ShortCode == row[1].ToString()).Id;
                    addtable.LocationType = (int)LocationsTypes.Area;
                    addtable.ShortCode = row[0].ToString();
                    addtable.Category = (int)LocationsCategory.indoor;
                    addtable.IsActive = 1;
                    addtable.Isdeleted = 0;
                    existedLocations.Add(addtable);
                    connection.Locations.InsertOnSubmit(addtable);
                    connection.SubmitChanges();
                    addtable = new Location();
                }
            }
        }

the sheets headers is defined as following sheet1

sheet2

So, what's the problem? Your `result` don't contain only the columns you provided in `FilterColumn`? — Magnetron, Mar 15 '19 at 15:39
It's the other way around, you return true if you **want** the column, and false if you don't. [Check the Github page](https://github.com/ExcelDataReader/ExcelDataReader#asdataset-configuration-options). **FilterColumn**: _Gets or sets a callback to determine whether to include the specific column in the DataTable. Called once per column after reading the headers._ — Magnetron, Mar 15 '19 at 15:45

Magnetron · Accepted Answer · 2019-03-15T16:24:12.723

5

Well, you have two sheets, with the same headers but in different position. Your code is adding the headers of the first sheet to the list and then the ones from the second sheet. So, when you look for the headers to filter in the second sheet, you get the indexes from the first one, as IndexOf will get the first occurence.

Also, it appears that you're only using headers list to filter the columns, so you can simplify:

var result = reader.AsDataSet(new ExcelDataSetConfiguration()
{
    ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
    {
        UseHeaderRow = true,

        FilterColumn = (columnReader, columnIndex) =>
        {
            string header = columnReader.GetString(columnIndex);
            return (header == "LOCATION" || 
                    header == "PARENT" || 
                    header == "DESCRIPTION"
                   );                        
        }           
    }
});

edited Mar 15 '19 at 16:24

answered Mar 15 '19 at 15:48

Magnetron

7,495
1
25
41

it is still getting columns that does not have specified headers @Magnetron – Mohammed Ehab Mar 15 '19 at 15:57
@MohammedEhab That's strange because it worked for me. You're passing the headers you want or the ones that you don't want? Also, the header names are correct, including the case? Are the headers in the first line of the sheet? – Magnetron Mar 15 '19 at 16:12
yes they are but i think there is a problem with Index as in each sheet the ordering of headers is different – Mohammed Ehab Mar 15 '19 at 16:17
@MohammedEhab Ok, so you have multiple sheets with the same headers but in different order, thats the problem. Check my edit, my second solution should work for you. I'll make another edit just to make more clear and remove the pieces that don't work. – Magnetron Mar 15 '19 at 16:19
1

worked like a charm i just added `header.ToLower() == "location" || header.ToLower() == "parent" ||header.ToLower() == "description"` to have more control over the column names – Mohammed Ehab Mar 15 '19 at 16:42
Hey, if you try this method and you get an exception thrown due to the `columnReader.GetString(columnIndex);` Try: `Convert.ToString(columnReader.GetValue(columnIndex));` – R_Shobu May 15 '20 at 15:40

C# ExcelDataReader read from specific columns only

1 Answers1