Ignore specific columns from loading into the DataSet using ExcelDataReader?

Question

I'm using ExcelDataReader to load the data into an DataSet, which will be eventually be loaded into an DataGrid in WPF. I have specific columns with headers as SLNO and FY which I don't want to include. How can I ignore those columns using FilterColumn mentioned here?

Ali Tooshmalani · Answer 1 · 2018-06-24T09:39:55.020

1

If you want to filter these columns at the time of the display, use the following code :

    gridview.Columns["ColumnName"].Visible = false;

Or :

   DataView view = new DataView(DataSet.table[0]);
   DataTable table2 = view.ToTable(false, "FirstColumn", "SecondColumn",      "ThirdColumn");

   GridDataView.DataSource = Table2;

edited Jun 24 '18 at 09:39

answered Jun 24 '18 at 09:32

Ali Tooshmalani

241
2
7

The first line of code you posted is not for WPF it seems. Anyways, I used `DataTable.Columns.Remove["ColumnName"]` for now. Actually I don't even want those columns in my current application, they are for some other purposes. – Dante123 Jun 25 '18 at 07:50

score 0 · Accepted Answer · answered Jun 25 '18 at 09:08

0

You could handle the AutoGeneratingColumn event:

private void DataGrid_AutoGeneratingColumn(object sender, DataGridAutoGeneratingColumnEventArgs e)
{
    e.Cancel = e.PropertyName == "SLNO" || e.PropertyName == "FY";
}

answered Jun 25 '18 at 09:08

mm8

163,881
10
57
88

I know it works. But my question was different. Thought why to even add them in the first place i.e. into the `DataSet`. Maybe loading performance can be improved a tiny bit lol. – Dante123 Jun 25 '18 at 11:03
Seems like you can only use the FilterColumn property to filter out columns based on their index: https://github.com/ExcelDataReader/ExcelDataReader/blob/e6000f5578c500d4e3818ae3d2b464bf40305372/test/ExcelDataReader.Tests/ExcelBinaryReaderTest.cs – mm8 Jun 25 '18 at 11:26
It's okay. The current solution is working fine. Though can you post sample code? – Dante123 Jun 25 '18 at 11:35
Filtering out columns using `FilterColumn` based on their index. I saw a post [here](https://stackoverflow.com/questions/49908729/exceldatareader-filtercolumn-usage) but didn't quite understand that. – Dante123 Jun 25 '18 at 11:43
See the link in my comment. – mm8 Jun 25 '18 at 11:43

score 0 · Answer 3 · answered Jan 19 '19 at 02:21

This is how FilterColumn works

using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
{
    List<string> skipColumns = new List<string> { "SLNO", "FY" };
    using (var reader = ExcelReaderFactory.CreateReader(stream))
    {
        var result = reader.AsDataSet(new ExcelDataSetConfiguration()
        {
            ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
            {
                UseHeaderRow = true,
                FilterColumn = (rowReader, columnIndex) =>
                {
                    return !skipColumns.Contains((rowReader[columnIndex].ToString()));
                }
            }
        });
    }
}

Ignore specific columns from loading into the DataSet using ExcelDataReader?

3 Answers3