Add a filter in an Excel worksheet

This REST API indicates to add a filter for a filter column on an Excel Worksheet.

RSET API


PUT http://api.aspose.cloud/v3.0/cells/{name}/worksheets/{sheetName}/autoFilter/filter

The request parameters are:

Parameter Name	Type	Path/Query String/HTTPBody	Description
name	string	Path	The workbook name.
sheetName	string	Path	The worksheet name.
range	string	Query
fieldIndex	integer	Query
criteria	string	Query
matchBlanks	string	Query	true/false
refresh	string	Query	true/false
folder	string	Query	Original workbook folder.
storageName	string	Query	Storage name.

The OpenAPI Specification defines a publicly accessible programming interface and lets you carry out REST interactions directly from a web browser.

You can use cURL command-line tool to access Aspose.Cells web services easily. The following example shows how to make calls to Cloud API with cURL.

Request


curl -v "http://api.aspose.cloud/v3.0/cells/Book1.xlsx/worksheets/Sheet1/autoFilter/filter?range=A1:B1&fieldIndex=0&criteria=Year" \
-X PUT \
-H "Content-Type: application/json" \
-H "Accept: application/json" \
-H "Authorization: Bearer <jwt token>"

Response

{
  "Code": 200,
  "Status": "OK"
}

Cloud SDK Family

Using an SDK is the best way to speed up the development. An SDK takes care of low-level details and lets you focus on your project tasks. Please check out the GitHub repository for a complete list of Aspose.Cells Cloud SDKs.

The following code examples demonstrate how to make calls to Aspose.Cells web services using various SDKs:

	// For complete examples and data files, please go to https://github.com/aspose-cells-cloud/aspose-cells-cloud-dotnet/
	namespace Aspose.Cells.Cloud.SDK.Example
	{
	using Aspose.Cells.Cloud.SDK.Api;
	using Aspose.Cells.Cloud.SDK.Request;
	using System;
	public partial class CellsApiExample
	{
	public void PutWorksheetFilter1Example()
	{
	CellsApi cellsApi = new CellsApi(Environment.GetEnvironmentVariable("CellsCloudTestClientId"), Environment.GetEnvironmentVariable("CellsCloudTestClientSecret"));
	PutWorksheetFilterRequest request = new PutWorksheetFilterRequest
	{
	name = "Book1.xlsx",
	sheetName = "Sheet1", range ="A1:C10",
	fieldIndex = 0,
	criteria = "test",
	matchBlanks = true,
	refresh = true,
	folder = "TestData/In"
	};
	cellsApi.PutWorksheetFilter(request);
	}
	}
	}

view raw Examples-DotNet-CSharp-Worksheet-AddWorksheetFilter-1.cs hosted with ❤ by GitHub

Java

	package com.aspose.cloud.cells.api;
	import com.aspose.cloud.cells.client.*;
	import com.aspose.cloud.cells.model.*;
	import com.aspose.cloud.cells.request.*;
	import java.io.IOException;
	import org.junit.Test;
	import java.util.ArrayList;
	import java.util.List;
	import java.io.File;
	import java.util.HashMap;

	public class Example {
	private CellsApi api;
	public Example(){
	try {
	api = new CellsApi(
	System.getenv("CellsCloudClientId"),
	System.getenv("CellsCloudClientSecret"),
	"v3.0",
	System.getenv("CellsCloudApiBaseUrl")
	);
	} catch (ApiException e) {
	e.printStackTrace();
	}
	}
	public void Run(){
	try{
	String remoteFolder = "TestData/In";

	String localName = "Book1.xlsx";
	String remoteName = "Book1.xlsx";

	UploadFileRequest uploadFileRequest = new UploadFileRequest();
	uploadFileRequest.setPath( remoteFolder + "/" + remoteName );
	uploadFileRequest.setStorageName( "");
	HashMap<String,File> files = new HashMap<String,File>();
	files.put( localName , new File(localName ));
	uploadFileRequest.setUploadFiles(files);
	api.uploadFile(uploadFileRequest);
	PutWorksheetFilterRequest request = new PutWorksheetFilterRequest();
	request.setName(remoteName);
	request.setSheetName("Sheet1");
	request.setRange("RANGE");
	request.setFieldIndex(0);
	request.setCriteria("test");
	request.setMatchBlanks(true);
	request.setRefresh(false);
	request.setFolder(remoteFolder);
	request.setStorageName("");

	CellsCloudResponse response = api.putWorksheetFilter(request);

	} catch (ApiException e) {
	e.printStackTrace();
	}catch (IOException e) {
	e.printStackTrace();
	}
	}
	}

view raw Examples-Java-filters-AddFilterToFilterColumnExample-1.java hosted with ❤ by GitHub

PHP

Ruby

	# For complete examples and data files, please go to https://github.com/aspose-cells-cloud/aspose-cells-cloud-ruby
	require 'aspose_cells_cloud'

	class Cell

	include AsposeCellsCloud


	def initialize
	#Get client_id and client_secret from https://cloud.aspose.com

	@instance = AsposeCellsCloud::CellsApi.new($client_id,$client_secret,$api_version,$baseurl)
	end

	# Clear cells contents.
	def put_worksheet_filter
	name = $BOOK1
	sheet_name = $SHEET1
	range = $RANGE
	field_index = 0
	criteria = 'test'
	match_blanks = true
	refresh = true
	folder = $TEMPFOLDER
	result = @instance.upload_file( folder+"/"+name, ::File.open(File.expand_path("data/"+name),"r") {\|io\| io.read(io.size) })
	expect(result.uploaded.size).to be > 0
	result = @instance.cells_auto_filter_put_worksheet_filter(name, sheet_name, range, field_index, criteria, {:match_blanks=>match_blanks, :refresh=>refresh, :folder=>folder})
	expect(result.code).to eql(200)
	end

	end

	cell = Cell.new()
	puts cell.put_worksheet_filter

view raw Examples-Ruby-AutoFilter-put_worksheet_filter-.rb hosted with ❤ by GitHub

Node.js

	const { CellsApi, UploadFileRequest, CellsAutoFilter_PutWorksheetFilterRequest } = require("asposecellscloud");
	const clientId = process.env.CellsCloudClientId;
	const clientSecret = process.env.CellsCloudClientSecret;
	const ApiURL = process.env.CellsCloudApiBaseUrl;
	const fs = require('fs');
	const path = require('path');
	const cellsApi = new CellsApi(clientId, clientSecret,"v3.0",ApiURL);
	const localPath = "D:/aspose-cells-cloud-node/TestData/"

	const filename = "Book1.xlsx";
	const data =fs.createReadStream(path.join(localPath, filename));

	const req = new UploadFileRequest({
	path: "Temp/" + filename,
	file: data
	});
	cellsApi.uploadFile(req)
	.then((result) => {

	const req = new CellsAutoFilter_PutWorksheetFilterRequest();
	req.name = filename;
	req.sheetName = "Sheet1";
	req.range = "A1:C10";
	req.fieldIndex = 0;
	req.criteria = "test";
	req.matchBlanks = true;
	req.refresh = true;
	req.folder = "Temp";
	cellsApi.cellsAutoFilterPutWorksheetFilter(req)
	.then((result) => {
	console.log("successful")

	}).catch((error) => {
	console.error('Unhandled Promise Rejection:', error);
	})
	})

view raw Examples-Node.js-SDK-Worksheet-AddWorksheetFilter-1.js hosted with ❤ by GitHub

Android

	try
	{
	CellsApi api = new CellsApi(CellsApiUtil.GetClientId(),CellsApiUtil.GetClientSecret(),CellsApiUtil.GetAPIVersion(),CellsApiUtil.GetBaseUrl());
	String name = BOOK1;
	String sheetName = SHEET1;
	String range = RANGE;
	Integer fieldIndex = 0;
	String criteria = "test";
	Boolean matchBlanks = true;
	Boolean refresh = true;
	String folder = TEMPFOLDER;
	CellsApiUtil.Upload(api, folder , name);
	CellsCloudResponse response = api.cellsAutoFilterPutWorksheetFilter(name, sheetName, range, fieldIndex, criteria, matchBlanks, refresh, folder,null);
	}
	}
	catch (Exception ex)
	{
	ex.printStackTrace();
	}

view raw Examples-Android-filters-AddFilterToFilterColumnExample-1.java hosted with ❤ by GitHub

Perl

	# For complete examples and data files, please go to https://github.com/aspose-cells-cloud/aspose-cells-cloud-perl/
	use strict;
	use warnings;
	use utf8;
	use File::Slurp;
	use MIME::Base64;
	use AsposeCellsCloud::CellsApi;
	my $config = AsposeCellsCloud::Configuration->new( client_id => $ENV{'ProductClientId'}, client_secret => $ENV{'ProductClientSecret'});
	my $instance = AsposeCellsCloud::CellsApi->new(AsposeCellsCloud::ApiClient->new( $config));

	my $remoteFolder = 'TestData/In';
	my $remoteName = 'Book1.xlsx';
	my $request = AsposeCellsCloud::Request::PutWorksheetFilterRequest->new();
	$request->{name} = $remoteName;
	$request->{sheet_name} = 'Sheet1';
	$request->{range} = 'A1:B1';
	$request->{field_index} = 0;
	$request->{criteria} = 'Year';
	$request->{match_blanks} = 'false';
	$request->{refresh} = 'true';
	$request->{folder} = $remoteFolder;
	$request->{storage_name} = '';
	my $result = $api->put_worksheet_filter(request=> $request);

view raw Examples-Perl-Worksheet-AddWorksheetFilter-1.pl hosted with ❤ by GitHub

Get an auto filter in an Excel worksheet Add a color filter in an Excel worksheet