Convert Dataset to Excel in ASP.net

naimish's Avatar
Banned
The below method accepts a Dataset as an argument and generates an excel file with the specified file name in the second argument to the method.
Nowadays in many web application, we need to display the contents of a dataset into an excel file, so that all records can be viewed by the user.
The below method will provide a solution to this requirement.
All the user needs to do is call the method specifying the dataset and the filename.
eg:-
ExportDataSetToExcel(dsEmployeeDetails,"EmpDetails .xls");
Note : This is not applicable for Microsoft Office 2007 and above

Code:
    /// <summary>   
    /// To Export data Set to Excel   
    /// </summary>   
    /// <param name="ds"></param>   
    /// <param name="filename"></param>.   
    public void ExportDataSetToExcel(DataSet ds, string filename)   
    {   
        HttpResponse response = HttpContext.Current.Response;   
  
        // first let's clean up the response.object   
        response.Clear();   
        response.Charset = "";   
  
        // set the response mime type for excel   
        response.ContentType = "application/vnd.ms-excel";   
        response.AddHeader("Content-Disposition", "attachment;filename=\"" + filename + "\"");   
  
        // create a string writer   
        using (StringWriter sw = new StringWriter())   
        {   
            using (HtmlTextWriter htw = new HtmlTextWriter(sw))   
            {   
                // instantiate a datagrid   
                DataGrid dg = new DataGrid();   
                dg.DataSource = ds.Tables[0];   
  
                dg.DataBind();   
                dg.RenderControl(htw);   
                response.Write(sw.ToString());   
                response.End();   
            }   
        }   
  
    }

Last edited by shabbir; 30Jun2009 at 09:28.. Reason: Code blocks
0
shabbir's Avatar, Join Date: Jul 2004
Go4Expert Founder
Original Author : http://tim.mackey.ie/HowtoExportADat...elCAspnet.aspx
0
nimesh's Avatar, Join Date: Apr 2009
Invasive contributor
Yes, it's almost same.

This one uses Datagrid, which was available in .Net 1.0
From .Net2.0 it's now changed to Gridview.

Few day's back I was searching for similar thing but for 2.0, I mean with Gridview and not DataGrid.
And found below articles,

C# - http://www.c-sharpcorner.com/UploadF...l_asp2_dc.aspx
http://weblogs.asp.net/gunnarpeipman...-to-excel.aspx

VB - http://aspnetlibrary.com/articledeta...dView-to-Excel

GridView Versus DataGrid - http://msdn.microsoft.com/en-us/maga...163933.aspx#S1

Shabbir: Did you use Copyscape?
0
shabbir's Avatar, Join Date: Jul 2004
Go4Expert Founder
Yes and thats the reason its still left in forums and not moved to articles.
0
naimish's Avatar
Banned
I don't know why people are also getting same code as we used...anyways....I will take care next times or rather won't post it
0
shabbir's Avatar, Join Date: Jul 2004
Go4Expert Founder
Quote:
Originally Posted by naimish View Post
I don't know why people are also getting same code as we used...anyways....I will take care next times or rather won't post it
They used it before you posted and so its not they got what you posted but other way round but good that you know the guidelines now.
0
nimesh's Avatar, Join Date: Apr 2009
Invasive contributor
Now when I check I'm not getting the same result from copyscape
0
nimesh's Avatar, Join Date: Apr 2009
Invasive contributor
Naimish, since you have used this before, I would like you ask you one question.

On my web page, there are many more things beside this gridview/datagrid

when i use this code, I get all the things in my excel, which I don't want.

Any suggestions?
0
nimesh's Avatar, Join Date: Apr 2009
Invasive contributor
I got it as I was expecting it.

I used some things from this page here

And this is my final code (it's in VB)

Code:
        Dim response As HttpResponse

        response = HttpContext.Current.Response
        response.Clear()
        response.Charset = ""
        response.ContentType = "application/vnd.ms-excel"
        response.AddHeader("Content-Disposition", "attachment;filename=" + filename)

        If ViewState("sortExpr") <> "" Or ViewState("sortDir") <> "" Then
            ViewState("sortExpr") = ""
            ViewState("sortDir") = ""
        End If
        GridView1.PageIndex = 0
        GridView1.AllowPaging = False
        GridView1.AllowSorting = False
        GridView1.EditIndex = -1

        'To do all the data fetching and adding to gridview
        disp_report()

        Dim sw As System.IO.StringWriter = New System.IO.StringWriter
        Dim hw As System.Web.UI.HtmlTextWriter = New HtmlTextWriter(sw)

        Dim frm As New HtmlForm
        Me.GridView1.Parent.Controls.Add(frm)
        frm.Attributes("runat") = "server"
        frm.Controls.Add(GridView1)
        frm.RenderControl(hw)

        response.Write(sw.ToString)
        response.End()
0
naimish's Avatar
Banned
How did you use it in VB ? HTTP referece ?