Geeks With Blogs
Annie 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? 
Technorati Tags:
Posted on Thursday, January 29, 2009 10:40 AM C# Code , SharePoint | Back to top


Comments on this post: SharePoint Adventures - Reading an Excel Spreadsheet From A Stream

# re: SharePoint Adventures - Reading an Excel Spreadsheet From A Stream
Requesting Gravatar...
Nice,

Now do it using IsolatedStorage while trying to clean-up after yourself and still supporting multiple session as in a web app.

It's a shame excel file (be it 2003/2007) are such a trouble to parse.

David
Left by David on Mar 13, 2009 5:21 AM

# re: SharePoint Adventures - Reading an Excel Spreadsheet From A Stream
Requesting Gravatar...
Hello,
First of all thank you for introducing me to excelreader. I didnt get the approach you have taken for Excel 2007. Are you manually saving the file or programmatically? my users will upload the 2007 excel file and i want to read that on 64-bit m/c. Please let me know how have u done this?
Left by San on May 21, 2009 5:36 PM

# re: SharePoint Adventures - Reading an Excel Spreadsheet From A Stream
Requesting Gravatar...
Hey Great post. I have a financial doc loaded into a sharepoint lib and I need to extract this every month. Can you send the code & instructions please.
Thanks
P :)


Left by Phil on May 28, 2009 3:56 AM

# re: SharePoint Adventures - Reading an Excel Spreadsheet From A Stream
Requesting Gravatar...
I have never had problems opening an excel in Sharepoint. But I will bookmark your site, just incase I experience it in the future. Thanks!
Left by british gas on May 09, 2010 11:08 AM

# re: SharePoint Adventures - Reading an Excel Spreadsheet From A Stream
Requesting Gravatar...
Anne, your blog is very good, I am working with SharePoint with code.
Thank you very much.
Left by movh on Jun 09, 2011 1:51 PM

# re: SharePoint Adventures - Reading an Excel Spreadsheet From A Stream
Requesting Gravatar...
Nice article. When referencing using a mapped path, this is great. Has anyone ever opened excel using HTTP?
Left by Clem on Aug 19, 2013 12:10 AM

# re: SharePoint Adventures - Reading an Excel Spreadsheet From A Stream
Requesting Gravatar...
Useful. So far, the only solution I found. It is a shame that OleDb does not support any kind of credentials.
Left by Paulo on Jul 14, 2014 6:33 AM

Your comment:
 (will show your gravatar)


Copyright © Annie Bougie | Powered by: GeeksWithBlogs.net