Excel 可直接開啟 Tab 分隔的文字檔,命名為 .xls 即可關聯開啟,產生速度最快、相容性最好。
<%
Dim referer, CName, action, daytime, filename, fs, myfile, x, strLine
referer = Request.ServerVariables("HTTP_REFERER")
CName = "../Excel/"
action = Request.Form("action")
' 檔名含時間戳記
daytime = Year(Now) & "年" & Month(Now) & "月" & Day(Now) & "日" & Hour(Now) & "時"
Set rs = Server.CreateObject("ADODB.Recordset")
sql = "SELECT * FROM gbook ORDER BY id DESC"
rs.Open sql, conn
If rs.EOF And rs.BOF Then
Response.Write ""
Response.End
End If
Set fs = Server.CreateObject("Scripting.FileSystemObject")
filename = Server.MapPath(CName & daytime & ".xls")
' 刪除同名舊檔
If fs.FileExists(filename) Then fs.DeleteFile(filename)
Set myfile = fs.CreateTextFile(filename, True)
' 寫入欄位名(第一列)
strLine = ""
For Each x In rs.Fields
strLine = strLine & x.Name & Chr(9) ' Tab 分隔
Next
myfile.WriteLine strLine
' 寫入資料列
Do While Not rs.EOF
strLine = ""
For Each x In rs.Fields
strLine = strLine & x.Value & Chr(9)
Next
myfile.WriteLine strLine
rs.MoveNext
Loop
rs.Close: Set rs = Nothing
conn.Close: Set conn = Nothing
myfile.Close: Set myfile = Nothing
Set fs = Nothing
Response.Write "匯出成功
"
Response.Write "下載 .xls 檔 | "
Response.Write "返回"
%>
CSV 為通用格式,適合跨系統匯入,但需注意欄位含逗號、換行時需雙引號包裹。
<%
' ... 前置連線查詢同方案一 ...
Dim Fs, Exc, ExcelString, ExcelHead, Dest_FileName
Dest_FileName = Server.MapPath(CName & daytime & ".csv")
' 自訂標題列(對應資料表欄位順序)
ExcelHead = "識別號,主題類型,公司名稱,姓名,性別,職位,城市省份,城市,公司地址,郵編,郵件,電話,傳真,手機,公司網址,詳細內容,IP地址,留言時間"
Set Fs = Server.CreateObject("Scripting.FileSystemObject")
Set Exc = Fs.CreateTextFile(Dest_FileName, True)
' 寫入標題
Exc.WriteLine ExcelHead
' 寫入資料(簡易版,未處理欄位內含逗號/換行)
Do While Not rs.EOF
ExcelString = ""
For Each x In rs.Fields
ExcelString = ExcelString & """" & Replace(x.Value, """", """""") & ""","
Next
' 移除尾端逗號
If Len(ExcelString) > 0 Then ExcelString = Left(ExcelString, Len(ExcelString)-1)
Exc.WriteLine ExcelString
rs.MoveNext
Loop
Exc.Close: Set Exc = Nothing
Set Fs = Nothing
%>
Function WriteCSVLine(fileObj, fields)
Dim line, i, val
line = ""
For i = 0 To UBound(fields)
val = CStr(fields(i))
If InStr(val, ",") > 0 Or InStr(val, """") > 0 Or InStr(val, vbCrLf) > 0 Then
val = """" & Replace(val, """", """""") & """" ' 雙引號跳脫並包裹
End If
line = line & val & ","
Next
If Len(line) > 0 Then line = Left(line, Len(line)-1)
fileObj.WriteLine line
End Function
' 使用範例
Dim rowData(5)
rowData(0) = "A001"
rowData(1) = "張三"
rowData(2) = "台北市,信義區" ' 含逗號
rowData(3) = "備註""引號"
rowData(4) = "多行" & vbCrLf & "文字"
rowData(5) = "正常"
WriteCSVLine Exc, rowData
Response.BinaryWrite 直接串流輸出,不落地檔案,避免磁碟 I/O。Response.ContentType = "application/vnd.ms-excel" 或 text/csv。