A GridView is a very important and useful control of ASP.Net. In this article a grid is exported into an .xls file using HtmlTextWriter and StringWriter ASP.Net controls.

Now we will see one by one how to fill a GridView from a database and then get the GridView data saved as an Excel file from the GridView.

Step 1

Go to the SQL Database side.

Create an Employee Information table.

  1. create table EmployeeDB
  2. (
  3. EmpID int primary key identity(1,1),
  4. EmpName varchar(20),
  5. EmpGender varchar(10),
  6. EmpDOB date null,
  7. EmpDepartment varchar(20)
  8. )

Go to SQL Database Side

Some data is inserted into the database as in the following screen.

data insert into Database

Now we will do something with all this selected data filled into the GridView.

Step 2

Go to the Visual Studio side as in the following.

Now go to your project design (.aspx) page.

project design

Add a page using Master page.

Add page

Provide a specific name for the page and click the Add button.

Next add a GridView control and button control on the design page.

Configure your database connection string in your project's web.config file.

  1. <connectionStrings>
  2. <add name="connstr" connectionString="Data Source=RAKESH-PC;Initial Catalog=SqlServerTech;User ID=sa;Password=your SQL DB Password" providerName="System.Data.SqlClient"/>
  4. <add name="Pratical_testConnectionString" connectionString="Data Source=RAKESH-PC;Initial Catalog=Pratical_test;User ID=sa password=****" providerName="System.Data.SqlClient"/>
  5. </connectionStrings>
Page.aspx.cs side

Go to the Page Code side and write code to fill in the GridView and the GridView data is exported into Excel file format.
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Web;
  5. using System.Web.UI;
  6. using System.Web.UI.WebControls;
  7. using System.Configuration;
  8. using System.Data;
  9. using System.Data.SqlClient;
  10. using System.IO;
  11. using System.Text;
  12. using System.Web.UI.HtmlControls;
  14. namespace Test_WebApplication.UI
  15. {
  16. public partial class GridToExcel : System.Web.UI.Page
  17. {
  18. string conString = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
  19. protected void Page_Load(object sender, EventArgs e)
  20. {
  21. if (!Page.IsPostBack)
  22. {
  23. EmployeeDataGrid();
  24. }
  25. }
  26. private void EmployeeDataGrid()
  27. {
  28. using (SqlConnection con = new SqlConnection(conString))
  29. {
  30. using (SqlCommand cmd = new SqlCommand("select * from EmployeeDB"))
  31. {
  32. SqlDataAdapter dt = new SqlDataAdapter();
  33. try
  34. {
  35. cmd.Connection = con;
  36. con.Open();
  37. dt.SelectCommand = cmd;
  39. DataTable dTable = new DataTable();
  40. dt.Fill(dTable);
  41. SqlDataReader sdr = cmd.ExecuteReader();
  42. GridEmpData.DataSource = dTable;
  43. GridEmpData.DataBind();
  44. }
  45. catch (Exception)
  46. {
  47. // Error msg display here
  48. }
  49. }
  50. }
  51. }
  53. protected void btnExportGridintoExcel_Click(object sender, EventArgs e)
  54. {
  55. ExportGridToExcel();
  56. }
  58. private void ExportGridToExcel()
  59. {
  60. Response.Clear();
  61. Response.AddHeader("content-disposition", "attachment;filename=ExportGridData.xls");
  62. Response.ContentType = "File/Data.xls";
  63. StringWriter StringWriter = new System.IO.StringWriter();
  64. HtmlTextWriter HtmlTextWriter = new HtmlTextWriter(StringWriter);
  66. GridEmpData.RenderControl(HtmlTextWriter);
  67. Response.Write(StringWriter.ToString());
  68. Response.End();
  69. }
  71. public override void VerifyRenderingInServerForm(Control control)
  72. {
  73. // controller
  74. }
  75. }
  76. }
Now run your page and display the data in the web page as in the following:

run your page

Now click on the button and save the displayed data in the .xls file as in the following:

click on button

Finally you are seeing the GridView data in the Excel file.

Grid view data

Finally I hope you understand how to GridView data is exported into an Excel file and saved on disk.