CODEWITHSUNDEEP
c#exceldesktop-applicationexceldatareader
Mohammed Ehab
Mohammed Ehab

Reputation: 187

C# ExcelDataReader read from specific columns only

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 enter image description here

sheet2 enter image description here

Upvotes: 4

Views: 7972

Answers (1)

Magnetron
Magnetron

Reputation: 8553

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

Upvotes: 5

Related Questions