|
|
在ASP.NET中建立本地的Excel表，并由服務(wù)器向外傳播是容易實(shí)現(xiàn)的，而刪除掉嵌入的Excel.exe進(jìn)程是困難的。所以 你不要打開任務(wù)管理器 ，看Excel.exe進(jìn)程相關(guān)的東西是否還在內(nèi)存里面。我在這里提供一個(gè)解決方案 ，里面提供了兩個(gè)方法 ：
"CreateExcelWorkbook"(說明 建立Excel工作簿) 這個(gè)方法 運(yùn)行一個(gè)存儲(chǔ)過程 ，返回一個(gè)DataReader 并根據(jù)DataReader 來生成一個(gè)Excel工作簿 ，并保存到文件系統(tǒng)中，創(chuàng)建一個(gè)“download”連接，這樣 用戶就可以將Excel表導(dǎo)入到瀏覽器中也可以直接下載到機(jī)器上。
第二個(gè)方法：GenerateCSVReport 本質(zhì)上是做同樣的一件事情，僅僅是保存的文件的CSV格式 。仍然 導(dǎo)入到Excel中，CSV代碼能解決一個(gè)開發(fā)中的普片的問題：你有一列 里面倒入了多個(gè)零，CSV代碼能保證零不變空 。（說明： 就是在Excel表中多個(gè)零的值 不能保存的問題）
在可以下載的解決方案中，包含一個(gè)有效的類 ” SPGen” 能運(yùn)行存儲(chǔ)過程并返回DataReader ，一個(gè)移除文件的方法 能刪除早先于一個(gè)特定的時(shí)間值。下面出現(xiàn)的主要的方法就是CreateExcelWorkbook
注意：你必須知道 在運(yùn)行這個(gè)頁面的時(shí)候，你可能需要能在WebSever 服務(wù)器的文件系統(tǒng)中寫 Excel，Csv文件的管理員的權(quán)限。處理這個(gè)問題的最簡單的方法就是運(yùn)行這個(gè)頁面在自己的文件夾里面并包括自己的配置文件。并在配置文件中添加下面的元素<identity impersonate ="true" ... 。你仍然需要物理文件夾的訪問控制列表(ACL)的寫的權(quán)限，只有這樣運(yùn)行的頁面的身份有寫的權(quán)限，最后，你需要設(shè)置一個(gè)Com連接到Excel 9.0 or Excel 10 類型庫 ，VS.NET 將為你生成一個(gè)裝配件。我相信 微軟在他們Office網(wǎng)站上有一個(gè)連接，可以下載到微軟的初始的裝配件。（可能不準(zhǔn)，我的理解是面向.NET的裝配件）
<identity impersonate="true" userName="adminuser" password="adminpass" />
特別注意 下面的代碼塊的作用是清除Excel的對(duì)象。
// 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!
這是必須的 ，因?yàn)閛Sheet", "oWb" , 'oRng", 等等 對(duì)象也是COM的實(shí)例，我們需要
Marshal類的ReleaseComObject的方法把它們從.NET去掉
private void CreateExcelWorkbook(string spName, SqlParameter[] parms)
{
string strCurrentDir = Server.MapPath(".") + "http://";
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 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 web.config, 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 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.
<identity impersonate="true" userName="adminuser" password="adminpass" />
Note especially the code block that does the "cleanup" of the Excel objects:
// 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!
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.
private void CreateExcelWorkbook(string spName, SqlParameter[] parms)
{
string strCurrentDir = Server.MapPath(".") + "http://";
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 ;
}
}
-翻譯匆忙 ,有誤請(qǐng)諒解 ,歡迎指點(diǎn),探討 ---小徐
AspNet技術(shù)：在Asp.net用C#建立動(dòng)態(tài)Excel，轉(zhuǎn)載需保留來源！
鄭重聲明：本文版權(quán)歸原作者所有，轉(zhuǎn)載文章僅為傳播更多信息之目的，如作者信息標(biāo)記有誤，請(qǐng)第一時(shí)間聯(lián)系我們修改或刪除，多謝。
