我使用 <% Class ExcelGen Private objSpreadsheet Private iColOffset Private iRowOffset
Sub Class_Initialize() Set objSpreadsheet = Server.CreateObject("OWC.Spreadsheet") iRowOffset = 2 iColOffset = 2 End Sub
Sub Class_Terminate() Set objSpreadsheet = Nothing 'Clean up End Sub
Public Property Let ColumnOffset(iColOff) If 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表里的字体 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 '居中 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 mm=replace(objField.Value ,"-","/") objSpreadsheet.Cells(iRow, iCol).Value = mm objSpreadsheet.Columns(iCol).AutoFitColumns 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") 'oConn_Start objRS.Open "SELECT top 25 numberlist as 进仓编号,resent as 进库时间,out as 出库时间,balename as 货物品名,piece as 件数,weight as 重量,bulk as 尺码,ahead as 唛头,site as 送货人地点,charge as 进仓费,trademark as 承运车辆牌号和司机,carrier as 承运单位,linkone as 承运单位联系方式,client as 委托人,linktwo as 委托人联系方式 FROM search", Conn,1,1 Dim SaveName SaveName = "test" Dim objExcel Dim ExcelPath ExcelPath = SaveName & ".xls" Set objExcel = New ExcelGen objExcel.RowOffset = 1 objExcel.ColumnOffset = 1 objExcel.GenerateWorksheet(objRS) If objExcel.SaveWorksheet(Server.MapPath(ExcelPath)) then Response.Write "<html><body bgcolor=""gainsboro"" text=""#000000"">已保存为Excel文件. "&_ "<a href='test.xls'>下载</a>" Else Response.Write "在保存过程中有错误!" End If Set objExcel = Nothing objRS.Close Set objRS = Nothing %> 时正确，但用 Set objSpreadsheet = Server.CreateObject("OWC10.Spreadsheet") 产生错误，
Microsoft VBScript runtime error '800a01b6'
Object doesn't support this property or method: 'objSpreadsheet.Columns(...).AutoFitColumns'
/test/admin/ToExcel.asp, line 50
