Note:

You can find the CFFiddle demo of this function and other spreadsheet functions as part of a project that is shared with you.

Click the button below to launch CFFiddle.

To copy the project in your workspace in CFFiddle, follow the steps below:

  1. Log in with your Gmail or Facebook credentials.
  2. Navigate to the project in the left pane.
  3. Once you make some changes in any cfm in the project, a pop up displays asking you to save the project.
  4. Give the project a suitable name and click Save.

Description

Adds auto filters to the spreadsheet.

Returns

Nothing

Category

Syntax

SpreadSheetAddAutofilter(Spreadsheetobj, autofilter)

History

ColdFusion 11: Added the function.

Parameters

Parameter

Description

spreadsheetObj

The Excel spreadsheet object to which to add the column.

autofilter

The Excel filter that needs to be applied to the sheet. Note that the value should not contain spaces or invalid characters. The cell range must be in the form A1:J1.

Example

<cfscript>
       /*Get data from table orders*/
       ArtOrders=QueryExecute("SELECT orderid,customerfirstname,customerlastname,address,total,city FROM orders
       ORDER BY orderid", [] ,{datasource="cfartgallery"});
       /*Create spreadsheet object*/
       myObj=SpreadSheetNew("Art");
       SpreadSheetAddRow(myObj,"Order,First Name,Last Name,Address,Amount,City");
       SpreadsheetFormatRow(myObj, {bold=TRUE, alignment="center",color="red"}, 1);
       // Add rows to the spreadsheet
       SpreadsheetAddRows(myObj,ArtOrders);
       dirName=GetDirectoryFromPath(GetCurrentTemplatePath());
       // Add auto filters from rows A1 to F1
       SpreadSheetAddAutoFilter(myObj,"A1:F1");
       SpreadsheetWrite(myObj,"#dirName#ArtOrders.xls",true);
</cfscript>

Output

spreadsheetaddautofilter

Using member function

<cfscript>
       /*Get data from table orders*/
       ArtOrders=QueryExecute("SELECT orderid,customerfirstname,customerlastname,address,total,city FROM orders
       ORDER BY orderid",[],{datasource="cfartgallery"});
       /*Create spreadsheet*/
       myObj=SpreadSheetNew("Art");
       SpreadSheetAddRow(myObj,"Order,First Name,Last Name,Address,Amount,City");
       SpreadsheetFormatRow(myObj, {bold=TRUE, alignment="center",color="red"}, 1);
       SpreadsheetAddRows(myObj,ArtOrders);
       dirName=GetDirectoryFromPath(GetCurrentTemplatePath());
       myObj.autoAddFilter("A1:F1");
       SpreadsheetWrite(myObj,"#dirName#ArtOrders.xls",true);
</cfscript>

This work is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License  Twitter™ and Facebook posts are not covered under the terms of Creative Commons.

Legal Notices   |   Online Privacy Policy