在asp.net中使用excel模板

Imports System.Runtime.InteropServices.Marshal

Then, replace the default Page_Load event with the code in Figure 1.

Private Sub Page_Load(ByVal sender As System.Object, _

  ByVal e As System.EventArgs) Handles MyBase.Load

    Dim oExcel As New Excel.Application()

    Dim oBooks As Excel.Workbooks, oBook As Excel.Workbook

    Dim oSheets As Excel.Sheets, oSheet As Excel.Worksheet

    Dim oCells As Excel.Range

    Dim sFile As String, sTemplate As String

    Dim dt As DataTable = _

    CType(Application.Item("MyDataTable"), DataTable)

 

    sFile = Server.MapPath(Request.ApplicationPath) & _

      "/MyExcel.xls"

 

    sTemplate = Server.MapPath(Request.ApplicationPath) & _

       "/MyTemplate.xls"

 

    oExcel.Visible = False : oExcel.DisplayAlerts = False

 

    'Start a new workbook

    oBooks = oExcel.Workbooks

    oBooks.Open(Server.MapPath(Request.ApplicationPath) & _

    "/MyTemplate.xls") 'Load colorful template with chart

    oBook = oBooks.Item(1)

    oSheets = oBook.Worksheets

    oSheet = CType(oSheets.Item(1), Excel.Worksheet)

    oSheet.Name = "First Sheet"

    oCells = oSheet.Cells

 

    DumpData(dt, oCells) 'Fill in the data

 

    oSheet.SaveAs(sFile) 'Save in a temporary file

    oBook.Close()

 

    'Quit Excel and thoroughly deallocate everything

    oExcel.Quit()

    ReleaseComObject(oCells) : ReleaseComObject(oSheet)

    ReleaseComObject(oSheets) : ReleaseComObject(oBook)

    ReleaseComObject(oBooks) : ReleaseComObject(oExcel)

    oExcel = Nothing : oBooks = Nothing : oBook = Nothing

    oSheets = Nothing : oSheet = Nothing : oCells = Nothing

    System.GC.Collect()

    Response.Redirect(sFile) 'Send the user to the file

End Sub

 

'Outputs a DataTable to an Excel Worksheet

Private Function DumpData(ByVal _

  dt As DataTable, ByVal oCells As Excel.Range) As String

    Dim dr As DataRow, ary() As Object

    Dim iRow As Integer, iCol As Integer

 

    'Output Column Headers

    For iCol = 0 To dt.Columns.Count - 1

        oCells(2, iCol + 1) = dt.Columns(iCol).ToString

    Next

 

    'Output Data

    For iRow = 0 To dt.Rows.Count - 1

        dr = dt.Rows.Item(iRow)

        ary = dr.ItemArray

        For iCol = 0 To UBound(ary)

            oCells(iRow + 3, iCol + 1) = ary(iCol).ToString

            Response.Write(ary(iCol).ToString & vbTab)

        Next

    Next

End Function