Альтернативный способ фильтрации циклом for to next

Очень часто в документах на одном листе необходимо удалить несколько строк данных с разными значениями по нескольким параметрам. Если строк в документе на так много, а налаживать программно множество фильтров нецелесообразно можно удалить такие строки при помощи цикла for to next. Он удаляет данные построчно и работает чуть медленнее классических фильтров, но все же удобен в небольших документах.

Рассмотрим пример кода на конкретной задаче - из файла Реестр документов по МХ нам нужно удалить данные "Название контрагента"  - "Производство, Пекарня, Столовая для персонала", т.е. оставив только цеха фасовки:

Sub Delete_for

' Обозначаем текущий активный диапазон листа (первая левая верхняя - последняя нижняя

' правая ячейка)  - стандартная функция

 oSheet = ThisComponent.CurrentController.getActiveSheet()

 myrows=oSheet.getrows

 oCellCursor = oSheet.createCursor()

 oCellCursor.GotoStartOfUsedArea(True)

 oCellCursor.GotoEndOfUsedArea(True)

 Start_Row = oCellCursor.getRangeAddress.StartRow+1

 End_Row = oCellCursor.getRangeAddress.EndRow + 1

 For i=End_Row To Start_Row step -1

' Вводим переменную text1 и указываем в каком столбце нужно будет искать ее значения

' В данном случае в 6-м столбце (5,i)

  text1 = osheet.getcellbyposition(5,i).String

'Вводим условие if then - какие значения нам нужно искать, если значений несколько они 'соединяются оператором OR. Функция Left - дает нам строку из указанного к ней в скобках

' количества симоволов, например для слова Производство - это 12 символов

   If Left(text1,12) = "Производство" OR Left(text1,7) = "Пекарня" OR Left(text1,22) = "Столовая для персонала"  Then

'Говорим функции, что сделать с найденными строками - в данном случае удалить

    myrows.removebyindex(i,1)

'Закрываем условный оператор и цикл:

   End if   

   Next i

End Sub

Задача: В Файле Реестр документов по МХ, в колонке Название типа операции удалить программно циклом подтипы Производство АР, Утиль производства ГМ, Питание персонала

Наборы операций с данными рабочего листа

В этом посте разберем основные используемые в работе функции рабочего листа, такие как вставка/удаление столбцов/строк, копирование столбцов в другое место, автоширина столбцов, объединение ячеек и др. На каждую функцию будет приведен готовый код, который просто копируется в нужную часть макроса с заменой параметров.

Удаление столбцов:

ThisComponent.Sheets(0).Columns.removeByIndex(0,0) 

Данный код удаляет столбец 1 на листе 1.

Удаление строк:

ThisComponent.Sheets(0).Rows.removeByIndex(0,0) 

В случае когда нужно удалить несколько столбцов (строк) код можно скопировать, не забывая о том что после выполнения верхних строчек кода порядок изменится и нужно удалять столбцы (строки) с учетом оставшихся.
Более простым путем при удалении нескольких смежных столбцов является удаление через явное указание диапазона. следующий код удаляет столбцы с 1 по 3 (А-С) с листа 1:

oSheet = ThisComponent.Sheets (0)
oRangeAddress = oSheet.getCellRangeByName("A1:C65536").getRangeAddress()
oSheet.removeRange(oRangeAddress,  com.sun.star.sheet.CellDeleteMode.LEFT)

Установку оптимальной ширины столбцов лучше проводить через цикл for to, указав в коде необходимый лист и номера стоблцов с ...по который нужно установить автоширину. Например, следующий код устанавливает оптимальную ширину  столбцов с 1 по 6 на листе 1:

For I = 0 To 5
Columns = ThisComponent.Sheets (0).Columns(I)
Columns.OptimalWidth = True
Next I

Копирование столбца в другое место может потребоваться в нескольких случаях. Самый распространенный - некоторые данные нужно поставить вперед предыдущих -  поменять местами столбцы с номером документа и датой документа. Копирование выполняет след код:

Sub Copy_Columns
'Опеределяем столбец для копирования, в данном случае столбец 3 на листе 1
oCol = ThisComponent.Sheets(0).getColumns().getByIndex(2)
' Выбираем его
ThisComponent.getCurrentController().select(oCol)
' Вызываем функцию копирования
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
'Определяем верхнюю ячейку столбца куда хотим скопировать, в данном случае это А1
oCell = ThisComponent.Sheets(0).GetCellbyPosition( 0, 0 )
ThisComponent.CurrentController.Select(oCell)
'Производим вставку
dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())
End Sub

Объединение ячеек выполняет след. код:

oRange = ThisComponent.Sheets(0).getCellRangeByName("A1:E1")
oRange.merge(True)

В данном случае мы объединили ячейки A1:E1 на  листе 1

Для вставки столбцов/строк вместо функции remove применяется insert, сам код выглядит также:

ThisComponent.Sheets(0).Columns.insertByIndex(0,0) 
ThisComponent.Sheets(0).Rows.insertByIndex(0,0) 

Задача: В файле реестр документов по МХ поменять местами столбцы Номер документа и Дата документа, удалить столбцы Шифр Контрагента, Пользователь и DETAIL применив различные способы.

Сортировка

Сортировка данных программным методом важная часть макроса - сортировка помогает ускорить процесс обработки данных и сразу представить их пользователю в удобном для восприятия виде.
Рассмотрим код:

Sub Sort
Dim oSheet
Dim oRange
Dim oSortFields(0) as new com.sun.star.util.SortField
Dim oSortDesc(0) as new com.sun.star.beans.PropertyValue
'Установка листа, где будет проводится сортировка, в данном случае лист 1
oSheet = ThisComponent.Sheets(0)
'Установка диапазона сортировки, важно  - диапазон не должен включать в себя пустые столбцы
 ' вне диапазона (внутри можно)
oRange = oSheet.getCellRangeByName("A1:L65535")
'Указываем поле для сортировки, нумерация начинается с нуля
' В данном случае это первый столбец
oSortFields(0).Field = 0
' Устанавливаем порядок сортировки - True по возрастанию, False - по убыванию
oSortFields(0).SortAscending = True
'Служебные команды выполняющие непосредственно сортировку
oSortDesc(0).Name = "SortFields"
oSortDesc(0).Value = oSortFields()
oRange.Sort(oSortDesc())
End Sub

Обычно макрос сортировки вызывается как подпрограмма в основной макрос.

Задача: В файле Реестр документов по МХ выполнить сортировку программным способом:
а) по убыванию даты документа
б) по возрастанию суммы документа

Копирование и вставка

Функция копирования и вставки применяется в макросах очень часто, когда обработанные данные нужно вставить на другой лист, или в новый документ, а также убрать формулы из столбца в котором производились расчеты (в данном случае применяется "Копировать - Вставить как - Значения, Текст).
Для того чтобы скопировать диапазон его нужно сначала выделить. Делается это командами полученными из макрорекодера (так проще). Сначала выделяется верхняя левая ячейка нужного диапазона (обычно это А1), затем происходит выделение до крайней нижней правой и проводится копирование.
Рассмотрим код:

Sub Copyng
'Выделяем ячейку А1 на листе 1
  oCell = ThisComponent.Sheets (0).getCellByPosition(0, 0)
ThisComponent.CurrentController.Select(oCell)
' Выделяем (простаскиваем) до конца диапазона и копируем
dim document   as object
dim dispatcher as object
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "Sel"
args1(0).Value = true
dispatcher.executeDispatch(document, ".uno:GoToEndOfData", "", 0, args1())
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
End Sub

Код стандартный в комментариях особо не нуждается. Обычно он вызывается как подфункция в основной макрос.

Теперь рассмотрим вставку:

Sub Paste
' Выделяем первую левую верхнюю ячейку в которую хотим вставить данные
' В данном случае это ячейка А1 листа 2
oCell = ThisComponent.Sheets (1).getCellByPosition(0, 0)
ThisComponent.CurrentController.Select(oCell)
' Проводим вставку
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())
End Sub

Задача: В документе Реестр документов по МХ программно скопировать все данные листа 1, создать лист 2 и вставить на него данные. Как вставлять новые листы вы уже должны знать.

Теперь разберем такое стандартное копирование-вставку как убирание формул. Перед выполнением этого кода нужный столбец должен быть выделен. Лучше всего этот код вставлять сразу после модуля вставки формулы - при протягивании формулы столбец остается выделенным и нужно просто выполнить код. Рассмотрим:

   Sub Delete_formul
dim document   as object
dim dispatcher as object
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
dim args2(5) as new com.sun.star.beans.PropertyValue
args2(0).Name = "Flags"
args2(0).Value = "SV"
args2(1).Name = "FormulaCommand"
args2(1).Value = 0
args2(2).Name = "SkipEmptyCells"
args2(2).Value = false
args2(3).Name = "Transpose"
args2(3).Value = false
args2(4).Name = "AsLink"
args2(4).Value = false
args2(5).Name = "MoveMode"
args2(5).Value = 4
dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args2())
End sub

Здесь пояснять особо нечего: функция стандартна, ее можно просто копировать в любой модуль. Она проводит копирование, а потом вставку по выделенным флагам - в данном случае выбраны флаги "Значения" и "Текст".

Задача: Добавить данный код в одну вкладку с кодом вставки формул, который мы разобрали в предыдущем посте. Добавить строчку вызова этой функции в конец макроса вставки формул и выполнить его. Убедиться, что после расчета формул в ячейках остались только значения.

Вставка формул в отдельную ячейку и столбец

Программная вставка формул является важной частью любого макроса в котором проводятся какие либо расчеты. Формулу можно вставить как в отдельную ячейку (например вывести сумму какого-либо диапазона, посчитать число ячеек в нем, просуммировать по условию и т.д.) так и заполнить столбец (строку) одинаковыми формулами построчно.
Рассмотрим первый вариант:

Sub Formula
 ThisComponent.Sheets(0).getCellByPosition (0, 0).Setformula  ("=SUM('Лист2'.D2:D65536)/2")
End Sub

Данная строчка в ячейке А1 листа 1 вставляет  формулу суммы диапазона D2:D65536 листа 2 деленную на два. Если нужно суммировать весь столбец программно, то можно не искать его окончание - просто суммировать до конца листа - ячейки 65536

Задача: В Отчете Реестр документов по МХ в ячейке D1 программно получите сумму Столбца "NDS".

Теперь перейдем к заполнению столбца рядом однотипных формул. Рассмотрим код на примере. Нам нужно сложить в каждой строке Сумму и НДС из Реестра документов по МХ.

Sub Formula2
odoc=thiscomponent
oSheet = ThisComponent.Sheets(0)
oCellCursor = oSheet.createCursor()
oCellCursor.GotoStartOfUsedArea(True)
oCellCursor.GotoEndOfUsedArea(True)
Start_Row = oCellCursor.getRangeAddress.StartRow
End_Row = oCellCursor.getRangeAddress.EndRow
currcell = osheet.getcellbyposition(11, End_Row)
currcell2 = osheet.getcellbyposition(2, End_Row)
currcell3 = osheet.getcellbyposition(3, End_Row)
oConv = thiscomponent.createInstance("com.sun.star.table.CellAddressConversion")
oConv.Address = currcell.getCellAddress
oConv2 = thiscomponent.createInstance("com.sun.star.table.CellAddressConversion")
oConv2.Address = currcell2.getCellAddress
oConv3 = thiscomponent.createInstance("com.sun.star.table.CellAddressConversion")
oConv3.Address = currcell3.getCellAddress
 currcell.setFormula ("=SUM(" & oConv2.UserInterfaceRepresentation &  ";" & oConv3.UserInterfaceRepresentation &  ")"
 odoc.getCurrentController().Select(currcell)
 dim document   as object
dim dispatcher as object
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dim args1(1) as new com.sun.star.beans.PropertyValue
args1(0).Name = "By"
args1(0).Value = 1
args1(1).Name = "Sel"
args1(1).Value = true
dispatcher.executeDispatch(document, ".uno:GoUpToStartOfData", "", 0, args1())
dispatcher.executeDispatch(document, ".uno:FillUp", "", 0, Array())
ThisComponent.Sheets(0).getCellByPosition (11, 0).String = "Сумма с НДС"
ThisComponent.Sheets (0).getCellRangeByName("L2:L65535").NumberFormat=2
 End Sub

Прокомментируем код:

Sub Formula2
'Определяем текущий документ (стандартная функция)
odoc=thiscomponent
'Указываем нужный лист
oSheet = ThisComponent.Sheets(0)
'Создаем определители всего диапазона (от первой левой верхней заполненной ячейки
' до последней правой нижней) - это стандартное требование
oCellCursor = oSheet.createCursor()
oCellCursor.GotoStartOfUsedArea(True)
oCellCursor.GotoEndOfUsedArea(True)
Start_Row = oCellCursor.getRangeAddress.StartRow
End_Row = oCellCursor.getRangeAddress.EndRow
' Определяем номера столбцов, которые будут суммироваться. а также столбец где будет
'находится сама конечная формула, вставка формулы проводится снизу - в нижнюю правую
' ячейку, а затем мы протянем формулу вверх, переменные сurcell должны уметь уникальное
' название или номер
'Здесь будет формула
currcell = osheet.getcellbyposition(11, End_Row)
' А это суммируемые столбцы
currcell2 = osheet.getcellbyposition(2, End_Row)
currcell3 = osheet.getcellbyposition(3, End_Row)
'Конвертируем формулы в виду понятному Calc (стандартное преобразование)
' Переменные oConv должны иметь уникальный номер, лучше тот же что и currcell
oConv = thiscomponent.createInstance("com.sun.star.table.CellAddressConversion")
oConv.Address = currcell.getCellAddress
oConv2 = thiscomponent.createInstance("com.sun.star.table.CellAddressConversion")
oConv2.Address = currcell2.getCellAddress
oConv3 = thiscomponent.createInstance("com.sun.star.table.CellAddressConversion")
oConv3.Address = currcell3.getCellAddress
'Вставляем в ячейку currcell (последняя ячейка в 12 столбце) нашу формулу суммы:
 currcell.setFormula ("=SUM(" & oConv2.UserInterfaceRepresentation &  ";" & oConv3.UserInterfaceRepresentation &  ")"
'Выделяем эту ячейку:
 odoc.getCurrentController().Select(currcell)
' Следующий блок протягивает формулу вверх до конца:
 dim document   as object
dim dispatcher as object
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dim args1(1) as new com.sun.star.beans.PropertyValue
args1(0).Name = "By"
args1(0).Value = 1
args1(1).Name = "Sel"
args1(1).Value = true
dispatcher.executeDispatch(document, ".uno:GoUpToStartOfData", "", 0, args1())
dispatcher.executeDispatch(document, ".uno:FillUp", "", 0, Array())
'Вставляем в первую ячейку осмысленное название полученного столбца
ThisComponent.Sheets(0).getCellByPosition (11, 0).String = "Сумма с НДС"
' Устанавливаем на весь столбец формат два знака после запятой
ThisComponent.Sheets (0).getCellRangeByName("L2:L65535").NumberFormat=2
 End Sub

Важно! Столбец в который вы хотите вставить формулу должен прилегать к заполненной области и не быть от нее оторванным (не должно быть пустого столбца слева). Таким образом вставить формулы можно и в столбец заполненный какими-то ненужными данными (они будут стерты)

Задача: В Реестре документов по МХ в стоблце G рассчитать построчно разность столбцов "Сумма документа" и "NDS".
Подсказка, формула будет иметь вид:

 currcell.setFormula ("=(" & oConv2.UserInterfaceRepresentation &  "-" & oConv3.UserInterfaceRepresentation &  ")  "

Поиск и замена

Часто в обрабатываемых документов требуется провести поиск и замену каких-либо символов для очистки документа или его подготовки для целей фильтрации, чтобы придать определенному столбцу универсальный вид. Рассмотрим код поиска и замены, реализованного без вывода каких-либо дополнительных окон:

Sub Zamena
oReplace = ThisComponent.Sheets(0).createReplaceDescriptor()
oReplace.setSearchString("•Производственные цеха•Производство")
oReplace.setReplaceString("")
oReplace.SearchWords = False
nCount = ThisComponent.Sheets(0).replaceAll(oReplace)
oReplace = ThisComponent.Sheets(0).createReplaceDescriptor()
End Sub

Прокомментируем:
' Определяем лист на котором будет проводить поиск и замену, в данном случае лист 1
oReplace = ThisComponent.Sheets(0).createReplaceDescriptor()
"Определяем какую фразу следует искать
oReplace.setSearchString("•Производственные цеха•Производство")
"Определяем на какую фразу нужно заменить искомую фразу - в данном случае на пустое 'значение
oReplace.setReplaceString("")
' Определяем что искать нужно не только слова но и символы (False)
oReplace.SearchWords = False
' Следующими строками запускаем сам обработчик с указанием нужно листа
nCount = ThisComponent.Sheets(0).replaceAll(oReplace)
oReplace = ThisComponent.Sheets(0).createReplaceDescriptor()

Задача: Создайте в документе Реестр документов по МХ обработчик, который заменит сочетание "ПРОБАК" на "Цех фасовки бакалеи"

Функцию поиска и замены также удобно использовать когда нужно провести фильтрацию по ряду признаков, чтобы не применять множество фильтров - можно заменить не нужные значения на какое-то условное, а затем отфильтровать по нему.
Например Вам нужно убрать из отчета строки с данными цехов фасовки - применяется замену к каждому цеху - можно заменить на поль и затем отфильтровать по условию не равно нулю.

Закрепление материала 1

Закрепим пройденный материал созданием комплексного макроса, но сперва введем и напомним несколько операторов:
Вставка листа код:
oSheet = ThisComponent.createInstance ("com.sun.star.sheet.Spreadsheet")
ThisComponent.Sheets.insertByName ("Имя", oSheet)
Удаление листа код:
oSheet = ThisComponent.createInstance ("com.sun.star.sheet.Spreadsheet")
ThisComponent.Sheets.removeByName ("Имя", oSheet)

Соединение отдельных модулей в общую программу производится путем добавления имени под программы в код первой программы без слова Sub -  в конец, после основного кода.
Например, у нас есть макросы:

Sub Programm
......

End Sub

Sub Filt
......
End Sub

Sub Svod
......
End Sub

Нам нужно. чтобы поочередно выполнился код сначала Programm, затем Filt, затем Svod. Мы программируем эти блоки отдельно в одной вкладке редактора, а затем в Programm в конец модуля дописывает какие подпрограммы он должен вызвать. Выглядить это будет так:


Sub Programm
.......
Filt
Svod
End Sub

И мы запуском макроса Programm выполним весь цикл работ включенных в него макросов.

Задание: В файле Реестр документов по МХ отфильтровать данные по полю "Название места хранения (откуда) в операции" по критерию Производство на вставленный лист "Производство", затем фильтровать данные по полю "Название типа операции" по критерию "Ингредиенты производства ГМ", с полученного листа запустить сводную - Поле строки "Дата" - Поле данных - "Сумма". Удалить из книги все листы. кроме листа со сводной.

Для вызова редактора макросов - открытом файле Calc нажмите ALT+F11.Найдите в ней библиотеку Standart и создайте в ней новый модуль. В открывшемся окне удалите все строчки и начните разработку. Рекомендуется промежуточный результат копировать с открытый блокнот, чтобы в случае сбоя не потерять время формируя код снова.
Когда программа будет сделана, закрываем окно разработки и в файле снова нажимаем ALT+F11. Ищем нужную библиотеку и модуль. Выделяем эту строчку курсором и жмем "Выполнить". Проверяем работу макроса.

Создание сводной таблицы

Сводные таблицы являются часто используемым инструментом для сведения итогов данных в отчетах и являются очень удобной для отбора уникальных значений (сумм уникальных значений) в любых списках. Разберем пример программного построения сводной таблицы с одним полем строки и одним полем данных.

Sub CreateDataPilotTable()
oSheet = ThisComponent.createInstance ("com.sun.star.sheet.Spreadsheet")
ThisComponent.Sheets.insertByName ("Сводная", oSheet)
Dim oRange
Dim oRangeAddress
Dim oTables
Dim oTDescriptor
Dim oFields
Dim oField
Dim oCellAddress As New com.sun.star.table.CellAddress
CreateDataPilotSource = oRange
oRange = CreateDataPilotSource("Сводка")
oSheet = ThisComponent.getSheets().getByIndex(0)
oRangeAddress = ThisComponent.getSheets().getByIndex(0).getCellRangeByName("A1:L65535").getRangeAddress()
oRangeAddress2 = ThisComponent.Sheets (1).getCellByPosition(0, 0).getCellAddress()
oSheet = ThisComponent.Sheets.getByName("Сводная")
oTables = oSheet.getDataPilotTables()
oTDescriptor = oTables.createDataPilotDescriptor()
oTDescriptor.setSourceRange(oRangeAddress)
oFields = oTDescriptor.getDataPilotFields()
oField = oFields.getByIndex(2)
oField.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.ROW
oField = oFields.getByIndex(0)
oField.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.DATA
oField.Function = com.sun.star.sheet.GeneralFunction.MAX
oTables.insertNewByName("MyFirstDataPilot", oRangeAddress2, oTDescriptor)
oSheet = ThisComponent.Sheets (1)
oSheet.Rows.removeByIndex(0,1)
oSheet.Rows.removeByIndex(0,1)
oSheet.Rows.removeByIndex(0,1)
oSheet.Rows.removeByIndex(0,1)
oSheet.Rows.insertByIndex(0,1)
oSheet.getCellByPosition(0, 0).String = "Код"
oSheet.getCellByPosition(1, 0).String = "Наименование"
oCell = ThisComponent.Sheets (1).getCellByPosition(0, 0)
ThisComponent.CurrentController.Select(oCell)
oSheet = ThisComponent.CurrentController.getActiveSheet()
 myrows=oSheet.getrows
 oCellCursor = oSheet.createCursor()
 oCellCursor.GotoStartOfUsedArea(True)
 oCellCursor.GotoEndOfUsedArea(True)
 Start_Row = oCellCursor.getRangeAddress.StartRow
 End_Row = oCellCursor.getRangeAddress.EndRow + 1
 For i=End_Row To Start_Row step -1
  text1 = osheet.getcellbyposition(0,i).String
   If Left(text1,3) = "ито" OR Left(text1,3) = "(пу" Then
    myrows.removebyindex(i,1)
   End if   
   Next i
End Sub

Приведенный код выполняет несколько больше, чем простое формирование сводной таблицы. есть еще и удаление верхних пустых столбцов, замена заголовка на привычный формат, удаление итогов и пустых значений. Данные строки идут уже после кода формирования сводной, и если в них нет необходимости. их можно не включать в программу. Разберем подробно:

Sub CreateDataPilotTable()
' Сперва вставляем новый лист, на который будет выведена сводная
oSheet = ThisComponent.createInstance ("com.sun.star.sheet.Spreadsheet")
ThisComponent.Sheets.insertByName ("Сводная", oSheet)
' объявляем переменные, диапазоны, дескрипторы (стандартный текст)
Dim oRange
Dim oRangeAddress
Dim oTables
Dim oTDescriptor
Dim oFields
Dim oField
Dim oCellAddress As New com.sun.star.table.CellAddress
CreateDataPilotSource = oRange
' Даем имя диапазону, откуда будут браться данные (для каждой сводной - уникальное)
oRange = CreateDataPilotSource("Сводка")
'Определяем лист, с которого будут браться данные  - в данном случае лист 1
oSheet = ThisComponent.getSheets().getByIndex(0)
' Определяем диапазон ячеек, откуда будут браться данные - важно чтобы все стобцы
' в этом диапазоне были с с заполненными данные, а не пустыми (пропуски и отдельные
' пустые ячейки допускаются)
oRangeAddress = ThisComponent.getSheets().getByIndex(0).getCellRangeByName("A1:L65535").getRangeAddress()
' Определяем верхнюю левую ячейку на листе вывода данных, в данном случае,
' ячейка A1 на листе 2
oRangeAddress2 = ThisComponent.Sheets (1).getCellByPosition(0, 0).getCellAddress()
'Определяем лист вывода
oSheet = ThisComponent.Sheets.getByName("Сводная")
' Блок построений (стандартные операторы)
oTables = oSheet.getDataPilotTables()
oTDescriptor = oTables.createDataPilotDescriptor()
oTDescriptor.setSourceRange(oRangeAddress)
oFields = oTDescriptor.getDataPilotFields()
' Определяем какой столбец мы хотим в качестве строки (здесь - 3-й)
oField = oFields.getByIndex(2)
oField.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.ROW
' Определяем какой столбец мы хотим в качестве Поля данных
' и какое действие для него применить - SUM - суммирование, AVERAGE - среднее
' MIN - самые минимальные значения, MAX - максимальные значения.
oField = oFields.getByIndex(0)
oField.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.DATA
oField.Function = com.sun.star.sheet.GeneralFunction.SUM
' Служебная строка вставки сводной
oTables.insertNewByName("MyFirstDataPilot", oRangeAddress2, oTDescriptor)
' На этом код построения сводной окончен, далее идут дополнительные функции
' Если эти строчки не нужны их просто удаляют или меняют значения на нужные
' Удаляем три пустых верхних строки + строку с заголовком (итого 4) с нужного листа
oSheet = ThisComponent.Sheets (1)
oSheet.Rows.removeByIndex(0,1)
oSheet.Rows.removeByIndex(0,1)
oSheet.Rows.removeByIndex(0,1)
oSheet.Rows.removeByIndex(0,1)
'вставляем пустую строку сверху
oSheet.Rows.insertByIndex(0,1)
' Вставляем более удобные названия в ячейки A1 и В1
oSheet.getCellByPosition(0, 0).String = "Цех"
oSheet.getCellByPosition(1, 0).String = "Сумма"
' Получаем текущий диапазон на нужном листе в память для циклической проверки и
' удаления пустых строк и итогов
' Здесь мы будем искать и удалять значения на листе 1
oCell = ThisComponent.Sheets (1).getCellByPosition(0, 0)
ThisComponent.CurrentController.Select(oCell)
oSheet = ThisComponent.CurrentController.getActiveSheet()
 myrows=oSheet.getrows
 oCellCursor = oSheet.createCursor()
 oCellCursor.GotoStartOfUsedArea(True)
 oCellCursor.GotoEndOfUsedArea(True)
 Start_Row = oCellCursor.getRangeAddress.StartRow
 End_Row = oCellCursor.getRangeAddress.EndRow + 1
' Запускаем цикл суть которого в том чтобы найти строчки начинающие со слогов ито
' и (пу то есть ище начало слов итого и пусто
 For i=End_Row To Start_Row step -1
' В этой строке мы сказываем в каком столбце искать (0,i) - первый столбец
  text1 = osheet.getcellbyposition(0,i).String
' Эту строчку дословно можно понять как Если слева три символа в ячейке равны
' ито или (пу - удалить эти строки
   If Left(text1,3) = "ито" OR Left(text1,3) = "(пу" Then
    myrows.removebyindex(i,1)
'Завершение условного оператора
   End if   
' Завершение цикла проверки
   Next i
End Sub

Задача: В файле Реестр документов по МХ создать программно сводную с выводом на новый лист с полями "Название типа операции" - "Сумма документа"

Стандартный одиночный числовой фильтр

Числовой фильтр отличается от текстового лишь блоком фильтруемых параметров в коде, который начинается со строки With oField. Стандартно он выглядит следующим образом:

With oField

  .Field = 5

   .Operator = com.sun.star.sheet.FilterOperator.GREATER_EQUAL

  .isNumeric = True

  .NumericValue = 0.004

  End With

Прокомментируем:

With oField

'В этой строчке мы определяем номер столбца по которому будет проводится фильтр
' по заданному значению, в данном случае это столбец 6. Нумерация столбцов, как и 
' нумерация листов начинается с 0.  0  - это первый столбец на листе.

  .Field = 5

' Определяем критерий фильтрации, окончание этой строки, определяет, как именно
'нужно отфильтровать данные слово EQUAL обозначает РАВНО, NOT_EQUAL - не 
'равно, GREATER_EQUAL - больше или равно, LESS_EQUAL - меньше или равно,

' GREATER - больше, LESS - меньше

   .Operator = com.sun.star.sheet.FilterOperator.GREATER_EQUAL

'Устанавливаем что это именно числовой фильтр

  .isNumeric = True

' Определяем значение, свыше (ниже которого нужно отфильтровать)

'десятичный разделитель - точка (это важно!)

  .NumericValue = 0.004

  End With

Все остальные строки кода будут теми же что в строковом фильтре.

Задача: Открыть файл Реестр документов по МХ и провести фильтрацию по столбцу "Сумма документа" по критерию не равно нулю через макрос, изменив код из предыдущего поста.

Стандартный одиночный текстовый фильтр

Функция фильтрации по какому-либо текстовому значение в каком-либо столбце с выводом данных на новый лист является весьма типичной для стандартных обработок файла. Множественные фильтры также могут состоять из ряда одиночных, с удалением промежуточных результатов. Рассмотрим код:

Sub Filt
 Dim oDescriptor,oNewDescriptor,bCopyOutputData as Boolean, bUseCriteria as Boolean 
 oDoc = ThisComponent 
   oSheet = ThisComponent.getSheets().getByIndex(0) 
    oAddr = oSheet.getCellRangeByName("A1:L65535").getRangeAddress() 
    oDoc.DatabaseRanges.addNewByName("MyName", oAddr) 
  oRange = oDoc.DatabaseRanges.getByName("MyName") 
set oDescriptor = oRange.getFilterDescriptor
oSheet = ThisComponent.createInstance ("com.sun.star.sheet.Spreadsheet")
ThisComponent.Sheets.insertByName ("Торговый зал", oSheet)
oCellAddress = ThisComponent.Sheets (1).getCellByPosition(0, 0).getCellAddress()
oFD = oRange.getFilterDescriptor()
With oFD
.CopyOutputData  = True
.OutputPosition = (oCellAddress)
End With
oField = createUnoStruct("com.sun.star.sheet.TableFilterField")
With oField
  .Field = 4
  .Operator = com.sun.star.sheet.FilterOperator.EQUAL
  .StringValue = "Торговый зал"
  End With
oFD.setFilterFields(Array(oField))
oRange.refresh()
End Sub

Отсюда, и в дальнейшем, я буду сперва предоставлять чистый код макроса, а затем приводить этот же код с комментариями, чтобы было понятно, за что какая строчка отвечает.
Комментарии в StarBasic можно писать прямо по коду - они не влияют на ход выполнения программы. Строчка комментария начинается со слова REM или с символа апострофа -  ' (любой вариант как вам удобно). Комментарий идет выше строки, которую он поясняет. Комментарии можно писать на любом языке, в т.ч. на русском.

Sub Filt
' Стандартная строка определения диапазонов и дескрипторов:
 Dim oDescriptor,oNewDescriptor,bCopyOutputData as Boolean, bUseCriteria as Boolean 
' Определяем переменную oDoc - она будет обозначать текущий документ 
oDoc = ThisComponent 
' Определяем номер листа где находятся исходные данные для фильтрации, в данном
' случае это первый лист (нулевой)
   oSheet = ThisComponent.getSheets().getByIndex(0) 
' Определяем область фильтрации, с какого по какой столбцы будут отфильтрованны
' на новый лист, если у вас справа есть ненужные стоблцы, область можно обрезать,
'оставив лишь нужные. Начало диапазона обычно всегда начинается с ячейки А1
' концом диапазона всегда лучше объявлять конечную ячейку последнего столбца в 
' диапазоне (65535), чтобы не зависеть от возможной разной размерности диапазонов
    oAddr = oSheet.getCellRangeByName("A1:L65535").getRangeAddress() 
' Даем имя этому диапазону - любое на ваш выбор, но только на латинице
' двух одинаковых имен в одной книге быть не может
    oDoc.DatabaseRanges.addNewByName("MyName", oAddr) 
  oRange = oDoc.DatabaseRanges.getByName("MyName") 
' устанавливаем область фильтрации (стандартная строчка)
set oDescriptor = oRange.getFilterDescriptor
' Вставляем новый лист в книгу, с нужным нам именем, на который будут 'отфильтрованы данные
oSheet = ThisComponent.createInstance ("com.sun.star.sheet.Spreadsheet")
ThisComponent.Sheets.insertByName ("Торговый зал", oSheet)
'Определяем ячейку верхнюю левую для вывода отфильтрованного диапазона
' в индексном формате, в данном случае это будет ячейка (0,0) или А1 на листе 2
' который мы назвали Торговый зал
oCellAddress = ThisComponent.Sheets (1).getCellByPosition(0, 0).getCellAddress()
' Стандартная конструкция вывода:
oFD = oRange.getFilterDescriptor()
With oFD
.CopyOutputData  = True
.OutputPosition = (oCellAddress)
End With
' Создаем фильтр:
oField = createUnoStruct("com.sun.star.sheet.TableFilterField")
With oField
'В этой строчке мы определяем номер столбца по которому будет проводится фильтр
' по заданному значению, в данном случае это столбец 9. Нумерация столбцов, как и 
' нумерация листов начинается с 0.  0  - это первый столбец на листе.
  .Field = 8
' Определяем критерий фильтрации, окончание этой строки, определяет, как именно
'нужно отфильтровать данные слово EQUAL обозначает РАВНО, NOT_EQUAL - не 
'равно
  .Operator = com.sun.star.sheet.FilterOperator.EQUAL
'Определяем, какому значению должна быть равна (или не равна) строка в заданном 
'диапазоне
  .StringValue = "Торговый зал"
' Завершаем процесс установкой массива и его обновлением (стандартные строки)
  End With
oFD.setFilterFields(Array(oField))
oRange.refresh()
'Завершаем программу

End Sub

Задача:
1. Скопировать первоначальный код и запустить его в отчете Реестр документов по МХ.
2. С отфильтрованных данных сделать фильтр по следующему столбцу по критерию не равно "Производство" и вывести его на новый лист. Для данного задания вы будете должны в исходном коде переделать:
а) номер листа с которого забираются данные
б) имя фильтруемого диапазона
в) другое произвольное имя вставляемого листа
г) другие данные для листа вывода
д) другой столбец фильтрации
е) другой критерий фильтрации
ж) другое значение строки для фильтрации

P.S, Для текстовых фильтров также разрешается фильтрация по пустому значению
.StringValue = ""
прочеркам и любым символам
.StringValue = "-"
Иногда, в БД в текстовую строку добавляют невидимые пробелы, вы пытаетесь отфильтровать только по тексту, но фильтр работает не корректно - проверьте - можно нужно вставить несколько пробелов (проще скопировать и вставить строку). Например:
.StringValue = "шт      "

Проверка - тот ли файл открыли

Перед обработкой любого файла нужно убедится, что это именно тот файл, для которого предназначен запускаемый макрос. Если не провести эту проверку, в случае если пользователь начнет выполнение программы не в том файле, программа исполнится и обработает его не корректно, что может привести к его повреждению и зависанию системы.
Рассмотрим код проверки:

Sub Programm
Dim oCellAddress 
oCelles = ThisComponent.Sheets(0).getCellRangeByName("F1") 
If oCelles.String <> "Количество" Then 
MsgBox " Данный файл не является отчетом" & Chr$(13) & "Реестр товарных позиций, либо имеет поврежденную структуру" & Chr$(13) & "Экспортируйте отчет Реестр товарных позиций за нужный период и откройте его",16, "Предупреждение:" 
Exit Sub
End If
End sub

Разберем код:
Любая программа начинается со строчки Sub Имя_модуля и заканчивается строчкой End Sub.
Эти строчки стандарты и справедливы для любой программы. В одной вкладке редактора не может быть двух модулей с одинаковым именем.
Dim oCellAddress  - объявление переменной адресов ячеек, стандартная строчка
oCelles = ThisComponent.Sheets(0).getCellRangeByName("F1")  - данная строчка оперделяет, что переменная oCelles равна содержимому ячейки F1 первого листа в книге (нумерация листов в StarBasic начинается с нуля - т.е. первый лист это Sheets(0), второй лист это Sheets(1)).
If oCelles.String <> "Количество" Then  - данная строчка дословно переводится Если строчное значение переменной oCelles не равно строчке "Количество" Тогда.
If.....Then это стандартная конструкция условий во многих языках, после нее в следующих строках пишут. что нужно сделать. если условие выполняется. Данный блок всегда заканчивается строчкой End if которая закрывает конструкцию.
MsgBox " Данный файл не является отчетом" & Chr$(13) & "Реестр товарных позиций, либо имеет поврежденную структуру" & Chr$(13) & "Экспортируйте отчет Реестр товарных позиций за нужный период и откройте его",16, "Предупреждение:"  - функция MsgBox выводит окно предупреждения к кнопкой "ОК". После объявления функции в кавычках пишут нужный текст, номер типа окна (в данном случае 16 - окно об ошибке) и заголовок этого окна (в данном случае "Предупреждение").
Exit Sub - данная строчка досрочно завершает процедуру и дальнейший код не выполняется, предотвращая ошибку, в том случае, если файл не прошел проверку в блоке if ...then.
При создании новой программы, каждый раз не нужно писать блок заново - копируем блок из другой программы, меняем проверяемые значения:
1. Какую ячейку и на каком листе проверяем
2. Какой текст ищем
3. Что пишем в сообщении (какой отчет нужен).
За время обучения и вашего практического применения StarBasic для программирования различных программ у вас накопится множество блоков, выполняющих стандартные функции и все программирование сведется к копированию этих блоков в новые программы и замене значений под новые условия.
Задание - скопируйте код в новый программный модуль ООО и выполните его.
Копируйте все коды, которые будут в этом и дальнейших заданиях в отдельные текстовые файлы, чтобы в случае сбоя ООО ваши данные были сохранение. Очень часто придется вносить правки в уже созданные макросы и будет хорошо если они у вас будут где-то отдельно сохранены, чтобы внести необходимые изменения не переписывая все заново.

Введение

Любая программа в среде openoffice calc представляет собой скриптовую программу написанную на языке StarBasic.
Скрип - это записанная последовательность стандартных действий и функций, которые пользователь может произвести вручную.
Любая программа представляет собой собранный конструктор из этих стандартных функций, объединенный в общий код.
Также часто в программировании применяются дополнительные возможности - то что пользователь не может сделать вручную, либо не таким способом как это описано в программе.
Изложение материала будет строится на основе примеров обработки стандартных файлов, с начала и до конца. От простых макросов к сложным.