CODEWITHSUNDEEP
sql-serverasp.net-mvcexcelentity-framework
NoName
NoName

Reputation: 124

Import and export data to excel for asp.net mvc using entity framework

I want to import and export excel file data into SQL server using ASP.NET MVC using entity framework.

All code that I found explain that without using entity framework. So what should I do to make it?

Upvotes: 3

Views: 8928

Answers (1)

Mujahid Daud Khan
Mujahid Daud Khan

Reputation: 2071

EPPlus is a .NET library that reads and writes Excel 2007/2010 files using the Open Office Xml format (xlsx). Here is sample code to use this libarary in ASP.net MVC.

public FileContentResult Download()
{

    var fileDownloadName = String.Format("FileName.xlsx");
    const string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";


    // Pass your ef data to method
    ExcelPackage package = GenerateExcelFile(_db.Contexts.ToList());

    var fsr = new FileContentResult(package.GetAsByteArray(), contentType);
    fsr.FileDownloadName = fileDownloadName;

    return fsr;
}

private static ExcelPackage GenerateExcelFile(IEnumerable<Context> datasource)
{

    ExcelPackage pck = new ExcelPackage();

    //Create the worksheet 
    ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Sheet 1");

    // Sets Headers
    ws.Cells[1, 1].Value = "Column 1";
    ws.Cells[1, 2].Value = "Column 2";
    ws.Cells[1, 3].Value = "Column 3";

    // Inserts Data
    for (int i = 0; i < datasource.Count(); i++)
    {
        ws.Cells[i + 2, 1].Value = datasource.ElementAt(i).Serial;
        ws.Cells[i + 2, 2].Value = datasource.ElementAt(i).WarrantyStart;
        ws.Cells[i + 2, 3].Value = datasource.ElementAt(i).WarrantyEnd;
    }

    // Format Header of Table
    using (ExcelRange rng = ws.Cells["A1:C1"])
    {

        rng.Style.Font.Bold = true;
        rng.Style.Fill.PatternType = ExcelFillStyle.Solid; //Set Pattern for the background to Solid 
        rng.Style.Fill.BackgroundColor.SetColor(Color.Gold); //Set color to DarkGray 
        rng.Style.Font.Color.SetColor(Color.Black);
    }
    return pck;
}

And insert this link in your view and it will download your file.

@Html.ActionLink("Download Data as Excel", "Download");

Similarly you can import data from excel, here is an example to get started.

Upvotes: 4

Related Questions