Export Model to DataTable

Question

I want to convert my Model data to DataSet or DataTable (to export in excel format)

db.EMPs.ToList()

db is DataContext , EMPs is dataclass .

How to export this list to DataTable, I can export rows to excel but how to access column name from header (column name should not be added manually in DataTable)

Salah Akbari · Accepted Answer · 2017-03-02T09:25:34.027

6

You can use ToDataTable extension method but you need to install MoreLinq first. To install MoreLINQ, run the following command in the Package Manager Console:

PM> Install-Package morelinq

Then add the following line to your using directives:

using MoreLinq;

And finally you can use ToDataTable extension method:

DataTable s = db.EMPs.ToDataTable();

edited Mar 02 '17 at 09:25

answered Mar 02 '17 at 08:58

Salah Akbari

39,330
10
79
109

Andre Odendaal · Answer 2 · 2017-03-02T10:03:33.123

2

To quickly create a file that can be read in Excel you could map the contents to a comma-separated value list using LINQ and then save the array to a file stream.

var records = db.EMP
                .ToList()
                .Select(record => $"\"{record.stringField}\",{record.numberField}")
                .ToArray();
File.WriteAllLines("file.csv", records);

edited Mar 02 '17 at 10:03

answered Mar 02 '17 at 09:14

Andre Odendaal

557
6
19

What does stringField and numberField means here ? – Saurabh Mar 02 '17 at 09:18
It shows
LINQ to Entities does not recognize the method 'System.String Format(System.String, System.Object, System.Object)' method, and this method cannot be translated into a store expression. – Saurabh Mar 02 '17 at 09:22
It was to indicate fields in your table that are strings or numbers. strings can be enclosed in quotes to easier reading by Excel. – Andre Odendaal Mar 02 '17 at 09:45
Ah! The exception is because you cannot pass string formatting to Entity Framework. I've updated the code to retrieve the data and then convert it to a string array. – Andre Odendaal Mar 02 '17 at 10:01

score 0 · Answer 3 · edited Mar 02 '17 at 10:27

Using MoreLinq is the best way to convert a class to DataTable as already answered by S.Akbari Below is another way I found to accomplish this by using System.Reflection

List<EMP> list= db.EMPs.ToList();
DataTable dt = new DataTable();       
PropertyInfo[] Props = typeof(EMP).GetProperties(BindingFlags.Public | BindingFlags.Instance);
foreach (PropertyInfo prop in Props)
{
    //Setting column names as Property names
    dt.Columns.Add(prop.Name);
}

foreach (EMP e in list)
{
    var values = new object[Props.Length];
    for (int i = 0; i < Props.Length; i++)
    {
        //inserting property values to datatable rows
        values[i] = Props[i].GetValue(e, null);
    }
    dt.Rows.Add(values);
}

Export Model to DataTable

3 Answers3

Linked

Related