在Asp.net用C#建立動態Excel(外文翻譯)

作者：Eve Cole 更新時間：2009-06-30 16:40:25

在Asp.net中建立本地的Excel表，並由伺服器向外傳播是容易實現的，而刪除掉嵌入的Excel.exe進程是困難的。所以你不要打開工作管理員，看Excel.exe進程相關的東西是否還在記憶體裡面。我在這裡提供一個解決方案，裡面提供了兩個方法：
"CreateExcelWorkbook"(說明建立Excel工作簿) 這個方法運行一個預存程序，傳回一個DataReader 並根據DataReader 來產生一個Excel工作簿，並且儲存到檔案系統中，建立一個「download」連接，這樣使用者就可以將Excel表格匯入到瀏覽器中也可以直接下載到機器上。

第二個方法：GenerateCSVReport 本質上是做同樣的事情，只是已儲存的檔案的CSV格式。仍然匯入到Excel中，CSV程式碼能解決一個開發中的普片的問題：你有一列裡面倒入了多個零，CSV程式碼可以保證零不變空。（說明：就是在Excel表中多個零的值所不能保存的問題）

在可以下載的解決方案中，包含一個有效的類別」 SPGen」能運行預存程序並返回DataReader ，一個移除檔案的方法能刪除早先於一個特定的時間值。下面出現的主要的方法就是CreateExcelWorkbook

注意：你必須知道在運行這個頁面的時候，你可能需要能在WebSever 伺服器的檔案系統中寫Excel，Csv檔案的管理員的權限。處理這個問題的最簡單的方法就是運行這個頁面在自己的資料夾裡面並包括自己的設定檔。並在設定檔中加入下面的元素<identity impersonate ="true" ... 。你仍然需要實體資料夾的存取控制清單(ACL)的寫的權限，只有這樣運行的頁面的身份有寫的權限，最後，你需要設定一個Com連接到Excel 9.0 or Excel 10 類型庫，VS.NET將為你生成一個裝配件。我相信微軟在他們Office網站上有一個連接，可以下載到微軟的初始的裝配件。（可能不准，我的理解是面向.net的裝配件）

特別注意下面的程式碼區塊的作用是清除Excel的物件。

// Need all following code to clean up and extingush all references!!!
oWB.Close(null,null,null);
oXL.Workbooks.Close();
oXL.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject (oRng);
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(); // force final cleanup!

這是必須的，因為oSheet", "oWb" , 'oRng", 等等物件也是COM的實例，我們需要

Marshal類別的ReleaseComObject的方法把它們從.NET去掉

private void CreateExcelWorkbook(string spName, SqlParameter[] parms )
{
string strCurrentDir = Server.MapPath(".") + "\";
RemoveFiles(strCurrentDir); // utility method to clean up old files
Excel.Application oXL;
Excel._Workbook oWB;
Excel._Worksheet oSheet;
Excel.Range oRng;

try
{
GC.Collect();// clean up any other excel guys hangin' around...
oXL = new Excel.Application();
oXL.Visible = false;
//Get a new workbook.
oWB = (Excel._Workbook)(oXL.Workbooks.Add( Missing.Value ));
oSheet = (Excel._Worksheet)oWB.ActiveSheet;
//get our Data

string strConnect = System.Configuration.ConfigurationSettings.AppSettings["connectString"];
SPGen sg = new SPGen(strConnect,spName,parms);
SqlDataReader myReader = sg.RunReader();
// Create Header and sheet...
int iRow =2;
for(int j=0;j<myReader.FieldCount;j++)
{
oSheet.Cells[1, j+1] = myReader.GetName(j).ToString();
}
// build the sheet contents
while (myReader.Read())
{
for(int k=0;k < myReader.FieldCount;k++)
{
oSheet.Cells[iRow,k+1]= myReader.GetValue(k).ToString();
}
iRow++;
}// end while
myReader.Close();
myReader=null;
//Format A1:Z1 as bold, vertical alignment = center.
oSheet.get_Range("A1", "Z1").Font.Bold = true;
oSheet.get_Range("A1", "Z1").VerticalAlignment =Excel.XlVAlign.xlVAlignCenter;
//AutoFit columns A:Z.
oRng = oSheet.get_Range("A1", "Z1");
oRng.EntireColumn.AutoFit();
oXL.Visible = false;
oXL.UserControl = false;
string strFile ="report" + System.DateTime.Now.Ticks.ToString() +".xls";
oWB.SaveAs( strCurrentDir + strFile,Excel.XlFileFormat.xlWorkbookNormal,
null,null,false,false,Excel.XlSaveAsAccessMode.xlShared,false,false,null,null,null);
// Need all following code to clean up and extingush all references!!!
oWB.Close(null,null,null);
oXL.Workbooks.Close();
oXL.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject (oRng);
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(); // force final cleanup!
string strMachineName = Request.ServerVariables["SERVER_NAME"];
errLabel.Text="<A href=http://" + strMachineName +"/ExcelGen/" +strFile + ">Download Report</a>";

}
catch( Exception theException )
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat( errorMessage, theException.Message );
errorMessage = String.Concat( errorMessage, " Line: " );
errorMessage = String.Concat( errorMessage, theException.Source );
errLabel.Text= errorMessage ;
}
}

以下是原文

Create Dynamic ASP.NET Excel Workbooks In C#

By Peter A. Bromberg, Ph.D.
Printer - Friendly Version

There is also, in the downloadable solution, a utility class "SPGen" that handles running stored

Generating native Excel spreadsheets from your web server is not that difficult with ASP.NET. What can be difficult is. you don't open up TaskMgr and see 123 instances of EXCEL.EXE still sitting in memory. I provide here a solution that has two methods, "CreateExcelWorkbook", which runs a stored eduative it, saves it to the filesystem, and creates a "Download" link so the user can either load the report into Excel in their browser, or download the XLS file. The second method, GenerateCSVReport, does essentially the samething essentially file that will, of course, also load into Excel. The CSV code correctly handles a common developer problem in that if you have a column that has leading zeroes, they are preserved.

procedures and returning DataReaders, and a RemoveFiles utility method that cleans up any XLS or CSV file older than the specified number of minutes. The key method presented below is the CreateExcelWorkbook method

. page under an account that has administrative privileges as it needs write permissions to store the generated Excel or CSV files on the webserver's file system. Proly the easiest way to handle this is to have the page system. Proly the easiest way to handle this is to have the page 膜and insert an <identity impersonate ="true" ... elment. You may also need to enable ACL permissions on the physical folder as well so that the identity the page runs under has write permissions. Finally, you'll need to set a'll COM reference to the Excel 9.0 or Excel 10 Typelibrary and let VS.NET generate the Interop assemblies for you. I believe MS also has a link on their Office site where you can download the Office primary Interop Assemblies.

Note especially the code block that does the "cleanup" of the Excel objects:

This is necessary because all those littlle objects "oSheet", "oWb" , 'oRng", etc. are all COM instances and we need to use the InteropServices ReleaseComObject method of the Marshal class to get rid themprivate inv

. CreateExcelWorkbook(string spName, SqlParameter[] parms)
{
string strCurrentDir = Server.MapPath(".") + "\";
RemoveFiles(strCurrentDir); // utility method to clean up old files
Excel.Application oXL;
Excel._Workbook oWB;
Excel._Worksheet oSheet;
Excel.Range oRng;

http://macroxu-1982.cnblogs.com/archive/2006/05/31/413879.html