question

kumarrr avatar image
0 Votes"
kumarrr asked LanHuang-MSFT answered

export sql server data to excel template file

How to export sql server data to excel template file in asp.net with c#?

I have two sheets First and Second sheets

I want to export sql data into the second sheet in asp.net with c#

dotnet-aspnet-webpages
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

LanHuang-MSFT avatar image
0 Votes"
LanHuang-MSFT answered

Hi @Jackson1990-7147,
1. First you need to download DocumentFormat.OpenXml and ClosedXml libraries,

 PM> Install-Package ClosedXML
 PM> Install-Package DocumentFormat.OpenXml -Version 2.13.1

2.The HTML markup consists of a Button which when clicked will trigger the process of exporting the data from SQL Server table to Excel file.

 <asp:Button Text="Export" On Click="ExportExcel" ru nat="se rver" />

3.You will need to import the following namespaces.

 using System.IO;
 using System.Data;
 using ClosedXML.Excel;
 using System.Configuration;
 using System.Data.SqlClient;

4.When the Export button is clicked, the following event handler is executed. A DataTable is populated with records from the Customers table.
Then a Workbook object is created to which the DataTable is added as Worksheet using the Add method which accepts DataTable and the name of the Sheet as parameters.
Once the DataTable is added as a Worksheet to the Workbook, the WorkBook is saved to a MemoryStream.
Finally MemoryStream is written to the Response which initiates the File download.

 protected void ExportExcel(object sender, EventArgs e)
         {
             using (SqlConnection con = new SqlConnection(***))
             {
                 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, "Sheet2");
     
                                 Response.Clear();
                                 Response.Buffer = true;
                                 Response.Charset = "";
                                 Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                                 Response.AddHeader("content-disposition", "attachment;filename=SqlExport.xlsx");
                                 using (MemoryStream MyMemoryStream = new MemoryStream())
                                 {
                                     wb.SaveAs(MyMemoryStream);
                                     MyMemoryStream.WriteTo(Response.OutputStream);
                                     Response.Flush();
                                     Response.End();
                                 }
                             }
                         }
                     }
                 }
             }
         }

If the answer is helpful, please click "Accept Answer" and upvote it.
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.
Best regards,
Lan Huang


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.