Solved

Export Dataset to Excel xlsx

Posted on 2016-09-20
8
125 Views
Last Modified: 2016-09-26
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
0
Comment
Question by:Jamil Muammar
8 Comments
 
LVL 32

Expert Comment

by:Rob Henson
ID: 41806885
Which software is the Export being done from?
What format does the export create?
0
 

Author Comment

by:Jamil Muammar
ID: 41806895
in ASP.NET C#

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

Thanks
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 41806906
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
0
 
LVL 2

Expert Comment

by:Kyaw Wanna
ID: 41806914
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

0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 24

Expert Comment

by:Pawan Kumar
ID: 41807036
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

--
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 41807148
where is your XLWorkbook coming from?
0
 

Author Comment

by:Jamil Muammar
ID: 41808026
user should download it
0
 
LVL 69

Accepted Solution

by:
Éric Moreau earned 500 total points
ID: 41808431
the question about XLWorkbook was for Pawan Kumar Khowal. It is in its code and it is not a .Net intrinsic object.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now