Automatically open xls table in C # from a SharePoint site in read / write mode

Question

I wrote a procedure that will open xls from a local disk, update the data in it, and then save it again. It works great.

The problem occurs when I replace the file name to point to a SharePoint site. It opens the file in order. Updates the file, but when it tries to save the file, it throws an exception with the message "Unable to save as this name. The document was opened read-only." If I try to save the file with a different file name, then it works fine.

Does anyone know what I am missing? I think this should be due to the way I open the file. Is there any other way that I can force to open a file in read / write mode?

private static void RefreshExcelDocument(string filename) { var xls = new Microsoft.Office.Interop.Excel.Application(); xls.Visible = true; xls.DisplayAlerts = false; var workbook = xls.Workbooks.Open(Filename: filename, IgnoreReadOnlyRecommended: true, ReadOnly: false); try { // Refresh the data from data connections workbook.RefreshAll(); // Wait for the refresh occurs - *wish there was a better way than this. System.Threading.Thread.Sleep(5000); // Save the workbook back again workbook.SaveAs(Filename: filename); // This is when the Exception is thrown // Close the workbook workbook.Close(SaveChanges: false); } catch (Exception ex) { //Exception message is "Cannot save as that name. Document was opened as read-only." } finally { xls.Application.Quit(); xls = null; } } 

Thanks a lot in advance for the suggestions.

Jonathan

Answer 1

Unfortunately, you cannot save directly to SharePoint using the Excel API. This is why the file opens as read-only - it is not allowed.

The good news is that this is possible, but you must submit the form via a web request. Even better news is that the code example is on MSDN ! In particular, pay attention to the PublishWorkbook method, which sends a local copy of the Excel file to the server through a web request:

 static void PublishWorkbook(string LocalPath, string SharePointPath) { WebResponse response = null; try { // Create a PUT Web request to upload the file. WebRequest request = WebRequest.Create(SharePointPath); request.Credentials = CredentialCache.DefaultCredentials; request.Method = "PUT"; // Allocate a 1K buffer to transfer the file contents. // The buffer size can be adjusted as needed depending on // the number and size of files being uploaded. byte[] buffer = new byte[1024]; // Write the contents of the local file to the // request stream. using (Stream stream = request.GetRequestStream()) using (FileStream fsWorkbook = File.Open(LocalPath, FileMode.Open, FileAccess.Read)) { int i = fsWorkbook.Read(buffer, 0, buffer.Length); while (i > 0) { stream.Write(buffer, 0, i); i = fsWorkbook.Read(buffer, 0, buffer.Length); } } // Make the PUT request. response = request.GetResponse(); } finally { response.Close(); } } 

The sample code describes the scenario for the 2007 versions of these products, but other versions should behave the same.

Answer 2

What does the name of a failed example look like? Are documents stored in the database used in the database? Or am I misunderstanding your problem? Otherwise, I could assume that the file you are trying to save is write-protected by the operating system and cannot be modified.