Asp.net create an execel table

In asp.net, it is easy to create an Excel table and distribute it externally by the server. It is difficult to delete the embedded excel.exe process. Therefore, do not open the task manager to check whether the excel.exe process is still in the memory. Here I provide a solution, which provides two methods:

"CreateExcelWorkbook" (indicating creating an Excel Workbook) This method runs a stored procedure, returns a DataReader, generates an Excel Workbook Based on the DataReader, and saves it to the file system, create a "download" connection, so that you can import the Excel table to the browser and directly download it to the machine.

The second method: GenerateCSVReport is essentially to do the same thing, just to save the CSV format of the file. Still imported to Excel, the CSV code can solve the problem of a general piece in development: You have added multiple zeros in one column, and the CSV code can ensure zero and no null. (Note: multiple zero values in an Excel table cannot be saved)


The downloadable solution contains a valid class "SPGen" that can run the stored procedure and return DataReader. A method for removing files can be used to delete a specific time value. The main method shown below is CreateExcelWorkbook.


Note: You must know that when running this page, you may need the Administrator permission to write Excel and Csv files in the file system of the WebSever server. The simplest way to solve this problem is to run this page in your own folder and include your own configuration file. Add the following element <identity impersonate = "true"... to the configuration file ".... You still need the write permission for the access control list (ACL) of the physical folder. Only the identity of the page running like this has the write permission. Finally, you need to set a Com to connect to the Excel 9.0 or Excel 10 library,. NET will generate an assembly for you. I believe that Microsoft has a connection on their Office website and can download the initial accessories from Microsoft. (It may not be correct. I understand it as a. net-oriented accessory installation)


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


Note that the following code block is used to clear 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 required, because oSheet "," oWb ", 'orng", and so on are also COM instances. We need

The ReleaseComObject method of the Marshal class 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 'round...

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. deleettings ["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 );

// 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;

}

}