Go4Expert

Go4Expert (http://www.go4expert.com/)
-   ASP.NET (http://www.go4expert.com/forums/asp-net/)
-   -   Convert Dataset to Excel in ASP.net (http://www.go4expert.com/forums/convert-dataset-excel-aspnet-t18260/)

naimish 30Jun2009 08:06

Convert Dataset to Excel in ASP.net
 
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(); 
            } 
        } 
 
    }


shabbir 30Jun2009 09:52

Re: Convert Dataset to Excel in ASP.net
 
Original Author : http://tim.mackey.ie/HowtoExportADat...elCAspnet.aspx

nimesh 30Jun2009 11:03

Re: Convert Dataset to Excel in ASP.net
 
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?

shabbir 30Jun2009 12:47

Re: Convert Dataset to Excel in ASP.net
 
Yes and thats the reason its still left in forums and not moved to articles.

naimish 30Jun2009 14:50

Re: Convert Dataset to Excel in ASP.net
 
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 :(

shabbir 30Jun2009 14:56

Re: Convert Dataset to Excel in ASP.net
 
Quote:

Originally Posted by naimish (Post 50800)
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.

nimesh 30Jun2009 21:13

Re: Convert Dataset to Excel in ASP.net
 
Now when I check I'm not getting the same result from copyscape :(

nimesh 2Jul2009 10:25

Re: Convert Dataset to Excel in ASP.net
 
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?

nimesh 2Jul2009 10:57

Re: Convert Dataset to Excel in ASP.net
 
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()


naimish 2Jul2009 11:15

Re: Convert Dataset to Excel in ASP.net
 
How did you use it in VB ? HTTP referece ?


All times are GMT +5.5. The time now is 14:39.