Export Dataset to Excel xlsx

Hello Experts,
I have a Dataset contains few Datatables

How do I Export this Dataset to Excel xlsx Files, each DataTable will be exported to a sheet in the workbook

Thanks

Microsoft ExcelASP.NETC#

Last Comment

Éric Moreau

8/22/2022 - Mon

Rob Henson

9/20/2016

Which software is the Export being done from?
What format does the export create?

Jamil Muammar

9/20/2016

ASKER

in ASP.NET C#

the export creates an excel file type xlsx, each sheet in the excel will contain on Datatable from DataSet

Thanks

Éric Moreau

9/20/2016

I see ASP.Net in your topics. Do you want to run that from a web server?

If you can afford it, Aspose.Cells would be a great tool: http://www.aspose.com/products/cells/net

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat

William Peck

Kyaw Wanna

9/20/2016

If you write in c#, please refer the example as per below :

   dt = datatable.GetAllData();//your datatable
    string attachment = "attachment; filename=excelfile.xls";
    Response.ClearContent();
    Response.AddHeader("content-disposition", attachment);
    Response.ContentType = "application/vnd.ms-excel";
    string tab = "";
    foreach (DataColumn dc in dt.Columns)
    {
        Response.Write(tab + dc.ColumnName);
        tab = "\t";
    }
    Response.Write("\n");
    int i;
    foreach (DataRow dr in dt.Rows)
    {
        tab = "";
        for (i = 0; i < dt.Columns.Count; i++)
        {
            Response.Write(tab + dr[i].ToString());
            tab = "\t";
        }
        Response.Write("\n");
    }
    Response.End();

Open in new window

Pawan Kumar

9/20/2016

or may be you can try

--


using (SqlCommand cmd = new SqlCommand(query))
        {
            using (SqlDataAdapter dataadapter = new SqlDataAdapter())
            {
                cmd.Connection = con;
                dataadapter.SelectCommand = cmd;
                using (DataSet ds = new DataSet())
                {
                    dataadapter.Fill(ds);
                    ds.Tables[0].TableName = "A";
                    ds.Tables[1].TableName = "B";
					ds.Tables[2].TableName = "C"; -- example
 
                    using (XLWorkbook wb = new XLWorkbook())
                    {
                        foreach (DataTable dt in ds.Tables)
                        {                            
                            wb.Worksheets.Add(dt);
                        } 
                        
                        Response.Clear();
                        Response.Buffer = true;
                        Response.Charset = "";
                        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                        Response.AddHeader("content-disposition", "attachment;filename=ExportFileName.xlsx");
                        using (MemoryStream PawanMemoryStream = new MemoryStream())
                        {
                            wb.SaveAs(PawanMemoryStream);
                            PawanMemoryStream.WriteTo(Response.OutputStream);
                            Response.Flush();
                            Response.End();
                        }
                    }
                }
            }
        }


--