public void Result( )
{
try
{
StringBuilder sql = new StringBuilder();
List<SqlParameter> parameters = new List<SqlParameter>();
sql.Append(@"sql 查询语句");
DataTable dt = DbHelperSQL.Query(sql.ToString()).Tables[0];
StringBuilder TableHead = new StringBuilder();
StringBuilder TableFoot = new StringBuilder();
TableHead.Append("<tr style="font-weight: bold; white-space: nowrap;">")
.Append("<td rowspan ="2"></td></tr>")
.Append("<tr></tr>");
TableFoot.Append("<tr><td colspan = "4">导出时间：" + DateTime.Now.ToString("yyyy年MM月dd日 HH:mm:ss") + "</td></tr>");
DataTableToExcel(dt, TableHead.ToString(), TableFoot.ToString(), name + "汇总_" + DateTime.Now.ToString("yyyyMM"));
}
catch (Exception ex)
{
}
}
/// <summary>
/// DataTable导出Excel
/// </summary>
/// <param name="data">集合</param>
/// <param name="ChartHead">表头</param>
/// <param name="fileName">文件名称</param>
public static void DataTableToExcel(DataTable data, string ChartHead, string ChartFoot, string fileName)
{
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HttpContext.Current.Response.ContentEncoding = Encoding.UTF8;
HttpContext.Current.Response.Charset = "Utf-8";
HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName + ".xls", Encoding.UTF8));
StringBuilder sbHtml = new StringBuilder();
sbHtml.AppendLine("<meta http-equiv="Content-Type" content="text/html; charset=utf-8">");
sbHtml.AppendLine("<table cellspacing="0" cellpadding="5" rules="all" border="1">");
//写出表头
sbHtml.AppendLine(ChartHead);
string NumberAsTextExp = "vnd.ms-excel.numberformat:@";
//写数据
foreach (DataRow row in data.Rows)
{
sbHtml.Append("<tr>");
for (int i = 0; i < row.ItemArray.Count(); i++)
{
if (row[i].GetType() == typeof(string))
{
sbHtml.Append("<td style='" + NumberAsTextExp + "'>").Append(row[i]).Append("</td>");
}
else
{
sbHtml.Append("<td>").Append(row[i]).Append("</td>");
}
}
sbHtml.AppendLine("</tr>");
}
//写汇总行
sbHtml.AppendLine(ChartFoot);
sbHtml.AppendLine("</table>");
HttpContext.Current.Response.Write(sbHtml.ToString());
HttpContext.Current.Response.End();
}