Programmatically get a user who locks an Excel workbook

Question

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

At some point, I need to change the access to the workbook object (ewb) file for writing as follows:

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

Before changing access to a file, I check if the file is locked on the server. If the file is locked, I will notify the user that the action will be repeated at a later point.

Now I want to include a username that blocks the excel file in the notification. I searched msdn, netoffice forum etc. And did not find a solution. I know that if you open the excel readwrite file, it will save the username in the xlsx file. How can I access this specific piece of information through C #?

EDIT: I ended up with 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 use the excel 2003 and excel 2007+ format (.xls and .xlsx). For .xls, I had to look into the .xls file. For .xlsx, the lock user is stored in the ~ $ temp file. I know that for the .xls file this is dirty code, but I do not know how the .xls file structure is structured. So I just read a bunch of bytes that includes the ascii username, and do a regex to extract that username.

Answer 1

it will save the username in the xlsx file

No, not a .xlsx file. Excel creates another file to store the username. It has the hidden file attribute enabled, so you cannot normally see it using Explorer.

Usually it has the same name as the source file, but with the prefix ~$ . Therefore, for a file named test.xlsx you will get a file named ~$test.xlsx . This is a binary file and contains the username encoded on the default code page and in utf-16. Hash 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 ans 0000000040: 00 20 00 50 00 61 00 73 │ 00 73 00 61 00 6E 00 74 P assant 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 odd word 0x0C in the file represents the length of the string in characters (not bytes), and then 54 characters to save the username filled with spaces. The easiest way to read this 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 can improve the code and try to find the utf-16 line (not with ReadString) if 8-bit encodings do not work in your locale. Seek () for offset 0x37 first. Be sure to use the method correctly, it has an implicit race condition, so make sure you use it only after the operation has failed, and expect the empty string to return anyway. I can not guarantee that this method will work correctly in all versions of Excel, including future ones, I tested only Office 2013 on a workstation class machine.

Answer 2

What part of your problem?

Without knowing anything about xslx, I can only assume that: you want to open the file and specify FileAccess.Read and FileShare.ReadWrite, as here: How to read an open excel file in C # after that you use some kind of library to turn XSLX into DataTable and retrieve the specific row that you need.