Пример использования ВПР функции в LibreOffice Calc

Функция ВПР работает так же как и в Microsoft Office. Откройте LibreOffice Calc. Создайте второй лист (Смотрите рисунок 1 цифра 1). Переименуйте листы с именами «Лист 1» и «Лист 2» в листы «Результат» и «Детали» соответственно (Смотрите рисунок 1 цифра 2).

Рисунок 1. Добавление нового листа в LibreOffice Calc

На листе «Результат» создайте таблицу как показано на рисунке 2. На листе «Детали» создайте таблицу как показано на рисунке 3. 

Рисунок 2. Пример таблицы "Результат"

 

Рисунок 3. Пример таблицы "Детали"

Таблица «Детали» - это уникальная таблица не повторяющихся значений, содержит наименование детали и её инвентарный номер.
Таблица «Результат» - это сводная таблица, куда мы будем автоматически подставлять инвентарные номера деталей при помощи ВПР функции для соответствующей детали из таблицы «Детали».

Функция ВПР принимает в себя 4 параметра:
1 — номер ячейки значение которой будем искать
2 — диапазон ячеек в которых ищем значение из ячейки указной в параметре 1
3 — номер столбца, где будем брать значение для подстановки, которое соответствует найденному значению из параметра 1. Нумерация столбцов начинается с 1.
4 – Если выставлен 0, то ищется точное совпадение. Если выставлен 1, то ищется приближённое значение.

Заполнять будем столбец «Инвентарный номер детали» в таблице «Результат». Искать будем по значениям столбца «Наименование используемой детали» в таблице «Результат». Искать значения будем в таблице «Детали» и брать соответствующие значения из столбца «Инвентарный номер детали» таблицы «Детали», который имеет номер 2 для ВПР функции в параметре 3.

=ВПР(C4;$Детали.$A$2:$B$10;2;0)
=ВПР(C5;$Детали.$A$2:$B$10;2;0)
------------------------------------------------- и так для остальных ячеек (с C4 по C18)
=ВПР(C18;$Детали.$A$2:$B$10;2;0)

Разберём первую из ВПР функций.

=ВПР(C4;$Детали.$A$2:$B$10;2;0)

Эта функция выводит результат работы в ячейку D4 таблицы «Результат» столбца «Инвентарный номер детали» (Рисунок 2 цифра 2). Первый параметр ВПР функции получает значение ячейки C4 таблицы «Результат» столбца «Наименование используемой детали» (Рисунок 2 цифра 1), которое является «Деталь 2». Второй параметр ВПР функции получает диапазон значений ячеек с A2 по B10 таблицы «Детали» из первого (Наименование детали) и второго (Инвентарный номер детали) столбцов (Рисунок 3 цифра 1). Третий параметр ВПР функции указывает на столбец 2 (Инвентарный номер детали) таблицы «Детали». Четвертый параметр ВПР функции устанавливаем в ноль, искать будем точное совпадение.

Таким образом в ячейке D4 таблицы «Результат» отобразится значение 100-00002, которое соответствует «Деталь 2». И так для всех деталей в таблицы «Результат».

[VBA][LibreOffice] Макрос вывода списка файлов определенной папки по маске

Напишем пример VBA программы в LibreOffice 6.4.4.2. Я уже писал о том, как написать простой макрос в этой статье. Перейдите по этой ссылке и посмотрите с чего начать. Наш макрос будет читать имена файлов в выбранной директории и выводить их в ячейки таблицы по определенной маске. Маска будет задаваться в коде макроса. Директория будет выбираться при помощи стандартного диалога Windows. Код сопровождается необходимыми комментариями, что бы понять работу макроса.

Рисунок 1. Результат работы макроса

Код макроса:

Sub Main
 Dim oFP As Object
 Dim DirName As String
 'Вызываем диалог выбора директории и сохраним путь папки в переменной DirName
 DirName = ""
 oFP = CreateUnoService("com.sun.star.ui.dialogs.FolderPicker")
 oFP.DisplayDirectory = ConvertToURL("C:\")
 oFP.Description = "Select a directory"
 oFP.Title = "Select the backup directory"
 
 If oFP.execute = _
  com.sun.star.ui.dialogs.ExecutableDialogResults.OK Then
  DirName = oFP.Directory
 End If
 
    Dim TableHeaderRangeData(0, 2)
    Dim TableHeaderRangeObject as Object
    Dim MainDocument as Object 
    MainDocument = ThisComponent
    'Очистим весь контент
    MainDocument.CurrentSelection.ClearContents(1 OR 2 OR 4 OR 8 OR 16 OR 32 OR 64 OR 128 OR 256 OR 512)
    'Сформируем заголовки таблицы (Сканируемая папка, Фильтр, Список файлов)
    TableHeaderRangeData(0, 0) = "Scan Folder"
    TableHeaderRangeData(0, 1) = "Filter"
    TableHeaderRangeData(0, 2) = "List Files"
 
    TableHeaderRangeObject = MainDocument.Sheets(0).getCellRangebyName("A1:C1")
    TableHeaderRangeObject.charWeight = com.sun.star.awt.FontWeight.BOLD
    TableHeaderRangeObject.setDataArray(TableHeaderRangeData)
 'Сформируем маску
 ReDim TableHeaderRangeData(0, 1)
 Dim strMaskSearch as String
 strMaskSearch = "*.pdf*"
 'Выведем путь папки и фильтр в ячейки таблицы
 TableHeaderRangeData(0, 0) = DirName
    TableHeaderRangeData(0, 1) = strMaskSearch 
 TableHeaderRangeObject = MainDocument.Sheets(0).getCellRangebyName("A2:B2") 
 TableHeaderRangeObject.charWeight = com.sun.star.awt.FontWeight.NORMAL
 TableHeaderRangeObject.setDataArray(TableHeaderRangeData)
 'Выведем список файлов в ячейки таблицы
 Dim FileName As String
 Dim iCounter As Integer
 FileName = Dir(DirName & "/" & strMaskSearch, 0)
 iCounter = 0
 Do While (FileName <> "")
     iCounter = iCounter + 1
 
  MainDocument.Sheets(0).getCellByPosition(2,iCounter).String = ""
     MainDocument.Sheets(0).getCellByPosition(2,iCounter).String = FileName
     FileName = Dir()
 Loop
End Sub

Ссылки на материалы по LibreOffice

1. https://wiki.documentfoundation.org/Faq/General/ru - Часто задаваемые вопросы. Общие вопросы по использованию LibreOffice. Может кому-нибудь будет интересно почитать.
2. https://help.libreoffice.org/Common/Starting_the_Software_With_Parameters/eu - Запуск LibreOffice из командной строки.  

Написание VBA макросов в LibreOffice

Внимание! Написание макроса выполнялось в LibreOffice 4.4.3.2 и Windows XP.

Запустите LibreOffice Calc. Выберите пункт меню Сервис -> Макросы -> Управление макросами -> LibreOffice Basic

В окне Макросы Libre Office BASIC в поле "Имя макроса" пишем "Main". В поле "Макрос из" выбираем "Мои макросы" - "Standard" и нажимаем кнопку "Создать". Смотрите рисунок 1.

Рисунок 1. Окно макросы LibreOffice Basic

В окне Новый модуль задаем название "Module1" и нажимаем кнопку "OK". Смотрите рисунок 2.

Рисунок 2. Создание нового модуля

Далее в окне редактора макрасов LibreOffice Basic приступаем к созданию диалога и написанию макроса. Смотрите рисунок 3.

Рисунок 3. Редактор макросов LibreOffice Basic

Сам модуль Module1 с главной процедурой Main у нас созданы. Чтобы создать диалог выберите в редакторе макросов LibreOffice Basic пункт меню Сервис -> Макросы -> Управление диалогами...

Выбираем вкладку "Диалоги". В разделе "Диалог" выбираем "Мои диалоги" -> "Standard" и нажимаем кнопку "Создать". Смотрите рисунок 4.

Рисунок 4. Создание диалога макроса

В окне Новый диалог задаем название "Dialog1" и нажимаем кнопку "OK". Смотрите рисунок 5. Потом кнопку "Закрыть" окна Управление макросами LibreOffice Basic.

Рисунок 5. Создание нового диалога

Далее в окне редактора макросов LibreOffice Basic дважды нажимаем на нашем только что созданом диалоге с именем "Dialog1", смотрите цифру 1 на рисунке 6. После этого откроется визуальный редактор диалога, смотрите цифру 2 на рисунке 6.

Рисунок 6. Редактор диалога LibreOffice Basic

Теперь перенесите на наш диалог визуальный элемент под именем метка и визуалный элемент кнопка. Визуальные элементы можно перетащить из панели визуальных элементов, смотрите цифру 3 на рисунке 6.

Если выделить визуальный элемент Метку, то откроется набор свойств данного элемента. Через эти свойства можно настроить элемент. Присвоим имя lblNow элементу метка и текст оставим пустым. Для элемента кнопка имя присвоем OKButton и текст зададим OK. Смотрите цифры 1,2,3 рисунка 7.

Рисунок 7. Добавление визуальных элементов в редакторе диалогов LibreOffice Basic

Как я писал выше, модуль Module1 с главной процедурой Main создана (смотрите рисунок 8), но она еще не заполнена.

Рисунок 8. Добавляем исходный код макроса

Процедура Main является основной, в ней мы загружаем диалоговое окно с именем Dialog1, это следующие строчки кода:

DialogLibraries.LoadLibrary("Standard")
Dlg = CreateUnoDialog(DialogLibraries.Standard.Dialog1)

Формируем строку с текущей датой и временем, это следующие строчки кода:

Dim strNow As String

strNow = Format(Now(), "yyyy-mm-dd ")
strNow = strNow + Format(Time(), "hh:mm")

Присваеваем строку с датой и временем визуальному элементу метка с именем lblNow, это следующие строчки кода:

Dim clblNow As Object

clblNow = Dlg.getControl("lblNow")
clblNow.Text = strNow

Последней строчкой кода в процедуре Main мы выполняем диалоговое окно с именем Dialog1:

Dlg.Execute()

Есть еще одна процедура, ее имя OKButton_execute, это событийная процедура, она выполняется в момент нажатия кнопки с именем OKButton, и выполняет она закрытие диалогового окна с именем Dialog1, это следующая строчка кода:

Dlg.endExecute()

Теперь присвойте процедуру с именем OKButton_execute кнопке с именем OKButton как событие на "Отпускание клавишы мыши". Для этого откройте визкальный редактор диалога Dialog1, смотрите цифру 1 на рисунке 9. Выделите Кнопку с именем OKButton и перейдите на вкладку "События", смотрите цифру 2 на рисунке 9. В событие "Отпускание клавиши мыши" укажите процедуру с именем OKButton_execute, нажав конопку с тремя точками, смотрите цифру 3 на рисунке 9. 

Рисунок 9. Назначение события кнопке ОК

Все необходимое для запуска макроса мы сделали. Для начала посмотрим что у нас делает макрос. Под цифрой 2 рисунка 10 вы видите созданное диалоговое окно с отображаемой текущей датой и временем. Если нажать кнопку OK, то диалоговое окно закроется.

Рисунок 10. Окно нашего макроса

Далее рассмотрим все варианты запуска нашего макроса по порядку. 

1. Запуск макроса из редактора макросов LibreOffice Basic.Откройте в редакторе макросов LibreOffice Basic наш код и выполнтите его нажав клавишу F5 или нажав на кнопку выполнения макроса, смотрите цифру 1 рисунка 10.

2. Запуск макроса из диалогового окна "Выбор макроса", смотрите цифру 1 на рисунке 11. В диалоговом окне нам нужно выбрать наш макрос и нажать кнопку "Выполнить", смотрите цифру 2 на рисунке 11. 

Рисунок 11. Запуск макроса из диалога "Выбор макроса"

3. Запуск макроса из меню и подменю в приложениях LibreOffice Calc или LibreOffice Writer, смотрите цифру 0 рисунка 12. Для создания меню и подменю выберите пункт меню "Сервис" и подменю "Настройка", смотрите цифры 1 и 2 рисунка 12. В диалоговом окне "Настройка" на вкладке "Меню" нажмите кнопку "Создать...", введите имя нашего меню (например "Макросы"), смотрите цифру 3 рисунка 12. Чтобы создать подменю нашего меню "Макросы" нажмите кнопку "Добавить..." (смотрите цифру 4 рисунка 12.). 

Рисунок 12. Запуск макроса из меню и подменю в приложениях LibreOffice Calc или LibreOffice Writer

Потом в диалоговом окне "Добавить команды" выберите команду которая будет выполнятся при выборе нашего подменю (смотрите цифры 1 и 2 рисунка 13), т.е. мы выбираем наш макроc с именем Main. 

Рисунок 13. Добавляем команду для подменю

Переименуйте наше подменю выбрав пункт меню "Переименовать..." кнопки "Изменить"  диалогового окна "Настройка" вкладки "Меню" (смотрите цифры 1,2,3,4 рисунка 14). Подменю назовите "Макрос окно с датой".

Рисунок 14. Переименование подменю

4. Запуск макроса в приложениях LibreOffice Calc или LibreOffice Writer по нажатию сочетания клавиш на клавиатуре. Для это откройте диалог "Настройка" и выбирите вкладку "Клавиатура" (смотрите цифру 1 рисунка 15). Потом выбираем команду, которая является нашим макросом с именем "Main" (смотрите цифру 2 рисунка 15). Потом выбираем свободную комбинацию клавиш и назначаем их нашей команде, т.е. макросу с именем Main (смотрите цифры 3,4,5 рисунка 15).

Рисунок 15. Назначение комбинации клавиш для запуска макроса

5. Запуск макроса в приложениях LibreOffice Calc или LibreOffice Writer по нажатию кнопки на панели инструментов (смотрите цифру 4 рисунка 16). Для создания кнопки на панели инструментов выберите пункт меню "Сервис" и подменю "Настройка", смотрите цифру 1 рисунка 16. В диалоговом окне "Настройка" на вкладке "Панели инструментов" нажмите кнопку "Создать...", введите имя нашей панели инструментов (например "Панель макросов"), смотрите цифру 2 рисунка 16. Чтобы создать кнопку на панели инструментов нажмите кнопку "Добавить..." (смотрите цифру 3 рисунка 16). 

Рисунок 16. Запуск макроса из панели инструментов

Потом в диалоговом окне "Добавить команды" выберите команду которая будет выполнятся при нажатие по нашей кнопке нашей панели инструментов  (смотрите цифры 1 и 2 рисунка 13), т.е. мы выбираем наш макроc с именем Main. 

 Переименуйте нашу кнопку нашей панели инструментов выбрав пункт меню "Переименовать..." кнопки "Изменить"  диалогового окна "Настройка" вкладки "Панели инструментов" (смотрите цифры 1,2,3,4 рисунка 17). Кнопку назовите "Макрос окно с датой".

Рисунок 17. Переименование кнопки панели инструментов

6. Запуск макроса в приложениях LibreOffice Calc или LibreOffice Writer в ответ на какое-то событие, например события запуска приложения. Таким образам наш макрос выполняется когда запускается приложение LibreOffice Calc или LibreOffice Writer. Для создания события выберите пункт меню "Сервис" и подменю "Настройка", смотрите цифру 1 рисунка 18.  В диалоговом окне "Настройка" на вкладке "События" выберите событие "Запуск приложения" и нажмите кнопку "Макрос...", смотрите цифру 2 рисунка 18.

Рисунок 18. Назначение события для запуска макроса

Далее выберите макрос Main в окне "Выбор макроса" (смотрите цифры 1,2,3 рисунка 19). 

Рисуной 19. Диалог выбора макроса

Перезапустите приложение LibreOffice Calc или LibreOffice Writer и вы увидете что наш макрос запускается.

Ниже размещен полный код макроса.

REM  *****  BASIC  *****

Dim Dlg As Object

Sub OKButton_execute 

Dlg.endExecute()

End Sub

Sub Main

DialogLibraries.LoadLibrary("Standard")

Dlg = CreateUnoDialog(DialogLibraries.Standard.Dialog1)

Dim strNow As String

strNow = Format(Now(), "yyyy-mm-dd ")

strNow = strNow + Format(Time(), "hh:mm")

Dim clblNow As Object

clblNow = Dlg.getControl("lblNow")

clblNow.Text = strNow

Dlg.Execute()

End Sub