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();
}
}