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();   
            }   
        }   
  
    }

 

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?

 

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

 

Now when I check I'm not getting the same result from copyscape

 

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?

 

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()

 

How did you use it in VB ? HTTP referece ?

 

I didn't got that?
What exactly are you asking about?

 

How did you write that code in VB 6.0 ? or have you write in VB.NET ?

 

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#

 

Ohhk buddy, will need to check it, will tell you ASAP......!

 

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?

 

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");

 

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();