Using Excel templates in asp.net

asp.net|excel| Template 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 = "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 a 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