ASP.NET

Write Data In Excel Using C#

In This Article I will Show You How To Display Data From an Excel File From A Database And ASP.NET(.aspx) Page By Using Visual C# .NET.

So Let’s Start,

Firstly Create A Table Named Customers In A DataBase

Now Create The Project As Following:

1.Open Visual Studio -> File->New Project-> Under Web->ASP.NET Web Application(c#)

2.Give Project Name Such As GenrateExcelFile

3.Open Solution Explorer And Right Click On Your Project And Select

4.Add->New Item->Under Web-> Select WebForm ->WebForm.aspx

5.After That Download ClosedXml Package From Nugget Package Manager

Now the WebForm.aspx source code will be as follows:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm.aspx.cs" Inherits="GenrateExcelFile.WebForm" %>
<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <h2 style="color: #0094ff; font-size: x-large; font-weight: bolder;">Genrate Excel File</h2>
        <div>
            <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Create Excel File" />
        </div>
    </form>
</body>
</html>

Now Change The Connection String In The web.config File To Specify Your Server Location.

After That Put Follwing Code On The Button Click Event in WebForm.aspx.cs

protected void Button1_Click(object sender, EventArgs e)
       {
           string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
           {
               using (SqlConnection con = new SqlConnection(constr))
               {
                   using (SqlCommand cmd = new SqlCommand("Select * from Customers"))
                   {
                       using (SqlDataAdapter sda = new SqlDataAdapter())
                       {
                           cmd.Connection = con;
                           sda.SelectCommand = cmd;
                           using (DataTable dt = new DataTable())
                           {
                               sda.Fill(dt);
                               using (XLWorkbook wb = new XLWorkbook())
                               {
                                   wb.Worksheets.Add(dt, "GenrateExcelTable");
                                   Response.Clear();
                                   Response.Buffer = true;
                                   Response.Charset = "";
                                   Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                                   Response.AddHeader("content-disposition", "attachment;filename=GenrateExcelTable.xlsx");

                                   using (MemoryStream MymemoryStream = new MemoryStream())
                                   {
                                       wb.SaveAs(MymemoryStream);
                                       MymemoryStream.WriteTo(Response.OutputStream);
                                       Response.Flush();
                                       Response.End();
                                   }


                               }
                           }
                       }
                   }
               }

           }

       }

Now Run The WebForm.aspx page And It’s Genrate Excel File

Now Open The GenrateExcelTable.xlsx File And You Can See That The Excel File Is Created From The Database And The Records Will Look As Bellow.

Aatisha Dalwadi

I am Aatisha Dalwadi.I Started My Journey As Asp .Net Developer At Vision Infotech.I Completed My MCA From Sarvjanik College Of Enginnering AND Technology.Good Experience In Designing And Development Of Web Applications using C# .Net ,ASP.NET , Microsoft .Net Framework.