萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> ASP 程式設計

ASP 將 Access 資料匯出為 Excel(.xls/.csv)檔案下載

方案一:產生 Tab 分隔文字檔(.xls)

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(含自訂標題)

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
%>

進階:標準 CSV 寫入函數(處理逗號、換行、雙引號)

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。
  • 分批寫入,避免單次 Request 逾時。

安全性提醒

  • 匯出功能務必加入權限檢查(管理員/特定角色)。
  • 檔名使用亂數或 GUID,避免被猜測下載他人匯出檔。
  • 定期清理匯出暫存目錄。
copyright © 萬盛學電腦網 all rights reserved