在asp.net中使用excel模板

核心提示：Imports System.Runtime.InteropServices.MarshalThen, replace the default Page_Load event with the code in Figure 1.Private Sub Page_Load(ByVal sender As System.O

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