loads of useful information, examples and tutorials pertaining to web development utilizing asp.net, c#, vb, css, xhtml, javascript, sql, xml, ajax and everything else...

 



Advertise Here
C-Sharpener.com - Programming is Easy!  Learn Asp.Net & C# in just days, Guaranteed!

Destroy those pesky orphaned Excel processes in .Net programs

by naspinski 5/2/2008 9:23:00 AM

If you have ever worked with Excel in the .Net environment, you likely have run across the occasional orphan and they can be a pain to clean up

Recently I was doing just this, and my Task Manager was filling up with orphans fast.  I was calling for the Excel Process to close, but that wasn't working most of the time.  So I came up with a more elaborate way to make them disappear.  Basically I found out that in order to close the application, you have to close the workbooks, and in order to close the workbooks, you have to close each workbook individually, and to close those, you have to close each worksheet in those workbooks individually.  I also included COM management, so we are hitting this with multiple attacks to make sure they stay dead!  So the super-overkill-do-everything process is:

 

  1. Collect each worksheet individually
  2. Collect each workbook individually
  3. Delete the worksheets
  4. Release the worksheets
  5. Null the worksheets
  6. Delete the workbooks
  7. Release the workbooks
  8. Null the workbooks
  9. Close the workbooks collection
  10. Quit the application
  11. Release the application
  12. Null the application
  13. Collect garbage

 

Here's how

After your work is done, you are left with a Microsoft.Office.Interop.Excel.Application named 'exc', call killExcel(exc):

protected void killExcel(Microsoft.Office.Interop.Excel.Application exc)
{
    try
    {
        List<Microsoft.Office.Interop.Excel.Workbook> wbs = new List<Microsoft.Office.Interop.Excel.Workbook>();
        List<Microsoft.Office.Interop.Excel.Worksheet> wss = new List<Microsoft.Office.Interop.Excel.Worksheet>();
        foreach (Microsoft.Office.Interop.Excel.Workbook wb in exc.Workbooks)
        {
            foreach (Microsoft.Office.Interop.Excel.Worksheet ws in wb.Worksheets)
                wss.Add(ws); // collect worksheets
            wbs.Add(wb); // collect workbooks
        }
        for (int i = 0; i < wss.Count; i++)
        {
            wss[i].Delete();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(wss[i]); // release it
            wss[i] = null; // null it
        }
        for (int i = 0; i < wbs.Count; i++)
        {
            wbs[i].Close(null, null, null);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(wbs[i]); // release it
            wbs[i] = null; // null it
        }
        exc.Workbooks.Close(); // so you can close this
        exc.Quit(); // so you can quit this
        System.Runtime.InteropServices.Marshal.ReleaseComObject(exc); // release it
        exc = null;
        GC.Collect(); // this sets up the finalizers
        GC.WaitForPendingFinalizers();
        GC.Collect(); //apparently this kills it
        GC.WaitForPendingFinalizers();
    }
    catch (Exception ex)
    {
        // deal with it fool!
    }
}

Yes this is overkill, but I think I covered all possible ways to kill you processes, so they should be more dead than Elvis; no more orphans - yay for iteration!

 

Some useful links:

http://www.thescarms.com/dotnet/ExcelObject.aspx 

http://www.devcity.net/Articles/239/3/article.aspx 

http://krgreenlee.blogspot.com/...ting-excel_10.html 

Tags: ,

c# | excel

Exporting a Gridview to Excel

by naspinski 3/14/2008 12:00:00 AM

Matt Berseth supplies an awesome way to export to excel with pretty much no hassle whatsoever

Just add in his class and call it withing as event like this 

GridViewExportUtil.Export("save_as_this.xls", this.GridViewName);
And that is it, nothing else is necessary. And the code is very simple and easy to follow. Here is the link to Matt's site with more details on it.


Converting an Excel Spreadsheet to a DataSet, DataTable and Multi-Dimensional Array

by naspinski 2/10/2008 4:52:00 AM

A way to easily manipulate data from an excel spreadsheet programmatically

I work in an office environment which is dominated by Microsoft Excel spreadsheets, and so often, people want to use them in programs.  I am a SQL junky myself, but Excel is appropriate in a lot of cases, but integrating them into programs can be a pain.  More specifically, getting the data from the Excel worksheet to a form where it is workable can be a pain. 

 

With this example, you can see how to take in an Excel Spreadsheet to get the data into commonly manipulatable data types in c#; then you can use/change it however you want to.  This demo shows first how to put the data into a dataset, then datatable (almost the same thing), then takes the same data in puts it into a multi-dimensional array (I prefer to work it arrays).  Since this is a linear progression, the demo only outputs the information from the array into an html table to show that it worked. 

 

The demo uses the very first row as the column names (as it is used in datatable) and assumes that the Spreadsheet is in default format with Sheet1 as it's first sheet.  The demo only processes Sheet1.  This can be easily customized if you want to process more. 

 

With this, you can take/change/display the excel data however you want to using asp.net controls like ListView or GridView or just simple c#.  The code is pretty well commented, so you should be able to pull the parts that you may need.  *NOTE: you need full trust enabled for this to work properly.