Anne Bougie

When I needed to read an Excel spreadsheet from a SharePoint site, it seemed like a simple enough request. Previously, whenever I needed to open an Excel file, I used an OleDb connection with the following connection string:

string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;
       Data Source={0};Persist Security Info=False;
       Extended Properties=""Excel 12.0;HDR=YES""";
connectionString = string.Format(connectionString, filePath);

Of course, you can't open the file from the SharePoint site this way. So, my first hurdle was to figure out how to download the file from the web site. The System.Net namespace provides a very easy way to do this:

WebClient client = new WebClient();
client.UseDefaultCredentials = true;
Stream stream = client.OpenRead(url);

Now I had my file as a stream, so I thought I'd just extract it, save it to a temporary file, then read it in in the usual way. Problem solved. This next bit of code should probably be optimized a little so I'm not resizing the array every single loop:

BinaryReader brdr = new BinaryReader(stream);
byte[] result = new byte[0];
int bufferSize = 32768; // 32k
byte[] buffer = new byte[bufferSize];
long pos = 0;

while (true)
{
      buffer = brdr.ReadBytes(bufferSize);
      if (pos > 0)
      {
            // copy old data to bigger result
            byte[] temp = new byte[result.LongLength];
            Array.Copy(result, temp, result.LongLength);
            result = new byte[temp.LongLength + buffer.Length];
            Array.Copy(temp, result, temp.LongLength);
            // add new data
            for (int i = 0; i < buffer.Length; i++)
            {
                result[pos + i] = buffer[i];
            }
            pos += buffer.Length;
      }
      else
      {
            result = new byte[buffer.Length];
            Array.Copy(buffer, result, buffer.Length);
            pos = buffer.Length;
      }
      if (buffer.Length < bufferSize)
            break;
}
string tempFile = Path.Combine(Environment.GetEnvironmentVariable("TMP"),
      "CopyList.xlsx");
using (var fs = new FileStream(tempFile, FileMode.OpenOrCreate))
{
      var writer = new BinaryWriter(fs);
      writer.Write(result, 0, result.Length);
      writer.Close();
      fs.Close();
}


This worked beautifully on my development machine, but this is part of a larger program that is manipulating a SharePoint site via the SharePoint API. Therefore, it has to run on the server, which is 64-bit. When I first ran it, I received the error "The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine" . So, I did a little digging around, and found out that maybe I needed to install the driver. It is available from Microsoft as a separate download, so I thought I was good to go. I installed it, and was still receiving the same error. Huh??!! Then I did a little *more* digging, and found out that there is no OleDb driver for opening Excel files on a 64-bit machine. Fine. No problem. Just compile the project as x86, and I'm good to go. Right? Well, wrong, actually. When the project is compiled in 32-bit, I can't access the SharePoint sites. Aaaaarrrrrrrhhhh! What to do? Then, I'm looking all over, trying to find a way to read an Excel file directly from a stream. How hard can it be, right?

<SARCASM>
Actually, Microsoft has this really handy little guide that tells you just how to do it. It's only 349 pages. Turns out Excel's native format is something called a BIFF (Binary Interchange File Format). So, you just have to parse all the bytes from the stream into the right format. What a treat!
</SARCASM>

I know who I'd like to BIFF about now. Anyway, I did *even more* digging around, and I found some code from someone who has done just that. Excel Data Reader  Thanks, iciobanu! However, it only works for Excel 2003 format. Now that is finally a problem I can easily solve! Save the spreadsheet to 2003 format, and read it in. Are we there yet? Almost! I was getting errors because the stream object returned from the web download didn't support seeking, which this code relies on. So, save the stream out to a temp file (back to that again), and read it back in as a FileStream object, which supports seeking, and my problems are, at long last, solved.

And that is how you open an Excel file from a SharePoint site into a spreadsheet without using a generic OleDb connection. Sucess at long last! Isn't there some famous quote somewhere about persistence paying off? 

• Share This Post:
• 
• 
•