Using C # to build dynamic excel_ practical skills in asp.net

It is easy to create a local Excel table in asp.net and spread out by the server, and it is difficult to remove the embedded Excel.exe process. So you don't open Task Manager to see if the Excel.exe process is still in memory. I offer a solution here that provides two methods:

"Createexcelworkbook" (Description of creating an Excel workbook) This method runs a stored procedure, returns a DataReader and generates an Excel workbook based on DataReader, and saves it to the file system, creating a " Download connection so that users can import Excel tables into the browser or download them directly to the machine.

The second method: Generatecsvreport is essentially doing the same thing, just the CSV format of the saved file. Still importing into Excel, the CSV code solves the problem of a developing pop: you have a column with multiple zeros in it, and the CSV code guarantees that 0 is not empty. (Note: A problem where multiple zeros in an Excel table cannot be saved)

In a solution that can be downloaded, including a valid class "Spgen" that can run the stored procedure and return DataReader, a method of removing the file can remove the previous value from a specific time. The main method that follows is Createexcelworkbook.

Note: You must know that when you run this page, you may need administrator permissions to write excel,csv files on the Websever server's file system. The easiest way to handle this problem is to run the page in your own folder and include your own configuration file. and add the following element to the configuration file <identity impersonate = "true" .... You still need the Access control List (ACL) of the physical folder to write permission, only such a running page identity has write permission, finally, you need to set up a COM connection to Excel 9.0 or Excel 10 type library, Vs.net will generate a fitting for you. I believe Microsoft has a connection on their office website that can be downloaded to Microsoft's initial assembly. (Probably not, my understanding is to be oriented to. NET assembly parts)

<identity impersonate= "true" Username= "Adminuser" password= "AdminPass"/>

Note that the following code block is useful for clearing objects in Excel.

Need all following the 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 object are also COM instances where we need

The ReleaseComObject method of marshal classes removes 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 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 = "" "+ 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 the 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;

}

}

Here is the original article

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

Generating native Excel spreadsheets from your Web server was not this difficult with asp.net. What can be difficult are making instances of Excel.exe go away so don ' t open up taskmgr and--123 instances of Excel . EXE still sitting in memory. I provide a solution that has two methods, "Createexcelworkbook", which runs a stored proceduire that returns a Datar Eader 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 "their" to Excel in browser, or download the XLS file. The second method, Generatecsvreport, does essentially the same thing but creates a CSV file that would, of course, also lo Ad into Excel. The CSV code correctly handles a common developer problem in so if you have a column that has leading zeroes, they are p Reserved.

Procedures and returning datareaders, and a removefiles utility method so cleans up any XLS or CSV file older than th E specified number of minutes. The key method presented below are the Createexcelworkbook method.

Note:you should be aware and you'll probably need to run this page under a 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 it own folder with it own web.config, and insert an <i Dentity impersonate = "true" ... elment. You could also need to enable ACLs permissions on the physical folder as "so" the identity the page runs under has WR ITE permissions. Finally, you'll need to set a COM reference to the Excel 9.0 or Excel typelibrary and let vs.net generate the Interop a Ssemblies 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 "cleanup" of the "does" to the Excel objects:

Need all following the 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 the all those Littlle objects "osheet", "oWB", "orng", etc. are all COM instances and we need to us E 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._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 = "" "+ 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 the 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;

}

}

-translation haste, mistake please understanding, welcome to guide, discuss---xiao Xu