利用owc建立EXECL的例子

呵呵，这个是我介绍制作EXECL的第三种方法了，对于OWC，我在前面的文章里也已经有所介绍，废话少说，请看代码：

<%
  Option Explicit

  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
        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
          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 titles", "DSN=FooBar"

  Dim objExcel
  Set objExcel = New ExcelGen

  objExcel.RowOffset = 4
  objExcel.ColumnOffset = 1

  objExcel.GenerateWorksheet(objRS)
  If objExcel.SaveWorksheet(Server.MapPath("foo.xls")) then
    Response.Write "Worksheet saved.  <a href=""foo.xls"">Download</a>"
  Else
    Response.Write "Error in saving worksheet!"
  End If

  Set objExcel = Nothing

  objRS.Close
  Set objRS = Nothing
%>

注意：请注意权限的问题……经常有人发EMAIL给我关于这方面的问题，实际上你只要给iuser_machine以适当的权限就可以了，比如对存放在EXECL文件的地方要有写的权限

：），下次我看看会再介绍一种生成EXECL的方法，可以利用BCP做的。