Filtered SharePoint List for Export to Excel

Question

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?

Answer 1

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!

Answer 2

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).

Answer 3

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.

Answer 4

Well, I have the same problem a lot of years later. I found a solution on internet, for anyone interested in

Link: https://www.c-sharpcorner.com/article/export-to-excel-sharepoint-filtered-list-items-only/

Note: Sharepoint has a internal export data to excel method, but it export all list data, with no filter. Only filter by View. Ypu can filter only in the excel before.

If you want only to export filtered data to excel, try this link above