ASP.NET匯出Excel檔案


try
{
#region 撈資料

Tran objT = new Tran();
DataTable dt = objT.GetIncomeData(this.Q_PAY_DATE_S.Text.Trim(), this.Q_PAY_DATE_E.Text.Trim(), this.Q_COMP.Text);

#endregion

Excel.Application oXL;
Excel.Workbook oWB;
Excel.Worksheet oSheet;
GC.Collect();
oXL = new Excel.Application();
oXL.Visible = false;
oWB = oXL.Workbooks.Add(System.Reflection.Missing.Value);
oSheet = (Excel.Worksheet)oWB.ActiveSheet;

#region 設定表頭

oSheet.Cells[1, 1] = "信託財產支付日";
oSheet.Cells[1, 2] = "契約編號";
oSheet.Cells[1, 3] = "契約執行商品";
oSheet.Cells[1, 4] = "信託商品";

#endregion

#region 塞資料

for (int i = 0; i < dt.Rows.Count; i++)
{
oSheet.Cells[i + 2, 1] = dt.Rows[i]["PAY_DATE"].ToString();
oSheet.Cells[i + 2, 2] = dt.Rows[i]["CONTRACT_NO"].ToString();
oSheet.Cells[i + 2, 3] = dt.Rows[i]["P_PROD_NAEM"].ToString();
oSheet.Cells[i + 2, 4] = dt.Rows[i]["C_PROD_NAME"].ToString();
}

#endregion

oXL.Visible = false;
oXL.UserControl = false;
//存檔
string FileName = Guid.NewGuid() + ".xls";
string FilePath = Server.MapPath("~/TempFile/" + FileName);
oWB.SaveAs(FilePath, Excel.XlFileFormat.xlWorkbookNormal, null, null, false, false, Excel.XlSaveAsAccessMode.xlShared, false, false, null, null, null);
oWB.Close(null, null, null);
oXL.Workbooks.Close();
oXL.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(oXL);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oWB);
oSheet = null;
oWB = null;
oXL = null;
GC.Collect();

//產生下載檔連結
this.Q_Link.Text = "系統若無自動跳出下載畫面，請點按此連結下載檔案";
this.AjaxPostBackJs.Value += "$('#Q_LinkA')[0].click()";
}
catch (Exception ex)
{
WebUtility.logger.Error(ex.Message, ex);
this.ShowMessage("轉出Excel錯誤!");
}


如果出現
由於發生下列錯誤，為具有 CLSID {00024500-0000-0000-C000-000000000046} 的元件擷取 COM Class Factory 失敗: 80070005。

之類的錯誤，
可以在Web.config裡面加上


......

讓ASP.NET的執行緒，有權限去Create Excel的Object

另外，Server上，也要安裝Office唷~