Shulou(Shulou.com)06/02 Report--

Today, I will talk to you about how to implement ASP.NET Excel dynamically. Many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

The dynamic implementation of ASP.NET Excel first establishes the local Excel table in Asp.net and propagates it out by the server, but it is difficult to delete the embedded Excel.exe process. So don't open the task manager to see if something related to the Excel.exe process is still in memory. I provide a solution here, which provides two methods:

The method of "CreateExcelWorkbook" (instructions for creating an ASP.NET Excel dynamic workbook) runs a stored procedure, returns a DataReader and generates an Excel workbook according to DataReader, and saves it to the file system, creating a "download" connection so that the user can import the Excel table into the browser or download it directly to the machine.

The second method: GenerateCSVReport essentially does the same thing, just the CSV format of the saved file. Still imported into Excel, CSV code can solve the problem of a developing tablet: you have a column with multiple zeros, and the CSV code can keep the zeros blank. (description: it is the problem that the values of multiple zeros cannot be saved in the Excel table)

In the downloadable solution, there is a valid class "SPGen" that runs the stored procedure and returns DataReader, and a method to remove the file can delete the earlier value in a specific time. The main method that appears below is CreateExcelWorkbook

Note: you must know that when running this page, you may need the permissions of an administrator who can write Excel,Csv files to the file system of the WebSever server. The easiest way to deal with this problem is to run the page in your own folder and include your own configuration file. And add the following element < identity impersonate = "true" to the configuration file. You still need write access to the access control list (ACL) of the physical folder, and only the identity of the page running like this has write permission, * you need to set up a Com to connect to the Excel 9.0 or Excel 10 type library, and VS.NET will generate an assembly for you. I believe Microsoft has a link on their Office website that can be downloaded to Microsoft's initial assembly. (maybe not, my understanding is for. Net assemblies)

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

Note in particular that the purpose of the following code block is to clear ASP.NET Excel dynamic 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 oSheet "," oWb "," oRng ", and so on are also instances of COM, and we need the ReleaseComObject method of the Marshal class to remove them from .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 jacuzzi j < myReader.FieldCount;j++) {oSheet.Cells [1, juni1] = myReader.GetName (j). ToString ();} / / build the sheet contents while (myReader.Read ()) {for (int kryptonk < 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 Excel.XlVAlign.xlVAlignCenter; 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 referrals 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 }} after reading the above, do you have any further understanding of how to implement ASP.NET Excel dynamic implementation? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.