生成excel控制类
/// <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();
}
}
}
