Avoid "file in use by another process" IO exception

Question

The code that I have included below successfully writes to a CSV file. But if the CSV file that I am writing to happens to be open in Excel, I get a System.IO.Exception that indicates that "the file is being used by another process."

How can I change my code so that the program will continuing running and wait until the CSV is no longer open in Excel?

private void timer1_Tick(object sender, EventArgs e)
    {
        int actmonth, actyear, actsecond;
        System.DateTime fecha = System.DateTime.Now;
        actmonth = fecha.Month;
        actyear = fecha.Year;
        if (actmonth <= 9)
        {
            valorfechaact = System.Convert.ToString(actyear) + "00" + System.Convert.ToString(actmonth);
        }
        else
        {
            valorfechaact = System.Convert.ToString(actyear) + "0" + System.Convert.ToString(actmonth);
        }

        actsecond = fecha.Second;

        string label;
        label = label1.Text;
        string at = "@";
        string filename = valorfechaact + ".csv";

        string ruta3 = System.IO.Path.Combine(at, label, filename); 
        if (Directory.Exists(label1.Text))
        {
            StreamWriter wr = new StreamWriter(ruta3, true);
            wr.WriteLine("1asd" + actsecond);
            wr.Close();
            wr.Dispose();
        }
        else
        {
            System.Console.WriteLine("no se puede escribir en el archivo");
            timer1.Stop();
        }
    }

You cannot overwrite the file if its open within Excel as Excel will lock it. You could always write the file again with a unique name if you encounter the IO error. — Alex K., Aug 22 '17 at 12:08

score 1 · Answer 1 · answered Aug 22 '17 at 14:20

You could take a look at this question:

Checking if an Excel Workbook is open

One of the approaches that are discussed is to simply attempt to access the file. If that throws an exception, you can wait and try again.

If you really want to wait until the workbook is writable you can do that, e.g. by using a while loop (probably you'll want to add a time out, or if relevant alert the user that he/she needs to close the file in Excel). In code it could be something like:

int someLargeNumberOfIterations = 100000000;

while(FileIsLocked(filepath) && elapsedMs < timeoutMs) {
   Thread.SpinWait(someLargeNumberOfIterations);

   // Set elapsed
}

// Write the file

where FileIsLocked is a function you write based on the aforementioned post and timeoutMs is some appropriate timeout.

Lukas · Accepted Answer · 2017-08-22T14:52:38.960

You can write a Methode which try to open the File with a FileStream and return a boolean Flag

A possible Solution is

public static class FileInfoExtension
{
    public static bool IsLocked(this FileInfo file)
    {
        FileStream stream = null;
        try
        {
            stream = file.Open(FileMode.Open, FileAccess.Read, FileShare.None);
        }
        catch (IOException)
        {
            return true;
        }
        finally
        {
            stream?.Close();
        }
        return false;
    }
}

Then you can use it

    var fileInfo = new FileInfo(ruta3);
    if (!fileInfo.IsLocked())
    {
       // do code
    }

A very simple (and bad) Solution to wait is

    while (file.IsLocked())
    {
       Thread.Sleep(100);
    }

General is your Code unclear and difficult to read.

You have much redudant code and few variable are bad named. Maybe this Guidline can help you https://github.com/dennisdoomen/CSharpGuidelines

Maybe a little bit clearer solution is

    private void timer1_Tick(object sender, EventArgs e)
    {
        var directory = label1.Text;
        if (!Directory.Exists(directory))
        {
            Console.WriteLine("no se puede escribir en el archivo");
            timer1.Stop();
            return;
        }
        var now = DateTime.Now;
        _valorfechaact = now.Month <= 9 ? $"{now.Year}00{now.Month}" : $"{now.Year}0{now.Month}";
        var fullname = Path.Combine("@", directory, $"{_valorfechaact}.csv");
        var fileInfo = new FileInfo(fullname);
        if (fileInfo.IsLocked())
        {
            Console.WriteLine($"The File {fullname} is locked!");
            return;
        }
        using (var wr = new StreamWriter(fullname, true))
        {
            wr.WriteLine("1asd" + now.Second);
        }
    }

Avoid "file in use by another process" IO exception

2 Answers2