Use C# to create dynamic Excel in Asp.net (foreign language translation)

Author：Eve Cole Update Time：2009-06-30 16:40:25

It is easy to create a local Excel table in Asp.net and spread it out from the server, but it is difficult to delete the embedded Excel.exe process. So don't open the task manager to see if things related to the Excel.exe process are still in the memory. I provide a solution here, which provides two methods:
"CreateExcelWorkbook" (describes how to create an Excel workbook) This method runs a stored procedure, returns a DataReader and generates an Excel workbook based on the DataReader, saves it to the file system, and creates a "download" connection so that the user can download Excel The table can be imported into the browser or downloaded directly to the machine.

The second method: GenerateCSVReport essentially does the same thing, except that it saves the file in CSV format. Still imported into Excel, the CSV code can solve a common problem in development: you have multiple zeros poured into a column, and the CSV code can ensure that zeros are not empty. (Explanation: This is the problem that multiple zero values cannot be saved in the Excel table)

The downloadable solution contains a valid class "SPGen" that runs a stored procedure and returns a DataReader, and a remove method that deletes files older than a specific time value. The main method that appears below is CreateExcelWorkbook

Note: You must be aware that when running this page, you may need administrator rights to write Excel and Csv files in the WebSever server's file system. The easiest way to deal with this problem is to run the page in its own folder and include its own configuration file. And add the following element <identity impersonate ="true" ... in the configuration file. You still need write access to the access control list (ACL) of the physical folder, only so that the identity you run the page has write access. Finally, you need to set up a COM connection to the Excel 9.0 or Excel 10 type library, VS.NET An assembly will be generated for you. I believe Microsoft has a link on their Office website to download Microsoft's initial assembly. (It may not be accurate, my understanding is that it is an assembly for .net)

Pay special attention to the following code block, which clears 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 oSheet", "oWb", 'oRng", and other objects are also instances of COM. 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 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,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;
}
}

The following is the original articleCreate

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 procedure 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 essentially 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 should 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 VS.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.

Note especially the code block that does the "cleanup" of the Excel objects:

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

http://macroxu-1982.cnblogs.com/archive/2006/05/31/413879.html