How do I perform File.ReadAllLines on a file that is also open in Excel?

Question

How do I read all lines of a text file that is also open in Excel into string[] without getting IO exception?

There is this question which could be a part of the answer, though I don't know how I could use what's in there: How do I open an already opened file with a .net StreamReader?

Answer 1

Your problem is Excel opens the file as read/write. File.ReadAllLines() cannot access the file when it is open for writing in another application. If you opened the csv in Excel as read only, you wouldn't encounter this exception.

This is because the implementation in .Net does not open the internal stream with appropriate permissions to access the file when another application has write permissions to it.

So the fix here is simple, write your own ReadAllLines() method that sets the appropriate permissions when initiating the underlying Stream.

Here's an idea that borrows heavily from what ReadAllLines() does on its own:

public string[] WriteSafeReadAllLines(String path)
{
    using (var csv = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
    using (var sr = new StreamReader(csv))
    {
        List<string> file = new List<string>();
        while (!sr.EndOfStream)
        {
            file.Add(sr.ReadLine());
        }

        return file.ToArray();
    }
}

The only difference between this and what ReadAllLines does is the FileShare permission is set to FileShare.ReadWrite, which allows the file to be opened even when it is open with Read/Write permissions in another application.

Now, you have to understand the issues that can arise from this as there can be complications since another application has write permissions to the file.

1. You are going to be reading the last saved version of the file, so if you have unsaved changes in Excel, this method will not read them
2. If you save the file in Excel while this method is in the middle of reading it you are going to probably get an exception depending on the circumstances. This is because the file is completely locked while it is saving, so if you try to read the file while it locked, it will throw an System.IO.IOException.
3. And if you save the file and manage to avoid an exception (extremely unlikely, but possible given specific timing), you are going to read the newly saved file, not the original.

---

To understand why you cannot read the file when it is open for writing by another application, you have to look at the actual implementation in .NET. (This is the implementation in .Net 4.5 so it may be slightly different if you are looking at a difference version of .Net).

This is what File.ReadAllLines() actually looks like:

public static string[] ReadAllLines(string path)
{
  if (path == null)
    throw new ArgumentNullException("path");
  if (path.Length == 0)
    throw new ArgumentException(Environment.GetResourceString("Argument_EmptyPath"));
  else
    return File.InternalReadAllLines(path, Encoding.UTF8);
}


private static string[] InternalReadAllLines(string path, Encoding encoding)
{
  List<string> list = new List<string>();
  using (StreamReader streamReader = new StreamReader(path, encoding))
  {
    string str;
    while ((str = streamReader.ReadLine()) != null)
      list.Add(str);
  }
  return list.ToArray();
}

And to peek at what StreamReader is doing internally:

internal StreamReader(string path, Encoding encoding, bool detectEncodingFromByteOrderMarks, int bufferSize, bool checkHost)
{
  if (path == null || encoding == null)
    throw new ArgumentNullException(path == null ? "path" : "encoding");
  if (path.Length == 0)
    throw new ArgumentException(Environment.GetResourceString("Argument_EmptyPath"));
  if (bufferSize <= 0)
    throw new ArgumentOutOfRangeException("bufferSize", Environment.GetResourceString("ArgumentOutOfRange_NeedPosNum"));
  this.Init((Stream) new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.Read, 4096, FileOptions.SequentialScan, Path.GetFileName(path), false, false, checkHost), encoding, detectEncodingFromByteOrderMarks, bufferSize, false);
}

So here we come to the reason why the exception is throw, when supplied with a path, StreamReader creates a FileStream that has the FileShare parameter set to Read. This means that it cannot share a file with another application with Read/Write access to the file. To override this behavior you need to give it a Stream with a different setting for FileShare, which is what I did in the solution I provided above.

Answer 2

You cannot open file for reading only when it is opened with a read restriction. Otherwise all methods including ReadAllLines will work without throwing permission exceptions.