C# Excel

Imports System.Xml.Xsl
Imports System.Web.Security
Imports System.Configuration
Imports System.Web.UI.WebControls
Imports System.Web.UI.HtmlControls
Imports System.Web.UI.WebControls.WebParts


Namespace webprint
	Public Enum ExportFormat
		''' <summary>
		''' XLS
		''' </summary>
		XLS
		''' <summary>
		''' CSV
		''' </summary>
		CSV
		''' <summary>
		''' DOC
		''' </summary>
		DOC
		''' <summary>
		''' TXT
		''' </summary>
		TXT
	End Enum
	Public Class FileSave
		''' <summary>
		''' 替换特殊字符
		''' </summary>
		''' <param name="input">字符串</param>
		''' <returns></returns>
		Public Shared Function ReplaceSpecialChars(input As String) As String
			' space -> _x0020_
			' %	 -> _x0025_
			' #	 ->	_x0023_
			' &	 ->	_x0026_
			' /	 ->	_x002F_
			input = input.Replace(" ", "_x0020_").Replace("%", "_x0025_").Replace("#", "_x0023_").Replace("&", "_x0026_").Replace("/", "_x002F_")
			Return input
		End Function
		''' <summary>
		''' 导数据源的数据
		''' </summary>
		''' <param name="dt">数据源</param>
		''' <param name="exportFormat">导出文件的格式</param>
		''' <param name="fileName">输出文件名</param>
		''' <param name="encoding">编码</param>
		Public Shared Sub Export(dt As DataTable, exportFormat As ExportFormat, fileName As String, encoding As Encoding)
			Dim dsExport As New DataSet("Export")
			Dim dtExport As DataTable = dt.Copy()

			dtExport.TableName = "Values"
			dsExport.Tables.Add(dtExport)

			Dim headers As String() = New String(dtExport.Columns.Count - 1) {}
			Dim fields As String() = New String(dtExport.Columns.Count - 1) {}

			For i As Integer = 0 To dtExport.Columns.Count - 1
				headers(i) = dtExport.Columns(i).ColumnName
				fields(i) = ReplaceSpecialChars(dtExport.Columns(i).ColumnName)
			Next
			Export(dsExport, headers, fields, exportFormat, fileName, encoding)
		End Sub
		''' <summary>
		''' 导出SmartGridView的数据源的数据
		''' </summary>
		''' <param name="ds">数据源</param>
		''' <param name="headers">导出的表头数组</param>
		''' <param name="fields">导出的字段数组</param>
		''' <param name="exportFormat">导出文件的格式</param>
		''' <param name="fileName">输出文件名</param>
		''' <param name="encoding">编码</param>
		Private Shared Sub Export(ds As DataSet, headers As String(), fields As String(), exportFormat As ExportFormat, fileName As String, encoding As Encoding)
			HttpContext.Current.Response.Clear()
			HttpContext.Current.Response.Buffer = True
			HttpContext.Current.Response.ContentType = [String].Format("text/{0}", exportFormat.ToString().ToLower())
			HttpContext.Current.Response.AddHeader("content-disposition", [String].Format("attachment;filename={0}.{1}", fileName, exportFormat.ToString().ToLower()))
			HttpContext.Current.Response.ContentEncoding = encoding

			Dim stream As New MemoryStream()
			Dim writer As New XmlTextWriter(stream, encoding)

			CreateStylesheet(writer, headers, fields, exportFormat)
			writer.Flush()
			stream.Seek(0, SeekOrigin.Begin)

			Dim xmlDoc As New XmlDataDocument(ds)
			Dim xslTran As New XslCompiledTransform()
			xslTran.Load(New XmlTextReader(stream))

			Dim sw As New System.IO.StringWriter()
			xslTran.Transform(xmlDoc, Nothing, sw)

			HttpContext.Current.Response.Write(sw.ToString())
			sw.Close()
			writer.Close()
			stream.Close()
			HttpContext.Current.Response.[End]()
		End Sub

		''' <summary>
		''' 动态生成XSL，并写入XML流
		''' </summary>
		''' <param name="writer">XML流</param>
		''' <param name="headers">表头数组</param>
		''' <param name="fields">字段数组</param>
		''' <param name="exportFormat">导出文件的格式</param>
		Private Shared Sub CreateStylesheet(writer As XmlTextWriter, headers As String(), fields As String(), exportFormat__1 As ExportFormat)
			Dim ns As String = "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 i As Integer = 0 To headers.Length - 1
				writer.WriteString("""")
				writer.WriteStartElement("xsl:value-of")
				writer.WriteAttributeString("select", "'" & headers(i) & "'")
				writer.WriteEndElement()
				' xsl:value-of
				writer.WriteString("""")
				If i <> fields.Length - 1 Then
					writer.WriteString(If((exportFormat__1 = ExportFormat.CSV), ",", vbTab))
				End If
			Next
			' xsl:for-each
			writer.WriteStartElement("xsl:for-each")
			writer.WriteAttributeString("select", "Export/Values")
			writer.WriteString(vbCr & vbLf)

			' xsl:value-of for data fields
			For i As Integer = 0 To fields.Length - 1
				writer.WriteString("""")
				writer.WriteStartElement("xsl:value-of")
				writer.WriteAttributeString("select", fields(i))
				writer.WriteEndElement()
				' xsl:value-of
				writer.WriteString("""")
				If i <> fields.Length - 1 Then
					writer.WriteString(If((exportFormat__1 = ExportFormat.CSV), ",", vbTab))
				End If
			Next

			writer.WriteEndElement()
			' xsl:for-each
			writer.WriteEndElement()
			' xsl-template
			writer.WriteEndElement()
			' xsl:stylesheet
		End Sub
		''' <summary>
		''' 导出数据源的数据
		''' </summary>
		''' <param name="dt">数据源</param>
		''' <param name="columnIndexList">导出的列索引数组</param>
		''' <param name="exportFormat">导出文件的格式</param>
		''' <param name="fileName">输出文件名</param>
		''' <param name="encoding">编码</param>
		Public Shared Sub Export(dt As DataTable, columnIndexList As Integer(), exportFormat As ExportFormat, fileName As String, encoding As Encoding)
			Dim dsExport As New DataSet("Export")
			Dim dtExport As DataTable = dt.Copy()

			dtExport.TableName = "Values"
			dsExport.Tables.Add(dtExport)

			Dim headers As String() = New String(columnIndexList.Length - 1) {}
			Dim fields As String() = New String(columnIndexList.Length - 1) {}

			For i As Integer = 0 To columnIndexList.Length - 1
				headers(i) = dtExport.Columns(columnIndexList(i)).ColumnName
				fields(i) = ReplaceSpecialChars(dtExport.Columns(columnIndexList(i)).ColumnName)
			Next
			Export(dsExport, headers, fields, exportFormat, fileName, encoding)
		End Sub
		''' <summary>
		''' 导出数据源的数据
		''' </summary>
		''' <param name="dt">数据源</param>
		''' <param name="columnIndexList">导出的列索引数组</param>
		''' <param name="headers">导出的列标题数组</param>
		''' <param name="exportFormat">导出文件的格式</param>
		''' <param name="fileName">输出文件名</param>
		''' <param name="encoding">编码</param>
		Public Shared Sub Export(dt As DataTable, columnIndexList As Integer(), headers As String(), exportFormat As ExportFormat, fileName As String, encoding As Encoding)
			Dim dsExport As New DataSet("Export")
			Dim dtExport As DataTable = dt.Copy()
			dtExport.TableName = "Values"
			dsExport.Tables.Add(dtExport)
			Dim fields As String() = New String(columnIndexList.Length - 1) {}
			For i As Integer = 0 To columnIndexList.Length - 1
				fields(i) = ReplaceSpecialChars(dtExport.Columns(columnIndexList(i)).ColumnName)
			Next
			Export(dsExport, headers, fields, exportFormat, fileName, encoding)
		End Sub
	End Class
End Namespace