How to export data to excel in C# using Linq

Question

I have the following controller method that will return the data in model. How do I export all the data to excel?

public ActionResult iDeal_Table_Display(Guid? SA = null)
{
    var iDConfig = webservice.Get_iDealConfigs(SA,null,null,null,null,null,null, out retStatus, out errorMsg);

    var model = iDConfig.Select(ic => new iDealModel2
    {
        SaPrefix = ic.PrefixName,
        CalendarCode = ic.CalendarCodeName,
        CashnCarry = ic.isCashnCarry,
        FreeMM = ic.isFreeMM,
        OnContract = ic.isOnContract,
        ProductId = ic.ProductName,
        RequestTypeId = ic.RequestTypeName
    }).ToList();

    return PartialView(model);
}

Possible duplicate of [Create Excel (.XLS and .XLSX) file from C#](http://stackoverflow.com/questions/151005/create-excel-xls-and-xlsx-file-from-c-sharp) — Steve, Dec 02 '15 at 04:34
What you use to get the data does not matter. The creation of an Excel workbook is a separate issue. You will find answers in the link that @Steve provided. Then providing the result to the user is just a matter of returning a FileResult — Anders Marzi Tornblad, Dec 02 '15 at 10:06

Awais Mahmood · Answer 1 · 2015-12-02T04:51:28.433

you can use DataTables and can do this as:

public ActionResult ExportToExcel()
{
    var products = new System.Data.DataTable();
    products.Columns.Add("code", typeof(int));
    products.Columns.Add("description", typeof(string));

    // you can add your columns here as many you want

    var a = db.ABC.Where(x => x.S2 == "ASSETS").FirstOrDefault();
    var l = db.ABC.Where(x => x.S2 == "LIABILITIES").FirstOrDefault();

    // in this way you can get data from database if you are using.. or u may use any other way to seed your file as per your need

    products.Rows.Add(a.S1, a.S2, a.S39, a.S40);
    products.Rows.Add(l.S1, l.S2, l.S39, l.S40);

    // seeding the rows

    var grid = new GridView();
    grid.DataSource = products;
    grid.DataBind();
    Response.ClearContent();
    Response.Buffer = true;

    Response.ContentType = "application/application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    Response.AppendHeader("content-disposition", "attachment; filename=filename.xlsx");

    Response.Charset = "";
    StringWriter sw = new StringWriter();
    HtmlTextWriter htw = new HtmlTextWriter(sw);
    grid.RenderControl(htw);
    Response.Output.Write(sw.ToString());
    Response.Flush();
    Response.End();

    return View("MyView");
}

score 0 · Accepted Answer · edited Dec 18 '18 at 15:59

Sorry about the 1st answer. i found a better way without installing office references

public void toExcel()
{
    var grid = new GridView();

    var iDConfig = blergo.Get_iDealConfigs(null, null, null, null, null, null, null, out retStatus, out errorMsg);

    var model = iDConfig.Select(ic => new iDealModel2
    {
        SaPrefix = ic.PrefixName,
        CalendarCode = ic.CalendarCodeName,
        CashnCarry = ic.isCashnCarry,
        FreeMM = ic.isFreeMM,
        OnContract = ic.isOnContract,
        ProductId = ic.ProductName,
        RequestTypeId = ic.RequestTypeName
    }).ToList();

    grid.DataSource = from data in model.OrderBy(x => x.SaPrefix)
                      select new
                      {
                          SAPrefix = data.SaPrefix,
                          CalendarCode = data.CalendarCode,
                          isCash = data.CashnCarry,
                          FreeMM = data.FreeMM,
                          onContract = data.OnContract,
                          Product = data.ProductId,
                          RequestType = data.RequestTypeId
                      };

    grid.DataBind();

    Response.ClearContent();
    Response.AddHeader("content-disposition", "attachment; filename=iDealConfig.xls");
    Response.ContentType = "application/excel";
    StringWriter sw = new StringWriter();
    HtmlTextWriter htmlTextWriter = new HtmlTextWriter(sw);

    grid.RenderControl(htmlTextWriter);
    Response.Write(sw.ToString());
    Response.End();
}

How to export data to excel in C# using Linq

2 Answers2