1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Convert Dataset to Excel in ASP.net

Discussion in 'ASP.NET' started by naimish, Jun 30, 2009.

  1. naimish

    naimish New Member

    Joined:
    Jun 29, 2009
    Messages:
    1,046
    Likes Received:
    18
    Trophy Points:
    0
    Occupation:
    Software Engineer
    Location:
    On Earth
    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 a moderator: Jun 30, 2009
  2. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,287
    Likes Received:
    364
    Trophy Points:
    83
  3. nimesh

    nimesh New Member

    Joined:
    Apr 13, 2009
    Messages:
    770
    Likes Received:
    20
    Trophy Points:
    0
    Occupation:
    Oracle Apps Admin
    Location:
    Mumbai
    Home Page:
    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/Upload...asp2_dc11032006003657AM/exportxl_asp2_dc.aspx
    http://weblogs.asp.net/gunnarpeipman/archive/2007/09/16/exporting-gridview-data-to-excel.aspx

    VB - http://aspnetlibrary.com/articledetails.aspx?article=How-to-export-a-GridView-to-Excel

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

    Shabbir: Did you use Copyscape?
     
  4. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,287
    Likes Received:
    364
    Trophy Points:
    83
    Yes and thats the reason its still left in forums and not moved to articles.
     
  5. naimish

    naimish New Member

    Joined:
    Jun 29, 2009
    Messages:
    1,046
    Likes Received:
    18
    Trophy Points:
    0
    Occupation:
    Software Engineer
    Location:
    On Earth
    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 :(
     
  6. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,287
    Likes Received:
    364
    Trophy Points:
    83
    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.
     
  7. nimesh

    nimesh New Member

    Joined:
    Apr 13, 2009
    Messages:
    770
    Likes Received:
    20
    Trophy Points:
    0
    Occupation:
    Oracle Apps Admin
    Location:
    Mumbai
    Home Page:
    Now when I check I'm not getting the same result from copyscape :(
     
  8. nimesh

    nimesh New Member

    Joined:
    Apr 13, 2009
    Messages:
    770
    Likes Received:
    20
    Trophy Points:
    0
    Occupation:
    Oracle Apps Admin
    Location:
    Mumbai
    Home Page:
    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?
     
  9. nimesh

    nimesh New Member

    Joined:
    Apr 13, 2009
    Messages:
    770
    Likes Received:
    20
    Trophy Points:
    0
    Occupation:
    Oracle Apps Admin
    Location:
    Mumbai
    Home Page:
    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()
     
  10. naimish

    naimish New Member

    Joined:
    Jun 29, 2009
    Messages:
    1,046
    Likes Received:
    18
    Trophy Points:
    0
    Occupation:
    Software Engineer
    Location:
    On Earth
    How did you use it in VB ? HTTP referece ?
     
  11. nimesh

    nimesh New Member

    Joined:
    Apr 13, 2009
    Messages:
    770
    Likes Received:
    20
    Trophy Points:
    0
    Occupation:
    Oracle Apps Admin
    Location:
    Mumbai
    Home Page:
    I didn't got that?
    What exactly are you asking about?
     
  12. naimish

    naimish New Member

    Joined:
    Jun 29, 2009
    Messages:
    1,046
    Likes Received:
    18
    Trophy Points:
    0
    Occupation:
    Software Engineer
    Location:
    On Earth
    How did you write that code in VB 6.0 ? or have you write in VB.NET ?
     
  13. nimesh

    nimesh New Member

    Joined:
    Apr 13, 2009
    Messages:
    770
    Likes Received:
    20
    Trophy Points:
    0
    Occupation:
    Oracle Apps Admin
    Location:
    Mumbai
    Home Page:
    ok.

    That's in VB.NET

    What I wanted to mention earlier is that, this code is using VB as the language and not C# ;)
     
  14. naimish

    naimish New Member

    Joined:
    Jun 29, 2009
    Messages:
    1,046
    Likes Received:
    18
    Trophy Points:
    0
    Occupation:
    Software Engineer
    Location:
    On Earth
    Ohhk buddy, will need to check it, will tell you ASAP......!
     
  15. nimesh

    nimesh New Member

    Joined:
    Apr 13, 2009
    Messages:
    770
    Likes Received:
    20
    Trophy Points:
    0
    Occupation:
    Oracle Apps Admin
    Location:
    Mumbai
    Home Page:
    I'm back with a problem.

    I was using the approach I mentioned before to get only the gridview to excel.
    till now I was happy with it but now I found that the file getting downloaded is not an excel but an html with ext xls
    it opens normally without any problems, but when I save the file, it created the html's associated folder "abc_files"

    Also one more problem I encountered was that the data appearing in gridview does not appear same in excel.
    excel interprets the datatype and displays accordingly.
    like the value 10.0 in gridview displays as 10 only in excel. :(

    Anything that you can suggest?
     
  16. cikaPero

    cikaPero New Member

    Joined:
    Mar 8, 2010
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    0
    There is also a much cleaner way to export DataSet to Excel with GemBox.Spreadsheet Excel .NET component:

    Code:
    // Create new ExcelFile.
    ExcelFile ef = new ExcelFile();
                                                                
    // Imports all the tables from DataSet to new file.
    foreach (DataTable dataTable in dataSet.Tables)
    {
         // Add new worksheet to the file.
         ExcelWorksheet ws = ef.Worksheets.Add(dataTable.TableName);
                                                                
         // Insert the data from DataTable to the worksheet starting at cell "A1".
         ws.InsertDataTable(dataTable, "A1", true);
    }
                                                                
    // Save the file to XLS format.
    ef2.SaveXls("DataSet.xls");
     
  17. cikaPero

    cikaPero New Member

    Joined:
    Mar 8, 2010
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    0
    Hi,

    you can use GemBox.Software .NET Excel library to convert DataSet to Excel in ASP.NET applications:
    Code:
    // Create new ExcelFile.
    var ef = new ExcelFile();
    
    // Imports all the tables from DataSet to new file.
    foreach (var dataTable in dataSet.Tables)
    {
        // Add new worksheet to the file.
        var ws = ef.Worksheets.Add(dataTable.TableName);
    
        // Insert the data from DataTable to the worksheet starting at cell "A1".
        ws.InsertDataTable(dataTable, "A1", true);
    }
    
    // Stream file to browser.
    Response.Clear();
    Response.ContentType = "application/vnd.ms-excel";
    Response.AddHeader("Content-Disposition", "attachment; filename=Employee.xls");
    ef.SaveXls(Response.OutputStream);
    Response.End();
     

Share This Page