Reading data from an Excel document stored in Sharepoint?

Question

How can I 'read' an excel 2003 document stored as a sharepoint spfile? I can retrieve the document from the library with no problems using the SPFile.OpenBinary() and then putting that into a MemoryStream.

The original idea was to use OpenXML to interrogate the document (which will take this object type as a constructor), but the Excel version (2003) prohibits this.

Just to cloud the issue further, there is no guarantee that I will have any Excel version on the host machine, so possibly won't be able to use the interop assemblies either.

Suggestions or solutions will be gratefully received.

Answer 1

I've found this [library] (http://exceldatareader.codeplex.com/) on codeplex which seems to be able to read any Excel version. There might be a lot more on the web

Answer 2

When you say read what exactly do you mean? There seems to be some great debate amongst developers as to what the term's definition is. Either way it shouldn't really matter if Excel is on their system or not, on account of I am only pretty sure that if the person wanted to view the file any way they would need at the very least a reader. So that being said I believe your fear is a moot point and that using a MemoryStream should suffice.

Answer 3

SpreadsheetGear for .NET can open a xls and xlsx workbooks from a memory stream with SpreadsheetGear.Factory.GetWorkbookSet().Workbooks.OpenFromStream(System.IO.Stream) and also has the ability to open directly from a byte array with OpenFromMemory(byte[]). Once opened, SpreadheetGear has a comprehensive API, calculation engine, rendering engine and more.

You can see live samples here and download the free trial here.

Disclaimer: I own SpreadsheetGear LLC

Answer 4

When I say read, I mean pull data from named ranges, cell references etc. All of the open source libraries I have found (Exceldatareader, NOPI, OpenXML) have some limitation or another that prohibits their use. e.g. can't load macro enabled sheets

The excel document is loaded into a sharepoint library which exposes this list as a collection of SPFile(s). These files can be read into a MemoryStream simply enough, but most of the libraries I have tried require a filestream constructor, which means writing to the filesystem on the application server

I've not tried SpreadsheetGear, but if there's no footprint on the filesystem, then I'll take a look for sure, but this is not an option on this project. I'll update this thread with my findings...

I'm reduced to using the PIA's. Dirty, dirty, dirty.