Programmatically get user that is locking an excel workbook

Question

I am using C# framework 4.5, netoffice 1.6 and sharpdevelop 4.4.1 to manipulate an excel workbook, located on a network share, from within Outlook.

At some point I need to change the file access of the workbook object (ewb) to readwrite like so:

ewb.ChangeFileAccess(Excel.Enums.XlFileAccess.xlReadWrite, System.Reflection.Missing.Value, true);

Before I change the file access, I check if the file is locked on the server. If the file is locked, I will notify the user to retry the action at a later point.

Now, I want to include the username that is locking the excel file in the notification. I have searched msdn, netoffice forum, etcetera... and have not found a solution. I know that, if you open the excel file readwrite, it will store the user's name in the xlsx file. How can I access that particular piece of information through c#?

EDIT: I ended up doing this:

public string GetExcelFileOwner(string path, NetOffice.ExcelApi.Enums.XlFileFormat ffmt) {
        string tempmark = "~$";
        if(ffmt==NetOffice.ExcelApi.Enums.XlFileFormat.xlExcel8) {
            tempmark = "";
        }
        string uspath = Path.Combine(Path.GetDirectoryName(path), tempmark + Path.GetFileName(path));
        if (!File.Exists(uspath)) return "";
        var sharing = FileShare.ReadWrite | FileShare.Delete;
        using (var fs = new FileStream(uspath, FileMode.Open, FileAccess.Read, sharing))
        using (var br = new BinaryReader(fs, Encoding.Default)) {
            if(ffmt==NetOffice.ExcelApi.Enums.XlFileFormat.xlExcel8) {
                byte[] ByteBuffer = new byte[500];
                br.BaseStream.Seek(150, SeekOrigin.Begin);
                br.Read(ByteBuffer, 0, 500);
                return matchRegex(System.Text.Encoding.UTF8.GetString(ByteBuffer), @"(?=\w\w\w)([\w, ]+)").Trim();
            }
            else {
                return br.ReadString();
            }
        }
    }

    private static string matchRegex(string txt, string rgx) {
        Regex r;
        Match m;
        try {
            r = new Regex(rgx, RegexOptions.IgnoreCase);
            m = r.Match(txt);
            if (m.Success) {
                return m.Groups[1].Value.ToString();
            }
            else {
                return "";
            }
        }
        catch {
            return "";
        }
    }

We are using excel 2003 and excel 2007+ file format (.xls and .xlsx). For .xls I had to look in the .xls file itself. For .xlsx, the locking user is stored in the ~$ temp file. I know, for the .xls file, it is dirty code, but I have no clue of how the .xls file format is structured. Therefore, i just read a bunch of bytes which includes the ascii username and do a regex to extract that username.

Answer 1

it will store the user's name in the xlsx file

No, not the in .xlsx file. Excel creates another file to store the user name. It has the Hidden file attribute turned on so you cannot normally see it with Explorer.

It normally has the same name as the original file, but prefixed with ~$. So for a file named test.xlsx you'll get a file named ~$test.xlsx. It is a binary file and contains the user name both encoded in the default code page and in utf-16. A hex dump to show what it looks like:

0000000000: 0C 48 61 6E 73 20 50 61 │ 73 73 61 6E 74 20 20 20  ♀Hans Passant
0000000010: 20 20 20 20 20 20 20 20 │ 20 20 20 20 20 20 20 20
0000000020: 20 20 20 20 20 20 20 20 │ 20 20 20 20 20 20 20 20
0000000030: 20 20 20 20 20 20 20 0C │ 00 48 00 61 00 6E 00 73         ♀ H a n s
0000000040: 00 20 00 50 00 61 00 73 │ 00 73 00 61 00 6E 00 74     P a s s a n t
0000000050: 00 20 00 20 00 20 00 20 │ 00 20 00 20 00 20 00 20
0000000060: 00 20 00 20 00 20 00 20 │ 00 20 00 20 00 20 00 20
0000000070: 00 20 00 20 00 20 00 20 │ 00 20 00 20 00 20 00 20
0000000080: 00 20 00 20 00 20 00 20 │ 00 20 00 20 00 20 00 20
0000000090: 00 20 00 20 00 20 00 20 │ 00 20 00 20 00 20 00 20
00000000A0: 00 20 00 20 00          │

The oddish 0x0C word in the file is the string length in characters (not bytes), followed by 54 characters to store the user name, padded with spaces. Easiest way to read it is with BinaryReader.ReadString():

public static string GetExcelFileOwner(string path) {
    string uspath = Path.Combine(Path.GetDirectoryName(path), "~$" + Path.GetFileName(path));
    if (!File.Exists(uspath)) return "";
    var sharing = FileShare.ReadWrite | FileShare.Delete;
    using (var fs = new FileStream(uspath, FileMode.Open, FileAccess.Read, sharing))
    using (var br = new BinaryReader(fs, Encoding.Default)) {
        return br.ReadString();
    }
}

But not necessarily the most correct way, you might want to improve the code and try to locate the utf-16 string (not with ReadString) if 8-bit encodings don't work well in your locale. Seek() to offset 0x37 first. Be sure to use the method correctly, it has an implicit race condition so make sure you only use it after the operation failed and expect an empty string return anyway. I cannot guarantee this method will work correctly on all Excel versions, including future ones, I only tested for Office 2013 on a workstation class machine.

Answer 2

Which part are you having problems with?

WIthout knowing anything about xslx, I can only guess that: you want to open file and specify FileAccess.Read & FileShare.ReadWrite as here: How to read open excel file at C# after that, you use some kind of library to turn XSLX into DataTable, and extract the specific row which you need.