MVC5 EXPORT TO EXCEL

Question

I used the below article and developed

https://github.com/KalyanAllam/SIS

https://learn.microsoft.com/en-us/aspnet/mvc/overview/getting-started/database-first-development/

I want to add export to excel to Student page could you please suggest on this.

CaptureStudentindex.JPG

script SQL.txt

Answer 1

Hi @KALYANA ALLAM,

You can export and download data as Microsoft Excel files using the ClosedXML Excel library, which is a wrapper around the OpenXml Excel library.(For newer versions of ClosedXML, you need to install SixLabors.Fonts before installing ClosedXML, because newer versions of ClosedXML depend on SixLabors.Fonts.)

Right Click the Project in Solution Explorer and click Manage NuGet Packages from the Context Menu.

SixLabors.Fonts(In the Manage NuGet Packages UI, check the Include prerelease and it would appear)

ClosedXML

Include an HTML submit button in the Students/Index.cshtml Form with the Action method specified as Export.

When this button is clicked, the data will be exported and downloaded as an Excel file.

@using (Html.BeginForm("Export", "Students", FormMethod.Post))
{
    <input type="submit" value="Export" />
}

StudentsController

[HttpPost]
        public FileResult Export()
        {
           
            DataTable dt = new DataTable("Student");
            dt.Columns.AddRange(new DataColumn[5] { new DataColumn("FirstName"),
                                            new DataColumn("LastName"),
                                            new DataColumn("PhoneNo"),
                                            new DataColumn("Email"),
                                            new DataColumn("ClassName")});

            var students = from student in db.Students
                            select student;

            foreach (var student in students)
            {
                dt.Rows.Add(student.FirstName, student.LastName, student.PhoneNo, student.Email,student.Class.ClassName);
            }

            using (XLWorkbook wb = new XLWorkbook())
            {
                wb.Worksheets.Add(dt);
                using (MemoryStream stream = new MemoryStream())
                {
                    wb.SaveAs(stream);
                    return File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Student.xlsx");
                }
            }
        }

Best regards,
Lan Huang

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.