Programming MS Office 2000 Web Components Chapter I Section I (Part II)

Sorting and filtering

When reading this section, if you open the sortfilterexample.htm file under the samples \ chap02directory of the book CD. You will find this example useful. The code and the specific environment described in this section are from this file.

The workbook component supports basic sorting and filtering functions in Excel and provides these functions through programming models and user interfaces. However, in the workbook user interface, the sorting and filtering functions are somewhat enhanced than excel. Let's look at an example.

 

DHTMLAdventure

The attribute toolkit is an incredible masterpiece developed by Eric matteson and Cesar Alvarez, a talented programmer in two owc groups, the property toolbox also proves that it is difficult to try to use DHTML to imitate the office user interface style. In the early days, we insisted that we should make the attribute toolbox as similar as the standard office user interface as possible. Eric and Cesar did spend several months to change HTML and IE browsers, so that it can meet the needs. Most people do not believe that the results are actually in HTML. However, although most people can use the web site effectively, it is still confused by many advanced dialogs in office applications, therefore, the debate on whether HTML is used to simulate the office user interface to make controls easier to use is still very prominent.

 

For developers who want to use Office Web Components on web pages, I suggest not to waste time trying to integrate HTML into the traditional Microsoft forms application interface, however, we should take advantage of the simplicity and Dynamic Layout of HTML to develop a more natural and easy-to-use interface for your applications.

 

Suppose you have developed a workbook for listing your current product line. The workbook shows the unit price, inventory quantity, and order quantity of each product, there is also a sales rate calculation column to show potential value. Now, users need to sort the product list in descending order based on the potential value of the product. On the workbook user interface, you can simply select the area to be sorted (or select any units in the area) and click the drop-down toolbar button. When a button is clicked, a menu that does not exist in Excel is displayed below the button, as shown in 2-2.

 

 

Figure 2-2Workbook Component User Interface in use

 

One of the common problems that users encounter when sorting an Excel area is to select the area to be sorted and the column to sort. The workbook component allows you to easily select the area to be sorted. Then, when you click the ascending or descending sort button on the toolbar, a list of column names is displayed, this allows you to select which columns to sort. You can also use the sorting function through the sort method of the range object. It allows developers to conveniently sort a list by clicking or double-clicking a column header.

 

You have noticed that the workbook component can only sort the list by one column at a time. Excel provides a sorting dialog box that allows you to sort by a maximum of three key columns at the same time (for example, whether to sort by type, sort by shipper, and finally sort by potential value ). The workbook component does not have a user interface to complete this function, but the underlying engine supports this function. You can use the following functions to simulate multi-column sorting.

'-------------------------------------------------------------------------- ' MultiColumnSort 'Purpose: Sort workbooks by multiple columns at the same time 'Input: reference of the area to be sorted in the workbook, 'Array of column numbers of sorted columns, 'Sort direction indicator array (Same size as the above array) 'Output: none (sorting completed) ' Sub MultiColumnSort(Spreadsheet, Range, Columns, Directions) 'Start an undo unit so that you can undo the task in a complete unit.     Spreadsheet.BeginUndo()   'Disable the screenupdating attribute so that when we set filter and sort 'And the workbook will not be repainted when the filter is performed.     Spreadsheet.ScreenUpdating = False   'Now, the columns array and the ctions array are gradually traversed, 'Achieve the effect we need     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     'Enable the screenupdating attribute to make the workbook redraw.     Spreadsheet.ScreenUpdating = True         ' End the undo unit     Spreadsheet.EndUndo()         End Sub 'MultiColumnSort()

 

The trick to implement multi-column sorting is to execute the sorting action in reverse order of the Order Defined by the sorted column. For example, if you want to sort by category first and then by shipping provider, the function first sorts the list by shipping provider and then by category. When a workbook is sorted by a new column, the order of each item in the new column (a group of rows with the same column value in the new column) will remain unchanged. The function we just read receives three parameters: an area to be sorted, an array of column numbers, and an array of direction values (in descending or ascending order ). The function traverses these two arrays in descending order to achieve multi-column sorting. Note that the beginundo and endundo methods are also used to combine all sorting operations in one undo action block, so that when the user selects the undo command, these sorting actions will be revoked together.

 

The workbook component also supports a new user interface that is automatically filtered. The filter function in the component is similar to the filter function in Excel, but the automatic filter drop-down list in the component user interface has some differences. Suppose you need to filter out some product categories from the product list we just checked in Figure 2-2 to see how it will affect high-potential products. Developers or users can enable the automatic filtering function. Click the automatic filtering arrow in the Category column to see the interface shown in Figure 2-3.

 

 

Figure 2-3. automatically filter the user interface of the workbook component

 

In Excel, you can easily select a single item. However, to select multiple items, you need to use the advanced automatic filtering dialog box. This operation is very difficult if you only want to exclude four or five items. In the workbook component, the automatic filter drop-down list provides a simplified check box for each item, and there is a "show all" item at the top, this allows you to quickly switch the status of all items (select or not ).

 

Alert readers will find that the drop-down list of automatic filtering in the workbook component does not include two useful settings in Excel. For example, you cannot find the "first 10 bits" option. This option enables you to quickly filter out the first 10 bits (or the first N bits. You cannot find the "Custom" option. This option allows you to implement more complex filtering functions than simply including or not including filtering. Unfortunately, these more advanced features are not included in the workbook component. However, you can easily simulate these functions by calling the Spreadsheet control's programming model.

 

You can use the following functions to simulate the "First N bits" filter function:

'-------------------------------------------------------------------------- ' TopNFilter 'Purpose: filter the first n items in the list based on the given column number. 'Input: Reference and column number of the workbook and region, 'The number of rows to be filtered out, and the direction value of the first n rows or the last n rows 'Out: none (filtering required) ' 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 it can be undo with a complete unit in the future     Spreadsheet.BeginUndo()   'Disable the screenupdating attribute so that when we set filter, sort, and apply again 'When filtering, the workbook will not be repainted.     Spreadsheet.ScreenUpdating = False     'Clear any existing filter definitions     ClearFilters Spreadsheet   'Sort the list of columns according to the passed column number in the given data area.     If LCase(Direction) = "bottom" Then         rngData.Sort ColumnNum, c.ssAscending, c.ssNo     Else         rngData.Sort ColumnNum, c.ssDescending, c.ssNo     End If     'If n + 1, n + 2, and so on have the same value as row N, Then, the result of the "First N bits" filter can contain more than N rows. 'So check whether N + 1 and other rows are the same as the value of row N in a loop, 'Until a different value is found.     vNValue = rngData.Cells(N,ColumnNum).Value         While rngData.Cells(N+1,ColumnNum).Value = vNValue         N = N + 1     Wend     The current value of 'n' is the number of rows that need to be included in the filtering result.     Set fltr = af.Filters(ColumnNum)     fltr.Criteria.FilterFunction = c.ssFilterFunctionInclude         For ct = 1 To N         fltr.Criteria.Add(rngData.Cells(ct,ColumnNum).Text)     Next     'Automatic filtering of last reference     af.Apply   'Enable the screenupdating attribute to make the workbook redraw.     Spreadsheet.ScreenUpdating = True     'End undo Unit     Spreadsheet.EndUndo()   End Sub 'TopNFilter()

 

The "First N bits" filtering function seems very simple. You only need to sort the data first and then view the first n rows. However, the true "First N bits" filter function may return results that exceed n rows, because it actually means "rows that contain the first N bits ". If the values of the 10th and 11th bits are the same after sorting, the "first 10 bits" filter will return these products together because they are all in the top 10 bits. In addition, the above Code simply filters out the first N bits and the last n BITs by changing the sorting direction (ascending and descending.

 

Similarly, you can use the following functions to simulate expression-Based Filtering:

'-------------------------------------------------------------------------- ' ExpressionFilter 'Purpose: use an expression that can be computed by VBScript to filter the list on a given column. 'Input: the column number to be filtered and the expression to be filtered by pointing to the reference of the workbook and area. 'Output: none (list filtered) ' Sub ExpressionFilter(Spreadsheet, Range, ColumnNum, Expression) Dim sexp temporary expression variable Dim vvalue' temporary storage variable         Set c = Spreadsheet.Constants     Set rngData = Range     Set af = Spreadsheet.ActiveSheet.AutoFilter   'Start an undo Unit so that it can be undone as a complete unit in the future.     Spreadsheet.BeginUndo()   'Disable the screenupdating attribute toggle so that the workbook will not be repainted when the filter attribute is reset, sorted, and filtered again .'     Spreadsheet.ScreenUpdating = False     'Clear any existing filtering settings     ClearFilters Spreadsheet   'Get the filtering object of the specified column and set the filtering attribute to "include"     Set fltr = af.Filters(ColumnNum)     fltr.Criteria.FilterFunction = c.ssFilterFunctionInclude   'Check whether the expression contains a column Value Replacement character, 'Set the flag if it contains     fValueToken = cbool( _         instr(1, Expression, g_sValueToken, vbTextCompare) > 0)   'Traverse the value of this column in each row     For Each cell In rngData.Columns(ColumnNum).Cells 'Get the value of the current Unit         vValue = cell.Value         'If vvalue is a string, to prevent spaces from being contained in it 'We Need To enclose it with quotation marks         If vartype(vValue) = vbString Then                vValue = """" & vValue & """"         End If         'Makes up the expression we need to execute and inserts the value of the current row into the expression 'Appropriate location         If fValueToken Then             sExp = "g_fEval = cbool(" & Replace(Expression, _                 g_sValueToken, vValue, 1, -1, vbTextCompare) & ")"        Else             sExp = "g_fEval = cbool(" & vValue & " " & Expression & ")"         End If         'Execution expression         window.execScript sExp, "VBScript"         'The global variable g_feval has been set to true or false. 'If it is true, this row will be included in the filter result.         If g_fEval Then             fltr.Criteria.Add cell.Text         End If     Next 'ct   'Finally, execute automatic filtering.     af.Apply   'Enable screenupdating so that workbooks can be repainted.     Spreadsheet.ScreenUpdating = True     'End withdrawal Unit     Spreadsheet.EndUndo()   End Sub 'ExpressionFilter()

 

The above functions use the execScript method execution expression in the Document Object Model (DOM) (Dom is the programming model provided for scripts in Internet Explorer ). In this method, the string script code is passed to the dynamic engine script (VBScript in this example) for computation. The script code then stores the expression results in a global variable so that the results can be used to determine whether the expression is true or false. If the expression is true, the row is included in the filtered set. If the expression is false, the row is excluded.

 

You can also use the eval method in the worksheet object of the workbook component to calculate the expression. Eval uses the function library and expression calculator of the workbook component. It can replace the dynamic script engine, which means it is very useful in containers outside of IE, in addition, it is useful when you want to allow users to use the function or region reference of a workbook in expressions. However, the dynamic Script Engine provides a powerful expression calculator. Also, it allows you to use other scripting languages, such as ECMA scripts (also known as JavaScript ).