Exporting Data To Excel Sheet in ASP.NET (C#) Without using External DLLs(Libraries)

Exporting data is an integral part of any application we develop irrespective of the technology. We need to export data while generating various reports to take care of the activities going on. For an example; lets say you need to send out the annual sales report to your CA/CS for Tax filing, in that case what you have to do if you are maintaining all the things in a web application ? Definitely we need to export all the data for the specified time period to some specified file formats may it be excel, pdf or csv.

In this case if you are a developer you must have tried many things using some external libraries, methods. I too tried any methods. But each time I did not feel good, as I don't like using external libraries.

After doing a series of experiments and searching a lot in Google, finally got some ideas that really works awesome for exporting data to the required file formats as expected.

Here I will walk you through the code for exporting data in Excel sheet in ASP.NET.

Here is the tag where you can find all the Export Data Articles.

Download source code here.

In order to export data into excel sheet, first we need some data. In your case you can get it from DB or anywhere you want. You can have it in DataTable format or any custom List format or anything you want.

Here in my case I am using a method to create a dummy DataTable and feeds some data into it. I will use this DataTable as my data source, which needs to get exported into excel sheet.

Below is the code you can have a look and then I will discuss every bit of it for your understanding.
    private void ExportToExcelSheet()
    {
        //Get the data we want to export.
        DataTable dt = GetData();

        //Create a dummy GridView and Bind the data source we have.
        GridView grdExportData = new GridView();

        grdExportData.AllowPaging = false;
        grdExportData.DataSource = dt;
        grdExportData.DataBind();

        //Clear the response and add the content types and headers to it.
        Response.Clear();
        Response.Buffer = true;
        Response.AddHeader("content-disposition", "attachment;filename=MyReport.xls");
        Response.Charset = "";
        Response.ContentType = "application/vnd.ms-excel";

        //We need this string writer and HTML writer in order to render the grid inside it.
        StringWriter swExportData = new StringWriter();
        HtmlTextWriter hw = new HtmlTextWriter(swExportData);

        //Lets render the Grid inside the HtmlWriter and then automatically we will have it converted into eauivalent string.
        grdExportData.RenderControl(hw);

        //Write the response now and you will get your excel sheet as download file
        Response.Output.Write(swExportData.ToString());
        Response.Flush();
        Response.End();
    }
I guess this must be very clear to everyone what I have written inside. Anyway let's discuss it here.

  1. First, I get the data source from the method GetData(), which is nothing but just a method that returns a datatable with some data inside it. You can have your own dataset here from DB or wherever you want.
  2. Second, I have taken a grid view so that I can render that data in it. If you have a grid view at your page level then no need to take one and bind it here, even you need not to get the data again if you have already that data rendered in any gridview or what in your page.
  3. Third, Add Response headers, content types for Excel download. Remember the Content Type is very important, this is code which decides the file type you are downloading. So here for Excel sheet download the content Type is "application/vnd.ms-excel". Also give name to the file you are downloading.
  4. Fourth, Get a string writer and Html writer and render the grid view inside it. Now the string writer will have the exact html of  the grid and we are all set to throw it into an Excel sheet.
  5. Fifth, Now write the string writer value into response object and you are all set to download the excel sheet.
If you are looking for the GetData() method to test out the feasibility of this process of exporting data into excel sheet in ASP.NET and C# then here is it.
     private DataTable GetData()
     {
         // Here we create a DataTable with four columns.
         DataTable dtSample = new DataTable();
         dtSample.Columns.Add("Dosage", typeof(int));
         dtSample.Columns.Add("Drug", typeof(string));
         dtSample.Columns.Add("Patient", typeof(string));
         dtSample.Columns.Add("Date", typeof(DateTime));

         // Here we add five DataRows.
         dtSample.Rows.Add(25, "Indocin", "David", DateTime.Now);
         dtSample.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);
         dtSample.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);
         dtSample.Rows.Add(21, "Combivent", "Janet", DateTime.Now);
         dtSample.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now);
         dtSample.Rows.Add(25, "Indocin", "David", DateTime.Now);
         dtSample.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);
         dtSample.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);
         dtSample.Rows.Add(21, "Combivent", "Janet", DateTime.Now);
         dtSample.Rows.Add(25, "Indocin", "David", DateTime.Now);
         dtSample.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);
         dtSample.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);
         dtSample.Rows.Add(21, "Combivent", "Janet", DateTime.Now);
         dtSample.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now);
         dtSample.Rows.Add(25, "Indocin", "David", DateTime.Now);
         
         return dtSample;

     }
Hope this article helps you, as this method helped me a lot to overcome the unnecessary usage of external dll for exporting data into excel sheets.

Demo:

Export Data From ASP.NET



No comments:

Post a Comment