narasimman g

narasimman g

  • 1.6k
  • 160
  • 352.1k

[WINFORM] Export Datagridview to excel prob.How to stop the EXCEL Process.

Dec 30 2010 2:19 AM

I'm creating an excel file!!! exporting gridview to excel!! gridview contains 1000+ rows so process taking 20 seconds
So am using application.doevents and showing "excel creation is in progress" Message in status bar (running progress bar using application.doevents) i have an option to stop the creation process!!!!
Problem is everytime i stop the Excel creation process!!!! There is an EXCEL.exe process running in taskmanager. When i shutdown !! Each excel process holds an excel book and askinggg do u want to save Book1,Book2? How to dispose?
Here is my code

if (myDataTable.Rows.Count != 0)

                {

                    if (MessageBox.Show("Message", "Confirm?", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) == DialogResult.Yes)

                    {

 

                        SaveFileDialog SaveFD1 = new SaveFileDialog();

                        string FileName = "";

                        SaveFD1.FileName = "SAMPLE";

                        SaveFD1.Title = "Save File As";

                        SaveFD1.Filter = "Excel (*.xls)|*.xls";

 

                        if (SaveFD1.ShowDialog() == DialogResult.OK)

                        {

                            CurrentProcess = "EXCEL";

 

                            FileName = SaveFD1.FileName;

                            toolStripStatusLabel1.Text = "Creating Excel File..";

 

                            Invoke(new MyDelegate(ShowProgressBar), true);

                            statusStrip2.Visible = true;

                            Application.DoEvents();

 

 

                            Excel.Application xlApp;

                            Excel.Workbook xlWorkBook;

                            Excel.Worksheet xlWorkSheet;

 

                            object misValue = System.Reflection.Missing.Value;

 

 

                            xlApp = new Excel.ApplicationClass();

                            xlWorkBook = xlApp.Workbooks.Add(misValue);

                            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

                            xlApp.Columns.ColumnWidth = 30;

                            xlWorkBook.SaveCopyAs("Sample.xls");

                            statusStrip2.Visible = true;

                            Application.DoEvents();

                            //For loop Couters

                            int i = 0;

                            int j = 0;

                            int k = 0;

                            string cell = "";

                            for (i = 0; i <= myDataTable.Rows.Count - 1; i++)

                            {

                                if (CurrentProcess != "EXCEL")

                                {

                                    releaseObject(xlWorkSheet);

                                    releaseObject(xlWorkBook);

                                    releaseObject(xlApp);

statusStrip2.Visible = false;

                                    return;

                                }

                                if (i % 50 == 0)

                                {

                                    Application.DoEvents();

                                }

                                k = -1;

                                for (j = 0; j <= myDataTable.Columns.Count - 1; j++)

                                {

                                    if (CurrentProcess != "EXCEL")

                                    {

                                        releaseObject(xlWorkSheet);

                                        releaseObject(xlWorkBook);

                                        releaseObject(xlApp);

                                        statusStrip2.Visible = false;

                                        return;

                                    }

                                        cell = MyDataTable.Rows[i][j].ToString();

 

                                    k++;

                                    xlWorkSheet.Cells[i + 1, k + 1] = cell;

                                }

                            }

                            object missing = System.Reflection.Missing.Value;

                            Cursor.Current = Cursors.Default;

 

                            xlWorkBook.SaveAs(FileName, Excel.XlFileFormat.xlWorkbookNormal, missing, missing, false, false, Excel.XlSaveAsAccessMode.xlShared, false, false, missing, missing, missing);

 

                            xlWorkBook.SaveCopyAs("Sa.xls");

                            xlWorkBook.Close(true, misValue, misValue);

                            xlApp.Quit();

 

                            releaseObject(xlWorkSheet);

                            releaseObject(xlWorkBook);

                            releaseObject(xlApp);

                            statusStrip2.Visible = false;

                            Application.DoEvents();

                            CurrentProcess = "";

                            MessageBox.Show("Excel file is created in below path \n" + FileName + "", "File created", MessageBoxButtons.OK, MessageBoxIcon.Information); 

                        }

                    }

                }

                else

                {

                    MessageBox.Show("No Records to create Excel book");

                }

            }

            catch (Exception ex)

            {

                CurrentProcess = "";

                statusStrip2.Visible = false;

                MessageBox.Show(ex.Message);

            }

        }

        //To Release the Object created for Excel

        private void releaseObject(object obj)

        {

            try

            {

                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);

                obj = null;

            }

            catch (Exception ex)

            {

                obj = null;

                MessageBox.Show("Exception Occured while releasing object " + ex.ToString());

            }

            finally

            {

                GC.Collect();

            }

        }

 
 


Answers (4)