SAPUI5: Download Responsive Table (sap.m.Table) to xls with filters applied

Hello All,

In our team we needed to build a new application which is responsive. So for our table representations, we decided to go with responsive table.

The next challenge was to enable certain features for our tables, which included:

• sorting/filtering the table columns
• download the table content to xls.

For the first requirement, we needed the option to click individual columns to enable sorting/filtering, as we do with Grid Table(sap.ui.table.Table). Thankfully I found a good blog post (by Sai Vellanki) to help with the same. For the second requirement, I needed to browse through a lot of different articles and UI5 official documentation to finally come up with a complete solution. So I wanted to share my research with you guys, hoping that it would save some of your time ?.

Following the mentioned blog by Sai Vellanki, you would be able to create a popup menu with options to sort/filter, on clicking individual columns. Next we need to add a method to handle table filtering:

        handleColFilterPress: function(oEvent) {
			//var _productTable, _activeTablesFilters = {}; define these two as global variables
			//_productTable = _oView.byId("productTable");
            var oValue = oEvent.getParameter("item").getValue();
            var oBindingPath = this.getView().getModel().getProperty("/bindingValue"); //Get Hold of Model Key value that was saved   
            	  
            //create new filter object
            var oFilter = new Filter(oBindingPath, "Contains", oValue);
            
          	_activeTablesFilters[_productTable.getId()][oBindingPath] = oFilter;
              
            var oItems = _productTable.getBinding("items");
            var allFilters = [];
            //iterate and fetch filter for each column of current table
            for (var key in _activeTablesFilters[_productTable.getId()]) {//key corresponds to a column
            	allFilters.push(_activeTablesFilters[_productTable.getId()][key]);
            }
            oItems.filter(allFilters, "Application");//apply filters
            
            //put the code to close the popup here
        },

In the above method, I use a dictonary _activeTablesFilters to hold all the applied filters for a given table(using combination of table id and column name as key). This will ensure that the past applied filters are not ignored. The above code is tailored to support filtering for String columns. If you need to use other column types, please tweak the code accordingly.

Next we need to handle table download to xls, while retaining the table filters.

Steps:

1. Add required libraries to the Controller: “sap/ui/core/util/Export”, “sap/ui/core/util/ExportTypeCSV”.
2. Create a responsive table control and bind it to a JSON file: Products.json.
3. Define the view as follows:

   <mvc:View id="downloadTable" xmlns:mvc="sap.ui.core.mvc" controllerName="portal.controllers.DownloadTable" 
   		xmlns="sap.m" xmlns:layout="sap.ui.layout">
        <Page showHeader="false" enableScrolling="true" class="sapUiContentPadding"
   		showNavButton="false">
   
   		<content>
   			<Table width="50%" id="productTable" growing="true" growingThreshold="10" items="{path: '/ProductCollection',sorter: { path: 'ProductId', descending: 'true' }}">
   				<headerToolbar>
                     	<OverflowToolbar width="auto" height="27px" design="Transparent" visible="true" enabled="true">
                         	<content>
                           	<ToolbarSpacer width=""/>
                             	<OverflowToolbarButton id="clearTabFilters" type="Transparent" visible="false" icon="sap-icon://clear-filter" iconFirst="true" width="auto" enabled="true" tooltip="Clear All Filters" iconDensityAware="false"  press="clearTableFilters"/>                                                          
                             	<OverflowToolbarButton type="Transparent" icon="sap-icon://download" iconFirst="true" width="auto" enabled="true" tooltip="Download Table Data" iconDensityAware="false"  press="productTableExport"/>
                         	</content>
                     	</OverflowToolbar>
                 	</headerToolbar>
   				<columns>
   					<Column>
   						<Text text="Name" />
   					</Column>
   					<Column>
   						<Text text="Status" />
   					</Column>
   					<Column>
   						<Text text="Supplier" />
   					</Column>
   					<Column>
   						<Text text="Category" />
   					</Column>
   				</columns>
   				<items>
   					<ColumnListItem>
   						<cells>
   							<Text text="{Name}" />
   							<Text text="{Status}" />
   							<Text text="{SupplierName}" />
   							<Text text="{MainCategory}" />
   						</cells>
   					</ColumnListItem>
   				</items>
   			</Table>
   		</content>
   		<footer>
   			<OverflowToolbar id="otbFooter">
   				<ToolbarSpacer />
   				<Button text="Order" press="onOrder">
   					<layoutData>
   						<OverflowToolbarLayoutData
   							moveToOverflow="false" />
   					</layoutData>
   				</Button>
   			</OverflowToolbar>
   		</footer>
   
   	</Page>
   </mvc:View>
   ​

4. Add the below methods to controller:

   		productTableExport: function(oEvent) { 
   			this.tableExportDownload(_productTable,
   					{
   						path : "/ProductCollection"
   					},
   					// column definitions with column name and binding info for the content
   					[{
   						name : "Name",
   						template : {
   							content : "{Name}"
   						}
   					}, {
   						name : "Status",
   						template : {
   							content : "{Status}"
   						}
   					}, {
   						name : "Supplier",
   						template : {
   							content : "{SupplierName}"
   						}
   					}, {
   						name : "Category",
   						template : {
   							content : "{MainCategory}"
   						}
   					}],
   					"ProductCollection"
   				);
   		},
   		
   		tableExportDownload: sap.m.Table.prototype.exportData || function(oTable,rowJsonObj,colJsonArray,oFileName, modelName) {
   			var rows = rowJsonObj;
   
   			var oFilters = [];
   			for(var key in _activeTablesFilters[oTable.getId()]) {
       		  oFilters.push(_activeTablesFilters[oTable.getId()][key]);
   			}
   			rows = {
                       path : rowJsonObj.path,
                       filters:oFilters
               };
   			var oExport = new Export({
   				// Type that will be used to generate the content. Own ExportType's can be created to support other formats				
   				exportType: new  sap.ui.core.util.ExportTypeCSV({
   					 separatorChar: "\t",
   					 mimeType: "application/vnd.ms-excel",
   					 charset: "utf-8",
   					 fileExtension: "xls"
   				}),
   
   				// Pass in the grid table model created
   				models : (modelName)?oTable.getModel(modelName):oTable.getModel(),
   				// binding information for the rows aggregation
   				rows : rows,
   				// column definitions with column name and binding info for the content (from caller)
   				columns : colJsonArray
   			});
   			// download export
   			oExport.saveFile(oFileName).always(function() {
   				this.destroy();
   			});
   		},

   In the above code snippet, the main action happens in the tableExportDownload method. We recover the filters applied to the table from the _activeTablesFilters dictionary and pass it to the rows object. This will ensure that the filtered table content is downloaded to the xls.

Hope the code snippets are fairly easy to follow. I have added comments wherever possible. Happy Coding!

Best Regards,

Bopanna