Link to home
Create AccountLog in
Avatar of Jamil Muammar
Jamil Muammar

asked on

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
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

Which software is the Export being done from?
What format does the export create?
Avatar of Jamil Muammar
Jamil Muammar

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
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
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

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();
                        }
                    }
                }
            }
        }


--

Open in new window

--
where is your XLWorkbook coming from?
user should download it
ASKER CERTIFIED SOLUTION
Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account