Exporting Large Table Data to Excel in ASP.Net

Nimit Joshi
10y
62.5k
0
1

Article

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:

USE [Sample]
GO
CREATE TABLE [dbo].[UserData](
[UserID] [int] Primary Key IDENTITY(1,1),
[Name] [varchar](50) NULL,
[Email] [nvarchar](50) NULL,
[City] [varchar](50) NULL
)

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:

USE [Sample]
GO
CREATE Procedure [dbo].[SP_GetExcelData]
@City varchar(50),
@RowsPerPage int,
@PageNumber int
AS
Begin
Select * from UserData where City= @City order by UserID
offset (@PageNumber) Rows Fetch Next @RowsPerPage Rows only
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:

<body>
<form id="form1" runat="server">
<div>
<table style="height: 116px; width: 283px">
<tr>
<td><asp:Label ID="LblCityName" runat="server">Enter City Name</asp:Label></td>
<td><asp:TextBox ID="TxtCityName" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td><asp:Button ID="BtnSubmit" runat="server" Text="Submit" OnClick="BtnSubmit_Click" /></td>
<td><asp:Label ID="LblMessage" runat="server"></asp:Label></td>
</tr>
</table>
</div>
</form>
</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:

<appSettings>
<add key="UserRecord" value="6"/>
</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:

using System.Data;
using System.Data.SqlClient;
namespace UserWebApp
{
public class DAL
{
SqlConnection con;
SqlDataAdapter adap;
DataTable dt;
public DAL()
{
con = new SqlConnection(@"Your Connection String");
}
public DataTable GetData(string UserCity, int RowsPerPage, int PageNumber)
{
adap = new SqlDataAdapter("SP_GetExcelData", con);
adap.SelectCommand.CommandType = CommandType.StoredProcedure;
adap.SelectCommand.Parameters.Add("@city", SqlDbType.VarChar).Value = UserCity;
adap.SelectCommand.Parameters.Add("@RowsPerPage", SqlDbType.Int).Value = RowsPerPage;
adap.SelectCommand.Parameters.Add("@PageNumber", SqlDbType.Int).Value = PageNumber;
dt = new DataTable();
adap.Fill(dt);
return dt;
}
public DataTable GetData(string UserCity)
{
adap = new SqlDataAdapter("Select * from UserData where City='" + UserCity + "'", con);
dt = new DataTable();
adap.Fill(dt);
return dt;
}
}
}

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:

using System;
using System.Configuration;
using System.Data;
using System.IO;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace UserWebApp
{
public partial class UserWebForm : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void BtnSubmit_Click(object sender, EventArgs e)
{
InsertRecordToExcel();
}
public void InsertRecordToExcel()
{
DAL ObjDal = new DAL();
string record = ConfigurationManager.AppSettings["UserRecord"];
string RecordPageNumber = ConfigurationManager.AppSettings["PageNumber"];
try
{
int RowsPerPage = Convert.ToInt32(record);
int PageNumber = 0;
string filename = Server.MapPath("UserDataSheet");
string City = TxtCityName.Text;
DataTable dt = new DataTable();
StringWriter writer = new StringWriter();
HtmlTextWriter htmlWriter = new HtmlTextWriter(writer);
DataTable dt2 = new DataTable();
dt2 = ObjDal.GetData(City);
for (int i = 0; i < dt2.Rows.Count; i++)
{
dt = ObjDal.GetData(City, RowsPerPage, PageNumber);
GridView gridView = new GridView();
DataTable dtn = new DataTable();
gridView.DataSource = dt;
gridView.AutoGenerateColumns = true;
gridView.ShowHeader = (i == 0);
gridView.DataBind();
gridView.RenderControl(htmlWriter);
Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=" + filename + ".xls");
Response.Charset = "";
PageNumber = PageNumber + RowsPerPage;
i = PageNumber;
}
htmlWriter.Close();
Response.Write(writer.ToString());
Response.End();
}
catch (Exception ex)
{
throw ex;
}
}
}
}