ASP.NET生成Excel并下载

实现生成Excel文件，并下载的功能，代码：

using System;

using System.Collections.Generic;

using System.Text;

using System.Xml;

using System.Xml.Xsl;

using System.Web;

using System.Data;

using System.IO;

namespace Web.Common

{

public class ExcelDownLoad

{/// <summary>

/// 的数据源的数据

/// </summary>

/// <param name="dt">数据源</param>

/// <param name="fileName">输出文件名</param>

public static void Export(DataTable dt, string fileName)

{

DataSet dsExport = new DataSet("Export");

DataTable dtExport = dt.Copy();

dtExport.TableName = "Values";

dsExport.Tables.Add(dtExport);

string[] headers = new string[dtExport.Columns.Count];

string[] fields = new string[dtExport.Columns.Count];

for (int i = 0; i < dtExport.Columns.Count; i++)

{

headers[i] = String.IsNullOrEmpty(dtExport.Columns[i].Caption) ? dtExport.Columns[i].ColumnName : dtExport.Columns[i].Caption;

fields[i] = ReplaceSpecialChars(dtExport.Columns[i].ColumnName);

}

Export(dsExport, headers, fields, fileName, Encoding.GetEncoding("GB2312"));

}

/// <summary>

/// 导出的数据源的数据

/// </summary>

/// <param name="ds">数据源</param>

/// <param name="fields">导出的字段数组</param>

/// <param name="fileName">输出文件名</param>

/// <param name="encoding">编码</param>

private static void Export(DataSet ds, string[] headers, string[] fields, string fileName, Encoding encoding)

{

HttpContext.Current.Response.Clear();

HttpContext.Current.Response.Buffer = true;

HttpContext.Current.Response.ContentType = "text/csv";

HttpContext.Current.Response.AddHeader("content-disposition", String.Format("attachment;filename={0}.{1}", HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8), "csv").Replace("+", "%20"));

HttpContext.Current.Response.ContentEncoding = encoding;

MemoryStream stream = new MemoryStream();

XmlTextWriter writer = new XmlTextWriter(stream, encoding);

CreateStylesheet(writer, headers, fields);

writer.Flush();

stream.Seek(0, SeekOrigin.Begin);

XmlDataDocument xmlDoc = new XmlDataDocument(ds);

XslCompiledTransform xslTran = new XslCompiledTransform();

xslTran.Load(new XmlTextReader(stream));

System.IO.StringWriter sw = new System.IO.StringWriter();

xslTran.Transform(xmlDoc, null, sw);

HttpContext.Current.Response.Write(sw.ToString());

sw.Close();

writer.Close();

stream.Close();

HttpContext.Current.Response.End();

}

/// <summary>

/// 动态生成XSL，并写入XML流

/// </summary>

/// <param name="writer">XML流</param>

/// <param name="headers">表头数组</param>

/// <param name="fields">字段数组</param>

/// <param name="exportFormat">导出文件的格式</param>

private static void CreateStylesheet(XmlTextWriter writer, string[] headers, string[] fields)

{

string ns = "http://www.w3.org/1999/XSL/Transform";

writer.Formatting = Formatting.Indented;

writer.WriteStartDocument();

writer.WriteStartElement("xsl", "stylesheet", ns);

writer.WriteAttributeString("version", "1.0");

writer.WriteStartElement("xsl:output");

writer.WriteAttributeString("method", "text");

writer.WriteAttributeString("version", "4.0");

writer.WriteEndElement();

// xsl-template

writer.WriteStartElement("xsl:template");

writer.WriteAttributeString("match", "/");

// xsl:value-of for headers

for (int i = 0; i < headers.Length; i++)

{

writer.WriteString("\"");

writer.WriteStartElement("xsl:value-of");

writer.WriteAttributeString("select", "'" + headers[i] + "'");

writer.WriteEndElement(); // xsl:value-of

writer.WriteString("\"");

if (i != fields.Length - 1) writer.WriteString(",");

}

// xsl:for-each

writer.WriteStartElement("xsl:for-each");

writer.WriteAttributeString("select", "Export/Values");

writer.WriteString("\r\n");

// xsl:value-of for data fields

for (int i = 0; i < fields.Length; i++)

{

writer.WriteString("\"");

writer.WriteStartElement("xsl:value-of");

writer.WriteAttributeString("select", fields[i]);

writer.WriteEndElement(); // xsl:value-of

writer.WriteString("\"");

if (i != fields.Length - 1) writer.WriteString(",");

}

writer.WriteEndElement(); // xsl:for-each

writer.WriteEndElement(); // xsl-template

writer.WriteEndElement(); // xsl:stylesheet

}

/// <summary>

/// 替换特殊字符

/// </summary>

/// <param name="input">字符串</param>

/// <returns></returns>

public static string ReplaceSpecialChars(string input)

{

// space -> _x0020_

// % -> _x0025_

// # -> _x0023_

// & -> _x0026_

// / -> _x002F_

if (!String.IsNullOrEmpty(input))

{

input = input.Replace(" ", "_x0020_")

.Replace("%", "_x0025_")

.Replace("#", "_x0023_")

.Replace("&", "_x0026_")

.Replace("/", "_x002F_");

}

return input;

}

}

}