匯出Excel~~~使用tool產生標準excel格式的xls-ExcelSpeedGen

前置：
需要準備一個excel的範本，標題同匯出結果的標題
--------------------------------------------------------------------------------

說明：
將sql讀出的資料塞入excel範本
然後匯出excel

以下為asp寫法(asp.net修改完有點問題)
--------------------------------------------------------------------------------

範例：

DIM UserId,sStartDate,sEndDate,sRepeat,sFileName, IDNo, sCarCategory
UserID = Request("UserID")
sStartDate = Request("StartDate")
sEndDate = Request("EndDate")
sRepeat = Request("IsRepeat")
sFileName = Request("FileName")
IDNo = Request("IDNo")
sCarCategory = Request("CarCategory")
Dim CarCategory, Organization, CertificateNum, Comp_IDNum, CName
Dim FinishDate, ApprovalNum, EmissionStandard, IsImport, ClassCode
Dim Manufacturer, Brand, Made_Area, Import_Area, EngineGroup
Dim CarYear, ApplyType, CCarModel, ECarModel, CarModelType
Dim Exhaust, TransmissionType, TransmissionNum, CarDoorCount, ModelNameOthers
Dim PowerSource, BasicEngine, Catalyst, Circulate, EEC
Dim PCV, HotReactor, AirPump, AirControl, Operception
Dim Decelerate, DownEffect, IOControlOther, IOControlComment, Transmission
Dim MaxPower, CarWeight, TestMethod, CO_R, NMHC_R
Dim NOx_R, HC_R, Modulus_Method, Modulus_Type, Modulus_CO
Dim Modulus_NMHC, Modulus_HC, Modulus_NOx, Modulus_Evap, LazyCO
Dim LazyHC, LazySpeed, HCEVAP_R
Dim dbFile ' Database File
Dim oConn ' ADO Connection object
Dim Rs1 ' Record Source 1
Dim XLS ' Excel SpeedGen Object
Dim SrcBook ' Source Workbook ' Open Microsoft Access Database
'dbFile = Server.MapPath("sample.mdb")
oConn = Server.CreateObject("ADODB.Connection")
oConn.Open (Connection_SQL) ' Open the RecordSets
Dim str_SQL str_SQL = "SELECT * FROM CertifExportCarISC WHERE UserID = '" + UserID + "' AND IDNo = '" + IDNo + "' ORDER BY TempNo"
Rs1 = oConn.Execute(str_SQL) ' SQL Statement (or Table name)
' Create Excel File
XLS = Server.CreateObject("XLSpeedGen.ASP")
XLS.EstimatedSize = 100000 ' Set Estimated Output File Size (Critical for speed)
' Set up Aliases
XLS.AddRS_ADO(Rs1, 20) ' RecordSource 1 (read 20 rows at a time)
' Add RecordSources
if sCarCategory = "1" THEN
SrcBook = Server.MapPath("ExportCert_C.xls") ' Location of Source Workbook
else
SrcBook = Server.MapPath("ExportCert_M.xls")
end if
' Generate SpreadSheet and Stream to Client, Open in Place '(True 以 IE 開啟，False 以 Excel 開啟)
XLS.Generate(SrcBook, sFileName, False)
XLS = Nothing ' Destroy object when done
' Cleanup Code
'oConn.close ' Close Connection and all Recordsets
oConn = Nothing