Opening Excel Workbooks with default Credentials on Sharepoint server

Question

I'm looking into extending some code that handles automatic opening and reading of Excel files. Ultimately this process needs to be able to run on a server so there is a strict requirement of no dialogs/user interactions required.

Currently this is all working fine for normal files, but now I need to be able to extend this functionality to access files on remote machines such as SharePoint/WebDAV systems.

The problem I've got at the moment in my little test application, is that as soon as I call the Open on an Excel Workbook I get a prompt asking me for my windows credentials. Now I can provide them, or click cancel (I'm assuming this defaults to current user credentials) and the file opens without problem.

What I need to do however, is find a way to access this file without the prompt...

Does anyone have any ideas on how to do this?

Answer 1

If you are working with Excel 2007 files then you don't need to use automation to open and read the files.

Excel 2007 (xlsx) files use the OpenXML file format. That is, they are basically just a set of XML documents wrapped up as a ZIP file. You can use the .NET Framework's Packaging API and the OpenXML SDK to create, read, and modify these documents.

Here are some resources:

Welcome to the Open XML Format SDK 2.0 http://msdn.microsoft.com/en-us/library/bb448854(office.14).aspx

OpenXML Developer http://openxmldeveloper.org/default.aspx

Reading Data from SpreadsheetML http://blogs.msdn.com/brian_jones/archive/2008/11/10/reading-data-from-spreadsheetml.aspx

Answer 2

Assuming that you are trying to access the file in a UNC path, you can give a User with Privileges to Read and Run the Application accessing the file with that user account. While running in Scheduled Job / Windows Service you have the option to specify the user account which which you need to run the App.

Answer 3

Yet another option for reading data from Excel using .NET: Excel Data Reader. I've only ever used this from a console application so I'm not sure how it would handle opening files over the network.

Answer 4

In the end I have used the C# webclient class. This can download from a sharepoint site with default, or custom user credentials.