C # export Excel using GridView,
Using System. Data;
Using System. Web;
Using System. Web. UI;
Using System. Web. UI. WebControls;
Using System. IO;
Using System. Text;
Namespace DotNet. Utilities
{
/// <Summary>
/// Summary description for GridViewExport
/// </Summary>
Public class GridViewExport
{
Public GridViewExport ()
{
//
// TODO: Add constructor logic here
//
}
Public static void Export (string fileName, GridView gv)
{
HttpContext. Current. Response. Clear ();
HttpContext. Current. Response. AddHeader (
"Content-disposition", string. Format ("attachment; filename = {0}", fileName ));
HttpContext. Current. Response. ContentType = "application/ms-excel ";
// HttpContext. Current. Response. Charset = "UTF-8 ";
Using (StringWriter sw = new StringWriter ())
{
Using (HtmlTextWriter htw = new HtmlTextWriter (sw ))
{
// Create a form to contain the grid
Table table = new Table ();
Table. GridLines = GridLines. Both; // Add a solid line between cells.
// Add the header row to the table
If (gv. HeaderRow! = Null)
{
PrepareControlForExport (gv. HeaderRow );
Table. Rows. Add (gv. HeaderRow );
}
// Add each of the data rows to the table
Foreach (GridViewRow row in gv. Rows)
{
PrepareControlForExport (row );
Table. Rows. Add (row );
}
// Add the footer row to the table
If (gv. FooterRow! = Null)
{
PrepareControlForExport (gv. FooterRow );
Table. Rows. Add (gv. FooterRow );
}
// Render the table into the htmlwriter
Table. RenderControl (htw );
// Render the htmlwriter into the response
HttpContext. Current. Response. Write (sw. ToString ());
HttpContext. Current. Response. End ();
}
}
}
/// <Summary>
/// Replace any of the contained controls with literals
/// </Summary>
/// <Param name = "control"> </param>
Private static void PrepareControlForExport (Control control)
{
For (int I = 0; I <control. Controls. Count; I ++)
{
Control current = control. Controls [I];
If (current is LinkButton)
{
Control. Controls. Remove (current );
Control. Controls. AddAt (I, new LiteralControl (current as LinkButton). Text ));
}
Else if (current is ImageButton)
{
Control. Controls. Remove (current );
Control. Controls. AddAt (I, new LiteralControl (current as ImageButton). AlternateText ));
}
Else if (current is HyperLink)
{
Control. Controls. Remove (current );
Control. Controls. AddAt (I, new LiteralControl (current as HyperLink). Text ));
}
Else if (current is DropDownList)
{
Control. Controls. Remove (current );
Control. Controls. AddAt (I, new LiteralControl (current as DropDownList). SelectedItem. Text ));
}
Else if (current is CheckBox)
{
Control. Controls. Remove (current );
Control. Controls. AddAt (I, new LiteralControl (current as CheckBox). Checked? "True": "False "));
}
If (current. HasControls ())
{
PrepareControlForExport (current );
}
}
}
/// <Summary>
/// Export Grid data (all) to Excel
/// Available when all fields are BoundField type
/// If the field is TemplateField, no data is obtained.
/// </Summary>
/// <Param name = "grid"> grid ID </param>
/// <Param name = "dt"> data source </param>
/// <Param name = "excelFileName"> File name of the Excel file to be exported </param>
Public static void OutputExcel (GridView grid, DataTable dt, string excelFileName)
{
Page page = (Page) HttpContext. Current. Handler;
Page. Response. Clear ();
String fileName = System. Web. HttpUtility. UrlEncode (System. Text. Encoding. UTF8.GetBytes (excelFileName ));
Page. Response. AddHeader ("Content-Disposition", "attachment: filename =" + fileName + ". xls ");
Page. Response. ContentType = "application/vnd. ms-excel ";
Page. Response. Charset = "UTF-8 ";
StringBuilder s = new StringBuilder ();
S. append ("<HTML> <HEAD> <TITLE>" + fileName + "</TITLE> <META http-equiv = \" Content-Type \ "content = \" text/ html; charset = UTF-8 \ ">
Int count = grid. Columns. Count;
S. Append ("<table border = 1> ");
S. AppendLine ("<tr> ");
For (int I = 0; I <count; I ++)
{
If (grid. Columns [I]. GetType () = typeof (BoundField ))
S. Append ("<td>" + grid. Columns [I]. HeaderText + "</td> ");
// S. Append ("<td>" + grid. Columns [I]. HeaderText + "</td> ");
}
S. Append ("</tr> ");
Foreach (DataRow dr in dt. Rows)
{
S. AppendLine ("<tr> ");
For (int n = 0; n <count; n ++)
{
If (grid. Columns [n]. Visible & grid. Columns [n]. GetType () = typeof (BoundField ))
S. Append ("<td>" + dr [(BoundField) grid. Columns [n]). DataField]. ToString () + "</td> ");
}
S. AppendLine ("</tr> ");
}
S. Append ("</table> ");
S. Append ("</body>
Page. Response. BinaryWrite (System. Text. Encoding. GetEncoding ("UTF-8"). GetBytes (s. ToString ()));
Page. Response. End ();
}
}
}