時常會遇到ASP.NET轉EXCEL的需求,這有多種寫法,比如通過CSV,datagrid,gridview轉EXCEL,也可以直接調用EXCELL.DLL通過代碼實現
由於有些銀行帳號、身份證號等在某些情況下用EXCEL打開時會用科學記數法顯示且超過15位的長度都默認為0,只能用些方法作些處理
今日在通過用datagrid轉excel得到了一個新的啟示：
將datatable先將銀行帳號等處理後再將全部資料列都用table元素來表示，用EXCEL打開時一樣正常
其相關範例如下:
.......
Dim inta As Integer
For inta = 0 To dt.Rows.Count - 1
dt.Rows(inta).Item(2) = " " & dt.Rows(inta).Item(2).ToString
Next
Response.ContentType = "application/ms-Excel"
Response.AddHeader("Content-Disposition", "inline;filename=test.xls")
Response.Write(ConvertDtToTDF(dt))
Response.End()
dt.Dispose()
dt = Nothing
Private Function ConvertDtToTDF(ByVal dt As DataTable) As String
Dim dr As DataRow, ary() As System.Object, i As Integer
Dim iCol As Integer
Dim swtr As String
swtr = "<table border=1 style='font-size:9pt;font-family:新細明體;'>"
' 输出列标题
swtr += "<tr>"
For iCol = 0 To dt.Columns.Count - 1
swtr += "<td>" & dt.Columns(iCol).ToString & "</td>"
Next
swtr += "</tr>"
'输出数据
For Each dr In dt.Rows
ary = dr.ItemArray
swtr += "<tr>"
For i = 0 To UBound(ary)
swtr += "<td>" & ary(i).ToString & "</td>"
Next
swtr += "</tr>"
Next
swtr += "</table>"
Response.Write(swtr)
End Function
