Sharepoint 2007:How to export all list items and columns to excel 2007 using object model in powershell script

  • From a specified list in a web or from all lists in a web or from all lists in all webs? – Robert Lindgren Jan 10 '14 at 6:42
  • From a specific list that comes by lopping all sites in that site colelction. (Means the same list can be in different sites of that site collecton. so it will retreive all values of that list). – priya Jan 10 '14 at 6:54

There are several approaches in accordance with, but to formulate in PowerShell your request it will be tough. Cannot help direclty providing the script, but it is viable to create the object directly and execute it, pointers :

  1. Cross site collections - either looping (not good) or best aproach - using Search API (examples are many, e.g. http://jsuhail.blogspot.ch/2014/01/show-sharepoint-results-in-spgrid-view.html or http://www.youtube.com/watch?v=NzjGbTX9e_M)
  2. Same site collection, cross-subsites - using SPSiteDataQuery (could be combined with caching, enable lists selection, view-fields selection, content-type based queries, etc.), see http://deewaker-blogs.blogspot.ch/2009/07/accessing-multiple-lists-across.html or http://aslambashablog.blogspot.ch/2012/08/querying-multiple-lists-using.html

As results will mainly be returned as DataSets or DataTables you can easily chose to send that file to the browser (with Powershell is even easier formatting) but for browser scenarios - using Content-Disposition with headers (other approaches also viable), as in

Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "Content-Disposition", "attachment; filename=excelTest.xls"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.