在Asp.net用C#建立動態Excel

　　在中建立本地的excel表並由服務器向外傳播是容易實現的而刪除掉嵌入的excelexe進程是困難的所以你不要打開任務管理器看Excelexe進程相關的東西是否還在內存裡面我在這裡提供一個解決方案裡面提供了兩個方法
CreateExcelWorkbook(說明建立Excel工作簿) 這個方法運行一個存儲過程返回一個DataReader 並根據DataReader 來生成一個Excel工作簿並保存到文件系統中創建一個download連接這樣用戶就可以將Excel表導入到浏覽器中也可以直接下載到機器上

　　第二個方法generatecsvreport 本質上是做同樣的一件事情僅僅是保存的文件的CSV格式仍然導入到Excel中CSV代碼能解決一個開發中的普片的問題你有一列裡面倒入了多個零CSV代碼能保證零不變空（說明就是在Excel表中多個零的值不能保存的問題）

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

　　注意你必須知道在運行這個頁面的時候你可能需要能在WebSever 服務器的文件系統中寫 ExcelCsv文件的管理員的權限處理這個問題的最簡單的方法就是運行這個頁面在自己的文件夾裡面並包括自己的配置文件並在配置文件中添加下面的元素

　　特別注意下面的代碼塊的作用是清除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的方法把它們去掉

　　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{
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="

　　}
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 making instances of Excel.exe go away so 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 proceduire that returns a DataReader and assembles a native Excel Workbook from 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 same thing but creates a CSV 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.

　　note: You should be aware that you will probably need to run this 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. Probably the easiest way to handle this is to have the page in its own folder with its own nfig, and insert an

　　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 of them in .NET.

From:http://tw.wingwit.com/Article/program/net/201311/12261.html