Translator's description: Welcome to my blog: http://daidaoke.donews.net/daidaoke/
The mistake in the translation is not to point out.
My email: tangtaike@163.com
For reprint, please notify us in advance.
Sort and filter
When reading this section, if you open the sortfilterexample.htm file under the Samples / Chap02 directory of the book disc. You will find this example is useful. This section shows the code and the specific environment described by this file.
The spreadsheet component supports the sorting and filtering functionality in Excel, and provides these features through programming models and user interfaces. However, in the user interface of the spreadsheet, sorting and filtration functions have some degree of extent to Excel. Let's take an example.
DHTML adventure
The attribute toolbox is an incredible masterpiece by the two OWC groups, Eric Matteson, and Cesar Alvarez developed, the attribute toolbox also proves that trying to use DHTML to imitate the Office's user interface style is a difficult thing. Early, we insist that we should make the property toolbox similar to the standard Office user interface, Eric and Cesar also spend several months of time to change HTML and IE browser so that it can meet the needs. Most people don't believe that the results are actually in HTML. However, because considering that general people can use the Web site very effective, they are also confused by many of the advanced dialogs in the Office application, so regarding the use of HTML to simulate whether the Office user interface makes the control easier to use. It is very prominent.
For developers who wish to use Office Web Components in the web page, my suggestion is not to waste time attempt to integrate HTML into the interface of the traditional Microsoft form application, and the advantage of HTML's simple and dynamic layout. To develop a more natural and easy-to-use interface for your application.
Suppose you have developed a spreadsheet for listing your current product line, the form of a unit price, inventory, quantity, and order quantity, and a quantity of sales rate, Show potential value. Now the user needs to be aligned in descending the product list according to the potential value of the product. The user can select the area where you need to sort in the spreadsheet user interface (or select any units in the area), and click the Descending Toolbar button. When the button is clicked, the menu in an Excel is displayed under the button, as shown in Figure 2-2.
Figure 2-2 Used spreadsheet components user interface
One of the common problems encountered when sorting the Excel area is to select the area to be sorted and select which column is sorted. The spreadsheet assembly allows users to easily select the area where the user needs to be sorted, and then display a list of column names when the user clicks the ascending or descending order of the toolbar, and the user can select which list can be sorted. Sort by the sort method of the Range object can also be used. It allows developers to easily complete a list of sorting when the user clicks or double-click a column header.
You can notice that the spreadsheet component can only be sorted according to a column at a time. Excel provides a sort dialog that allows you to sort by up to three key columns (eg, whether or not sorted according to the species, then sort by the shipper, finally sorted according to the potential value). The spreadsheet component does not complete the user interface of this function, but the underlying engine is supported. You can use the following functions to simulate multiple columns.
'------------------------------------- ------------------------- 'Multicolumnsort' Objective: To sort the spreadsheet according to Multi-column: Sort in the spreadsheet The reference, the array of column numbers of sorted columns, the array of 'sorting direction markers (the same size as the above array)' Output: None (Completed Sort Action) 'SUB MulticolumNsort (Spreadsheet, Range, Column, Directions) Start an undo unit so you can cancel the spreadsheet.Beginundo () closing spreadsheet.beginundo () in a full unit's task so that when we set filter, sort 'and then filter, the spreadsheet will not redraw spreadsheet.screenupdating = false
'Now deluxe COLUMNS arrays and Directions arrays,' Implementing the effect we need for CT = ubound (columns) to lbound (columns) Step -1 '0 is a guess for column Headings Range.Sort Column (CT), Directions (CT) ), 0 Next 'CT' Opens the ScreenupDating property switch, makes spreadsheetsheet.screenupdating = true 'end the undo unit spreadsheet.ndundo () end sub' multicolumnsort ()
Tips for achieving multi-column sorting are actually executed in the opposite order of the sequences defined by the sort sequence. For example, if you need to sort according to the category, then sort by the shipper, the function will first sort the list according to the delivery manager, and then sort according to the category. When the spreadsheet is based on a new column sequence list, each of the norms (a set of rows of the same column value in the norm) will remain unchanged according to the other column. The function we just read received three parameters: a region that needs to be sorted, an array of column numbers, an array of direction values (descending or ascending). Function descending sequentially traversed these two arrays, thereby achieving multiple column sorting effects. Note that the function also uses the Beginundo and Endundo methods to combine all sort operations in an undo action block so that when the user selects the undo command, these sorting action will be revoked together.
The spreadsheet components also support a new automatic filtering user interface. Filtering function in the component is similar, but the automatic filter drop-down list in the component user interface has some different places. Suppose you need to filter out some product categories from some product categories from Figure 2-2 to observe how it will affect high potential value. Developers or users can open the automatic filtering function, click on the automatic filter arrow on the category column, and see the interface shown in Figure 2-3.
Figure 2-3. Automatic filtering user interface for spreadsheet components
In Excel, you can choose a single item very convenient, however, you need to use the Advanced Automatic Filter dialog box when you just want to rule out four or five items. In the spreadsheet component, the automatic filtering list provides a simplified check box for each item, and there is a "display all" item at the top, so you can quickly switch the status of all items (select or don't choose) . The alert reader will find that the automatic filter drop-down list in the spreadsheet component does not include two setup options that are useful in Excel. For example, you can't find the "top 10" option, this option allows you to quickly filter, get the top 10 (or the first N-bit) item. You can't find the "Custom" option, this option allows you to complete the filtering capabilities than simple filtration complicated than simple. Unfortunately, these more advanced features are not included in the spreadsheet components. However, you can easily simulate these features by calling the programming model of the spreadsheet control.
You can use the following functions to simulate the "front n bit" filtering function:
'------------------------------------- ------------------------- 'TOPNFILTER' Purpose: Filter out the list of previous N items in the list: spreadsheets and regions The reference, column number, the number of rows of rows to filter, as well as the front N row, still N rings, and the direction of the N rows 'OUT: None (Complete the desired filter)' Sub Topnfilter (Spreadsheet, Range, ColumnnnUm, N, direction) set c = spreadsheet.constants set rngdata = range set AF = spreadsheet.activeesheet.autofilter 'launches a revocation unit so that it can revoke Spreadsheet.Beginundo () in full cells in full cells
'Close the ScreenupDating property switch so that when we filter, sort and apply' filtering, the spreadsheet does not redraw spreadsheet.screenupdating = false 'Clear any existing filtering definition Clearfilters Spreadsheet
'In a given data area, based on the column column list IF LCASE (Direction) = "Bottom" Then Rngdata.Sort ColumnnnnnnnnnnnnnnnnnnnUm, C.ssdescending, C.sssDescending, C.ssno End if 'If the row of n 1, n 2, etc. is the same value,', the result of "pre-n bit" filtration can contain a row of more than N. 'Therefore, it is the same value as the N 1 and the value of the N 1,' until it is found to find a different value. VnValue = rngdata.cells (n, color) .value while rngdata.cells (n 1, color) .Value = vnvalue n = n 1 Wend 'n The current value is that we need to include rows in the filter results number. Set fltr = af.Filters (ColumnNum) fltr.Criteria.FilterFunction = c.ssFilterFunctionInclude For ct = 1 To N fltr.Criteria.Add (rngData.Cells (ct, ColumnNum) .Text) Next 'the last reference to automatically filter af.Apply 'Open the ScreenupDating property switch, so that the spreadsheet will redraw spreadsheet.screenupdating = true' End undo unit spreadsheet.ndundo ()
End sub 'TOPNFILTER ()
"Pre-N bit" filtering function seems very simple, just sort first, then check the first N line. But the true "front n bit" filtering function may return more than N rows because it actually means "those rows including the value of the previous N-bit". If the value of the 10th, 11th bit is the same after sorting, "the top 10" filter returns these products together because they are all in the top 10 bits. Further, the above code implements the function of filtering the previous N-bit and the rear n bit by simply changing the sort direction (ascending sequence).
Similarly, you can simulate an expression-based filtering function by using the functions shown below:
'------------------------------------- ------------------------- 'EXPRESSIONFILTER' Purpose: Use a expression that can be calculated by VBScript, a list of a given column ' Enter: Point reference to the spreadsheet and area, filtering the number, and used to filter the expression. 'Output: None (list is filtered)' Sub ExpressionFilter (Spreadsheet, Range, ColumnNum, Expression) Dim sExp 'temporary variable expression Dim vValue' temporary storage variable Set c = Spreadsheet.Constants Set rngData = Range Set af = Spreadsheet.ActiveSheet .Autofilter
'Start a revocation unit so that you can cancel the work as a complete unit. Spreadsheet.beginundo () 'Turn off the ScreenupDating property switch to reset the filter attribute, sort and apply filter' when the filter is woading.
Spreadsheet.screenupdating = false 'Clear any existing filter settings Clearfilters Spreadsheet
'Get the filter object of the specified column and set the filter function attribute to "contain" set fltr = af.filters (columnnnum) fltriteria.filterfunction = C.ssfilterFunctionInClude
'Check if the expression contains a replacement of the column value,' If set, set the flag fvaluetoken = cbool (_ instr (1, expression, g_svaluetoken, vbtextcompare)> 0)
'Values for this column in each line for Each Cell in rngdata.columns (color). Cells' Get the value of the current unit vValue = Cell.Value 'If VVALUE is a string, in order to prevent the case where there is a space,' We need In its front and rear plus quotation IF VARTYPE (VVALUE) = VBString the vvalue = "" & vvalue & "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" " Suitable location If fvaluetoken dam = "g_Feval = CBool (" & Replace (Expression, _ g_svaluetoken, vvalue, 1, -1, vbtextcompare) & ")" else sexp = "g_Feval = CBool (" & Vvalue & " & Expression & ")" Endiff "Performs Expressions Window.execscript SEXP," VBScript "Global Variable G_FEVAL has now been set to true or false. 'If true, the row will be included in the filter results. IF g_feval Then fltr.criteria.add cell.text end if next 'CT
'Finally perform automatic filtering AF.Apply
'Open the ScreenupDating switch, make the spreadsheet can redraw spreadsheet.screenupdating = true' end revocation unit spreadsheet.endundo ()
End sub 'expressionfilter ()
The above function performs expressions using the document object model (DOM) Method (DOM is a programming model provided for scripting in Internet Explorer). This method passes the script code in the form of a string to the dynamic engine script (in this example, it is VBScript) to calculate. The script code stores the result of the expression in a global variable so that the result can be used to determine the true and false expression. If the expression is true, the row will be included in the filtered collection; if it is a false, the row will be excluded. In addition, you can also calculate the expression using the Eval method in the worksheet object of the spreadsheet component. Eval uses the library and expression calculator of the spreadsheet component, which can replace the dynamic script engine, which means it will be very useful in containers other than IE, and you want users to use electronics in expressions It will also be useful when a function or quote of the table is also useful. However, the dynamic scripting engine can provide a powerful expression calculator. Also, it allows you to use other scripting languages, such as ECMA scripts (also known as JavaScript).
Appendix: English
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 not 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 10 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 Stregths to Develop A More Natural, Easy-Use 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.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 Emulation Multicolumn Sorting, You Can Use The Following Routine:
'------------------------------------- ------------------------- 'Multicolumnsort' Purpose: Sorts The Spreadsheet by Many Column The Spread '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 differe nt. Suppose that in the list of products we examined in The developer or user could turn on AutoFilter, click the AutoFilter arrow Figure 2-2 you want to filter out some product categories to see how it would affect the products with high potential worth. For The Category Column, And See The Screen Shown in Figure 2-3.
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 will not 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 are not built in to the Spreadsheet component yet. However, you can easily emulate them by making a few calls to the Spreadsheet Control's PROG Ramming model.
To Emulate Top N filtering, you can use the following routine:
'------------------------------------- ------------------------- 'TopnFilter' Purpose: Filters for the top N items 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 reviewing 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, columnnnum) .value while rngdata.cells (n 1, color) .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 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 "incrude" 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 & "& expness &") "endiff" Execute the expression window.execscript sexp, "VBScript" 'The Global G_Feval Will Now Be set to true ife, the row shop be include in the filter. If g_feval the flter .cri Teria.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.ndundo ()
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 Uses Include Spreadsheet Functions Or Range References in The Expression Engine Can Give You a Powerf UL Expression Evaluator. Plus, IT Allows You To Use Other Scripting Languages, Such As ECMA Script (Also Known AS JavaScript).
