Hi. I'm able to create and save an excel file, no problems. But i cannot seem to close the instance of Excel. I've tried lots of websites, and the Microsoft site with no luck. Maybe someone here can help me with the following problems. Below is my code (basically).

 

1            Excel.Application objApp = null;
2            Excel._Workbook objBook = null;
3            Excel.Workbooks objBooks = null;
4            Excel.Sheets objSheets = null;
5            Excel._Worksheet oSheet = null;
6            Excel.Range objRange = null;
7    
8            try
9            {
10               objApp = new Excel.Application();
11               objBooks = objApp.Workbooks;
12               objBook = objBooks.Add(Missing.Value);
13               objSheets = objBook.Worksheets;
14               oSheet = (Excel._Worksheet)objSheets.get_Item(1);
15   
16               objApp.Visible = true;
17   
18               int row = 2;
19               int column = 1;
20   
21               //Column Titles
22               oSheet.Cells[1, column] = "Something";
23   
24               //Data
25               ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings["Something"];
26               string connectionString = settings.ConnectionString;
27               using (SqlConnection connection = new SqlConnection(connectionString))
28               {
29                   String queryString = "SELECT QUERY HERE";
30                   SqlCommand command = new SqlCommand(queryString, connection);
31                   connection.Open();
32                   SqlDataReader reader = command.ExecuteReader();
33                   try
34                   {
35                       while (reader.Read())
36                       {
37                           for (column = 1; column < reader.FieldCount; column++)
38                           {
39                               oSheet.Cells[row, column] = reader[column - 1].ToString();
40                           }
41                           row++;
42                       }
43                   }
44                   finally
45                   {
46                       reader.Close();
47                       connection.Close();
48                   }
49               }
50               
51               objRange = (Excel.Range)oSheet.Cells[1, 1];
52               objRange = objRange.EntireRow;
53               objRange.Font.Bold = true;
54   
55               //Keep Excel Hidden
56               oXL.Visible = false;
57               oXL.UserControl = false;
58   
59               objBook.SaveAs(strCurrentDir + strFile, Excel.XlFileFormat.xlWorkbookNormal, null, null, false, false,
60                           Excel.XlSaveAsAccessMode.xlShared, false, false, null, null, null);
61           }
62           catch (Exception theException)
63           {
64               String errorMessage;
65               errorMessage = "Error: ";
66               errorMessage = String.Concat(errorMessage, theException.Message);
67               errorMessage = String.Concat(errorMessage, " Line: ");
68               errorMessage = String.Concat(errorMessage, theException.Source);
69           }
70           finally
71           {
72               if (objRange != null)
73               {
74                   System.Runtime.InteropServices.Marshal.ReleaseComObject(objRange);
75                   objRange = null;
76               }
77               if (oSheet != null)
78               {
79                   System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet);
80                   oSheet = null;
81               }
82               if (objSheets != null)
83               {
84                   System.Runtime.InteropServices.Marshal.ReleaseComObject(objSheets);
85                   objSheets = null;
86               }
87               if (objBook !=null)
88               {
89                   objBook.Close(null, null, null);
90                   System.Runtime.InteropServices.Marshal.ReleaseComObject(objBook);
91                   objBook = null;
92               }
93               if (objBooks !=null)
94               {
95                   objBooks.Close();
96                   System.Runtime.InteropServices.Marshal.ReleaseComObject(objBooks);
97                   objBooks = null;
98               }
99               if (objApp != null)
100              {
101                  objApp.Quit();
102                  System.Runtime.InteropServices.Marshal.ReleaseComObject(objApp);
103                  objApp = null;
104              }            
105              GC.Collect(); // force final cleanup!/
106              GC.WaitForPendingFinalizers();
107              GC.Collect();
108              Response.Redirect("./Reports/" + strFile);
109          }

  If i remove everything except creating the objects and closing them, it works fine. oSheet.Cells will still allow it to close. Using the range won't let Excel close (Only declaring it or declaring and using it). Using sqldatareadere won't let Excel Close, Using ConnectionStringSettings settings won't let it close. In short, almost anything i do in between causes Excel to stay open in the processes. Any ideas?

Reeb_doog:

objApp.Visible = true;

 

Remove this.

Effectively setting Excel to be visible creates another reference from the UI that will hold it open until the user closes it. 

 My bad, that should be set to false, it was just like that when i was playing around with it to try and see if anything would work.

I've tried removing it as well with no luck.

Remember that Excel Automation on the server is not officially supported by
Microsoft.

--
Madhur

"Reeb_doog" wrote in message news:2339960@forums.asp.net...
> My bad, that should be set to false, it was just like that when i was
> playing around with it to try and see if anything would work.
>
> I've tried removing it as well with no luck.
>

This is small static function I have in my HelperClass. You can remove the static keyword if you don't want to put it in a class. 

using System.Diagnostics;
 // static method to kill all excel processes
        public static void KillExcel()
        {
            try
            {
                Process[] ps = Process.GetProcesses();
                foreach (Process p in ps)
                {
                    if (p.ProcessName.ToLower().Equals("excel"))
                    {
                        p.Kill();
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("ERROR " + ex.Message);
            }
        }

 

Madhur, how do you suggest i create the excel reports then?

 

Thanks Bullpit, i'll give this a shot. 

 From personal experience running excel on a web server to produce reports is a horrible idea.  It just doesn't work.  You WILL end up babysitting the server, killing leaked excel instances manually even if you have bullpit's code.  Depending on what the output requirements of the report are you have several options.  IMHO the best is to use the 'new' excel 2007 file format.  This gives you access to 98% of excel functionality in a file format you can actually write without excel.  But it requires users to either have excel 2007 or download the free plugin from microsoft for older versions.  Excel 97 and up have an xml file format that can produce pretty rich reports if you dont need to embed graphics or charts, this is probably your best option.  If you really need the 'full excel', you're probably going to have to resort to a 3rd-party library.  I've personally had a good experience with a company called syncfusion.  They make a component suite that's reasonably priced (I think we paid less than $1k).  Finally if all you need to do is give people something they can open in excel you can simply serve out either csv or html(with a single table) with excel headers and a 'xls' extension and excel will open it no problem.

Hi, I battled with a similar issue and ended up using Crystal reports to create the Excel output. Create a report using the wizard/gui, plug in your data and then you will be able to save a file based on the report in many different formats.

 Regards,

ASBO