First, use OWC
What is OWC?
OWC is the abbreviation of Office Web Compe, which is Microsoft Office Web Components, which provides flexible simultaneous mechanisms for drawing graphics in the Web. In an intranet environment, if you can assume that there is a specific browser and some powerful software (such as IE5 and Office 2000), you can provide an interactive graphics development environment using the Office WEB component. In this mode, the client workstation will share a lot of proportion in the entire task.
<% Option Explicit Class Excelgen Private Objspreadsheet Private ICOLOFFSET
Private irowoffset sub class_initialize () set objspreadsheet = server.createObject ("owc.spreadsheet") rowoffset = 2 icoloffset = 2 End Sub
SUB Class_Terminate () set objspreadsheet = Nothing 'Clean Up End Sub
Public Property Let Columnoffset (ICOLOFF) ICOLOFF> 0 Then ICOLOFFSET = ICOLOFF ELSE ICOLOFFSET = 2 End If End Property
Public Property Let RowOffset (iRowOff) If iRowOff> 0 then iRowOffset = iRowOff Else iRowOffset = 2 End If End Property Sub GenerateWorksheet (objRS) 'Populates the Excel worksheet based on a Recordset's contents' Start by displaying the titles If objRS.EOF then Exit Sub Dim objField, iCol, iRow iCol = iColOffset iRow = iRowOffset For Each objField in objRS.Fields objSpreadsheet.Cells (iRow, iCol) .Value = objField.Name objSpreadsheet.Columns (iCol) .AutoFitColumns' Excel table set font objSpreadsheet .Cells (irow, icol) .font.bold = true objspreadsheet.cells (irow, icol) .font.italic = false objspreadsheet.cells (iRow, ICOL) .Font.Size = 10 Objspreadsheet.cells (iRow, ICOL). Halignment = 2 'centrally iCol = iCol 1 Next' objField 'Display all of the data Do While Not objRS.EOF iRow = iRow 1 iCol = iColOffset For Each objField in objRS.Fields If IsNull (objField.Value) then objSpreadsheet. Cells (IROW, ICOL) .Value = "" Else Objspreadsheet.cells (irow, icol) .value = objfield.value objspreadsheet.columns (icol) .autofi tColumns objSpreadsheet.Cells (iRow, iCol) .Font.Bold = False objSpreadsheet.Cells (iRow, iCol) .Font.Italic = False objSpreadsheet.Cells (iRow, iCol) .Font.Size = 10 End If iCol = iCol 1 Next 'objField objRS.MoveNext Loop End Sub Function SaveWorksheet (strFileName)' Save the worksheet to a specified filename On Error Resume Next Call objSpreadsheet.ActiveSheet.Export (strFileName, 0) SaveWorksheet = (Err.Number = 0) End Function End Class
Dim objrs set objrs = server.createObject ("AdoDb.Recordset") objrs.open "Select * from xxxx", "provider = SQLOLEDB.1; PERSIST Security
Info = True; User ID = xxxx; Password = xxxx; Initial Catalog = xxxx; Data source = xxxx; "Dim SaveName SaveName = Request.Cookies (" savename ") (" name ") Dim objExcel Dim ExcelPath ExcelPath =" Excel / "& SaveName &" .xls "Set objExcel = New ExcelGen objExcel.RowOffset = 1 objExcel.ColumnOffset = 1 objExcel.GenerateWorksheet (objRS) If objExcel.SaveWorksheet (Server.MapPath (ExcelPath)) then 'Response.Write"
Saved as an Excel file.Note: "DATA" in the two functions is the ID of Table to be exported in the web page.
Export to Excel code
