Building dynamic Excel with C # 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.S