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.

Submit a Comment

Your email address will not be published. Required fields are marked *

Subscribe

Select Categories