最近需要做DataTable 导出Excel,由于DataTable 的结构是任意的,也就是说可以包含各种字段,于是DataTable 中可能含有Html 标记(<tr><td><br> 等),开始按常规方法导出,以\t, \r\n 做分隔符,就会出现问题,导出的Excel文件完全乱了。那些Html标记会在Excel中表现出来,比如<hr>就变成了横线。
System.Web.HttpContext.Current.Response.Write(cell1.ToString());
System.Web.HttpContext.Current.Response.Write(@"\t"));
System.Web.HttpContext.Current.Response.Write(cell2.ToString());
System.Web.HttpContext.Current.Response.Write(@"\r\n"));
System.Web.HttpContext.Current.Response.Write(@"\t"));
System.Web.HttpContext.Current.Response.Write(cell2.ToString());
System.Web.HttpContext.Current.Response.Write(@"\r\n"));
现在按HTML格式导出,就可以了。
另外DataTable 中可能含有字符串字段,但是该字段由数字组成,比如"身份证号码",也需要做处理,转换为字符串格式。
string strFileName = "Export.xls";
System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(strFileName)); //防止文件名出现乱码
System.Web.HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"; //设置输出文件类型为excel文件。
// 因为 DataTable 中可能含有 html (<hr> <br> 等)标记,所以要用特殊的导出方式,即按Html形式导出
StringBuilder line = new StringBuilder();
string NumberAsTextExp = "vnd.ms-excel.numberformat:@";
string s;
line.Append("<table border = 1>");
for (int i = 0; i < dtTable.Rows.Count; i++) //dtTable 是要导出的DataTable
{
line.Append("<tr>");
for (int j = 0; j < dtTable.Columns.Count; j++)
{
line.Append("<td style='"+ NumberAsTextExp + "'>"); //将数字按字符串格式导出,比如身份证号码
s = dtTable.Rows[i][j].ToString();
if (dtTable.Columns[j].DataType == typeof(string))
{
s = Server.HtmlEncode(s);
}
line.Append(s + "</td>");
}
line.Append("</tr>");
}
line.Append("</table>");
System.Web.HttpContext.Current.Response.Write(@"<html><head>");
System.Web.HttpContext.Current.Response.Write(@"<meta http-equiv=""content-type"" content=""text/html; charset=gb2312"">");
System.Web.HttpContext.Current.Response.Write("</head>");
System.Web.HttpContext.Current.Response.Write("<body>");
System.Web.HttpContext.Current.Response.Write(line.ToString());
System.Web.HttpContext.Current.Response.Write("</body>");
System.Web.HttpContext.Current.Response.Write(@"</html>");
System.Web.HttpContext.Current.Response.End();
System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(strFileName)); //防止文件名出现乱码
System.Web.HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"; //设置输出文件类型为excel文件。
// 因为 DataTable 中可能含有 html (<hr> <br> 等)标记,所以要用特殊的导出方式,即按Html形式导出
StringBuilder line = new StringBuilder();
string NumberAsTextExp = "vnd.ms-excel.numberformat:@";
string s;
line.Append("<table border = 1>");
for (int i = 0; i < dtTable.Rows.Count; i++) //dtTable 是要导出的DataTable
{
line.Append("<tr>");
for (int j = 0; j < dtTable.Columns.Count; j++)
{
line.Append("<td style='"+ NumberAsTextExp + "'>"); //将数字按字符串格式导出,比如身份证号码
s = dtTable.Rows[i][j].ToString();
if (dtTable.Columns[j].DataType == typeof(string))
{
s = Server.HtmlEncode(s);
}
line.Append(s + "</td>");
}
line.Append("</tr>");
}
line.Append("</table>");
System.Web.HttpContext.Current.Response.Write(@"<html><head>");
System.Web.HttpContext.Current.Response.Write(@"<meta http-equiv=""content-type"" content=""text/html; charset=gb2312"">");
System.Web.HttpContext.Current.Response.Write("</head>");
System.Web.HttpContext.Current.Response.Write("<body>");
System.Web.HttpContext.Current.Response.Write(line.ToString());
System.Web.HttpContext.Current.Response.Write("</body>");
System.Web.HttpContext.Current.Response.Write(@"</html>");
System.Web.HttpContext.Current.Response.End();