用ASP生成Excel資料

可能是將一個html 表格變成 Microsoft Excel 格式的最快方法。ContentType 屬性通知
   瀏覽器資料要被格式化為何種格式，在這裡我們要的格式是Microsoft Excel。當瀏覽器看到這個屬性的值是Excel時，它就提示使用者儲存或開啟這個檔案。如果使用者選擇開啟檔案，
   就啟動了Excel並在其中觀看資料。為使其工作正確，必須在向Response物件寫入任何內容之前設定ContentType 。此語法的例子如下：
   　　 Line 1: 〈 %@ LANGUAGE="VBSCRIPT" % 〉
   　　 Line 2: Response.ContentType = "application/msexcel"
   　　 Line 3: % 〉
   　　 點選這裡可以得到有關ASP的Response 物件的ContentType屬性的更多資訊。
   　　 當我試圖用Internet Explorer 4.x.測試時發現了一個問題，在Microsoft文章 Q185978曾經提到過。 這篇知識庫文章的內容可以概括如下：
   　　 “如果Internet Explorer 與一個動態生成Word、 Excel或其它活動文件的Web伺服器
   資源相連線時，Internet Explorer會為此資源發出兩個GET 請求。第二個GET 通常沒有ses
   sion 狀態資訊、臨時 cookies或者已經為客戶指定的證明資訊。這個錯誤可能影響到任何
   寄宿在Internet Explorer 結構視窗內的本地伺服器(EXE) 的活動文件應用程式。它在ISAP
   I、 ASP或 CGI 應用程式中發生最為頻繁，它們校驗HTTP "Content Type" 標頭檔案以識別所
   安裝的應用程式”。
   　　 因此如果你嘗試使用session 變數或 cookies， 並使用IE4，就有可能遭遇到這個錯
   誤。經證實，在IE5中這個問題已經得到解決。
   　　 一個用逗號分隔開的值檔案是將web頁面輸出到Excel可讀格式的第二種選擇。這種格
   式比ContentType 屬性有更大的靈活性。相對於其它方法，CSV還有兩個優勢：首先，不需
   要任何客戶機或伺服器上的 軟體去建立它，第二，檔案通常要比一個Excel 檔案小。
   　　 CSV格式的定義如下：逗號分隔列，回車分隔行。逗號作為分隔符， 也會與包含逗號
   的 域（如$1，234）引發一個問題; 這會在將要建立的行中導致一個額外的列。這個問題也
   很容易矯正， 方法是在每個結尾處用逗號將域封閉起來。
   　　 在提供的例子中把 CreateCSVFile()函式過一遍，就能瞭解CSV檔案是如何建立的。
   　　 Line 1: strFile = GenFileName()
   　　 Line 2: Set fs = CreateObject("
   Scripting.FileSystemObject")
   　　 Line 3: Set a = fs.CreateTextFile(server.MapPath(".") & "" & strFile &
   Line 4: ".csv"，True)
   　　 Line 5: If Not oRS.EOF Then
   　　 Line 6: strtext = chr(34) & "Year" & chr(34) & "，"
   　　 Line 7: strtext = strtext & chr(34) & "Region" & chr(34) & "，"
   　　 Line 8: strtext = strtext & chr(34) & "Sales" & chr(34) & "，"
   　　 Line 9: a.WriteLine(strtext)
   　　 Line 10: Do Until oRS.EOF
   　　 Line 11: For i = 0 To oRS.fields.Count-1
   　　 Line 12: strtext = chr(34) & oRS.fields(i) & chr(34) Line 13: & "，"
   　　 Line 14: a.Write(strtext)
   　　 Line 15: Next
   　　 Line 16: a.Writeline()
   　　 Line 17: oRS.MoveNext
   　　 Line 18: Loop
   　　 Line 19: End If
   　　 Line 20: a.Close
   　　 Line 21: Set fs=Nothing
   　 Line 22:Response.Write("Click〈 A HREF=" & strFile & ".csv 〉Here〈 /A 〉
   　　 Line 23: to get CSV file")
   　　 第一行呼叫GenFileName() 函式建立一個唯一的檔名，有關GenFileName() 函式將
   在稍後討論。
   　　 第2行到第4行，用FileSystemObject 物件和CreateTextFile函式將要寫入的文字文
   件。在這個例子中， 所寫入的檔案與原始檔在同一個路徑下，在實際工作中，你也許想要
   建立一個單獨的路徑儲存這些檔案。
   　　 第5-9行產生第一行的標題。因為報告通常都是相同的，我就把列名的程式碼固定下來，
   雖然也有可能讀 資料庫的列名並使用它們。注意我在各個域中是如何包含逗號的。使用Wri
   teLine 函式將它們與一個回 車一起傳送到檔案中。
   　　 第10行到18行在記錄集中迴圈，用引號給每個域做出標誌，後面跟著一個逗號。然後W
   rite函式將每個 域傳送到檔案。WriteLine 用回車結束每一行。
   　　 最後幾行關閉檔案、釋放物件、在頁面上放置一個連結以便能夠找回它。
   　　 當你點選生成的連結時，就會被提示儲存或開啟。如果選擇開啟，檔案就在Excel 中
   開啟（假設計算機 上已經安裝）。如果選擇了儲存，就將這個文字檔案儲存到儲存裝置上
   並將它輸入各個應用程式中。
   　　 我所討論的最後一種方法是用Microsoft Excel 物件建立一個實際的Excel(.xls ) 文
   件。要使用這些物件要求在Web 伺服器上安裝Excel。使用這些控制可以對格式化有更多的
   控制（如字型、顏色等）， 並允許你進行一切在真正的Excel 應用程式中可以進行的操
   作。一定要監視你的伺服器的效能，因為 Excel 可能成為一個相當大的物件並對效能造成
   衝擊，這取決於你如何使用它以及伺服器有多忙。
   　　 我發現在Excel 物件上得到更多資訊的最快最簡便的方法是使用Visual Basic物件瀏
   覽器，觀看物件 並使用上下文敏感幫助來得到更多細節。使用這個物件瀏覽器時：啟動Vis
   ual Basic， 建立一個工程檔案， 增加一個對Microsoft Excel物件庫的引用。在 View 菜
   單下，可以選擇一個物件瀏覽器然後指定Excel 庫，看到所有可用的物件。按 F1可得到當
   前標題的上下文敏感幫助。
   　　 在所提供的樣本中把CreateXlsFile() 函式過一遍，就可以看到如何建立一個Excel文
   件。 基本步驟與 建立 CSV檔案的基本相同，只是所建立的是一個Excel工作表。
   Line 1: Dim xlWorkSheet
   Line 2: Dim xlApplication
   Line 3: Set xlApplication = Server.CreateObject("Excel.Application")
   Line 4: xApplication.Visible = False
   Line 5:
   xlApplication.Workbooks.Add
   Line 6: Set xlWorksheet = xlApplication.Worksheets(1)
   Line 7: xlWorksheet.Cells(1，1).Value = "Year"
   Line 8: xlWorksheet.Cells(1，1).Interior.ColorIndex = 5
   Line 9: xlWorksheet.Cells(1，2).Value = "Region"
   Line 10: xlWorksheet.Cells(1，2).Interior.ColorIndex = 5
   Line 11: xlWorksheet.Cells(1，3).Value = "Sales"
   Line 12: xlWorksheet.Cells(1，3).Interior.ColorIndex = 5
   Line 13: iRow = 2
   Line 14: If Not oRS.EOF Then
   Line 15: Do Until oRS.EOF
   Line 16: For i = 0 To oRS.fields.Count-1
   Line 17: xlWorksheet.Cells(iRow，i 1).Value = oRS.fields(i)
   Line 18: xlWorkSheet.Cells(iRow，i 1).Interior.ColorIndex = 4
   Line 19: Next
   Line 20: iRow = iRow 1
   Line 21: oRS.MoveNext
   Line 22: Loop
   Line 23: End If
   Line 24: strFile = GenFileName()
   Line 25: xlWorksheet.SaveAs Server.MapPath(".") & "" & strFile & ".xls"
   Line 26: xlApplication.Quit ' Close the Workbook
   Line 27: Set xlWorksheet = Nothing
   Line 28: Set xlApplication = Nothing
   Line 29: Response.Write("Click 〈 A HRef=" & strFile & ".xls 〉Here〈 /A 〉
   to Line 30: get XLS file")
   　　 第1行和第2行，確定所使用的 Excel物件的維數。
   　　 第3行，建立Excel物件。同樣，為了工作正確，web伺服器上也必須有Excel。
   　　 第4行，將Excel的可見性設定為false，這樣它就沒有介面了。
   　　 第5行和第6行，增加一個容納工作表的工作簿，然後將當前工作表設定成第一個工作
   表(這是Excel 在預設狀態下建立的)。 還可以用 Worksheet物件的Add 函式增加一個新的
   工作表， 這就允許你的 Excel 檔案中有多個工作表。
   　　 第8-12行，建立工作表的標題。在這個例子中，我們把每個單元的值都設定成適當的
   標題，而且把內部 顏色設定成蘭色。你還可以用Range物件同時修改多個單元。
   　　 第13-23行，提供從記錄集裝載所有資料的迴圈。因為第一行中包含標題，我就在電子
   資料表的 第二行開始資料。裡面的 For迴圈把每一列裝載到行中，並把內部顏色設定為綠
   色。外部迴圈則為 每一行在記錄集中進行迴圈。
   　　 第24行， 通過呼叫GenFileName()函式，與CSV用同樣的函式來建立唯一的檔名。
   　　 第25行，進行電子表格的實際儲存。可以將表格儲存為 Excel中指定的多種格式。
   　　 下面的3行進行物件的整理。作為一個好的ASP程式設計師，就一定要整理所有的物件。
   　　 最後，我把到 Excel檔案的連結放在頁面上以便下載。
   　　 我建立了一個樣本，對以上討論過的每個技巧進行示範。要安裝樣本，只需要把所有
   的檔案複製到伺服器上，用 main.html 啟動應用程式。在伺服器上需要有 Excel以使用"Na
   tive Excel" 選項。 樣本使用一個Access資料庫 (無DSN連結)來儲存銷售資料。
   　　 你可以選擇一年或一個地區進行銷售報告。最後的選項是你希望如何返回資料。可以
   看到以下的螢幕映象：
   

   　　 下面的表格中是對樣本中提供的所有檔案的描述。
   檔名 描述
   DSN-SQL.asp 包含無DSN連結字串
   adovbs.inc 包含ADO常量
   TestDB.mdb 包含銷售資料的一個Access97資料庫。包含的銷售表格有3個域：year--Tex
   t， region--Text，sales-amt--numeric
   main.html 本檔案建立畫面的框架並裝載初始頁
   welcome.html 本檔案只在第一次建立結果通常所在的畫面框架時使用
   request.html 包含一個表單，用來收集使用者的選擇來建立報告
   runquery.asp 應用程式的內臟。本檔案建立SQL宣告、確定客戶機如何請求將被返回的數
   據、執行SQL並按照請求返回資料
   　　 大部分程式碼都相當容易理解。但是我還是要討論runquery.asp 檔案中的一些函式。我
   已經演示過如何建立CSV和Excel 檔案。
   　　 GenHTML()函式建立一個被請求資料的HTML表格。這個函式既用來作為HTML返回也用於
   ContentType請求。為了 ContentType請求工作，你要注意 Response.ContentType = "appl
   ication/msexcel" 是將要執行的最初幾行之一。
   　　 GenFileName()函式使用系統日期建立檔名的第一部分。這個檔名將是唯一的，這
   樣當你試圖儲存 檔案時就可以避免許多麻煩。副檔名( CSV或XLS )在儲存檔案時應用，這
   樣就允許同一個函式產生兩種型別的檔案。
   　　 BuildSQL()函式使用表單變數來建立一個SQL宣告，與使用者的請求相匹配，並將其返回
   呼叫者。
   　　 Recordset在指令碼的最頂部被開啟，因為它對於所選擇的顯示型別是獨立的。recordse
   t處理從BuildSQL()函式呼叫生成的SQL宣告，使用一個到Access 97的無DSN連結。
   　　 〈 BODY 〉標記中包含的程式碼僅僅是兩個 "if….then" 宣告，確定使用者所要求的顯示
   方法。 if宣告分流到生成正確返回型別的函式，該返回型別是基於使用者的"ReturnAS" 選
   擇。接著清除連結和記錄集物件。
   　　 注意: 這個樣本沒有涉及到使用者下載web伺服器上建立的檔案之後，對這些檔案的維護
   問題。我建議 這種維護要基於一段時間，時間到期後就刪除這些檔案。 我不主張把移走這
   些檔案的負擔轉嫁到客戶身上(通過頁面上的連結)，因為他們很容易忘記這些事情。

　　 結論
   　　 本文演示了將資料輸出到一個Excel可讀格式的三種方法。 我相信根據使用者的不同需
   要，這三種方法都有其可用之地。如果你願意快速但不漂亮地輸出到Excel，就用ContentTy
   pe好了。如果你想要一個格式有限但能夠裝載到許多不同應用程式中的檔案，那麼CSV格式
   適合你。如果你更喜歡包含完整格式、圖示或特殊Excel功能，那麼建立一個完全的Excel電
   子表格是適合的途徑。 但願這些方法能幫助其他程式設計師滿足客戶的要求或者至少幫助你選
   擇正確的途徑。