Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

I have an Issues List is SharePoint that contains over 75 different categories. I would like the ability so that when I use the filter on the column title and select a particular category, that then when I click Export to Excel it only exports the issues for that particular category - is this possible?

I've thought about creating different views for each category, which seems to work but it's very time consuming and not very flexible if new categories are added.

Any ideas? Thanks guys

share|improve this question
    
Same problem, and I also need to print them directly... –  Bargant Oct 25 '11 at 9:30
    
Please check the following: Export GridView to Excel and Export SPGridView to Excel spreadsheet –  saumilm Dec 8 '11 at 9:01

3 Answers 3

Unfortunately the default SharePoint Export to Excel behavior will only export a .iqy file that ignores any filters applied. As mentioned in the comments, there are a lot of 3rd party solutions to achieve this functionality that you might want to look into - for instance, Export Selected Items to Excel could do the trick (create a view with no pagination and select all to export).

Another option might be to script view creation into PowerShell. Here's a quick function I wrote that creates a view filtered by a single column:

function Add-ViewWithFilteredColumnToList {
param(
$webUrl,
$listName,
$viewName,
$fieldToFilter,
$fieldToFilterValue,
$fieldToFilterValueType
)
$web = Get-SPWeb $webUrl;
$list = $web.Lists[$listName];
$fields = $list.Views[0].ViewFields.ToStringCollection();
$strQuery = "<Where><Eq><FieldRef Name='"+$fieldToFilter+"'/><Value Type='"+$fieldToFilterValueType+"'>"+$fieldToFilterValue+"</Value></Eq></Where>";


#Arguments for 'Add': Name,fields,query,row limit, paged, default, type, personal view
$result = $list.Views.Add($viewName, $fields, $strQuery, 100, $True, $False , "HTML", $False)
}

#Usage
Add-ViewWithFilteredColumnToList "http://sp2010demo:47651/sites/delaylist" "TestIssues" "TestView2" "Status" "Resolved" "Text" 

You could write a script that loops through all category views and creates a view for each one- this would at least save time during the view creation process.

Alternatively, you can use the Open in Access functionality to achieve a workaround - provided you have Access installed:

  1. From your list, click on the List tab in the Ribbon
  2. Click Open in Access
  3. From Access, you can filter and export as necessary.

Hope this helps!

share|improve this answer

Depending on what you are trying to achieve it may be easier to come at this from a different angle. You could write a macro in excel that will allow you to grab the data for the entire list, then split by category into individual sheets (or books).

share|improve this answer

You can do this using the Microsoft PowerQuery addin for Excel as it is able to connect to SharePoint lists and provides you with a lot more query power. However, you would still need to specify the categories you want on the client (worksheet) side not the view.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.