Using Excel templates in

Source: Internet
Author: User
Tags count integer range|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) & _


stemplate = Server.MapPath (Request.applicationpath) & _


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) = "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


' 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)



End Function

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.