/// <summary>
/// DataTable通过流导出Excel
/// </summary>
/// <param name="ds">数据源DataSet</param>
/// <param name="columns">DataTable中列对应的列名(可以是中文),若为null则取DataTable中的字段名</param>
/// <param name="fileName">保存文件名(例如：a.xls)</param>
/// <returns></returns>
public bool StreamExport(System.Data.DataTable dt, string[] columns, string fileName, System.Web.UI.Page pages)
{
if (dt.Rows.Count > 65535) //总行数大于Excel的行数
{
throw new Exception("预导出的数据总行数大于excel的行数");
}
if (string.IsNullOrEmpty(fileName)) return false;
StringBuilder content = new StringBuilder();
StringBuilder strtitle = new StringBuilder();
content.Append("<html xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel' xmlns='http://www.w3.org/TR/REC-html40'>");
content.Append("<head><title></title><meta http-equiv='Content-Type' content=\"text/html; charset=gb2312\">");
//注意：[if gte mso 9]到[endif]之间的代码，用于显示Excel的网格线，若不想显示Excel的网格线，可以去掉此代码
content.Append("<!--[if gte mso 9]>");
content.Append("<xml>");
content.Append(" <x:ExcelWorkbook>");
content.Append(" <x:ExcelWorksheets>");
content.Append(" <x:ExcelWorksheet>");
content.Append(" <x:Name>Sheet1</x:Name>");
content.Append(" <x:WorksheetOptions>");
content.Append(" <x:Print>");
content.Append(" <x:ValidPrinterInfo />");
content.Append(" </x:Print>");
content.Append(" </x:WorksheetOptions>");
content.Append(" </x:ExcelWorksheet>");
content.Append(" </x:ExcelWorksheets>");
content.Append("</x:ExcelWorkbook>");
content.Append("</xml>");
content.Append("<![endif]-->");
content.Append("</head><body><table style='border-collapse:collapse;table-layout:fixed;'><tr>");
if (columns != null)
{
for (int i = 0; i < columns.Length; i++)
{
if (columns[i] != null && columns[i] != "")
{
content.Append("<td><b>" + columns[i] + "</b></td>");
}
else
{
content.Append("<td><b>" + dt.Columns[i].ColumnName + "</b></td>");
}
}
}
else
{
for (int j = 0; j < dt.Columns.Count; j++)
{
content.Append("<td><b>" + dt.Columns[j].ColumnName + "</b></td>");
}
}
content.Append("</tr>\n");
for (int j = 0; j < dt.Rows.Count; j++)
{
content.Append("<tr>");
for (int k = 0; k < dt.Columns.Count; k++)
{
object obj = dt.Rows[j][k];
Type type = obj.GetType();
if (type.Name == "Int32" || type.Name == "Single" || type.Name == "Double" || type.Name == "Decimal")
{
double d = obj == DBNull.Value ? 0.0d : Convert.ToDouble(obj);
if (type.Name == "Int32" || (d - Math.Truncate(d) == 0))
content.AppendFormat("<td style='vnd.ms-excel.numberformat:#,##0'>{0}</td>", obj);
else
content.AppendFormat("<td style='vnd.ms-excel.numberformat:#,##0.00'>{0}</td>", obj);
}
else
content.AppendFormat("<td style='vnd.ms-excel.numberformat:@'>{0}</td>", obj);
}
content.Append("</tr>\n");
}
content.Append("</table></body></html>