Connecting Tech Pros Worldwide Forums | Help | Site Map
bytes > topic > .net > answers

Fresh Eyes Needed: Help me find the reason why Excel process is not killed properly..

Newbie
  
Join Date: Jul 2008
Posts: 1
#1: Jul 24 '08
Hello,
First of all, Hi, and sorry for the lengthy explanation. :) Take a deep breath.

I'm trying to create a form which displays data in an html table, and an Export to Excel button which (amazingly) exports the displayed data to Excel (with all the formatting). I got this to work already, locally on my dev PC.
BTW, I am using another Excel file as a template.

However, I have 2 major problems.
1.) Firstly, the Excel process is not being terminated properly (note that I thought I understood Excel already, since I got another similar page to terminate Excel properly!!!) with the code below. Please give me your suggestions, or if you can, locate the specific problem.

2.) Secondly, when deploying/publishing this web application and accessing it remotely, an error occurs... more specifically,
"Server Error in '/' Application.
Runtime Error
Description: An application error occurred on the server. The current
custom error settings for this application prevent the details of the
application error from being viewed remotely (for security reasons). It
could, however, be viewed by browsers running on the local server
machine."
Actually, I managed to solve this problem, I just dunno how I did it. So this is not so much a problem for now, as long as its working. But I would like to know if any of you knows the true solution, I would appreciate knowing it.
I do understand (kinda) why this problem occurs. It's because of the web application. When I click the Export to Excel button, it uses the Excel Object on the Web Server, inserts data to the worksheet, and saves the file on the Web Server (for reference purposes by the company, of all generated files) and then sends (user download) the file to the user requesting for the export.


Expand|Select|Wrap|Line Numbers
  1.  
  2. protected void btnExport_Click(object sender, EventArgs e)
  3. {
  4. DataTable dt = (DataTable)Session["dtBEV"];
  5. string[] dayClass = (string[])Session["holidays"];
  6. bool showOTbefore10 = chkOTbefore10.Checked;
  7. bool showOTpast10 = chkOTpast10.Checked;
  8. bool showLateEarly = chkLateEarly.Checked;
  9. bool showSubsti = chkSubsti.Checked;
  10. bool showLeaves = chkLeaves.Checked;
  11. bool showSL = chkSL.Checked;
  12. bool showAWOL = chkAWOL.Checked;
  13. bool showHoliday = chkHoliday.Checked;
  14. int i, j;
  15. int emps = dt.Rows.Count;
  16. int month = int.Parse(DropDownListMonth.SelectedValue);
  17. int year = int.Parse(DropDownListYear.SelectedValue);
  18. int days = DateTime.DaysInMonth(year, month);
  19. string sTemp;
  20. string filename;
  21. string sCol;
  22. bool isHol = false;
  23.  
  24. string[,] header = new string[2, days];
  25. string[,] content = new string[emps, days + 2];
  26.  
  27. Excel.Application xlApp = null;
  28. Excel.Workbook xlWB = null;
  29. Excel.Worksheet xlWS = null;
  30. Excel.Range cell = null;
  31. Excel.Style style = null;
  32.  
  33. // Create Header
  34. for (i = 1; i <= days; i++)
  35. {
  36. header[0, i - 1] = i.ToString();
  37. }
  38. for (i = 0; i < days; i++)
  39. {
  40. header[1, i] = GetDayOfWeek(year, month, i + 1);
  41. }
  42.  
  43. // Create Content
  44. for (j = 0; j < emps; j++)
  45. {
  46. content[j, 0] = Convert.ToString(j + 1);
  47. content[j, 1] = dt.Rows[j]["name"].ToString();
  48. for (i = 0; i < days; i++)
  49. {
  50. content[j, i + 2] = dt.Rows[j]["day" + (i + 1)].ToString();
  51. }
  52. }
  53.  
  54. try
  55. {
  56. GC.Collect();
  57. xlApp = new Excel.Application();
  58. xlWB = xlApp.Workbooks.Open(Server.MapPath(".") + "\\..\\Templates\\DTR - Monthly Summary Birds Eye View.xls",
  59. 0, false, 5, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, true, false, Missing.Value, false);
  60. xlWS = (Excel.Worksheet)xlWB.ActiveSheet;
  61.  
  62. // create styles
  63. style = xlWB.Styles.Add("borderStyle", Type.Missing);
  64. style.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
  65. style.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
  66. style.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
  67. style.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
  68. style.Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThin;
  69. style.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThin;
  70. style.Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThin;
  71. style.Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThin;
  72. style.Font.Name = "Arial";
  73. style.Font.Size = "10";
  74.  
  75. // Write Headers 
  76. cell = xlWS.get_Range("C2", Type.Missing);
  77. cell.Value2 = year.ToString() + " " + new DateTime(year, month, 1).ToString("MMMM");
  78. System.Runtime.InteropServices.Marshal.ReleaseComObject(cell);
  79. cell = xlWS.get_Range("C3", Type.Missing);
  80. cell = cell.get_Resize(2, days);
  81. cell.Value2 = header;
  82.  
  83. // Search for Weekends and Holidays
  84. for (i = 1; i <= days; i++)
  85. {
  86. isHol = false;
  87. sTemp = GetDayOfWeek(year, month, i);
  88.  
  89. if (dayClass[i - 1] != null)
  90. {
  91. isHol = true;
  92. }
  93.  
  94. if (sTemp == "Sa" || sTemp == "Su" || isHol)
  95. {
  96. System.Runtime.InteropServices.Marshal.ReleaseComObject(cell);
  97. sCol = ColumnString(1 + i);
  98. cell = xlWS.get_Range(sCol + "3:" + sCol + "4", Type.Missing);
  99. cell.Font.ColorIndex = DayColor.AWOL;
  100. }
  101. }
  102.  
  103. // Write Content
  104. if (emps > 0)
  105. {
  106. System.Runtime.InteropServices.Marshal.ReleaseComObject(cell);
  107. cell = xlWS.get_Range("A5", Type.Missing);
  108. cell = cell.get_Resize(emps, days + 2);
  109. cell.Value2 = content;
  110.  
  111. System.Runtime.InteropServices.Marshal.ReleaseComObject(cell);
  112. cell = xlWS.get_Range("A5:AG" + (4 + emps), Type.Missing);
  113. cell.Style = "borderStyle";
  114.  
  115. System.Runtime.InteropServices.Marshal.ReleaseComObject(cell);
  116. cell = xlWS.get_Range("A5:A" + (4 + emps), Type.Missing);
  117. cell.HorizontalAlignment = Excel.Constants.xlRight;
  118.  
  119. System.Runtime.InteropServices.Marshal.ReleaseComObject(cell);
  120. cell = xlWS.get_Range("C5:AG" + (5 + emps), Type.Missing);
  121.  
  122. FindCells(cell, "0", (int)DayColor.Normal, (int)DayColor.Normal); // Search for Normal
  123. FindCells(cell, "-9", (int)DayColor.NotHired, (int)DayColor.NotHired); // Search for Not Hired
  124.  
  125. if (showHoliday) // Search for Holidays
  126. {
  127. FindCells(cell, "-8", (int)DayColor.Holiday, (int)DayColor.Holiday);
  128. }
  129. if (showAWOL) // Search for AWOL
  130. {
  131. FindCells(cell, "-7", (int)DayColor.AWOL, (int)DayColor.AWOL);
  132. }
  133. if (showSL) // Search for SL
  134. {
  135. FindCells(cell, "-6", (int)DayColor.SL, (int)DayColor.SL);
  136. }
  137. if (showLeaves) // Search for Leaves
  138. {
  139. FindCells(cell, "-5", (int)DayColor.Leaves, (int)DayColor.Leaves);
  140. }
  141. if (showSubsti) // Search for Substi
  142. {
  143. FindCells(cell, "-4", (int)DayColor.Substi, (int)DayColor.Substi);
  144. }
  145. if (showLateEarly) // Search for Late Early
  146. {
  147. FindCells(cell, "-3", (int)DayColor.LateEarly, (int)DayColor.LateEarly);
  148. }
  149. if (showOTpast10) // Search for OT Past 10
  150. {
  151. FindCells(cell, "-2", (int)DayColor.OTpast10, (int)DayColor.OTpast10);
  152. }
  153. if (showOTbefore10) // Search for OT Before 10
  154. {
  155. FindCells(cell, "-1", (int)DayColor.OTbefore10, (int)DayColor.OTbefore10);
  156. }
  157.  
  158. System.Runtime.InteropServices.Marshal.ReleaseComObject(cell);
  159. cell = xlWS.get_Range("C5:AG" + (5 + emps), Type.Missing);
  160. cell.Value2 = "";
  161. }
  162.  
  163. // Write Footer
  164. System.Runtime.InteropServices.Marshal.ReleaseComObject(cell);
  165. cell = xlWS.get_Range("C" + (7 + emps), Type.Missing);
  166. cell.Font.Bold = true;
  167. cell.Value2 = "Legend:";
  168. System.Runtime.InteropServices.Marshal.ReleaseComObject(cell);
  169. cell = xlWS.get_Range("C" + (8 + emps) + ":D" + (15 + emps), Type.Missing);
  170. cell.Style = "borderStyle";
  171.  
  172. if (showOTbefore10)
  173. {
  174. System.Runtime.InteropServices.Marshal.ReleaseComObject(cell);
  175. cell = xlWS.get_Range("C" + (8 + emps), Type.Missing);
  176. cell.HorizontalAlignment = Excel.Constants.xlCenter;
  177. cell.Value2 = "X";
  178. }
  179. System.Runtime.InteropServices.Marshal.ReleaseComObject(cell);
  180. cell = xlWS.get_Range("D" + (8 + emps), Type.Missing);
  181. cell.Interior.ColorIndex = 33;
  182. System.Runtime.InteropServices.Marshal.ReleaseComObject(cell);
  183. cell = xlWS.get_Range("E" + (8 + emps), Type.Missing);
  184. cell.Value2 = "Overtime (end before 10pm)";
  185.  
  186. if (showOTpast10)
  187. {
  188. System.Runtime.InteropServices.Marshal.ReleaseComObject(cell);
  189. cell = xlWS.get_Range("C" + (9 + emps), Type.Missing);
  190. cell.HorizontalAlignment = Excel.Constants.xlCenter;
  191. cell.Value2 = "X";
  192. }
  193. System.Runtime.InteropServices.Marshal.ReleaseComObject(cell);
  194. cell = xlWS.get_Range("D" + (9 + emps), Type.Missing);
  195. cell.Interior.ColorIndex = 5;
  196. System.Runtime.InteropServices.Marshal.ReleaseComObject(cell);
  197. cell = xlWS.get_Range("E" + (9 + emps), Type.Missing);
  198. cell.Value2 = "Overtime (end past 10pm)";
  199.  
  200. if (showLateEarly)
  201. {
  202. System.Runtime.InteropServices.Marshal.ReleaseComObject(cell);
  203. cell = xlWS.get_Range("C" + (10 + emps), Type.Missing);
  204. cell.HorizontalAlignment = Excel.Constants.xlCenter;
  205. cell.Value2 = "X";
  206. }
  207. System.Runtime.InteropServices.Marshal.ReleaseComObject(cell);
  208. cell = xlWS.get_Range("D" + (10 + emps), Type.Missing);
  209. cell.Interior.ColorIndex = 38;
  210. System.Runtime.InteropServices.Marshal.ReleaseComObject(cell);
  211. cell = xlWS.get_Range("E" + (10 + emps), Type.Missing);
  212. cell.Value2 = "Late time-in / Early time-out";
  213.  
  214. if (showSubsti)
  215. {
  216. System.Runtime.InteropServices.Marshal.ReleaseComObject(cell);
  217. cell = xlWS.get_Range("C" + (11 + emps), Type.Missing);
  218. cell.HorizontalAlignment = Excel.Constants.xlCenter;
  219. cell.Value2 = "X";
  220. }
  221. System.Runtime.InteropServices.Marshal.ReleaseComObject(cell);
  222. cell = xlWS.get_Range("D" + (11 + emps), Type.Missing);
  223. cell.Interior.ColorIndex = 44;
  224. System.Runtime.InteropServices.Marshal.ReleaseComObject(cell);
  225. cell = xlWS.get_Range("E" + (11 + emps), Type.Missing);
  226. cell.Value2 = "Substi";
  227.  
  228. if (showLeaves)
  229. {
  230. System.Runtime.InteropServices.Marshal.ReleaseComObject(cell);
  231. cell = xlWS.get_Range("C" + (12 + emps), Type.Missing);
  232. cell.HorizontalAlignment = Excel.Constants.xlCenter;
  233. cell.Value2 = "X";
  234. }
  235. System.Runtime.InteropServices.Marshal.ReleaseComObject(cell);
  236. cell = xlWS.get_Range("D" + (12 + emps), Type.Missing);
  237. cell.Interior.ColorIndex = 10;
  238. System.Runtime.InteropServices.Marshal.ReleaseComObject(cell);
  239. cell = xlWS.get_Range("E" + (12 + emps), Type.Missing);
  240. cell.Value2 = "VL, MVL, BL, PL";
  241.  
  242. if (showSL)
  243. {
  244. System.Runtime.InteropServices.Marshal.ReleaseComObject(cell);
  245. cell = xlWS.get_Range("C" + (13 + emps), Type.Missing);
  246. cell.HorizontalAlignment = Excel.Constants.xlCenter;
  247. cell.Value2 = "X";
  248. }
  249. System.Runtime.InteropServices.Marshal.ReleaseComObject(cell);
  250. cell = xlWS.get_Range("D" + (13 + emps), Type.Missing);
  251. cell.Interior.ColorIndex = 13;
  252. System.Runtime.InteropServices.Marshal.ReleaseComObject(cell);
  253. cell = xlWS.get_Range("E" + (13 + emps), Type.Missing);
  254. cell.Value2 = "SL";
  255.  
  256. if (showAWOL)
  257. {
  258. System.Runtime.InteropServices.Marshal.ReleaseComObject(cell);
  259. cell = xlWS.get_Range("C" + (14 + emps), Type.Missing);
  260. cell.HorizontalAlignment = Excel.Constants.xlCenter;
  261. cell.Value2 = "X";
  262. }
  263. System.Runtime.InteropServices.Marshal.ReleaseComObject(cell);
  264. cell = xlWS.get_Range("D" + (14 + emps), Type.Missing);
  265. cell.Interior.ColorIndex = 3;
  266. System.Runtime.InteropServices.Marshal.ReleaseComObject(cell);
  267. cell = xlWS.get_Range("E" + (14 + emps), Type.Missing);
  268. cell.Value2 = "AWOL";
  269.  
  270. if (showHoliday)
  271. {
  272. System.Runtime.InteropServices.Marshal.ReleaseComObject(cell);
  273. cell = xlWS.get_Range("C" + (15 + emps), Type.Missing);
  274. cell.HorizontalAlignment = Excel.Constants.xlCenter;
  275. cell.Value2 = "X";
  276. }
  277. System.Runtime.InteropServices.Marshal.ReleaseComObject(cell);
  278. cell = xlWS.get_Range("D" + (15 + emps), Type.Missing);
  279. cell.Interior.ColorIndex = 6;
  280. System.Runtime.InteropServices.Marshal.ReleaseComObject(cell);
  281. cell = xlWS.get_Range("E" + (15 + emps), Type.Missing);
  282. cell.Value2 = "Holiday";
  283.  
  284. // Close and Save file
  285. filename = Request.PhysicalApplicationPath + "Templates\\" + Session["EmployeeID"].ToString() +
  286. " - BEV Summary " + DateTime.Now.ToString("yyyy-MM-dd") + ".xls";
  287. xlWB.Close(true, filename, false);
  288. xlApp.Quit();
  289. }
  290. finally
  291. {
  292. System.Runtime.InteropServices.Marshal.ReleaseComObject(style);
  293. System.Runtime.InteropServices.Marshal.ReleaseComObject(cell);
  294. System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWS);
  295. System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWB);
  296. System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
  297. style = null;
  298. cell = null;
  299. xlWS = null;
  300. xlWB = null;
  301. xlApp = null;
  302. GC.Collect();
  303. }
  304.  
  305. DownloadFile(filename, true);
  306. }
  307.  
As you can see, I think I've managed to release every Excel Object I could find! Believe me, I tried to add the codes bit by bit to find out the problem. But I just dont understand anymore..
Any help would be appreciated! Thanks!

PS:
And oh, for more information, I am using C#, Visuall Studio 2005, ASP.Net and Excel 2000 (which is Office 9.0, I think).

DrBunchman's Avatar
Moderator
  
Join Date: Jan 2008
Location: Winchester, UK
Posts: 930
#2: Jul 24 '08

re: Fresh Eyes Needed: Help me find the reason why Excel process is not killed properly..

Hi treiz,

Welcome to Bytes.com! I hope you find the site useful.

You've posted your question in the ASP Forum which is for Classic ASP only - I've moved it for you but in future please post all ASP.NET questions in the .NET Forum.

Also, please could you wrap your code in CODE tags using the # button in the edit window - it makes it much easier for everybody to read your posts.

With regard to your second problem: the default customErrors mode is RemoteOnly which means that you won't see the true cause of any errors if you are not browsing the site from a local machine. Go to your web.config file and change the customErrors mode as below.
Expand|Select|Wrap|Line Numbers
  1.  
  2. <system.web>
  3.          <customErrors mode="Off" />
  4. </system.web>
  5.  
Then you will be able to view the real cause of the error on remote machines.

Thanks and good luck finding a solution to your other problem.

Dr B
Reply

« previous question | next question »

Similar .NET Framework bytes

/bytes/development

/bytes/it

Forums
Visit our community forums for general discussions and latest on Bytes

Our staff and community areas have moved to bytes.com/forums/

/bytes/about

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 226,471 network members.

dev questions:
algorithms | asp | asp.net | c/c++ | coldfusion | c# | flash | html/css | java | javascript |
mobile dev | .net | perl | php | python | ruby | software dev | vb.net | visual basic | xml
it questions:
access | apache | careers | db2 | iis | macosx | mysql | networking | oracle | postgresql | sql server | unix | windows

About Bytes | Copyright 1995-2009 BYTES. All rights Reserved