Macros/Calc/ba024/es

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

• Inicio
• Desarrollo
• Diseño
• Control de calidad
• Eventos
• Documentación
• Sitio web
• Regionalización
• Accesibilidad
• Mercadotecnia
• Diversity
• Wiki Help

• Macros
• Macros Generales
• Macros para Writer
• Macros para Calc
• Macros para Impress
• Macros para Draw
• Macros para Base
• Macros para Math
• Basic/Python

Summary

This page provides a LibreOffice Basic macro procedure that sorts a table based on the values in multiple columns. It is an example drawn from Chapter 13 ("Calc as a Database") of the 7.0 Calc Guide.

Description

A range can be sorted using up to three columns or rows in a macro. Sorting with extra columns or rows is as easy as adding extra sort fields to a sort descriptor. The macro below uses the class grade sheet in cells $Sheet1.A1:H11 of the linked spreadsheet to illustrate how to sort by two columns. The records are sorted in ascending order, first by Quiz #1 scores and then by Quiz #2 scores.

Code

Sub SortByQuizScores
  Dim oSheet
  Dim oRange
  Dim oSortFields(1) as new com.sun.star.util.SortField
  Dim oSortDesc(0) as new com.sun.star.beans.PropertyValue

  oSheet = ThisComponent.Sheets(0)

  REM <span lang="en" dir="ltr" class="mw-content-ltr">Set the range on which to sort</span>
  oRange = oSheet.getCellRangeByName("A1:H11")

  REM <span lang="en" dir="ltr" class="mw-content-ltr">Sort by the Quiz #1 field in the range</span>
  oSortFields(0).Field = 4
  oSortFields(0).SortAscending = True
  oSortFields(0).FieldType = com.sun.star.util.SortFieldType.NUMERIC

  REM <span lang="en" dir="ltr" class="mw-content-ltr">Sort by the Quiz #2 field in the range</span>
  oSortFields(1).Field = 5
  oSortFields(1).SortAscending = True
  oSortFields(1).FieldType = com.sun.star.util.SortFieldType.ALPHANUMERIC

  REM <span lang="en" dir="ltr" class="mw-content-ltr">Set the sort fields to use</span>
  oSortDesc(0).Name = "SortFields"
  oSortDesc(0).Value = oSortFields()
  
  REM <span lang="en" dir="ltr" class="mw-content-ltr">Now sort the range!</span>
  oRange.Sort(oSortDesc())
End Sub

This Calc spreadsheet contains the above LibreOffice Basic code.