分享
 
 
 

在Asp.net用C#建立动态Excel

王朝c#·作者佚名  2006-11-24
窄屏简体版  字體: |||超大  

在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的装配件)

<identity impersonate="true" userName="adminuser" password="adminpass" />

特别注意 下面的代码块的作用是清除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 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(".") + "\\";

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

[1] [2] 下一页

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有