Macros/Calc/ba026/es

This page is a translated version of the page Macros/Calc/ba026 and the translation is 3% complete.

TDF LibreOffice en español Document Liberation Project Blogs comunitarios Weblate Nextcloud Redmine Preguntas y respuestas Donar

Other languages:

Summary

This page provides a LibreOffice Basic macro procedure that creates a simple standard filter. It is an example drawn from Chapter 13 ("Calc as a Database") of the 7.0 Calc Guide.

Description

In a macro, filtering a data range is performed by calling the filter() method either on the range or the sheet in which the range is contained. Like a sort descriptor, a filter descriptor contains the filter settings for the current sheet, such as whether the first row or column in the sheet contains headers which should not be filtered. A filter descriptor can be generated by calling the createFilterDescriptor() method on a sheet or on a cell range:

createFilterDescriptor(Empty): Creates a filter descriptor. If the boolean flag Empty is set to TRUE, then an empty filter descriptor is created. If Empty is FALSE, then the descriptor is filled with the previous settings of the current object (such as a database range).

As with sort descriptors, filter descriptors can be created using this method, then modified and passed as an argument to the filter method. The macro below demonstrates this process by creating a simple standard filter for the first sheet in a document.

Nota:
When the filter method is called on a sheet, every empty row in the sheet is hidden. When filter is called on a range, only empty rows in the range itself are hidden.

Code

Sub SimpleSheetFilter()
  Dim oSheet       ' <span lang="en" dir="ltr" class="mw-content-ltr">Sheet that will contain the filter.</span>
  Dim oFilterDesc  ' <span lang="en" dir="ltr" class="mw-content-ltr">Filter descriptor.</span>
  Dim oFields(0) As New com.sun.star.sheet.TableFilterField
  
  oSheet = ThisComponent.getSheets().getByIndex(0)
  
  REM <span lang="en" dir="ltr" class="mw-content-ltr">If argument is True, creates an empty filter</span>
  REM <span lang="en" dir="ltr" class="mw-content-ltr">descriptor. If argument is False, create a</span> 
  REM <span lang="en" dir="ltr" class="mw-content-ltr">descriptor with the previous settings.</span>
  oFilterDesc = oSheet.createFilterDescriptor(True)
  
  With oFields(0)
    REM <span lang="en" dir="ltr" class="mw-content-ltr">You could use the Connection property to indicate</span>
    REM <span lang="en" dir="ltr" class="mw-content-ltr">how to connect to the previous field. This is</span>
    REM <span lang="en" dir="ltr" class="mw-content-ltr">the first field, so this is not required.</span>
    '.Connection = com.sun.star.sheet.FilterConnection.AND
    '.Connection = com.sun.star.sheet.FilterConnection.OR

    REM <span lang="en" dir="ltr" class="mw-content-ltr">The Field property is the zero based column</span>
    REM <span lang="en" dir="ltr" class="mw-content-ltr">number to filter. If you have the cell, you</span>
    REM <span lang="en" dir="ltr" class="mw-content-ltr">can use .Field = oCell.CellAddress.Column.</span>
    .Field = 5	' <span lang="en" dir="ltr" class="mw-content-ltr">The Quiz #2 grades field</span>

    REM <span lang="en" dir="ltr" class="mw-content-ltr">Compare using a numeric or a string?</span>
    .IsNumeric = True

    REM <span lang="en" dir="ltr" class="mw-content-ltr">The NumericValue property is used</span> 
    REM <span lang="en" dir="ltr" class="mw-content-ltr">because .IsNumeric = True from above.</span>
    .NumericValue = 80

    REM <span lang="en" dir="ltr" class="mw-content-ltr">If IsNumeric was False, then the</span> 
    REM <span lang="en" dir="ltr" class="mw-content-ltr">StringValue property would be used.</span>
    REM <span lang="en" dir="ltr" class="mw-content-ltr">.StringValue = " what ever"</span>

    REM <span lang="en" dir="ltr" class="mw-content-ltr">Valid operators include EMPTY, NOT_EMPTY, EQUAL,</span>
    REM <span lang="en" dir="ltr" class="mw-content-ltr">NOT_EQUAL, GREATER, GREATER_EQUAL, LESS,</span>
    REM <span lang="en" dir="ltr" class="mw-content-ltr">LESS_EQUAL, TOP_VALUES, TOP_PERCENT,</span>
    REM <span lang="en" dir="ltr" class="mw-content-ltr">BOTTOM_VALUES, and BOTTOM_PERCENT</span>
    .Operator = com.sun.star.sheet.FilterOperator.GREATER_EQUAL
  End With
  
  REM <span lang="en" dir="ltr" class="mw-content-ltr">The filter descriptor supports the following</span>
  REM <span lang="en" dir="ltr" class="mw-content-ltr">properties: IsCaseSensitive, SkipDuplicates,</span>
  REM <span lang="en" dir="ltr" class="mw-content-ltr">UseRegularExpressions,</span> 
  REM <span lang="en" dir="ltr" class="mw-content-ltr">SaveOutputPosition, Orientation, ContainsHeader,</span> 
  REM <span lang="en" dir="ltr" class="mw-content-ltr">CopyOutputData, OutputPosition, and MaxFieldCount.</span>
  oFilterDesc.setFilterFields(oFields())
  oFilterDesc.ContainsHeader = True
  oSheet.filter(oFilterDesc)
End Sub

This Calc spreadsheet contains the above LibreOffice Basic code.