Export To Excelsheet using closed xml

ClosedXML is a .NET library for reading, manipulating and writing Excel 2007+ (.xlsx, .xlsm) files. It aims to provide an intuitive and user-friendly interface to dealing with the underlying OpenXML API.

Install the library through .NET CLI

C:\source> dotnet add package ClosedXML

Also it can we installed using below nuget.

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="ExportToExcelsheet.aspx.cs" Inherits="ExportTOExcelsheet.ExportToExcelsheet" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <asp:Button runat="server" OnClick="ExportToExcelsheet_Click" Text="ExportToExcelsheet" />
        </div>
    </form>
</body>
</html>

using ClosedXML.Excel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace ExportTOExcelsheet
{
    public partial class ExportToExcelsheet : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }
        public DataSet GetEmployeeData()
        {
            DataSet dataSet = new DataSet();
            DataTable table1 = new DataTable("employee");
            table1.Columns.Add("name");
            table1.Columns.Add("id");
            table1.Rows.Add("Jhon", 1);
            table1.Rows.Add("K", 2);

            DataTable table2 = new DataTable("department");
            table2.Columns.Add("id");
            table2.Columns.Add("Department");
            table2.Rows.Add(1, "4546");
            table2.Rows.Add(2, "IT");

            // Create a DataSet and put both tables in it.
            DataSet set = new DataSet("EmployeeSheet");
            set.Tables.Add(table1);
            set.Tables.Add(table2);

            return set;
        }

        /// <summary>
        /// Export To Excelsheet using closed xml
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void ExportToExcelsheet_Click(object sender, EventArgs e)
        {
            //Get Data from dataset
            DataSet ds = GetEmployeeData();
            try
            {
                using (XLWorkbook wb = new XLWorkbook())
                {
                    wb.Worksheets.Add(ds);
                    wb.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                    wb.Style.Font.Bold = true;
                    Response.Clear();
                    Response.Buffer = true;
                    Response.Charset = "";
                    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                    Response.AddHeader("content-disposition", "attachment;filename= EmployeeDetailReport.xlsx");
                    using (MemoryStream MyMemoryStream = new MemoryStream())
                    {
                        wb.SaveAs(MyMemoryStream);
                        MyMemoryStream.WriteTo(Response.OutputStream);
                        Response.Flush();
                        Response.End();
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
    }
}

Export To Excelsheet using closed xml

1 Comments

Contact Form