CODEWITHSUNDEEP
c#linqdatatable
Missy
Missy

Reputation: 1368

Specify Which Columns Are In A Datatable

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.

Upvotes: 1

Views: 79

Answers (2)

Robert Synoradzki
Robert Synoradzki

Reputation: 2046

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.

Upvotes: 2

TheVillageIdiot
TheVillageIdiot

Reputation: 40537

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.

Upvotes: 2

Related Questions