排序和过滤
阅读本节时，如果您打开随书光盘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)。