Interop Excel: Before Saving the Excel file delete the old file in C# .Net

Last Reply on Mar 14, 2014 11:41 PM By Mudassar

Posted on Mar 14, 2014 11:39 PM

My code as follows
i want to delete the old file and replace the new one. private void Preseaintake() { try { SqlCommand cmd = new SqlCommand(); cmd.Connection = con; cmd.CommandText = "Presea_Intakerpts '" + dtfromdate.Text.Trim() + "', '" + dttodate.Text.Trim() + "'"; SqlDataAdapter adp = new SqlDataAdapter(cmd); DataTable table = new DataTable(); adp.Fill(table); Microsoft.Office.Interop.Excel.Application xlapp = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook xlWorkBook; Microsoft.Office.Interop.Excel.Worksheet xlsheet; object misvalue = System.Reflection.Missing.Value; xlWorkBook = (Microsoft.Office.Interop.Excel.Workbook)xlapp.Workbooks.Add(1); xlsheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.ActiveSheet; for (int i = 0; i < table.Columns.Count; i++) { xlsheet.Cells[1, i + 1] = table.Columns[i].ColumnName; } for (int i = 0; i < table.Rows.Count; i++) { for (int j = 0; j < table.Columns.Count; j++) { xlsheet.Cells[i + 2, j + 1] = table.Rows[i][j].ToString().Trim(); } } xlWorkBook.SaveAs(Application.StartupPath + "\\Preseaintake\\newtest.xls", misvalue, misvalue, misvalue, misvalue, misvalue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misvalue, misvalue, misvalue, misvalue, misvalue); xlWorkBook.Close(true, misvalue, misvalue); xlsheet = null; xlapp = null; } catch (Exception ex) { MessageBox.Show(ex.ToString(), "Error", MessageBoxButtons.OK); return; } } private void sendmail() { try { MailMessage mis = new MailMessage(); SmtpClient smtpserver = new SmtpClient("smtp.gmail.com"); smtpserver.Credentials = new NetworkCredential("reply@gmail.com", "hdfdf"); smtpserver.Host = "smtp.gmail.com"; smtpserver.Port = 587; smtpserver.EnableSsl = true; mis.From = new MailAddress("reply@gmail.com", "PreseaIntakeDetails"); mis.IsBodyHtml = true; mis.To.Add("gdf@gmail.com"); mis.Subject = "PreseaIntake Report"; System.Net.Mail.Attachment attachment; attachment = new System.Net.Mail.Attachment(Application.StartupPath + "\\Preseaintake\\newtest.xls"); mis.Attachments.Add(attachment); smtpserver.Send(mis); mis.DeliveryNotificationOptions = DeliveryNotificationOptions.OnFailure; } catch (Exception ex) { MessageBox.Show(ex.ToString(), "Error", MessageBoxButtons.OK); return; } } i want to delete the old file and replace with new one. for that how can i do? in my above code what changes i have to made. please help me. regards, narasiman P.

 

Posted on Mar 14, 2014 11:41 PM

Before the line

xlWorkBook.SaveAs(Application.StartupPath + "\\Preseaintake\\newtest.xls", misvalue, misvalue, misvalue, misvalue, misvalue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misvalue, misvalue,
                         misvalue, misvalue, misvalue);

Add the following code

if (System.IO.File.Exists(Application.StartupPath + "\\Preseaintake\\newtest.xls"))
{
    File.Delete(Application.StartupPath + "\\Preseaintake\\newtest.xls");
}