In this article I describe how to export data from Grid View to Excel and Csvusing ASP.NET and C#. Recently I was working on a project where I had a task: We need to export the datasets from the grid view to an Excel sheet with instructions. When I export datasets to an Excel sheet, I have to concentrate on formatting the Excel sheets.
We need to export the name of the GridView header and the background color of the string. I’ve successfully completed my task. I’ve now decided to share my experience with other developers who are looking for the same thing.
For the sake of clarity, I also created the user table in the database.
SQL script
CREATE TABLE [dbo].User](
[Id] [int] IDENTITY(1,1) NOT ZERO,
[name] [nvarchar](maximum) ZERO,
[E-Mail] [nvarchar](maximum) NOT ZERO,
[Password] [nvarchar](maximum) ZERO,
[About] [nvarchar](maximum) ZERO,
[DOB] [datetime2](7) ZERO,
[genus] [int] NOT ZERO,
[location] [nvarchar](max) ZERO,
[contact number] [nvarchar](max) ZERO,
LIMITATION [PK_USER] PRIMARY KEY CLUSTERED
(
[Id] ASC
)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] [PRIMARY].
- So let’s start by adding a blank page.
- Add a web form page to your project and copy the code below.
On my website I have two checks for exporting data to Excel and Csv. Click this button to export the grid data to an Excel spreadsheet or Csv file.
Suspension Code:
<%@ Page language=C# EnableEventValidation=false AutoEventWireup=true CodeFile=GridView.aspx.cs Inherits=GridView %>
Top of Form
Bottom of Form
Code for
using System.Collections.Generic;using System.Data;using System.Data.SqlClient;using System.Data.SqlClient;using System.IO;using System.Linq;using System.Text;using System.Web;using System.Web.UI;using System.Web.WebControls ;
public GridView subclass : System.Web.UI.Page
{
SqlConnection con = new SqlConnection(Data Source=ADEQUATE-ASHOK\SQLEXPRESS01;Initial Catalog=DemoDataBase;User ID=adk;Password=adk@1234);
secure void Page_Load(object sender, EventArgs e)
{
if(!)IsPostBack)
{
BindGrid() ;
}
}
public empty BindGrid()
{
UserDataGrid.DataSource = GetDataSet();
UserDataGrid.DataBind();
}
public DataSet GetDataSet()
{
SqlCommand cmd = new SqlCommand(choose * from Users, con);
SqlDataAdapter adp = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adp.Fill(ds);
return ds ;
}
GridView1_PageIndexChanging(Object sender, GridViewPageEventArgs e)
{
UserDataGrid.DataSource = GetDataSet();
UserDataGrid.PageIndex = e.NewPageIndex;
UserDataGrid.DataBind();
}.
secure empty btnexportexcel_Click(Object-Sender, EventArgs e){ExportGridToExcel();} secure empty btnExcportcsv_Click(Object-Sender, EventArgs e){ExportGridToCsv();} public empty ExportGridToExcel(){Response.Clear();Response.AddHeader(content-disposition, attachment;filename=ExportUserData.xls);Response.ContentType = file/data.xls;StringWriter StringWriter = new System.IO.StringWriter();HtmlTextWriter HtmlTextWriter = new HtmlTextWriter(StringWriter);UserDataGrid.RenderControl(HtmlTextWriter);Response.Write(StringWriter.ToString());Response.End().public void ExportGridToCsv(){// Define the name of the resulting attachment in the name report….. string fileName = Test ;
Response.Clear();
Response.Buffer = True;
Response.AddHeader(Content-Disposition, Attachment; Filename= + Filename + .csv);
Response.Character Set =;
Response.ContentType = Application/Text ;
System.Text.StringBuilder sb. = new System.Text.StringBuilder() ;
// Determine the header of the sorted columns
LinkButton headerLink = new LinkButton();
string headerText = string.Empty ;
for (int k = 0 ; k < UserDataGrid.HeaderRow.Cells.Count ; k++)
{
// separator
headerText = UserDataGrid.HeaderRow.Cells[k].Text ;
sb.Append(headerText + ,) ;
}
// add a new row
sb.Attach(rn) ;
for (int i = 0 ; i < UserDataGrid.Rows.Count ; i++)
{
for (int k = 0 ; k < UserDataGrid.HeaderRow.Cells.Count ; k++)
{
// add a separator and a dash, values of the returned content…..
s.Append(UserDataGrid.Rows[i].Cells[k].Text.Replace(, , , ) + ,) ;
}
// Add a new line
s.Append(rn) ;
}
Reply.Output.Write(s).ToString());
Response.Flush();
Response.End();
}
public override void VerifyRenderingInServerForm(control)
{
/* we call GridView1.RenderControl(htmlTextWriter) for data export,
* so you get an exception that the server control was viewed outside the form.*/
/* To avoid this exception, this code world confirms that the HtmlForm control was viewed at runtime for the specified ASP.NET
server control. */
}
}
You will notice that I have made two changes to the code to avoid the error below.
<%@ page ………… EnableEventValidation=wrong %>
public cancellation VerifyRenderingInServerForm(control)
{
}
If you want to give your Excel spreadsheet its own style and color, use the code below.
Export Grid view to Excel with custom formatting of values and color.
public void CustomExcelStyleExport()
{
Response.ClearContent();
Response.Buffer = true;
Response.AddHeader(table of contents, string.format(attachment; filename={0}, ExportUserData.xls));
Response.contentType = application/ms-excel;
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
UserDataGrid.AllowPaging = false;
UserDataGrid.HeaderRow.Style.Add (background color, #FFFFFFFFFFF);
// Application stlye on gridview cells
for (int i = 0; i < UserDataGrid.HeaderRow.Cells.Count; i+++)
{.
UserDataGrid.HeaderRow.Cells[i].Style.Add (Background color, #009688);
}
int j = 1;
//Set alternate color of the string
foreach (GridViewRow Hvrovrovism in UserDataGrid.Rows)
{
gvrow.BackColor = System.drawing.color.white;
if (j <= UserDataGrid.Rows.Count)
{
if (j % 2 != 0)
{
for (int k = 0; k < gvrow.Cells.Count; k++)
{
gvrow.cells[k].Style.Add (background color, #EFF3FB);
}.
}
j++;
}
UserDataGrid.RenderControl(htw);
Reply.Write(sw.ToString());
Reply.End();
}
Download source code
Related Tags:
wordpress beginners tips,wordpress blogging tips,wordpress tips reddit,wordpress for beginners,best wordpress themes,whatsapp tricks,wordpress basic tips,beginner wordpress,wordpress hacks 2020,wordpress table of images,wordpress columns with images,wordpress divi theme,wordpress tutorial,wordpress plugins,wordpress themes,wordpress tips and tricks 2020,wordpress tips and tricks pdf,wordpress tips 2020,wordpress design tips,wordpress beginner tips,tips for building a wordpress website