2008-03-20 匯出Excel 4276 0 ASP.NET 匯出Excel <P>Imports System.Diagnostics Protected Sub Button3_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button3.Click</P> ExportToExcel("exec big_queue") End Sub Sub ExportToExcel(ByVal pstrSql As String) beforetime = DateTime.Now Try Dim papplication As Excel.Application Dim xBk As Excel._Workbook Dim xSt As Excel._Worksheet Dim xQt As Excel._QueryTable Dim ExcelConn As String = "ODBC;DRIVER=SQL Server;SERVER=FLOW;UID=sa;PWD=1999;APP=Microsoft(R) Windows (R) 2000 Operating System;WSID=me;DATABASE=AOCEPI_PROD" papplication = New Excel.ApplicationClass() xBk = papplication.Workbooks.Add(True) xSt = CType(xBk.ActiveSheet, Excel._Worksheet) 'papplication.Cells(2, 2) = Me.Title 'xSt.Range(papplication.Cells(2, 2), papplication.Cells(2, 2)).Font.Bold = True 'xSt.Range(papplication.Cells(2, 2), papplication.Cells(2, 2)).Font.Name = "黑體" 'xSt.Range(papplication.Cells(2, 2), papplication.Cells(2, 2)).Font.Size = 22 'xSt.Shapes.AddPicture(Request.PhysicalApplicationPath & Format(Now, "yyyyMMddHH") & ".gif", Office.MsoTriState.msoTrue, Office.MsoTriState.msoTrue, 100, 100, 100, 100) xQt = xSt.QueryTables.Add(ExcelConn, xSt.Range(papplication.Cells(1, 1), papplication.Cells(1, 1)), pstrSql) xQt.Name = "導出Excel" xQt.FieldNames = True xQt.RowNumbers = False xQt.FillAdjacentFormulas = False xQt.PreserveFormatting = False xQt.BackgroundQuery = True xQt.RefreshStyle = Excel.XlCellInsertionMode.xlInsertDeleteCells xQt.AdjustColumnWidth = True xQt.RefreshPeriod = 0 xQt.PreserveColumnInfo = True xQt.Refresh(xQt.BackgroundQuery) papplication.Visible = True aftertime = DateTime.Now Catch ex As Exception aftertime = DateTime.Now killExcelProcess() End Try End Sub Sub killExcelProcess() Dim myprocesses As Process() Dim starttime As DateTime myprocesses = Process.GetProcessesByName("Excel") For Each myprocess As Process In myprocesses starttime = myprocess.StartTime If starttime > beforetime And starttime < aftertime Then myprocess.Kill() End If Next End Sub ASP.NET 回首頁