Create dynamic Excel with C # In Asp.net

In asp.net, it is easy to create an Excel table and distribute it externally by the server. However, 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;

}

}

Below 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 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 essential tially 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 shoshould 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. 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 specified Al 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 '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;

}

}

-Translation in a hurry. Please forgive me for any mistakes. Thank you for your advice. --- Xiao Xu