0

I am having an excel sheet with multiple rows and columns which I am fetching it in a dataset in my .NET application(using C#). When I see the returned dataset through a simple SELECT query, the excel sheet is having the required columns that I want and also there are some blank columns in between them which by default is given the name F1, F2, F3 and so on. What I want to do is remove these columns and only get the required columns that I want. Thanks in advance.

Sumit Roy
  • 413
  • 2
  • 9
  • 23

2 Answers2

2

Try it with DataTable and DataView:

DataTable oldTable = dataSet.Tables[0];
DataTable newTable = oldTable.DefaultView.ToTable(false, "Only columns you want", "Only columns you want");

With all columns names dynamic you might try this:

for (int col = dataTable.Columns.Count - 1; col >= 0; col--)
{
  bool removeColumn = true;
  foreach (DataRow row in dataTable.Rows)
  {
    if (!row.IsNull(col))
    {
      removeColumn = false;
      break;
    }
  }
  if (removeColumn)
    dataTable.Columns.RemoveAt(col);
}

Or shorter LinQ version:

for (var col = dataTable.Columns.Count - 1; col >= 0; col--)
    {
        bool removeColumn = dataTable.Rows.Cast<DataRow>().All(row => row.IsNull(col));
        if (removeColumn)
            dataTable.Columns.RemoveAt(col);
    }
Yeronimo
  • 1,731
  • 15
  • 28
  • I cannot write directly the column names as I have to deal with them dynamically in my later part of the code. – Sumit Roy Apr 18 '13 at 09:49
  • Then try this: http://stackoverflow.com/questions/1766902/remove-all-columns-with-no-data-from-datatable – Yeronimo Apr 18 '13 at 09:56
  • It was close but wasn't able to solve exactly what I was trying as the column names were not null but having some values by default eg. Emp ID., Emp Name, F1, F2, Salary, F4, Acc No. I need to remove F1, F2 and F4. Also the columns are dynamic every time I upload a new excel file. – Sumit Roy Apr 18 '13 at 11:20
  • And did you manage to get it working? I have tried it and it works for me. – Yeronimo Apr 19 '13 at 08:54
  • Yes its working fine but later on I got stuck into another problem. My excel sheet contains some sub columns for some columns, and these blank sub columns are not getting removed – Sumit Roy Apr 19 '13 at 09:11
0

@Yeronimo

your answer for dynamic columns is working fine... thank you very much sir...

and some related articles which might help

Check if rows in an excel sheet are empty with asp.net

Remove Column From GridView While Converting To Excel

http://www.daniweb.com/software-development/csharp/threads/297806/how-can-i-remove-empty-columns-in-spreadsheet

Community
  • 1
  • 1
user1986144
  • 105
  • 1
  • 1
  • 9