Sharepoint 2007:How to export all list items and columns to excel 2007 using object model in powershell script
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 :
- 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)
- 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"