How to remove empty rows and extra columns from datatable

Question

I have a process to import data from excel to DB using ExcelReaderFactory. But when there is empty rows/column, we are facing issue. Below is my original code:

IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(fileContent);
excelReader.IsFirstRowAsColumnNames = true;
DataSet result = excelReader.AsDataSet();                    
DataTable dataTable = result.Tables[0].Rows

It creates 2 issues:

1. If there is empty rows in end, they will be there in datatable.

2. If there is empty colmns in the end, they will be there in datatable.

Is there any way to remove both empty rows and column. I can remove empty rows from datatable using below code

IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(fileContent);
excelReader.IsFirstRowAsColumnNames = true;
DataSet result = excelReader.AsDataSet();

DataTable dataTable = result.Tables[0].Rows
                    .Cast<DataRow>()
                    .Where(row => !row.ItemArray.All(field => field is DBNull ||
                                                    string.IsNullOrWhiteSpace(field as string ?? field.ToString())))
                    .CopyToDataTable();

return dataTable;

But it will not remove empty columns. Is there a better way to do it?

How to remove also empty columns?

Please find below image for reference.

Answer 1

To answer the full question, including removing blank rows, use this extension:

        public static void RemoveEmptyColumnsAndRows(this DataTable table)
        {
            foreach (var column in table.Columns.Cast<DataColumn>().ToArray())
            {
                if (table.AsEnumerable().All(dr => dr.IsNull(column) || string.IsNullOrWhiteSpace(dr[column].ToString())))
                    table.Columns.Remove(column);
            }
            foreach (var row in table.Rows.Cast<DataRow>().ToArray())
            {
                if (row.ItemArray.All(field => field is DBNull || string.IsNullOrWhiteSpace(field as string)))
                    table.Rows.Remove(row);
            }
        }

To exclude user-defined columns, add this Where before the .ToArray() on column array:

.Where(col=>col.ColumnName.StartsWith("Column"))

Answer 2

You could use this extension:

public static void RemoveEmptyColumns(this DataTable table, int columnStartIndex = 0)
{
    for (int i = table.Columns.Count - 1; i >= columnStartIndex; i--)
    {
        DataColumn col = table.Columns[i];
        if (table.AsEnumerable().All(r => r.IsNull(col) || string.IsNullOrWhiteSpace(r[col].ToString())))
            table.Columns.RemoveAt(i);
    }
}

If you want to start with a given index, pass that to the method.