ASP.NET 產生 匯出 Excel 到Server端

假使要從Client產在一個excel檔到Server端
必須在web.config加上一個有權限執行excel的使用者
如:
<system.web>
<identity impersonate="true" userName ="Administrator" password ="123123"/>
</system.web>

參考自:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;306022

Try
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object

'Start a new workbook in Excel.
oExcel = CreateObject("Excel.Application")
oBook = oExcel.Workbooks.Add

For i As Integer = 0 To ds.Tables.Count - 1
oSheet = oBook.Worksheets.Add
'合併欄位
oSheet.Range("A1", "I1").Merge()
oSheet.Range("J1", "K1").Merge()
'文字置中
oSheet.Range("A1", "I1").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
oSheet.Range("J1", "K1").HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft
oSheet.cells(1, 1).Value = "自製工具個人分戶帳"
oSheet.cells(1, 10).Value = "資料日期: " & DateTime.Now.ToShortDateString
oSheet.cells(1, 1).Font.Bold = True
'加入外框線
oSheet.Range("A2", "K" & ds.Tables(i).Rows.Count + 2).Borders.LineStyle = 1
'強制轉型 不加這行的話如果字串是0002 匯到excel會變成2
oSheet.Cells.NumberFormatLocal = "@"
For j As Integer = 0 To ds.Tables(i).Rows.Count - 1
'Add data to cells of the first worksheet in the new workbook.
oSheet.Name = ds.Tables(i).Rows(j).Item(1)
'oSheet.Range("A1").Value = "Last Name"
oSheet.cells(2, 1).Value = "項次"
oSheet.cells(2, 2).Value = "借用人"
oSheet.cells(2, 3).Value = "借用人帳號"
oSheet.cells(j + 3, 1).value = RTrim(ds.Tables(i).Rows(j).Item(0))
oSheet.cells(j + 3, 2).value = RTrim(ds.Tables(i).Rows(j).Item(1))
oSheet.cells(j + 3, 3).value = RTrim(ds.Tables(i).Rows(j).Item(2))
'自動調整欄寬
oSheet.Columns("A:Z").EntireColumn.AutoFit()
Next
Next
'Save the Workbook and quit Excel.
'如果檔案已存在 則自動刪除
If System.IO.File.Exists("C:\" & filename & "borrow.xls") = True Then
System.IO.File.Delete("C:\" & filename & "borrow.xls")
End If
oBook.SaveAs("C:\" & filename & "borrow.xls")

oSheet = Nothing
oBook = Nothing
oExcel.Quit()
oExcel = Nothing
GC.Collect()

'KillExcel()
Catch ex As Exception

End Try

如果要強制刪除Excel的程序可用以下語法
不過要注意執行後會強制關掉畫面上所有的excel
Private Sub KillExcel()
Dim myProcesses() As Diagnostics.Process
Dim myProcess As Diagnostics.Process
' Returns array containing all instances of "EXCEL".
On Error Resume Next
myProcesses = Diagnostics.Process.GetProcessesByName("EXCEL")
For Each myProcess In myProcesses
myProcess.Kill()
Next
On Error GoTo 0
myProcesses = Nothing
myProcess = Nothing
End Sub