[ASP.Net] 分頁型Excel匯出

用產生xml的方式 gene
標頭用 xmlTextWriter 產生Excel 會在Excel 2003無法開啟
所以改用response.write產生
Response.Charset = "UTF8"
Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8")
Response.AddHeader("content-disposition", "attachment; filename=" & filename & ".xls")
Response.ContentType = "application/vnd.ms-excel"
Response.Write("<?xml version='1.0'?>")
Response.Write("<?mso-application progid='Excel.Sheet() '?>")
Response.Write("<Workbook xmlns='urn:schemas-microsoft-com:office:spreadsheet'")
Response.Write(" xmlns:o='urn:schemas-microsoft-com:office:office'")
Response.Write(" xmlns:x='urn:schemas-microsoft-com:office:excel'")
Response.Write(" xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet'")
Response.Write(" xmlns:html='http://www.w3.org/TR/REC-html40'>")

每個Sheet開頭
Response.Write("<Worksheet ss:Name='sheet1')


'以下的內容才能用xmlTextWriter 產生
'宣告xmlTextWriter
Dim sw As System.IO.StringWriter = New System.IO.StringWriter
Dim TempXml As System.Xml.XmlTextWriter = New System.Xml.XmlTextWriter(sw)
'Define Table
TempXml.WriteStartElement("Table")

'Define Row
TempXml.WriteStartElement("Row")

'Cell
TempXml.WriteStartElement("Cell")
TempXml.WriteStartElement("Data")
TempXml.WriteAttributeString("ss:Type", "String")
'儲存格數值
TempXml.WriteValue(儲存格數值)

'EndData
TempXml.WriteEndElement()

'End Cell
TempXml.WriteEndElement()

'End Row
TempXml.WriteEndElement()

'End Table
TempXml.WriteEndElement()

'End Worksheet
Response.Write("</Worksheet"<)
Response.Write("</Workbook"<)
Response.End()