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

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

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

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