export sql server data to excel template file

Question

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#

Answer 1

Hi @Peter_1985 ,

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

Answer 2

Except for ClosedXml, you can use another library - Spire.XLS for .NET to export data from database to Excel. Here is sample code snippet for your reference.

//connect database
OleDbConnection connection = new OleDbConnection();
connection.ConnectionString @"Provider=""Microsoft.Jet.OLEDB.4.0"";Data Source=""demo.mdb"";User Id=;Password="
OleDbCommand command = new OleDbCommand();
command.CommandText = "select * from parts";
DataSet dataSet = new System.Data.DataSet();
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(command.CommandText,connection);
dataAdapter.Fill(dataSet);
DataTable t = dataSet.Tables[0];
//export datatable to excel
Workbook book = new Workbook();
Worksheet sheet = book.Worksheets[0];
sheet.InsertDataTable(t, true, 1, 1);
book.SaveToFile("insertTableToExcel.xls",ExcelVersion.Version97to2003);