Solved

asp.net vb.net export dataset to excel

Posted on 2009-04-15
4
2,596 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 70

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

821 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