Solved

asp.net vb.net export dataset to excel

Posted on 2009-04-15
4
2,590 Views
Last Modified: 2012-05-06
How can in asp.net export a dataset to excel.
0
Comment
Question by:rflorencio
  • 2
4 Comments
 
LVL 14

Expert Comment

by:GiftsonDJohn
ID: 24146917
You have to bind the DataSet to DataGrid or GridView. Then you can use the following code to download as excel sheet.
DataGrid dg = new DataGrid();

                    dg.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;

                    dg.HeaderStyle.Font.Bold = true;
 

                    dg.DataSource = ds.Tables[0];

                    dg.DataBind();
 

                    Response.Clear();

                    Response.Buffer = true;

                    Response.ContentType = "application/vnd.ms-excel";

                    Response.AddHeader("Content-Disposition", "attachment; filename=Consolidated Report.xls");

                    Response.Charset = "";

                    this.EnableViewState = false;

                    System.IO.StringWriter oStringWriter = new System.IO.StringWriter();

                    System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);

                    dg.RenderControl(oHtmlTextWriter);

                    Response.Write(oStringWriter.ToString());

                    Response.End();

Open in new window

0
 
LVL 14

Expert Comment

by:GiftsonDJohn
ID: 24146922
Here is the VB.net equivalent
Dim dg As New DataGrid()

dg.HeaderStyle.HorizontalAlign = HorizontalAlign.Center

dg.HeaderStyle.Font.Bold = True
 

dg.DataSource = ds.Tables(0)

dg.DataBind()
 

Response.Clear()

Response.Buffer = True

Response.ContentType = "application/vnd.ms-excel"

Response.AddHeader("Content-Disposition", "attachment; filename=Consolidated Report.xls")

Response.Charset = ""

Me.EnableViewState = False

Dim oStringWriter As New System.IO.StringWriter()

Dim oHtmlTextWriter As New System.Web.UI.HtmlTextWriter(oStringWriter)

dg.RenderControl(oHtmlTextWriter)

Response.Write(oStringWriter.ToString())

Response.End()

Open in new window

0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 24146937
are you using VB2008? In this case, see how easy it is to generate a CSV or XML document from a dataset: http://www.emoreau.com/Entries/Articles/2009/04/Using-LINQ-and-XML-Literals-to-transform-a-DataTable-into-a-HTML-table.aspx
0
 
LVL 6

Accepted Solution

by:
badalpatel earned 500 total points
ID: 24147438
You dont need to bind your dataset to the datagrid.
You can directy export to excel.
Code is below

 Sub exportToexCEl()

        Dim sSQL As String

        sSQL = "select * from t_user where suserType='n'"

        Dim ds As DataSet = CommonMethods.GetDataset(sSQL)

        Dim dt As DataTable = ds.Tables(0)

        Dim attachment As String = "attachment; filename=userlist.xls"

        Response.ClearContent()

        Response.AddHeader("content-disposition", attachment)

        Response.ContentType = "application/vnd.ms-excel"

        Dim tab As String = ""

        For Each dc As DataColumn In dt.Columns

            Response.Write(tab + dc.ColumnName)

            tab = vbTab

        Next

        Response.Write(vbLf)

        Dim i As Integer

        For Each dr As DataRow In dt.Rows

            tab = ""

            For i = 0 To dt.Columns.Count - 1

                Response.Write(tab + dr(i).ToString().Replace(vbCrLf, " "))

                tab = vbTab

            Next

            Response.Write(vbLf)

        Next

        Response.End()

    End Sub

Open in new window

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

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

920 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

14 Experts available now in Live!

Get 1:1 Help Now