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)
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
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
EXEC master..xp_cmdshell 'bcp SettleDB.dbo.shanghu out c:\temp1.xls -c -q -S"GNETDATA/GNETDATA" -U"sa" -P""'