Hi @Peter_1985 ,
- First you need to download DocumentFormat.OpenXml and ClosedXml libraries,
PM> Install-Package ClosedXML PM> Install-Package DocumentFormat.OpenXml -Version 2.13.1
- 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" />
- You will need to import the following namespaces.
using System.IO; using System.Data; using ClosedXML.Excel; using System.Configuration; using System.Data.SqlClient;
- 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