14/03/2016 ExportingLargeTableDatatoExcelinASP.
Net
In Focus Roger
Ask a Question Contribute
TECHNOLOGIES ANSWERS BLOGS VIDEOS INTERVIEWS BOOKS C#CornerSearch
NEWS CHAPTERS CAREER JOBS CODE IDEAS
ARTICLE READER LEVEL:
Exporting Large Table Data to Excel in
ASP.Net
By Nimit Joshi on Sep 18, 2014
This article describes how to export a large amount of data to an Excel file with the
predefined number of records to export.
19.3 k 8 1
Download Files: UserWebApp.rar Download Aspose, it really helps
Download 100% FREE Spire Office APIs
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:
01. USE[Sample]
02. GO
03.
04. CREATETABLE[dbo].[UserData](
http://www.csharpcorner.com/uploadfile/4b0136/exportinglargetabledatatoexcelinaspnet/ 1/10
14/03/2016 ExportingLargeTableDatatoExcelinASP.Net
05. [UserID][int]PrimaryKeyIDENTITY(1,1),
06. [Name][varchar](50)NULL,
07. [Email][nvarchar](50)NULL,
08. [City][varchar](50)NULL
09. )
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:
01. USE[Sample]
02. GO
03.
04. CREATEProcedure[dbo].[SP_GetExcelData]
05.
06. @Cityvarchar(50),
07. @RowsPerPageint,
08. @PageNumberint
09. AS
10.
11. Begin
12. Select*fromUserDatawhereCity=@CityorderbyUserID
13. offset(@PageNumber)RowsFetchNext@RowsPerPageRowsonly
14. End
Step 4
We can also execute this procedure to show the results. Have a look:
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.
http://www.csharpcorner.com/uploadfile/4b0136/exportinglargetabledatatoexcelinaspnet/ 2/10
14/03/2016 ExportingLargeTableDatatoExcelinASP.Net
Step 2
Add a Web Form with the following code:
01. <body>
02. <formid="form1"runat="server">
03. <div>
04. <tablestyle="height:116px;width:283px">
05. <tr>
06. <td><asp:LabelID="LblCityName"runat="server">EnterCityName</asp:Label>
</td>
07. <td><asp:TextBoxID="TxtCityName"runat="server"></asp:TextBox></td>
08. </tr>
09. <tr>
10. <td>
<asp:ButtonID="BtnSubmit"runat="server"Text="Submit"OnClick="BtnSubmit_Click"/></td>
11. <td><asp:LabelID="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:
01. <appSettings>
02. <addkey="UserRecord"value="6"/>
03. </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:
01. usingSystem.Data;
02. usingSystem.Data.SqlClient;
03.
04. namespaceUserWebApp
05. {
06. publicclassDAL
07. {
08. SqlConnectioncon;
09. SqlDataAdapteradap;
10. DataTabledt;
11. publicDAL()
12. {
13. con=newSqlConnection(@"YourConnectionString");
14. }
15.
16. publicDataTableGetData(stringUserCity,intRowsPerPage,intPageNumber)
17. {
18. adap=newSqlDataAdapter("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=newDataTable();
24. adap.Fill(dt);
25. returndt;
http://www.csharpcorner.com/uploadfile/4b0136/exportinglargetabledatatoexcelinaspnet/ 3/10
14/03/2016 ExportingLargeTableDatatoExcelinASP.Net
26. }
27.
28. publicDataTableGetData(stringUserCity)
29. {
30. adap=newSqlDataAdapter("Select*fromUserDatawhereCity='"+UserCity+"'",con);
31. dt=newDataTable();
32. adap.Fill(dt);
33. returndt;
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:
01. usingSystem;
02. usingSystem.Configuration;
03. usingSystem.Data;
04. usingSystem.IO;
05. usingSystem.Web.UI;
06. usingSystem.Web.UI.WebControls;
07.
08. namespaceUserWebApp
09. {
10. publicpartialclassUserWebForm:System.Web.UI.Page
11. {
12.
13. protectedvoidPage_Load(objectsender,EventArgse)
14. {
15.
16. }
17.
18. protectedvoidBtnSubmit_Click(objectsender,EventArgse)
19. {
20. InsertRecordToExcel();
21. }
22.
23. publicvoidInsertRecordToExcel()
24. {
25. DALObjDal=newDAL();
26. stringrecord=ConfigurationManager.AppSettings["UserRecord"];
27. stringRecordPageNumber=ConfigurationManager.AppSettings["PageNumber"];
28. try
29. {
30. intRowsPerPage=Convert.ToInt32(record);
31. intPageNumber=0;
32. stringfilename=Server.MapPath("UserDataSheet");
33. stringCity=TxtCityName.Text;
34. DataTabledt=newDataTable();
35. StringWriterwriter=newStringWriter();
36. HtmlTextWriterhtmlWriter=newHtmlTextWriter(writer);
37. DataTabledt2=newDataTable();
38. dt2=ObjDal.GetData(City);
39. for(inti=0;i<dt2.Rows.Count;i++)
40. {
41. dt=ObjDal.GetData(City,RowsPerPage,PageNumber);
42. GridViewgridView=newGridView();
43. DataTabledtn=newDataTable();
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
http://www.csharpcorner.com/uploadfile/4b0136/exportinglargetabledatatoexcelinaspnet/ 4/10
14/03/2016 ExportingLargeTableDatatoExcelinASP.Net
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(Exceptionex)
61. {
62. throwex;
63. }
64. }
65. }
66. }
That's it.
Run the Application
Debug the application and enter the city name and click on Submit
You can see that the records are exported to the Excel file.
http://www.csharpcorner.com/uploadfile/4b0136/exportinglargetabledatatoexcelinaspnet/ 5/10
14/03/2016 ExportingLargeTableDatatoExcelinASP.Net
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.
Nimit Joshi
Nimit Joshi is a .NET techie, author and software developer. Nimit's background
includes Master's in Computer Application and Bachelor's in Computer Application.
Nimit is a one time Microsoft MVP and two times C# Corner ... Read more
Personal Blog:http://www.c-sharpcorner.com/authors/4b0136/nimit-joshi.aspx
http://www.csharpcorner.com/uploadfile/4b0136/exportinglargetabledatatoexcelinaspnet/ 6/10
14/03/2016 ExportingLargeTableDatatoExcelinASP.Net
Rank 3.4m Platinum 2 1
29 Read Member Times Time
RELATED ARTICLES
How to Export Data to One Worksheet and Export Gridview to Excel Using ASP.Net C#
Create Pivot Table in Another Based on the Export Data From Database Table to Excel File
Data in ASP.Net MVC
Export the Excel File in ASP.Net Web API Fastest way to read and write to Excel
Export HTML to Excel and More
COMMENTS 8 of 8
Nice article sir
Saineshwar Bageri Sep 22, 2014
89 2.3k 2.9m 1 1 Post Reply
Thanks Saineshwar...
Nimit Joshi Sep 22, 2014
29 4.8k 3.4m 0
Great Article ! Can you please tell me how can I decorate (css style) my excel
workbook.. like having some text bold, having a white background with no grid lines
etc.. and further is it easily possible to add multiple worksheets inside a single file?
Dinesh Sharma Sep 22, 2014
733 1 0 1 0 Post Reply
Super
Vithal Wadje Sep 25, 2014
18 7.3k 7.1m 1 1 Post Reply
Thanks Vithal Wadje Sir..
Nimit Joshi Sep 25, 2014
29 4.8k 3.4m 0
Offset (@PageNumber) Rows Fetch Next @RowsPerPage Rows only
umesh adroja Jul 25, 2015
732 2 0 0 0 Post Reply
Offset (@PageNumber) Rows Fetch Next @RowsPerPage Rows only ,,,,,,i m getting
error in this line how can i solve this
umesh adroja Jul 25, 2015
732 2 0 0 1 Post Reply
http://www.csharpcorner.com/uploadfile/4b0136/exportinglargetabledatatoexcelinaspnet/ 7/10
14/03/2016 ExportingLargeTableDatatoExcelinASP.Net
Please post your error in forums in this website.
Nimit Joshi Jul 27, 2015
29 4.8k 3.4m 0
Type your comment here and press Enter Key....
Follow Comments
COMMENT USING
0Comments Sortby Oldest
Addacomment...
FacebookCommentsPlugin
SPONSORED BY
Spire.Doc - Free .NET
Word API
Use Spire.Doc to create, read,
write, print, and convert word
documents to OpenXML, RTF,
TXT, XPS, EPUB, EMF, HTML,
Image and PDF.
TRENDING UP
SharePoint 2010: Customization Of
01 The Data View Web Part - XSLT
http://www.csharpcorner.com/uploadfile/4b0136/exportinglargetabledatatoexcelinaspnet/ 8/10
14/03/2016 ExportingLargeTableDatatoExcelinASP.Net
Top Reasons Why Web Developers
02 Choose AngularJS
.NET Core: Introduction To
03 Microsoft.Data.Sqlite
Useful SQL Queries
04
Top 10 Most Used SQL Queries
05
Paging, Sorting, And Filtering With
06 Partial View In ASP.NET MVC 5
Archive SharePoint List Items To SQL
07 Database using CSOM - Part Two
Archive SharePoint List Items to SQL
08 Database Using CSOM: Part One
Enable Rating In SharePoint
09 Document Library
Enable 'Sign in as Different User' in
10 SharePoint 2013
View All
Follow@csharpcorner 56.4Kfollowers
C#Corner
149,589likes
LikePage Share
Bethefirstofyourfriendstolikethis
MVPs MOST VIEWED LEGENDS NOW PRIZES REVIEWS SURVEY CERTIFICATIONS
DOWNLOADS
http://www.csharpcorner.com/uploadfile/4b0136/exportinglargetabledatatoexcelinaspnet/ Hosted By CBeyond Cloud Services
9/10
14/03/2016 ExportingLargeTableDatatoExcelinASP.Net
Hosted By CBeyond Cloud Services
ABOUT US MEDIA KIT MEMBERS STUDENTS LINKS
CONTACT US PRIVACY POLICY TERMS & CONDITIONS SITEMAP REPORT ABUSE
2016 C# Corner. All contents are copyright of their authors.
http://www.csharpcorner.com/uploadfile/4b0136/exportinglargetabledatatoexcelinaspnet/ 10/10
