Click here to Skip to main content
14,128,868 members
Rate this:
 
Please Sign up or sign in to vote.
See more:
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)
{// some code to build mail body with foreach (DataRow row in table.Rows)--

 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();
       
                    // Create physical path to store the files                  
                    string Savepath = ConfigurationManager.AppSettings["MailPath"].ToString() + "\\";
                 
                    // check already this directory exists or not
                    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);
                    //System.Threading.Thread.Sleep(9000); ---------tried this, thread sleep some times works fine,but failing when huge number of records in the table
                }
               
            }
            catch (Exception Ex)
            {
               
            }
        }      
}


What I have tried:

public static Boolean IsFileLocked(FileInfo file)
       {
           FileStream stream = null;

           try
           {
               //Don't change FileAccess to ReadWrite,
               //because if a file is in readOnly, it fails.
               stream = file.Open
               (
                   FileMode.Open,
                   FileAccess.Read,
                   FileShare.None
               );
           }
           catch (IOException)
           {
               //the file is unavailable because it is:
               //still being written to
               //or being processed by another thread
               //or does not exist (has already been processed)
               return true;
           }
           finally
           {
               if (stream != null)
                   stream.Close();
           }

           //file is not locked
           return false;
       }



tried with
System.Threading.Thread.Sleep(9000)
but not working.
Posted
Updated 17-Dec-18 3:10am
Comments
F-ES Sitecore 17-Dec-18 7:27am
   
What line causes the error?
   
I'm not sure on this, but sometimes code runs through the inside the if condition (true,when file is not locked)and sometimes don't (false)
if (TheFile.Exists && (!IsFileLocked(TheFile)))
{
File.Delete(TheFile.FullName);


Not understanding in which process file is still being accessed/opened.

}
Afzaal Ahmad Zeeshan 17-Dec-18 7:37am
   
Most likely your file is currently open in either one of your own processes, this very process, or likely in background. Who knows?

Also, you are checking if the file exists, and then in the later sections you are "creating" the file and then closing the stream, and writing to it via the helper methods. What exactly is the requirement?
CHill60 17-Dec-18 8:04am
   
Please stop thinking that just sleeping a thread will solve anything. And if you insist on swallowing errors using
catch (Exception Ex)
{
}
then you will never find out what the real problems are.

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

As the error says, the file is either open in another process or in your own process where you might have been reading it and forgot to close the file before trying to open it again for a write operation.

As others have said, calling Sleep doesn't solve anything at all and catching Exceptions and not doing anything with them just hides the problems you're code is having.

Having said that, your code is checking a function called "IsFileLocked", but the problem with this function is that when it runs, the file may be available, but just milliseconds later, may not be.

Before building the Excel workbook, you check to see if the file is locked. OK, fine. But then your code takes, say, 10 seconds to build the workbook before trying to write it to the file. Well, you tested for the file being locked 10 seconds ago. The result of that test means nothing just milliseconds after you performed that test.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web02 | 2.8.190518.1 | Last Updated 17 Dec 2018
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100