Specify Which Columns Are In A Datatable

Question

I have a stored procedure that returns extra columns. I don't have control of the stored procedure. I would like to use the following form to generate my worksheet:

ws.Cells.LoadFromDataTable(dt, true, OfficeOpenXml.Table.TableStyles.Light8)

How can I output just the columns I want?

I tried to figure out a Linq query but the problem is that the column names are inaccessible so I can't specify which columns I would like.

        SqlConnection cx = new SqlConnection(util.GetConnectionString());
        SqlCommand cmd = new SqlCommand("StoredRept", cx);
        cmd.CommandType =  CommandType.StoredProcedure;
        SqlDataAdapter ta = new SqlDataAdapter();
        ta.SelectCommand = cmd;
        DataTable dt = new DataTable();
        ta.Fill(dt);


        FileInfo newFile = new FileInfo("c:\temp");
        ExcelPackage epp = new ExcelPackage(newFile);
        var ws = epp.Workbook.Worksheets.Add("WS");

        // here is where I would like to copy certain columns off into another data table but I haven't been able to figure out how  

        ws.Cells.LoadFromDataTable(dt, true, OfficeOpenXml.Table.TableStyles.Light8);

Any help would be greatly appreciated.

Dump those `DataTable`s! But not like LINQPad `.Dump()`. Just dump. Don't use. — Robert Synoradzki, Sep 18 '17 at 23:27

score 2 · Accepted Answer · answered Sep 18 '17 at 22:57

If removing some columns from the table is the only problem, that is easy to resolve. Try something like this:

var dt = new DataTable();
dt.Columns.Add("First", typeof(string));
dt.Columns.Add("Second", typeof(string));
dt.Columns.Add("Third", typeof(string));
dt.Columns.Add("Fourth", typeof(string));
dt.Columns.Add("Fifth", typeof(string));

for (var i = 1; i < 6; i++)
{
    dt.Rows.Add($"First {i}", $"Second {i}", $"Third {i}",$"Fourth {i}",$"Fifth {i}");
}

//dt.Dump();//If you have linqpad this is handy to dump table to output

//REMOVE THE COLUMNS. 
dt.Columns.RemoveAt(1);
dt.Columns.RemoveAt(2); 

//dt.Dump();//again in linqpad this dumps table with remaining 3 columns

You can use following method to find column by name and remove it:

var col=dt.Columns["Second"];
dt.Columns.Remove(col);

Here is linq query to get list with desired columns.

var lq = (from DataRow r in dt.Rows
         select new { First = r[0], Second=r[1], Third=r["Fifth"]}
         ).ToList();
lq.Dump();

Note how you can use both column index or name to get value from row object.

Robert Synoradzki · Answer 2 · 2017-09-19T00:48:39.907

2

Can you load your spreadsheet with IEnumerable<T> instead of DataTable? If so:

var someColumns = dt
    .AsEnumerable()
    .Select(row => new
    {
        When    = row.Field<DateTime>("When"),
        What    = row.Field<string>("What"),
        HowMany = row.Field<int>("ColumnNameInDatabase"),
    });

If you need DataTable, you can convert IEnumerable to it via any method, like this or this.

BUT

Frankly, I answered your problem, but IMO you've a greater problem at hand, which is using those antiquated DataTable objects instead of IEnumerable<T>. If you consider, for example, Dapper library, you could do this:

util.GetConnectionString()
    .Query("StoredRept", commandType: CommandType.StoredProcedure) // Extension method from Dapper
    .Select(dyn => new
    {
        When    = (DateTime)dyn.When,
        What    = (string)dyn.What,
        HowMany = (int)dyn.ColumnNameInDatabase
    })

I see you're using EPPlus library for Excel manipulation. It can load IEnumerable<T> data, you are not restricted to DataTable.

edited Sep 19 '17 at 00:48

answered Sep 18 '17 at 23:04

Robert Synoradzki

1,766
14
20

I'll have to look at Dapper :) Thank you so much for your help! – Missy Sep 18 '17 at 23:43
Wait -- Dapper can divine the field names from stored procedures? That is so amazing!!! Delayed reaction there :) – Missy Sep 19 '17 at 00:13
1

That's not exactly it - no framework can divine ;) `Query` downloads an `IEnumerable` of `dynamic` objects, meaning that is the field exists (= the column name in stored procedure exists with that exact name), it will work, but if it doesn't, you'll get an exception - and also you don't get the Intellisense. That's how `dynamic` works in .NET, this is not Dapper specific. Please also note that there are overloads for `Query` - `Query`, where `T` is a class defining columns which will get returned from your stored procedure. You get Intellisense, but need to maintain and update this class. – Robert Synoradzki Sep 19 '17 at 00:54

Specify Which Columns Are In A Datatable

2 Answers2

BUT