C# - Excel Export a List

Question

Hi i have this code To export a List to An Excel:

private DataTable ListaDatiReportQuietanzamento(List<DatiReportQuietanzamento> datiReportQuietanzamento)
{
    DataTable dt = new DataTable("DatiReportQuietanzamento");
    dt.Columns.Add("Polizza");
    dt.Columns.Add("Posizione");
    dt.Columns.Add("Codice Frazionamento");

    var result = datiReportQuietanzamento.ToDataTable().AsEnumerable().Select(p =>
        new
        {
            n_polizza = p.Field<long>("n_polizza"),
            n_posizione = p.Field<byte>("n_posizione"),
            c_frazionamento = p.Field<string>("c_frazionamento")
        }).Distinct().ToList();

    foreach (var item in result)
    {
        dt.Rows.Add(item.n_polizza, item.n_posizione, item.c_frazionamento);
    }
    return dt;
}

This method works with Lists that does not contain many items , but when the list is very large , the method takes too many time.

There is a way to avoid the foreach and add to the rows the items directly? Maybe with Lambda Expression?

Thank you.

For starters you can avoid `datiReportQuietanzamento.ToDataTable().AsEnumerable()` and direactly do `datiReportQuietanzamento.Select()`. Also avoid `.ToList()` — Mahesh, Oct 05 '18 at 07:26
Also [here](https://stackoverflow.com/a/6183839/643104) is how you can fill one datatable with other datatable using selected columns — Mahesh, Oct 05 '18 at 07:29
[Returning datatable using entity framework](https://stackoverflow.com/a/32614944/205233) might also help. — Filburt, Oct 05 '18 at 07:30
I removed the tsql tag since you are using EntityFramework. If you actually want to use an SQL query there's an answer in the linked post. — Filburt, Oct 05 '18 at 07:32

score 0 · Answer 1 · answered Oct 05 '18 at 09:07

While you have not specified how the data is ultimately to be supplied to Excel, generally it is supplied a CSV (Comma Separated Values) file for easy import.

So this being the case you can eliminate your data table conversion entirely and create a list of strings as follows:

private List<string> ListaDatiReportQuietanzamento(List<DatiReportQuietanzamento> datiReportQuietanzamento)
{
     var result = new List<string>();
     foreach (var item in datiReportQuietanzamento)
     {
        result.AppendLine($"{item.n_polizza},{item.n_posizione},{item.c_frazionamento}");
     }

    return result;
}

Now the only simplification I have made is not to worry about encoding because strings should actually be escaped so item.c_frazionamento should actually be escaped.

Instead of doing this all yourself, I suggest you have a look at a NuGet package such as CsvHelper which will help you with creating CSV files and take all the hassle with escaping things out of the equation. It can also directly deal with a list of objects and convert it into a CSV file for you see specifically the first example in https://joshclose.github.io/CsvHelper/writing#writing-all-records

C# - Excel Export a List

1 Answers1