译者说明：欢迎访问我的Blog: http://daidaoke.donews.net/daidaoke/
译文中的错误望不吝指出。
我的Email：tangtaike@163.com
如需转载，请事先通知。
排序和过滤
阅读本节时，如果您打开随书光盘Samples\Chap02目录下的SortFilterExample.htm文件。您会发现这个例子很有用。本节展示的代码和描述的特定环境来自这个文件。
电子表格组件支持Excel中的基础的排序和过滤功能，并且通过编程模型和用户界面来提供这些功能。然而，在电子表格的用户界面中，排序和过滤的功能比起Excel有某种程度的增强。让我们来看一个例子。
DHTML探险
属性工具箱是由两位OWC小组的天才程序员，Eric Matteson和Cesar Alvarez开发的不可思议的杰作，属性工具箱也证明了试图使用DHTML来模仿Office的用户界面风格是一件困难重重的事。早期，我们坚持认为我们应该使属性工具箱尽可能地和标准的Office用户界面相似，Eric和Cesar也的确花费了数月的时间来改变HTML和IE浏览器，使得它能符合需求。大多数人都不相信结果竟然就在HTML中。然而，因为考虑到虽然一般人都可以很有效的使用web站点，但还是会被Office应用程序中的许多高级对话框所迷惑，所以关于使用HTML来模拟Office用户界面是否使得控件更易于使用的争论依然很突出。
对于那些希望在web页面中使用Office Web Components的开发者们，我的建议是不要浪费时间企图将HTML融入到传统的微软窗体应用程序的界面中，而应该利用HTML的简洁和动态布局的优势来为您的应用程序开发一个更加自然和易于使用的界面。
假设您已经开发了一个用于列出您当前的产品线的电子表格， 表格中显示了每一个产品的单价，库存数量，和定购数量，还有一个给出了销售率的计算列，用于显示潜在的价值。现在用户需要根据产品的潜在价值对产品列表进行降序排列。用户可以在电子表格用户界面中，简单的选择需要排序的区域(或者在区域中选择任意的一些单元)，并点击降序工具条按钮。当按钮被点击时，一个Excel中没有的菜单在按钮下方显示出来，如图2－2所示。
图2－2 使用中的电子表格组件用户界面
用户们在对Excel区域排序时所遇到的常见问题之一就是选择要排序的区域和选择根据哪一列进行排序。电子表格组件让用户能够方便的选择需要排序的区域，然后在用户点击工具条上的升序或降序排序按钮时，显示一个列名的列表，使用户能够选择根据哪一列进行排序。通过Range对象的Sort方法，也可以使用排序功能。它使开发人员可以在用户单击或者双击一个列标头时方便地完成一个列表的排序。
您可以已经注意到电子表格组件一次只能根据一列来对列表进行排序。Excel提供了一个排序对话框，可以让您同时根据最多三个关键列进行排序(例如，是否根据种类排序，然后根据发货人排序，最后根据潜在价值排序)。电子表格组件没有完成这个功能的用户界面，但是底层的引擎是支持这个功能的。您可以使用下列函数模仿多列的排序。
'--------------------------------------------------------------------------
' MultiColumnSort
' 目的: 同时根据多列对电子表格进行排序
' 传入: 电子表格中需要排序的区域的引用,
' 被排序的列的列编号的数组，
' 排序方向标志的数组(和上面的数组相同大小)
' 输出: 无 (完成排序动作)
'
Sub MultiColumnSort(Spreadsheet, Range, Columns, Directions)
' 启动一个撤消单位，以便可以以一个完整单位的任务进行撤消
Spreadsheet.BeginUndo()
' 关闭ScreenUpdating属性，使得当我们设置过滤，排序
' 和再过滤时电子表格不会重画
Spreadsheet.ScreenUpdating = False
' 现在递减遍历Columns数组和Directions数组,
' 实现我们需要的效果
For ct = ubound(Columns) To lbound(Columns) Step -1
' 0 is a guess for column headings
Range.Sort Columns(ct), Directions(ct), 0
Next 'ct
' 打开ScreenUpdating属性开关，使电子表格重画
Spreadsheet.ScreenUpdating = True
' End the undo unit
Spreadsheet.EndUndo()
End Sub 'MultiColumnSort()
实现多列排序的技巧在于，实际上是按照被排序列所定义次序的相反次序执行排序动作。例如，如果您需要先根据类别进行排序，然后再根据发货商进行排序，函数则会首先根据发货商排序列表，然后再根据类别进行排序。当电子表格根据一个新列排序列表时，新列的每一项(新列中相同列值的一组行)中，之前根据另一列进行排序产生的次序将保持不变。我们刚才阅读的函数接收三个参数：一个需要排序的区域，一个列编号的数组，一个方向值的数组(降序或升序)。函数降序遍历这两个数组，从而实现了多列排序的效果。请注意，函数还使用了BeginUndo和EndUndo方法来将所有的排序操作组合在一个撤消动作块中，这样当用户选择撤消命令时，这些排序动作就会被一起撤消。
电子表格组件也支持一种新的自动过滤的用户界面。组件中的过滤函数和Excel中的过滤函数相似，但是组件用户界面中的自动过滤下拉列表有一些不同的地方。假设您需要从图2－2中我们刚才查看过的产品列表中，过滤掉一些产品类别来观察它会怎样影响高潜在价值的产品。开发者或用户可以打开自动过滤功能，在类别列上点击自动过滤箭头，就会看到图2－3所示的界面。
图2－3. 电子表格组件的自动过滤用户界面
在Excel中，可以非常方便的选择单个项，然而，选择多项则需要使用高级自动筛选对话框，当您只是想要排除四五项时，这个操作就会十分费劲。而在电子表格组件中，自动过滤下拉列表为每一项都提供了一个简化复选框，在顶部还有一个”显示所有”项，使您能够快速切换所有项的状态(选择或不选择)。
机敏的读者会发现电子表格组件中的自动过滤下拉列表没有包括Excel中很有用的两个设置选项。例如，您找不到”前10位”的选项，这个选项使您能够快速过滤，得到前10位(或者前n位)的项。您也找不到”自定义”的选项，这个选项允许您完成比简单的包含或不包含的过滤复杂得多的过滤功能。很遗憾，这些更高级的功能还没有包含在电子表格组件中。不过，您可以通过调用电子表格控件的编程模型来容易得模拟这些功能。
可以使用下列函数来模拟”前n位”的过滤功能：
'--------------------------------------------------------------------------
' TopNFilter
' 目的: 根据给定的列编号过滤出列表的前N项
' 输入: 电子表格和区域的引用、列编号,
' 要过滤出的行的行数，以及标识过滤出前N行还是后N行的方向值
' Out: 无 (完成所需的过滤)
'
Sub TopNFilter(Spreadsheet, Range, ColumnNum, N, Direction)
Set c = Spreadsheet.Constants
Set rngData = Range
Set af = Spreadsheet.ActiveSheet.AutoFilter
' 启动一个撤消单元，以便将来能以完整的单元进行撤消
Spreadsheet.BeginUndo()
' 关闭ScreenUpdating属性开关，使得当我们在设置过滤，排序和再次应用
'过滤时，电子表格不会重画
Spreadsheet.ScreenUpdating = False
' 清除任何现存的过滤定义
ClearFilters Spreadsheet
' 在给定的数据区域中根据传入的列号的列排序列表
If LCase(Direction) = "bottom" Then
rngData.Sort ColumnNum, c.ssAscending, c.ssNo
Else
rngData.Sort ColumnNum, c.ssDescending, c.ssNo
End If
'如果N+1,N+2等等的行和第N行的值相同，
'则”前N位”过滤的结果可以会包含多过N的行。
'因此循环查看N+1等行是否和第N行的值相同，
'直到查找到一个不相同的值为止。
vNValue = rngData.Cells(N,ColumnNum).Value
While rngData.Cells(N+1,ColumnNum).Value = vNValue
N = N + 1
Wend
'N现在的值就是我们需要包括在过滤结果中的行的行数。
Set fltr = af.Filters(ColumnNum)
fltr.Criteria.FilterFunction = c.ssFilterFunctionInclude
For ct = 1 To N
fltr.Criteria.Add(rngData.Cells(ct,ColumnNum).Text)
Next
' 最后引用自动过滤
af.Apply
' 打开ScreenUpdating属性开关，使电子表格重画
Spreadsheet.ScreenUpdating = True
' 结束撤消单元
Spreadsheet.EndUndo()
End Sub 'TopNFilter()
“前N位”过滤功能似乎很简单，只要先排序，然后查看最开始的N行即可。但是真正的”前N位”过滤功能可能会返回超过N行的结果，因为它实际上的意思是”包括前N位的值的那些行”。如果在排序后，第10，11位的值相同，那么”前10位”过滤会将这些产品一起返回，因为它们都在前10位的值当中。还有，上述代码通过简单的改变排序方向(升序和降序)实现了过滤出前N位和后N位的功能。
同样地，您可以通过使用下面所示的函数来模拟基于表达式的过滤功能：
'--------------------------------------------------------------------------
' ExpressionFilter
' 目的: 使用一个可以被VBScript计算的表达式，在一给定的列上过滤列表
' 输入: 指向电子表格和区域的引用，进行过滤的列号,和用来进行过滤得表达式。
' 输出: 无 (列表被过滤)
'
Sub ExpressionFilter(Spreadsheet, Range, ColumnNum, Expression)
Dim sExp ' 临时表达式变量
Dim vValue ' 临时存储变量
Set c = Spreadsheet.Constants
Set rngData = Range
Set af = Spreadsheet.ActiveSheet.AutoFilter
' 启动一个撤销单元，以便将来可以作为一个完整单元的工作进行撤销。
Spreadsheet.BeginUndo()
' 关闭ScreenUpdating属性开关，以便当重置过滤属性，排序和再次应用过滤 ' 时电子表格不会重画
Spreadsheet.ScreenUpdating = False
' 清除任何现存的过滤设置
ClearFilters Spreadsheet
' 获得指定列的过滤对象,并设置过滤功能属性为”包含”
Set fltr = af.Filters(ColumnNum)
fltr.Criteria.FilterFunction = c.ssFilterFunctionInclude
' 检查是否表达式包含了列值的替换符,
' 如果包含则设置标志
fValueToken = cbool( _
instr(1, Expression, g_sValueToken, vbTextCompare) > 0)
'遍历各行中该列的值
For Each cell In rngData.Columns(ColumnNum).Cells
' 获得当前单元的值
vValue = cell.Value
' 如果vValue是一个字符串，为了防止其中包含了空格的情况
' 我们需要在它的前后加上引号
If vartype(vValue) = vbString Then
vValue = """" & vValue & """"
End If
' 组成我们需要执行的表达式，将当前行的值插入到表达式中
' 合适的位置处
If fValueToken Then
sExp = "g_fEval = cbool(" & Replace(Expression, _
g_sValueToken, vValue, 1, -1, vbTextCompare) & ")"
Else
sExp = "g_fEval = cbool(" & vValue & " " & Expression & ")"
End If
' 执行表达式
window.execScript sExp, "VBScript"
' 全局变量g_fEval现在已经被设置成True或者False.
' 如果是True，该行将会被包含在过滤结果中.
If g_fEval Then
fltr.Criteria.Add cell.Text
End If
Next 'ct
' 最后执行自动过滤
af.Apply
' 打开ScreenUpdating开关，使得电子表格可以重画
Spreadsheet.ScreenUpdating = True
' 结束撤销单元
Spreadsheet.EndUndo()
End Sub 'ExpressionFilter()
上述函数使用文档对象模型(DOM)中名为execScript的方法执行表达式(DOM是为Internet Explorer中的脚本提供的编程模型)。这个方法将字符串形式的脚本代码传递给动态引擎脚本(在这个例子中，是VBScript)来计算。之后脚本代码将表达式的结果存储在一个全局变量中，以便结果能够被用来判断表达式的真假。如果表达式为真，该行将会被包含在过滤后的集合中；如果为假，该行将被排除。
另外，您也可以使用电子表格组件的工作表对象中的Eval方法来计算表达式。Eval使用电子表格组件的函数库和表达式计算器，它可以代替动态脚本引擎，这就意味这在IE之外的容器中它会非常有用，另外当您希望可以让用户在表达式中使用电子表格的函数或区域的引用时它也会很有用。然而，动态脚本引擎可以提供一个强大的表达式计算器。还有，它允许您使用其它的脚本语言，例如ECMA脚本(也被称为JavaScript)。
附录：英文原文
Sorting and Filtering While you are reading this section, you might find it useful to open the Samples\ Chap02\SortFilterExample.htm file on the companion CD. The code shown in this section and the scenario described stem from that file.
The Spreadsheet component supports the basic sorting and filtering functionality found in Excel and exposes it through the programming model and the user interface. However, the sort and filter user interface in the Spreadsheet control is somewhat improved over that in Excel. Let's look at an example.
Adventures in DHTML
The Property Toolbox is an incredible piece of work created by two of OWC's talented developers, Eric Matteson and Cesar Alvarez. The Property Toolbox is also proof that attempting to emulate Office user interface conventions in DHTML is just asking for trouble. Early on, we decided that we should make the Property Toolbox look as much like the standard Office user interface as possible, and Eric and Cesar faithfully spent many months twisting and contorting HTML and Internet Explorer to make it comply. Most people can't believe that the result is actually in HTML. However, the jury's still out on whether emulating the Office user interface in HTML makes it any easier to use, considering that the average person can use web sites quite effectively but is still befuddled by many of the advanced dialog boxes in the Office applications.
I think everyone would agree that it would have been much easier to follow the user interface conventions made popular on the Web and present a new kind of command interface born and bred in HTML. For developers intending to use the Office Web Components in web pages, my advice is not to spend your time trying to wrangle HTML into a traditional Microsoft Windows application user interface. Instead, use HTML's simplicity and dynamic layout strengths to develop a more natural, easy-to-use interface for your application.
Suppose you have developed a spreadsheet that lists your current product line, showing each product's unit price, quantity in stock, and quantity on order, as well as a calculated column showing a potential worth given a sell-through rate. Now the user wants to sort the list of products by their potential worth in descending order. Through the Spreadsheet component user interface, the user simply selects the range to sort (or selects any cell in the range) and clicks the Sort Descending toolbar button. When this button is clicked, a menu appears below it that does not appear in Excel, as Figure 2-2 depicts.
[url=http://dev.csdn.net/javascript:fullSize(]
Figure 2-2. The Spreadsheet component user interface in action.
One of the common problems users encounter while sorting Excel ranges is selecting the range to sort and the column to sort by. The Spreadsheet component lets users easily select the range to sort, and it lets them choose the column to sort by from a list of column names that appears when the Sort Ascending Or Sort Descending toolbar button is clicked. The sorting functionality is also available through the programming model via the Sort method of the Range object. This allows the developer to easily enable list sorting when the user clicks or double-clicks a column heading.
You might notice that the Spreadsheet component lets you sort the list only one column at a time. Excel offers a Sort dialog box that lets you sort by up to three keys at once (for example, sort by category, then by shipper, and then by potential worth). The Spreadsheet component has no user interface for doing this, but the underlying engine does support it. To emulate multicolumn sorting, you can use the following routine:
'--------------------------------------------------------------------------
' MultiColumnSort
' Purpose: Sorts the spreadsheet by many columns at once
' In: References to the spreadsheet and range to sort,
' an array of column numbers on which to sort,
' and an array (same size) of direction indicators
' Out: Nothing (performs the sort)
'
Sub MultiColumnSort(Spreadsheet, Range, Columns, Directions)
' Start an undo unit so that this can undo as a whole unit of work
Spreadsheet.BeginUndo()
' Turn ScreenUpdating off so that the spreadsheet does not redraw
' while we are resetting filters, sorting, and reapplying filters
Spreadsheet.ScreenUpdating = False
' Now loop over the Columns and Directions arrays backwards,
' which will give us the effect we want
For ct = ubound(Columns) To lbound(Columns) Step -1
' 0 is a guess for column headings
Range.Sort Columns(ct), Directions(ct), 0
Next 'ct
' Turn ScreenUpdating back on so that the spreadsheet redraws
Spreadsheet.ScreenUpdating = True
' End the undo unit
Spreadsheet.EndUndo()
End Sub 'MultiColumnSort()
The trick to multicolumn sorts is to actually perform the sorts in the opposite order they're defined in. If you want to sort first by category and then by shipper, the routine first sorts the list by shipper and again by category. When the spreadsheet sorts a list by a new column, the previous ordering in another column is preserved within each item in the new column. The routine we just looked at accepts a range to sort, an array of column numbers, and an array of direction values (ascending or descending). The routine walks backwards along the two arrays, giving the effect of a multicolumn sort. Note that it also uses the BeginUndo and EndUndo methods to make all the sort operations part of one undo block so that they're undone together when the user chooses the Undo command.
The Spreadsheet component also sports a new AutoFilter user interface. The filtering functionality is similar to that found in Excel, but the AutoFilter drop-down lists in the user interface are a little different. Suppose that in the list of products we examined in Figure 2-2 you want to filter out some product categories to see how it would affect the products with high potential worth. The developer or user could turn on AutoFilter, click the AutoFilter arrow for the Category column, and see the screen shown in Figure 2-3.
[url=http://dev.csdn.net/javascript:fullSize(]
Figure 2-3. The Spreadsheet component's AutoFilter user interface.
In Excel, you can choose a single item quite easily; however, selecting more than one item requires using the Custom AutoFilter dialog box, which can be quite arduous when you simply want to exclude four or five items. In the Spreadsheet component, the AutoFilter drop-down list has check boxes next to each item, as well as a Show All item at the top that lets you quickly toggle all items between the checked and unchecked states.
The astute reader will notice that the AutoFilter drop-down lists do not include two of the useful settings found in Excel. For instance, you won't find a Top 10 item, which allows you to quickly filter for the top 10 (or any other number of) items. Nor will you find a Custom item, which you can use to perform more complex filtering than simple include and exclude filters allow. Unfortunately, these higher-level functions aren't built in to the Spreadsheet component yet. However, you can easily emulate them by making a few calls to the Spreadsheet control's programming model.
To emulate top N filtering, you can use the following routine:
'--------------------------------------------------------------------------
' TopNFilter
' Purpose: Filters for the top N items in the list given a column number
' In: References to the spreadsheet and range, column number,
' number of rows, and direction value that indicates
' top N or bottom N filtering
' Out: Nothing (performs the top N filter)
'
Sub TopNFilter(Spreadsheet, Range, ColumnNum, N, Direction)
Set c = Spreadsheet.Constants
Set rngData = Range
Set af = Spreadsheet.ActiveSheet.AutoFilter
' Start an undo unit so that this can undo as a whole unit of work
Spreadsheet.BeginUndo()
' Turn ScreenUpdating off so that the spreadsheet does not redraw
' while we are resetting filters, sorting, and reapplying filters
Spreadsheet.ScreenUpdating = False
' Clear any existing filters
ClearFilters Spreadsheet
' Sort the list in the data range by the column number
If LCase(Direction) = "bottom" Then
rngData.Sort ColumnNum, c.ssAscending, c.ssNo
Else
rngData.Sort ColumnNum, c.ssDescending, c.ssNo
End If
' Top N can actually include more than N rows if the N+1, N+2,
' and so on rows have the same value as the Nth row.
' Go to the N+1 row and see if it's the same as the Nth.
' Loop until there is a different value.
vNValue = rngData.Cells(N,ColumnNum).Value
While rngData.Cells(N+1,ColumnNum).Value = vNValue
N = N + 1
Wend
' N is now set to the number of rows we want to include in the filter
Set fltr = af.Filters(ColumnNum)
fltr.Criteria.FilterFunction = c.ssFilterFunctionInclude
For ct = 1 To N
fltr.Criteria.Add(rngData.Cells(ct,ColumnNum).Text)
Next
' Finally apply the AutoFilter
af.Apply
' Turn ScreenUpdating back on so that the spreadsheet redraws
Spreadsheet.ScreenUpdating = True
' End the undo unit
Spreadsheet.EndUndo()
End Sub 'TopNFilter()
Top N filtering might seem as easy as sorting and then viewing the first N rows. But true top N filtering can return more than N rows because it really means "include the top N values." If the tenth and eleventh values are identical after the sort, a top 10 filter will return both products since they are among the top 10 values. The previous code can perform both top and bottom N filters by merely changing the sort direction from descending to ascending.
Similarly, you can emulate expression-based filtering using a routine like this:
'--------------------------------------------------------------------------
' ExpressionFilter
' Purpose: Filters a list on a given column using an arbitrary expression
' that can be evaluated by VBScript
' In: References to the spreadsheet and range, column number to
' filter upon, and expression to use for evaluation
' Out: None (list is filtered)
'
Sub ExpressionFilter(Spreadsheet, Range, ColumnNum, Expression)
Dim sExp ' Temporary expression variable
Dim vValue ' Temporary value holder
Set c = Spreadsheet.Constants
Set rngData = Range
Set af = Spreadsheet.ActiveSheet.AutoFilter
' Start an undo unit so that this can undo as a whole unit of work
Spreadsheet.BeginUndo()
' Turn ScreenUpdating off so that the spreadsheet does not redraw
' while we are resetting filters, sorting, and reapplying filters
Spreadsheet.ScreenUpdating = False
' Clear any existing filters
ClearFilters Spreadsheet
' Get the filter object for the specified column,
' and set the filter function to "include"
Set fltr = af.Filters(ColumnNum)
fltr.Criteria.FilterFunction = c.ssFilterFunctionInclude
' Check whether the expression contains the column value token,
' and set a flag if it does
fValueToken = cbool( _
instr(1, Expression, g_sValueToken, vbTextCompare) > 0)
' Loop over the column values in all the rows
For Each cell In rngData.Columns(ColumnNum).Cells
' Get the current row's value
vValue = cell.Value
' If vValue is a string, we need to wrap quotes around it in
' case it contains spaces
If vartype(vValue) = vbString Then
vValue = """" & vValue & """"
End If
' Build the expression we need to execute by inserting the
' current row's value in the right place
If fValueToken Then
sExp = "g_fEval = cbool(" & Replace(Expression, _
g_sValueToken, vValue, 1, -1, vbTextCompare) & ")"
Else
sExp = "g_fEval = cbool(" & vValue & " " & Expression & ")"
End If
' Execute the expression
window.execScript sExp, "VBScript"
' The global g_fEval will now be set to True or False.
' If True, the row should be included in the filter.
If g_fEval Then
fltr.Criteria.Add cell.Text
End If
Next 'ct
' Finally apply the AutoFilter
af.Apply
' Turn ScreenUpdating back on so that the spreadsheet redraws
Spreadsheet.ScreenUpdating = True
' End the undo unit
Spreadsheet.EndUndo()
End Sub 'ExpressionFilter()
This routine uses a Document Object Model (DOM) method named execScript to evaluate expressions. (DOM is the programming model exposed to scripting in Internet Explorer.) This method passes the script code in string form to the Active Scripting Engine (in this case, VBScript) for evaluation. The script code stores the result of the expression in a global variable that is then used to determine whether the expression is True or False. If the expression is True, the row is included in the filtered set; if False, the row is not included.
Alternatively, you can evaluate expressions by using the Eval method of the Spreadsheet component's Worksheet object. Eval uses the Spreadsheet component's function libraries and expression evaluator instead of the active scripting engine, meaning it's useful in containers other than Internet Explorer or when you want to let users include spreadsheet functions or range references in the expression. However, the active scripting engine can give you a powerful expression evaluator. Plus, it allows you to use other scripting languages, such as ECMA Script (also known as JavaScript).
