Excel 만들기, 엑셀 닫기

 

 # 엑셀을 만들고, 엑셀을 정상적으로 종료시키는 소스임.

      : 엑셀 파일을 만든 후 엑셀 프로세스가 정상적으로 닫히지 않는 현상을 해결한 소스임.

      : 엑셀 파일을 정상적으로 만든 후 닫히는 것은 매우 잘 닫혔으나,

      : 엑셀의 각 셀 값을 넣거나 변환 하는 과정에서 엑셀관련 처리가 아닌 다른 사유로 예외 발생시

        프로세스에 Excel이 살아있는 현상에 대해서는 강제로 엑셀을 닫는 처리를 추가했다.

      : 엑셀을 만들때 주의 할 점은 한번에 접근하는 객체의 프로퍼티를 두단계 깊이 들어가면 안된다는 것.

        ex)

            바꾼 후

               xlWorkBooks = xlApp.Workbooks; 
               xlWorkBook = xlWorkBooks.Add(Type.Missing);

            바뀌기 전 ( 프로세스 안 닫힘! )

         xlWorkBook = xlApp.Workbooks.Add(Type.Missing);

 

       : 생성하고 닫는 순서가 정해져 있음.

         app > workbooks > workbook > sheets > sheet > irange

   ### 온갖 삽질을 통해서 완성된 소스를 담아둔다.

 

// 소스  ####################################################################

            string exceptionMessage = string.Empty; 
            int exProcessHandle = 0;

            Microsoft.Office.Interop.Excel.Application xlApp = null;
            Microsoft.Office.Interop.Excel.Workbooks xlWorkBooks = null;
            Microsoft.Office.Interop.Excel.Workbook xlWorkBook = null;
            Microsoft.Office.Interop.Excel.Sheets xlSheets = null;
            Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet = null;
            Microsoft.Office.Interop.Excel.Range IRange = null;

            try
            {
                xlApp = new Microsoft.Office.Interop.Excel.Application();
                xlApp.Visible = false;
                xlApp.DisplayAlerts = false;
                
                xlWorkBooks = xlApp.Workbooks;
                xlWorkBook = xlWorkBooks.Add(Type.Missing);
               
                xlSheets = xlWorkBook.Worksheets;
                xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlApp.ActiveSheet;
                //xlWorkSheet.Name = "시트명";  
                xlWorkSheet.Cells.Font.Name = "굴림체";
                xlWorkSheet.Cells.Font.Size = "9";
              
                // 페이지설정
                xlWorkSheet.PageSetup.PaperSize = XlPaperSize.xlPaperA4;
                xlWorkSheet.PageSetup.Orientation = XlPageOrientation.xlLandscape;
                xlWorkSheet.PageSetup.Zoom = 90;
                xlWorkSheet.PageSetup.LeftMargin = xlApp.CentimetersToPoints(0.4d);
                xlWorkSheet.PageSetup.TopMargin = xlApp.CentimetersToPoints(1.5d);
                xlWorkSheet.PageSetup.RightMargin = xlApp.CentimetersToPoints(0.4d);
                xlWorkSheet.PageSetup.BottomMargin = xlApp.CentimetersToPoints(0.7d);
                xlWorkSheet.PageSetup.HeaderMargin = xlApp.CentimetersToPoints(1.3d);
                xlWorkSheet.PageSetup.FooterMargin = xlApp.CentimetersToPoints(0.5d);
                
                //.... 데이타 처리 /* 생략 */ 
            }
            catch (Exception exc)
            {
                exceptionMessage = exc.Message;
            }
            finally
            {
                // 정상적으로 생성시 Excel 은 잘 Kill됨
                // 데이타를 넣다가 예외가 발생하면 안죽음!
                if( string.IsNullOrEmpty(exceptionMessage.Trim()) == false )
                {
                    if (xlWorkSheet != null) xlWorkSheet.Delete();
                    if (xlWorkBook != null) xlWorkBook.Close(null, null, null);
                    if (xlWorkBooks != null) xlWorkBooks.Close();
                    if (xlApp != null) {
                        xlApp.Quit();
                        // todo : Excel Kill
                        GetWindowThreadProcessId((IntPtr)xlApp.Hwnd, out exProcessHandle);
                        System.Diagnostics.Process[] process = System.Diagnostics.Process.GetProcessesByName("Excel");
                        foreach (System.Diagnostics.Process p in process)
                        {
                            if (p.Id == exProcessHandle)
                            {
                                try
                                {
                                    p.Kill();
                                }
                                catch { }
                            }
                        }
                    }
                }
 
                releaseComObject(IRange);
                releaseComObject(xlWorkSheet);
                releaseComObject(xlSheets);
                releaseComObject(xlWorkBook);
                releaseComObject(xlWorkBooks);
                releaseComObject(xlApp); 
              
                IRange = null;
                xlWorkSheet = null;
                xlSheets = null;
                xlWorkBook = null;
                xlWorkBooks = null;
                xlApp = null;

                GC.Collect();
                GC.WaitForPendingFinalizers();
                GC.Collect();
                GC.WaitForPendingFinalizers();
                GC.Collect();
                 
                if (string.IsNullOrEmpty(exceptionMessage.Trim()) == false)
                {
                    MessageBox.Show( exceptionMessage );
                }
            }

 

        /// <summary>
        /// 객체 COM 자원 반환
        /// </summary>
        /// <param name="obj"></param>
        private static void releaseComObject(object obj)
        {
            try
            {
                if (obj != null)
                {
                     Marshal.ReleaseComObject(obj); 
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                obj = null;
            }
        }