/// <summary>
/// 生成Execl控制类 /// </summary> public class ExeclManager { /// <summary> /// 通过DataTable生成Execl /// </summary> /// <param name="DTInputDataTable">输入DataTable</param> public static void DataTableToExcel(System.Data.DataTable DTInputDataTable) {//创建输出的GridView
GridView GVResponse = null; //创建会话 HttpContext HCRequest = HttpContext.Current;System.IO.StringWriter strWriter = null;
System.Web.UI.HtmlTextWriter htmlWriter = null;if (DTInputDataTable != null)
{ // 设置编码和附件格式 HCRequest.Response.Clear(); HCRequest.Response.Buffer = true; HCRequest.Response.ContentType = "application/vnd.ms-excel"; HCRequest.Response.AddHeader("Content-Disposition", "inline;filename=Execl.xls"); HCRequest.Response.ContentEncoding = System.Text.Encoding.UTF8; HCRequest.Response.Charset = "UTF-8"; HCRequest.Response.Write("<html><head><meta http-equiv=Content-Type content=\"text/html; charset=utf-8\">"); // 导出excel文件 strWriter = new System.IO.StringWriter(); htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter);// 为了解决GVResponse中可能进行了分页的情况，需要重新定义一个无分页的GridView
GVResponse = new GridView(); GVResponse.RowDataBound +=new GridViewRowEventHandler(GVResponse_RowDataBound); GVResponse.DataSource = DTInputDataTable; GVResponse.AllowPaging = false; GVResponse.DataBind(); // 输出数据 GVResponse.RenderControl(htmlWriter); HCRequest.Response.Write(strWriter.ToString()); HCRequest.Response.Write("</body></html>");HCRequest.Response.End();
} } static void GVResponse_RowDataBound(object sender, GridViewRowEventArgs e) { if (e.Row.RowType == DataControlRowType.DataRow) { //e.Row.Cells[0].Attributes.Add("style", "vnd.ms-excel.numberformat:@");//这里是将要导出到execl里的第一列格式化为字符类型。 //e.Row.Cells[3].Attributes.Add("style", "vnd.ms-excel.numberformat:￥#,###.00");//这里是格式化为货币类型。 for (int i = 0; i < e.Row.Cells.Count; i++) { e.Row.Cells[i].Attributes.Add("style", "vnd.ms-excel.numberformat:@"); } //1） 文本：vnd.ms-excel.numberformat:@ //2） 日期：vnd.ms-excel.numberformat:yyyy/mm/dd //3） 数字：vnd.ms-excel.numberformat:#,##0.00 //4） 货币：vnd.ms-excel.numberformat:￥#,##0.00 //5） 百分比：vnd.ms-excel.numberformat: #0.00% } }/// <summary>
/// 通过SqlDataSource生成Execl /// </summary> /// <param name="SDSInputSqlDataSource">SqlDataSource</param> public static void SqlDataSourceToExcel(SqlDataSource SDSInputSqlDataSource) {//创建输出的GridView
GridView GVResponse = null; //创建会话 HttpContext HCRequest = HttpContext.Current;System.IO.StringWriter strWriter = null;
System.Web.UI.HtmlTextWriter htmlWriter = null;if (SDSInputSqlDataSource != null)
{ // 设置编码和附件格式 HCRequest.Response.ContentType = "application/vnd.ms-excel"; HCRequest.Response.AddHeader("Content-Disposition", "inline;filename=Execl.xls"); HCRequest.Response.ContentEncoding = System.Text.Encoding.UTF8; HCRequest.Response.Charset = "UTF-8";// 导出excel文件
strWriter = new System.IO.StringWriter(); htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter);// 为了解决GVResponse中可能进行了分页的情况，需要重新定义一个无分页的GridView
GVResponse = new GridView(); GVResponse.DataSource = SDSInputSqlDataSource; GVResponse.AllowPaging = false; GVResponse.DataBind();// 输出数据
GVResponse.RenderControl(htmlWriter); HCRequest.Response.Write(strWriter.ToString()); HCRequest.Response.End(); } }/// <summary>
/// 通过GridView生成Execl /// </summary> /// <param name="GVInputGridView">GridView</param> public static void GridViewToExcel(GridView GVInputGridView) {//创建输出的GridView
GridView GVResponse = null; //创建会话 HttpContext HCRequest = HttpContext.Current;System.IO.StringWriter strWriter = null;
System.Web.UI.HtmlTextWriter htmlWriter = null;if (GVInputGridView != null)
{ // 设置编码和附件格式 HCRequest.Response.ContentType = "application/vnd.ms-excel"; HCRequest.Response.AddHeader("Content-Disposition", "inline;filename=Execl.xls"); HCRequest.Response.ContentEncoding = System.Text.Encoding.UTF8; HCRequest.Response.Charset = "UTF-8";// 导出excel文件
strWriter = new System.IO.StringWriter(); htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter);// 为了解决GVResponse中可能进行了分页的情况，需要重新定义一个无分页的GridView
GVResponse = GVInputGridView; GVResponse.AllowPaging = false; GVResponse.DataBind();// 输出数据
GVResponse.RenderControl(htmlWriter); HCRequest.Response.Write(strWriter.ToString()); HCRequest.Response.End(); } } }