Code2night
  • Home
  • Blogs
  • Tutorial
  • Post Blog
  • Tools
    • Json Beautifier
    • Html Beautifier
  • Members
    • Register
    • Login
  1. Home
  2. Blogpost
11 Jun
2022

Excel Export in Asp.Net MVC using XlWorkbook

by Shubham Batra

2426

Download Attachment


XlWorkBook

XlWorkbook is a third party library that helps you to export data into excel file in easy steps. It also helps in giving formatting to excel like background color, border, color and formulas

Exporting Excel using XlWorkbook in Asp.Net MVC

So, first of all you have to install ClosedXml nuget package in your project which is showed in the image

After installing the package , you have to go to the controller and add namespace

using ClosedXML.Excel;

Now you can use Xlworkbook in your code , Xlworkbook requires data as datatable to export it into excel. Please have a look at this sample code

 public ActionResult Download()
        {
            DataTable table = DummyDataTableSource();

            using (XLWorkbook workbook = new XLWorkbook())
            {
                table.TableName = "Table 1";/*Giving table name is mandatory and it must be unique between multiple worksheets*/
                workbook.Worksheets.Add(table);
                Response.Clear();
                Response.Buffer = true;
                Response.Charset = "";
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.AddHeader("content-disposition", "attachment;filename=Dummy Excel Export.xlsx");
                using (MemoryStream MyMemoryStream = new MemoryStream())
                {
                    workbook.SaveAs(MyMemoryStream);
                    MyMemoryStream.WriteTo(Response.OutputStream);
                }

            }

            Response.Flush();
            Response.End();
            return View();
        }

        private static DataTable DummyDataTableSource()
        {
            DataTable table = new DataTable();
            table.Columns.Add("Name");
            table.Columns.Add("Number");
            table.Columns.Add("Address");
            table.Columns.Add("City");


            for (int i = 0; i < 10; i++)
            {
                DataRow dr = table.NewRow();
                dr["Name"] = "Name " + i;
                dr["Number"] = "Number " + i;
                dr["Address"] = "Address " + i;
                dr["City"] = "City " + i;
                table.Rows.Add(dr);
            }

            return table;
        }

Here, we have to pass the data to worksheet in the datatable format and you have to provide a tablename to datatable. We will call this action from view like this

<a style="margin-top:10px" class="btn btn-primary" href="/Home/Download">Export to Excel</a>

So, if you will click on this button you will see your data is exported in excel format. You can check in the image

So, this is how you can export data in excel using XlWorkbook in Asp.Net mvc.

  • |
  • XlWorkbook , ClosedXml , C# , AspNet , AspNet MVC , Excel Export

Comments

Follow Us On Social Media - Like Us On Facebook

Best Sellers

product 1

Hand Hug Bracelet For Women Men Cuff Bangle Adjustable Lover Couple Bracelets

Can be given as a gift to your family, relatives, or friends

Buy $15.99
product 1

Teddy bear hug bear plush toy bear cub

Can be given as a gift to your family, relatives, or friends


Buy $49.99

Tags

LinkedinLogin
LinkedinProfile
GetLinkedinProfile
C#
Aspnet
MVC
Linkedin
ITextSharp
Export to Pdf
AspNet Core
AspNet
View to Pdf in Aspnet
Model Validation In ASPNET Core MVC 60
Model Validation
Model Validation In ASPNET Core MVC
Model Validation In ASPNET
Image Compression in AspNet
Compress Image in c#
AspNet MVC
Thank you for Downloading....!

Subscribe for more tutorials

Support our team

Continue with Downloading

Welcome To Code2night, A common place for sharing your programming knowledge,Blogs and Videos

  • Panipat
  • info@Code2night.com

Links

  • Home
  • Blogs
  • Tutorial
  • Post Blog

Popular Tags

Copyright © 2023 by Code2night. All Rights Reserved

  • Home
  • Blog
  • Login
  • SignUp
  • Contact
  • Terms & Conditions
  • Refund Policy
  • About Us
  • Privacy Policy
  • Json Beautifier