Post
Ask Question

Exporting Large Table Data to Excel in ASP.Net

Introduction

Generally it is easier to export the data of the database to Excel. We generally have no problems exporting a small amount of data to Excel but suppose when we have a large amount of data then we need to export the data depending upon the size. We can set the limit of the data for insertion into the Excel file.

For an example suppose we have 1000 records in the database table and we need to export all the data to Excel. We can export 100 records at one time and after exporting the next 100 records are added to the Excel file and it is continues to the end of the table data.

In this article I am creating this scenario and developing a web application. So, let's begin with the following procedure:

  • Working with the Database
  • Creating the Web Application
  • Run the Application

Working with the Database

In this section, we'll create the database table and create a Stored Procedure and apply SQL Paging to select the records. Use the following procedure.

Step 1

Create a table in the database with the following code:

  1. USE [Sample]  
  2. GO  
  3.    
  4. CREATE TABLE [dbo].[UserData](  
  5.     [UserID] [int] Primary Key IDENTITY(1,1),  
  6.     [Name] [varchar](50) NULL,  
  7.     [Email] [nvarchar](50) NULL,  
  8.     [City] [varchar](50) NULL  
  9. )  

There is a total 4 fields defined in the table named "UserData".

Step 2

Enter records into the table.

Step 3

Create a Stored Procedure to select the data using SQL Paging with the following code:

  1. USE [Sample]  
  2. GO  
  3.    
  4. CREATE Procedure [dbo].[SP_GetExcelData]  
  5.    
  6. @City varchar(50),  
  7. @RowsPerPage int,  
  8. @PageNumber int  
  9. AS  
  10.    
  11. Begin  
  12.             Select * from UserData where City= @City order by UserID  
  13.             offset (@PageNumber) Rows Fetch Next @RowsPerPage Rows only   
  14. End  

Step 4

We can also execute this procedure to show the results. Have a look:

Stored Procedure Execution

Creating Web Application

In this section we will create the web application and export the data to Excel. So, let's begin with the following procedure.

Step 1

Add an Excel file to the Solution.

Step 2

Add a Web Form with the following code:

  1. <body>  
  2.     <form id="form1" runat="server">  
  3.     <div>  
  4.         <table style="height: 116px; width: 283px">  
  5.             <tr>  
  6.                 <td><asp:Label ID="LblCityName" runat="server">Enter City Name</asp:Label></td>  
  7.                 <td><asp:TextBox ID="TxtCityName" runat="server"></asp:TextBox></td>  
  8.             </tr>  
  9.             <tr>  
  10.                 <td><asp:Button ID="BtnSubmit" runat="server" Text="Submit" OnClick="BtnSubmit_Click" /></td>  
  11.                 <td><asp:Label ID="LblMessage" runat="server"></asp:Label></td>  
  12.             </tr>  
  13.         </table>     
  14.     </div>  
  15.     </form>  
  16. </body>  

So far we have created a web form with a text box in which we can enter the city and fetch the records that are related to that city.

Step 3

Add the following code to the Web.Config file:

  1. <appSettings>  
  2.     <add key="UserRecord" value="6"/>  
  3. </appSettings>  

In the code above, we have defined the number of records to export at a single time.

Step 4

Add a class named DAL in the solution and replace the code with the following code:

  1. using System.Data;  
  2. using System.Data.SqlClient;  
  3.    
  4. namespace UserWebApp  
  5. {  
  6.     public class DAL  
  7.     {  
  8.         SqlConnection con;  
  9.         SqlDataAdapter adap;  
  10.         DataTable dt;  
  11.         public DAL()  
  12.         {  
  13.             con = new SqlConnection(@"Your Connection String");  
  14.         }  
  15.    
  16.         public DataTable GetData(string UserCity, int RowsPerPage, int PageNumber)  
  17.         {  
  18.             adap = new SqlDataAdapter("SP_GetExcelData", con);  
  19.             adap.SelectCommand.CommandType = CommandType.StoredProcedure;  
  20.             adap.SelectCommand.Parameters.Add("@city", SqlDbType.VarChar).Value = UserCity;  
  21.             adap.SelectCommand.Parameters.Add("@RowsPerPage", SqlDbType.Int).Value = RowsPerPage;  
  22.             adap.SelectCommand.Parameters.Add("@PageNumber", SqlDbType.Int).Value = PageNumber;  
  23.             dt = new DataTable();  
  24.             adap.Fill(dt);  
  25.             return dt;  
  26.         }  
  27.    
  28.         public DataTable GetData(string UserCity)  
  29.         {  
  30.             adap = new SqlDataAdapter("Select * from UserData where City='" + UserCity + "'", con);  
  31.             dt = new DataTable();  
  32.             adap.Fill(dt);  
  33.             return dt;  
  34.         }  
  35.     }  
  36. }  

In the code above, we are creating the methods to get the data from the database.

Step 5

In the code behind page, replace the code with the following code:

  1. using System;  
  2. using System.Configuration;  
  3. using System.Data;  
  4. using System.IO;  
  5. using System.Web.UI;  
  6. using System.Web.UI.WebControls;  
  7.    
  8. namespace UserWebApp  
  9. {  
  10.     public partial class UserWebForm : System.Web.UI.Page  
  11.     {  
  12.    
  13.         protected void Page_Load(object sender, EventArgs e)  
  14.         {  
  15.    
  16.         }  
  17.    
  18.         protected void BtnSubmit_Click(object sender, EventArgs e)  
  19.         {  
  20.             InsertRecordToExcel();          
  21.         }         
  22.    
  23.         public void InsertRecordToExcel()  
  24.         {  
  25.             DAL ObjDal = new DAL();  
  26.             string record = ConfigurationManager.AppSettings["UserRecord"];  
  27.             string RecordPageNumber = ConfigurationManager.AppSettings["PageNumber"];  
  28.             try  
  29.             {  
  30.                 int RowsPerPage = Convert.ToInt32(record);  
  31.                 int PageNumber = 0;  
  32.                 string filename = Server.MapPath("UserDataSheet");  
  33.                 string City = TxtCityName.Text;  
  34.                 DataTable dt = new DataTable();  
  35.                 StringWriter writer = new StringWriter();  
  36.                 HtmlTextWriter htmlWriter = new HtmlTextWriter(writer);  
  37.                 DataTable dt2 = new DataTable();  
  38.                 dt2 = ObjDal.GetData(City);  
  39.                 for (int i = 0; i < dt2.Rows.Count; i++)  
  40.                 {  
  41.                     dt = ObjDal.GetData(City, RowsPerPage, PageNumber);  
  42.                     GridView gridView = new GridView();  
  43.                     DataTable dtn = new DataTable();  
  44.                     gridView.DataSource = dt;  
  45.                     gridView.AutoGenerateColumns = true;  
  46.                     gridView.ShowHeader = (i == 0);  
  47.                     gridView.DataBind();  
  48.                     gridView.RenderControl(htmlWriter);  
  49.                     Response.Clear();  
  50.                     Response.AddHeader("content-disposition", "attachment;filename=" + filename + ".xls");  
  51.                     Response.Charset = "";  
  52.                     PageNumber = PageNumber + RowsPerPage;  
  53.                     i = PageNumber;  
  54.                 }  
  55.                 htmlWriter.Close();  
  56.                 Response.Write(writer.ToString());  
  57.                 Response.End();  
  58.             }  
  59.    
  60.             catch (Exception ex)  
  61.             {  
  62.                 throw ex;  
  63.             }  
  64.         }  
  65.     }  
  66. }  

That's it.

Run the Application

Debug the application and enter the city name and click on Submit

Web Form

You can see that the records are exported to the Excel file.

Excel File

Summary

This article described how to export the large amount of data to an Excel file with the predefined number of records to export. Thanks for reading.


Similar Articles