Class ExcelGen Private objSpreadsheet Private iColOffset Private iRowOffset Sub Class_Initialize() Set objSpreadsheet = Server.CreateObject("OWC11.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 Dim arrTitle arrTitle=Split("|订单号|订单类型|收订员|户名|地址|邮编|电话|订阅类型|报刊|起始订期|截止订期||折扣类型|金额|站名|投递段|投递方式|投递类型","|") For i=0 to UBound(arrTitle) objSpreadsheet.Cells(iRow, iCol).Value = arrTitle(i) objSpreadsheet.Columns(iCol).AutoFit '设置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).HorizontalAlignment = objSpreadsheet.Constants.xlHAlignCenter '居中 iCol = iCol + 1 Next 'objField 'Display all of the data Do While Not objRS.EOF iRow = iRow + 1 iCol = iColOffset if objRs("realname")<>"" then conn.Execute("update [CardSale] set xlsState=1 where id="&objRs("id")) for k=1 to 19 objSpreadsheet.Cells(iRow, 1).Value="" objSpreadsheet.Cells(iRow, 2).Value="" objSpreadsheet.Cells(iRow, 3).Value="报卡" objSpreadsheet.Cells(iRow, 4).Value="" objSpreadsheet.Cells(iRow, 5).Value=objRs("RealName") objSpreadsheet.Cells(iRow, 6).Value=objRs("Addr") objSpreadsheet.Cells(iRow, 7).Value=objRs("PostCode") objSpreadsheet.Cells(iRow, 8).Value=objRs("Mobile") objSpreadsheet.Cells(iRow, 9).Value=getClassName("cardType",objRs("CardId")) objSpreadsheet.Cells(iRow, 10).Value=getClassName("newspaper",objRs("CardId")) objSpreadsheet.Cells(iRow, 11).Value=objRs("StartDate") objSpreadsheet.Cells(iRow, 12).Value=objRs("EndDate") objSpreadsheet.Cells(iRow, 13).Value="" objSpreadsheet.Cells(iRow, 14).Value="" objSpreadsheet.Cells(iRow, 15).Value=getPrice(objRs("CardId")) objSpreadsheet.Cells(iRow, 16).Value=objRs("Station") objSpreadsheet.Cells(iRow, 17).Value=objRs("Dist") objSpreadsheet.Cells(iRow, 18).Value="" objSpreadsheet.Cells(iRow, 19).Value="" objSpreadsheet.Columns(iCol).AutoFit objSpreadsheet.Cells(iRow, iCol).Font.Size = 10 objSpreadsheet.Cells(iRow, 11).NumberFormat="yyyy-m-d" objSpreadsheet.Cells(iRow, 12).NumberFormat="yyyy-m-d" objSpreadsheet.Cells(iRow, 15).NumberFormat="#,##0.00" iCol = iCol + 1 next end if objRS.MoveNext Loop End Sub Function SaveWorksheet(strFileName) 'Save the worksheet to a specified filename Call objSpreadsheet.Export(strFileName, 0) SaveWorksheet = (Err.Number = 0) End Function End Class Dim objRS Set objRS = Server.CreateObject("ADODB.Recordset") objRS.Open "SELECT * FROM CardSale where xlsState=0",conn,1,1 Dim objExcel,ExcelPath,strOut ExcelPath="../UploadFile/mydoc.xls" Set objExcel = New ExcelGen objExcel.RowOffset = 1 objExcel.ColumnOffset = 1 objExcel.GenerateWorksheet(objRS) If objExcel.SaveWorksheet(Server.MapPath(ExcelPath)) then strOut="已保存为Excel文件.<a href="""&ExcelPath&""">点击下载</a>" Else strOut="在保存过程中有错误!" End If Set objExcel = Nothing objRs.Close set objRs=Nothing closeconn Response.Write(strOut)