[ASP.NET C#] 讀寫Office Excel之前要做的相關設定

Reference：http://www.dotblogs.com.tw/shadow/archive/2011/05/02/24043.aspx

[ASP.net/MSSQL] 讀寫Office Excel之前要做的相關設定

   

 

2013.5.14 追記黑暗執行緒的一篇好文：透過.NET程式操作Excel的注意事項

網站跑的是.net Framework 4 ，WebServer電腦沒有灌Microsoft Excel軟體

ASP.net程式在宣告以下的變數時：

Microsoft.Office.Interop.Excel.Application xlApp = null;

Workbook wb = null;

Worksheet ws = null;

Range aRange = null;

會跑出錯誤

The resource cannot be found.

所以要用ASP.net程式讀寫Excel的話

得先做以下設定

Step 1. 正式機Server的電腦要先灌Microsoft Excel軟體(否則會錯誤The resource cannot be found.)

Step 2. 接著做以下設定，否則仍會發生

System.NullReferenceException: 並未將物件參考設定為物件的執行個體
Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

由於發生下列錯誤，為具有 CLSID {00024500-0000-0000-C000-000000000046} 的元件擷取 COM Class Factory 失敗: 80070005。
描述: 在執行目前 Web 要求的過程中發生未處理的例外情形。請檢閱堆疊追蹤以取得錯誤的詳細資訊，以及在程式碼中產生的位置。

例外詳細資訊: System.UnauthorizedAccessException: 由於發生下列錯誤，為具有 CLSID {00024500-0000-0000-C000-000000000046} 的元件擷取 COM Class Factory 失敗: 80070005。

沒有授權 ASP.NET 存取要求的資源。請考慮將資源存取權授與 ASP.NET 要求識別。ASP.NET 有一個基本處理序識別 (通常在 IIS 5 上為 {MACHINE}\ASPNET，在 IIS 6 上為 Network Service)，會在應用程式未模擬的情況下使用。如果應用程式是透過 <identity impersonate="true"/> 模擬，這個識別將會是匿名使用者 (通常為 IUSR_MACHINENAME) 或經過驗證的要求使用者。

若要對檔案授與 ASP.NET 存取權，請在檔案總管中以滑鼠右鍵按一下檔案，選擇 [內容] 並選取 [安全] 索引標籤。按一下 [新增] 加入適當的使用者或群組。反白顯示 ASP.NET 帳戶，並且選取所需存取權限的核取方塊。

(Windows Server 2003 範例)

由系統管理工具>元件服務>電腦>我的電腦>DCOM設定>在右方找到Microsoft Excel應用程式(Application)

右鍵>內容>頁籤安全設定

點選啟動和啟用權限>編輯

因為是IIS 6，所以新增一個Network Service帳戶，且四個權限全部勾選

Server電腦到此設定完畢，另外

解決Win2008 Asp.NET使用Word.Selection會有System.NullReferenceException的問題 - 亂馬客- 點部落

的文章有提到識別身份(識別碼)，要再選擇「互動式使用者」，這邊我是保持預設設定「執行啟動的使用者」仍然還是可以Run的。

Step 3.為網站專案加入COM參考(Visual Studio中，專案右鍵>加入參考)

• 視Server安裝的 Office 版本而定，Excel 組件可能會稱為 [Excel 10 物件程式庫] 或 [Excel 11 物件程式庫(Office 2007)]。
  
  參考來源：MSDN文件 HOW TO：使用 COM Interop 來建立 Excel 試算表 (C#)
  Step 4.為網站專案加入.NET參考(Visual Studio中，專案右鍵>加入參考)
  
  12.0.0.0為2007版本，14.0.0.0為2010版本，同時加兩個dll的話，網站執行會錯誤，或電腦上已安裝了某版本Office，網站又加入參考而發生以下錯誤的話
  CS1758: Cannot embed interop type 'Microsoft.Office.Interop.Excel.Constants' found in both assembly 'c:\WINDOWS\Microsoft.NET\Framework\v4.0.30319\Temporary ASP.NET Files\網站名稱\a43c6b27\e0d3dd6d\assembly\dl3\6741d390\004ce13f_fab6cb01\Microsoft.Office.Interop.Excel.DLL' and 'c:\WINDOWS\assembly\GAC\Microsoft.Office.Interop.Excel\12.0.0.0__71e9bce111e9429c\Microsoft.Office.Interop.Excel.dll'. Consider setting the 'Embed Interop Types' property to false. An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.
  解決辦法：
  1.先把網站Bin目錄底下的
  Microsoft.Office.Interop.Excel.dll和xml、Microsoft.Vbe.Interop.dll、office.dll和xml都刪掉
  2.到IIS管理員停止IIS 網際網路服務
  3.刪除Server電腦 C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319\Temporary ASP.NET Files\網站名稱\(此資料夾底下所有檔案)
  (請注意.net framework版本不同，目錄也不同)
  4.再重新啟動IIS 網際網路服務就可以了
  
  
  
  
  網站設定這樣就完畢，ASP.net程式應該可以讀寫Excel檔案了。
  2011.5.19 追記，Windows Server 2008 除了以上設定外，要再加以下的設定
  
  否則會
  
  Microsoft Office Excel 無法存取檔案 'D:\1cd.xls'。可能原因如下:
  ‧檔案的名稱或路徑不存在。
  ‧這個檔案正被其他程式所使用中。
  ‧您要儲存的活頁簿名稱與現有開啟的活頁簿名稱相同。
  描述: 在執行目前 Web 要求的過程中發生未處理的例外情形。請檢閱堆疊追蹤以取得錯誤的詳細資訊，以及在程式碼中產生的位置。
  例外詳細資訊: System.Runtime.InteropServices.COMException: Microsoft Office Excel 無法存取檔案 'D:\1cd.xls'。可能原因如下:
  ‧檔案的名稱或路徑不存在。
  ‧這個檔案正被其他程式所使用中。
  ‧您要儲存的活頁簿名稱與現有開啟的活頁簿名稱相同。
  
  1. Web.config裡，加入<identity />區段，登入Server電腦的帳密
  
  2. 在C:\Windows\System32\config\systemprofile\底下建「Desktop」資料夾
  
  詳細來源：呆奇士 WinServer2008 & IIS & Excel - 無法存取檔案
  
  
  最後
  
  當然如果你的ASP.net 程式是跑在自己電腦上，自己的電腦又已經灌了Microsoft Excel軟體，就不用做以上繁雜的設定
  
  
  
  2011.6.17 追記
  
  今天在Windows Server 2008 R2 (它只有64位元版本)的環境上部署Web site
  
  先灌了Excel 2003軟體，但執行到Excel匯入程式時出現以下錯誤
  
  
  Compilation Error
  
  Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.
  
  Compiler Error Message: CS1758: Cannot embed interop type 'Microsoft.Office.Interop.Excel.Constants' found in both assembly 'c:\Windows\Microsoft.NET\Framework64\v4.0.30319\Temporary ASP.NET Files\blood_intra\c1a2dcd7\cd772699\assembly\dl3\da3f232c\001fb03e_fab6cb01\Microsoft.Office.Interop.Excel.DLL' and 'c:\Windows\assembly\GAC\Microsoft.Office.Interop.Excel\11.0.0.0__71e9bce111e9429c\Microsoft.Office.Interop.Excel.dll'. Consider setting the 'Embed Interop Types' property to false.
  
  
  以上訊息大致說Web site引用的參考檔案和Server上灌的Excel軟體的Excel.dll檔互衝
  
  解決方式：把Web site>Bin資料夾底下 
  
  Microsoft.Office.Interop.Excel.dll和xml、Microsoft.Vbe.Interop.dll、office.dll和xml
  Excel相關參考檔案都刪除
  
  讓Web site自己去抓Server上的Excel.dll即可。
  
  但後來再執行程式出現object reference not set to an instance of an object 的錯誤訊息(真是鬼打牆= =
  
  本來想照著以上說明去「元件服務」設定DCOM元件，結果發現竟然沒有Microsoft Excel Application(汗
  
  忽然想起來這是64位元的作業系統，我灌的是32位元的Excel軟體…Orz
  
  後來照著呆奇士 WinServer2008 & IIS & Excel - 無法存取檔案 裡頭文章所講
  
  設定Web.config檔的<identity>區段和新增資料夾C:\Windows\SysWOW64\config\systemprofile\Desktop
  
  才終於成功
   
  
  ※2012.10.17 追記[元件服務]在x64系統無法看見Microsoft Excel Application的問題：http://www.dotblogs.com.tw/gelis/archive/2010/12/25/20381.aspx
  2011.6.17 再追記 SQL Server 2008 (64位元) 如何新增一個Office Excel Driver：
  如果是Windows Server 2008 R2(64位元)上的SQL Server 2008 (64位元)，會發現無論怎麼灌Office Excel，連結伺服器的提供者都不會出現Excel相關Driver
  那是因為目前的Office軟體大都是32位元，SQL Server 2008 (64位元)不支援的關係
  解決方法：
  到Microsoft下載中心 下載Microsoft Access Database Engine 2010 可轉散發套件(挑最新版就好)
  因為環境都是64位元，所以要下載AccessDatabaseEngine_X64.exe，這個檔案
  然後安裝前，如果有先灌好Office 32位元軟體的話，程式會要求你先解除安裝，就先解除Office軟體後，再安裝AccessDatabaseEngine_X64.exe這個檔案
  連結伺服器的提供者就會出現一個Microsoft.ACE.OLEDB.12.0可以撈Excel資料(而且SQL Server上不用灌Office Excel軟體)
  MSDN的討論：如何新增連結伺服器的提供者
  
  2011.6.20 追記環境設定
  
  如何在Windows Server 2008 R2環境下，使用SQL Server 帳戶(sa)連線的ASP.net程式，下OpenRowSet語法指令匯入資料
  
  DB Server和Web Server兩台作業系統都是Windows Server 2008 R2
  
  1.兩台作業系統都新增相同的帳戶、相同的密碼(範例帳戶HelloWorld，密碼：HelloWorld，系統管理員)：
  
  
  
  2. 新增完後對Web Site的Web.config檔新增<identity>區段，如下：
  
  <identity impersonate="true" userName="HelloWorld" password="HelloWorld"   />
  
  3.在DB機器的C:\底下新增一個分享資料夾(範例：aspUpload)
  
  
  
  安全性加入一個Everyone，完全控制
  
  
  該資料夾點選共用：
  
  
  共用加入Everyone：
  
  
  如此一來，共用的「進階共用」的權限應該就會多一個Everyone的權限
  
  環境設定完畢，就著就可以寫Code了
  
   
      //Click按鈕事件
      protected void btn_Click(object sender, EventArgs e)
      {
          //檔案上傳到DB Server機器上
          fu.SaveAs(@"\\192.168.1.110\aspUpload\" + fu.FileName);
          DBUtil db = new DBUtil();//這是用sa登入的資料庫連線物件
          //SQL Server 撈本機路徑的資料
          DataTable dt = db.QueryDataTable(@"SELECT a.* from
                                             OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=yes;IMAX=1;Database=C:\aspUpload\" + fu.FileName + @"',[Sheet1$]) a");
          //GridView控制項呈現資料 - Debug用
          gv.DataSource = dt;
          gv.DataBind();
      }
  
  參考Windows Server 2003的設定：
  1.How to : 如何將使用者利用ASP.NET程式上傳的檔案傳送到另一台電腦中(不透過FTP)
  2.ASP.NET 如何將檔案寫入到網路芳鄰的分享目錄 
  對照以上兩篇文章，我沒有在Web Server新增虛擬目錄
  也沒有在WebServer的C:\WINDOWS\Microsoft.NET\Framework(和64)\v2.0.50727(和v4.xx版)\Temporary ASP.NET Files資料夾設定權限
  
  2011.7.16 上述程式碼做法是把檔案先上傳到DB Server機器，這邊再提供一個用sa撈AP Server機器上檔案的方法
  
  Step 1. AP Server端的資料夾要先做共用分享(假設everyone、讀寫都可以)
  Step 2. DB Server端和AP Server端電腦都要有相同的Windows帳密(沒有的話就新增一個，假設 MyAccount/P@ssw0rd)
  Step 3. 最重要的是以下這張圖
  
  
   
  (這邊用個人的測試環境Demo)把SQL Server的服務登入帳戶改成MyAccount/P@ssw0rd就可以了。
  
  附上MSDN論壇的討論：Win2008 R2+SQL2008 R2 使用sa帳戶下OPENROWSET撈不到另一台機器上的資料