Can I export a SharePoint list to an Excel file subdivided into separate worksheets?

Question

We have a SharePoint 2007 deployment which will have a substantially large document library. My client wants the ability to export this library to an Excel spreadsheet, but specifically wants the ability to divide the spreadsheet into several worksheets based on a specific field. Is this possible to accomplish in WSS 3.0, through the object model or otherwise?

There is a out-of-the-box Export to Spreadsheet, but it does not appear to support automated subdivision of the list items into separate worksheets. I do not know if Excel Services that come with MOSS are capable of it, but we do not have MOSS so we cannot consider it an option for now.

EDIT

It seems that by mentioning "out-of-the-box", I am implying that I'd prefer something quick and simple. Let's dispel that. I do a lot of heavy work in the object model. I only mentioned the Export to Spreadsheet because that's the only available method I know of off-hand, and its options are limitted. So I am comfortable with all manner of work level that can be suggested.

I should also note that keeping the list linked with the spreadsheet is undesired. We want to be able to download the spreadsheet as a reference. Because of the number of people who will be working on the list, it would be absolute chaos to try and synchronize all of the linked files. My client has agreed that it'll be easier to handle obsolete copies than to try some synchronized system.

The solution also needs to be deployable. So things which do not tailor to an individual site are best.

Answer 1

You won't be able to do this OOTB. You will have to write some code to iterate through the records of the list either using

• The SharePoint OM - Better performance and richer API but has to run on a Web Front End

• The web service - Can run on any machine

Then you can build up the Excel spreadsheet either by

• Using the Excel object model (aka Automation) if this is a quick kludge running from a workstation - but excel wasn't designed to be used from an unattended server and/or high volume so you may also want to look at

• A 3rd party component such as SpreadsheetGear to generate the Excel spreadsheet files.

Answer 2

A good bet is to quickly create views for your items (using filters as you want) mirroring your desired worksheets and then export those views into excel. Those views update with the list and you can manually grab new versions later. Still manual but OOTB and no excel hacking needed.

Answer 3

I posted this on SharePoint Overflow. One of the answers I received there was very useful, regarding the utility of the Open XML SDK. Thank you to those who answered... I looked over your suggestions. My client has decided to go through with this one on account that it does not cost money to implement (as Spreadsheet Gear or datapresentation's plugin would).