How to remove empty cells and rows from file using ExcelDataReader?

Question

I'm using ExcelDataReader to read excel file but the file which being processed has 16k columns and 1200 records each. In between only 52 columns are filled other are empty so I want to remove empty columns with rows.

tried with :

var dataset = reader.AsDataSet(new ExcelDataSetConfiguration() {
    ConfigureDataTable = _ => new ExcelDataTableConfiguration() {
        FilterRow = rowReader => rowReader.Depth != 0
    }
});

help will be appreciated thank you

Answer 1

AFAIK, There is not any simple way to filter empty columns or rows for it my best try is:

// use ExcelDataTableConfiguration.FilterRow to filter empty rows
FilterRow = rowReader =>
{
    var hasData = false;
    for (var i = 0; i < rowReader.FieldCount; i++)
    {
        if (rowReader[i] == null || string.IsNullOrEmpty(rowReader[i].ToString()))
        {
            continue;
        }

        hasData = true;
        break;
    }

    return hasData;
},
// use ExcelDataTableConfiguration.FilterColumn to filter empty columns
FilterColumn = (rowReader, colIndex) =>
{
    var hasData = false;
    rowReader.Reset();

    // this will skip first row as it is name of column
    rowReader.Read();

    while (rowReader.Read())
    {
        if (rowReader[colIndex] == null || 
            string.IsNullOrEmpty(rowReader[colIndex].ToString()))
        {
            continue;
        }

        hasData = true;
        break;
    }

    // below codes do a trick!
    rowReader.Reset();
    rowReader.Read();

    return hasData;
}

Using IExcelDataReader arguments inside FilterRow or FilterColumn is evil!
I think in main reader and each rowReader are referenced to one object! so you should always aware of how to use them, as you can see I add a trick to make reader to be ready after using it. - HTH ;)