I am creating a console application which triggers the mails with some data.
i'm getting the below error in method 'WriteToExcel'.
"The process cannot access the file xyzfilename.xls because it is being used by another process".
please figure out the reason for the error and suggest the soln.
class Program
{
static void Main(string[] args)
{dtResultInvoices = objBL.InvoicesDetailsBL();
Mailcontent(dtResultInvoices);
}
}
public static void Mailcontent(DataTable dtResultInvoices)
{
Helper.WriteToExcel(dtdistinctinvoices); ---- table dtdistinctinvoices created from dtResultInvoices with some filter operations.
Helper.Sendmail(strmailfrom, strmailto, strmailsubject, Mailcontent.ToString(), strmailcc, null, Helper.attachments);
}
class Helper
{
public static void Sendmail(string mailFrom, string toMail, string mailSubject, string mailBody, string mailCC, string mailBcc, List<string> MailAttach)
{
}
public static void WriteToExcel(DataTable Dtattach)
{
DataTable dtCopy = new DataTable();
try
{
StringBuilder builder = new StringBuilder();
if (Dtattach != null && Dtattach.Rows.Count > 0)
{
dtCopy = Dtattach.Copy();
string Savepath = ConfigurationManager.AppSettings["MailPath"].ToString() + "\\";
if (!Directory.Exists(Savepath))
{
Directory.CreateDirectory(Savepath);
}
Savepath = Savepath + "xyzfilename.xls";
FileInfo TheFile = new FileInfo(Savepath);
if (TheFile.Exists && (!IsFileLocked(TheFile)))
{
File.Delete(TheFile.FullName);
}
using (ExcelPackage objExcelPackage = new ExcelPackage())
{
ExcelWorksheet objWorksheet = objExcelPackage.Workbook.Worksheets.Add("AllocatedDetails");
objWorksheet.Cells["A1"].LoadFromDataTable(dtCopy, true);
objWorksheet.Protection.AllowDeleteRows = true;
objWorksheet.Protection.AllowAutoFilter = true;
objWorksheet.Protection.AllowFormatCells = true;
objWorksheet.Protection.AllowSort = true; objWorksheet.Cells.AutoFitColumns();
objWorksheet.Cells["A1:M1"].AutoFilter = true;
using (ExcelRange objRange = objWorksheet.Cells["A1:M1"])
{
objRange.Style.Font.Bold = true;
objRange.Style.Font.Color.SetColor(Color.White);
objRange.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
objRange.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
objRange.Style.Fill.PatternType = ExcelFillStyle.Solid;
objRange.Style.Fill.BackgroundColor.SetColor(Color.ForestGreen);
objRange.Style.Locked = true;
}
FileStream objFileStrm = File.Create(Savepath);
objFileStrm.Close(); File.WriteAllBytes(Savepath, objExcelPackage.GetAsByteArray());
objFileStrm.Dispose();
}
attachments.Clear();
attachments.Add(Savepath);
}
}
catch (Exception Ex)
{
}
}
}
What I have tried:
public static Boolean IsFileLocked(FileInfo file)
{
FileStream stream = null;
try
{
stream = file.Open
(
FileMode.Open,
FileAccess.Read,
FileShare.None
);
}
catch (IOException)
{
return true;
}
finally
{
if (stream != null)
stream.Close();
}
return false;
}
tried with
System.Threading.Thread.Sleep(9000)
but not working.