Макрос для "растягивания" формул

[Tigrik]

Для своей задачи было проведено очень много тестов и экспериментов, где приходилось копировать формулы в много тысяч строк (и ещё, наверное, многое предстоит протестировать).
Конечно, уже, с первых тысяч строк пришлось сделать макрос для "растягивания" формул и организовал это совместно с диалогом. Теперь, у меня рядом с любой рабочей таблицей есть ячейка с нужным диапазоном - скопировал её и -> в диалог.
В этих макросах нет ничего особенного - любой более-менее пользователь сделает подобное за пару-сек и более качественно, но мне очень это помогло - вывел на панель инструментов. Может быть, кому-то это пригодится.

Код Выделить Развернуть


Sub DialogFA
  Dim oDlgModel, oModel, DlgRez, s$
  Dim oTextEdit As Object

oDlgModel = CreateUnoService("com.sun.star.awt.UnoControlDialogModel")
oDlgModel.setPropertyValues( Array("PositionX", "PositionY", "Width", "Height", "Title"), Array(308, 200, 150, 44, "ЗАДАЙТЕ ДИАПАЗОН!" ) )
oDlg = CreateUnoService("com.sun.star.awt.UnoControlDialog")
oDlg.setModel(oDlgModel)
oDlg.createPeer(CreateUnoService("com.sun.star.awt.Toolkit"), null)

oModel = oDlgModel.createInstance("com.sun.star.awt.UnoControlEditModel")
oModel.setPropertyValues( Array("PositionX", "PositionY", "Width", "Height", "Align", "VerticalAlign"), Array(15, 15, 80, 14, 0, 1) )
oDlgModel.insertByName("TextFieldText",oModel)
oTextEdit = oDlgModel.getByName("TextFieldText")
oDlg.getControl("TextFieldText").setFocus()

oModel = oDlgModel.createInstance("com.sun.star.awt.UnoControlButtonModel")
oModel.setPropertyValues( Array(_
"PositionX", "PositionY", "Width", "Height", "Label", "PushButtonType", "Align", "VerticalAlign", "BackgroundColor", "DefaultButton"),_
Array(115, 15, 20, 14, "OK", com.sun.star.awt.PushButtonType.OK, 1, 1, RGB(55,255,155), True) )
oDlgModel.insertByName("ButtonOK", oModel)

DlgRez = oDlg.execute()
s = oTextEdit.Text
If makroFAFormula(s) = 0 Then
Msgbox "ОШИБКА!!!"
Exit Sub
EndIf
Msgbox "Всё сделано!!!"
End Sub

Function makroFAFormula(s As String) As Boolean
  Dim oDoc
oDoc = ThisComponent.CurrentSelection
If Not oDoc.supportsService("com.sun.star.sheet.SheetCell") Then
makroFAFormula() = 0
Exit Function
EndIf
oDoc.SpreadSheet.getCellRangeByName(s).fillAuto(com.sun.star.sheet.FillDirection.TO_BOTTOM, 1)
makroFAFormula() = 1
End Function


Диалог, естественно для меня, программируемый - мне так было удобнее. Без проблем можно поменять.
Сообщения для большого объема очень нужны - подскажет, что уже "всё сделано!"
Непонятно для меня осталось: 280 000 ячеек заполняет за одну минуту, а 600 000 - за 4 с половиной минут. Такая непропорциональность может быть связана с объемом памяти? Или другое?

Но мой основной вопрос - это по стандартной проверке, которая используется в моей функции makroFAFormula.
До этого, не было возможности и времени побольше узнать об этой проверке:

Код Выделить Развернуть


If Not oDoc.supportsService("com.sun.star.sheet.SheetCell")

Всё бы хорошо, но у меня в одном случае эта проверка выдаёт ошибку.
А именно - если запускается этот диалог и на текущем листе выделен ЛЮБОЙ ДИАПАЗОН! Если выделена ЯЧЕЙКА, то ошибки нет.
Так и должно быть?

[eeigor]

Но мой основной вопрос - это по стандартной проверке, которая используется в моей функции makroFAFormula.
До этого, не было возможности и времени побольше узнать об этой проверке:
Код:
If Not oDoc.supportsService("com.sun.star.sheet.SheetCell")
Всё бы хорошо, но у меня в одном случае эта проверка выдаёт ошибку.
А именно - если запускается этот диалог и на текущем листе выделен ЛЮБОЙ ДИАПАЗОН! Если выделена ЯЧЕЙКА, то ошибки нет.
Так и должно быть?

Да нет. Ошибку выдаёт строка
 oDoc = ThisComponent.CurrentSelection
когда нет активного (открытого) документа Calc при вызове из меню нажатием кнопки (куда Вы там её добавили).
Если Вы хотите отследить выделение одной ячейки (она же опорная для метода fillAuto, и делать тогда нечего), то сделайте так:
 If oDoc.supportsService("com.sun.star.sheet.SheetCell") And Not oDoc.supportsService("com.sun.star.sheet.SheetCellRange")
тогда происходит выход из функции с возвращением 0 и сообщением об ошибке.
Ошибка в Вашем коде вызвана тем, что сервис com.sun.star.sheet.SheetCellRange поддерживает одновременно и com.sun.star.sheet.SheetCell, однако обратное неверно: сервис com.sun.star.sheet.SheetCell не поддерживает com.sun.star.sheet.SheetCellRange. Это связано с тем, что отдельная ячейка является одновременно и диапазоном ячеек, состоящим из одной ячейки. В общем, как-то так...

Однако по мне так это проще нажать на крестик в правом нижнем углу опорной (первой) ячейки, и весь диапазон (CurrentRegion) заполнится. Будет достигнут практически тот же результат без указания диапазона вручную в окне Вашего диалога. А вот пустых строк, разрывающих диапазоны, быть просто не должно.
Вывод: макрос для "растягивания" формул не требуется.

[Tigrik]

Этот макрос у меня выведен на панель инструментов (стандартная кнопочка в ленте (строке) "Стандартная") и видна во всех документах Calc.

Лично мне, не надо было ничего отслеживать - просто, в какой-то момент, увидел сообщение об ошибке и понял, что это от выделение любого диапазона.
Поставил в макрос Вашу проверку - теперь нет этой ошибки.

Кстати, попробовал макрос, чтобы он заполнил диапазон на другом листе - ошибка ("Ошибка времени выполнения Basic.") в строке:

Код Выделить Развернуть


oDoc.SpreadSheet.getCellRangeByName(s).fillAuto(com.sun.star.sheet.FillDirection.TO_BOTTOM, 1)

Скорее всего, это, опять же, ругается на:

Код Выделить Развернуть


oDoc = ThisComponent.CurrentSelection

Но я не буду так делать - запускать этот макрос из другого листа.

Честно говоря, я не совсем понял, про Ваш последний абзац в сообщение.
Если этот крестик, с помощью которого и растягивается диапазон, тогда макрос, как раз, для того, чтобы и не тянуть тысячи и тысячи строк.
Конечно, каждый поступает как ему проще или как он привык и я совсем не рекламирую свой макрос.
Находясь в любом месте листа, задается диапазон (главное, чтобы первая строка диапазона была с формулами, которые необходимо "растянуть" на весь диапазон) и всё - работа выполнена.
Небольшой объем, наверное, удобнее с крестиком, но несколько тысяч (или несколько десятков тысяч) строк - это для макроса.
И, только что, обнаружил, что второй параметр в функции fillAuto - как заполнять диапазон строками с формулами (английский я знаю плохо - экспериментально узнал). Если 1 - каждую первую, то есть все строки в диапазоне, если 2 - каждую вторую и т.д. Вручную так, наверное, и не сделаешь, а через эту функцию - пожалуйста.

[eeigor]

Всё проще: по крестику надо сделать двойной щелчок ЛКМ, тянуть не надо. Будет вызвана та же функция fillAuto, что и в Вашем макросе.

А изучать чужой код, писать макросы самому – всегда полезно.

Edit:
Заметил в функции семантическую ошибку:
oDoc – это ThisComponent, а
ThisComponent.CurrentSelection – это в данном случае выделенный диапазон. Поправьте.
oSel = ThisComponent.CurrentSelection

Но в данной функции эти переменные Вам не нужны. Достаточно:
oSheet = ThisComponent.CurrentController.ActiveSheet
oSheet.getCellRangeByName(s).fillAuto ...

[Tigrik]

Всё проще: по крестику надо сделать двойной щелчок ЛКМ, тянуть не надо. Будет вызвана та же функция fillAuto, что и в Вашем макросе.

Спасибо.
А ведь знал об этом, но долго не пользовался и забыл.

Но, как я понял, здесь есть одно большое НО. Если я не прав, то поправьте.
Для того чтобы функция знала до какой строки ей тянуть диапазон необходим столбец (справа или слева, но вплотную к этому диапазону) с любым наполнением (символы или цифры).
Первая пустая ячейка в этом столбце остановить заполнение диапазона формулами. Значит этот столбец с данными, который покажет до какой строки идёт заполнение, нужно СОЗДАТЬ!. А это: или, также, в ручную тянуть; или как-то программно.

[Tigrik]

Скажите, пожалуйста, чем отличается это выражение, которое используется в моём макросе:

Код Выделить Развернуть


ThisComponent.CurrentSelection.SpreadSheet.getCellRangeByName(s).fillAuto ...

от этого, которое Вы предалагаете:

Код Выделить Развернуть


ThisComponent.CurrentController.ActiveSheet.getCellRangeByName(s).fillAuto ...


[eeigor]

На счёт большого «НО».
Да. Работает, я писал, в пределах текущего региона (CurrentRegion), но, как правило, этого достаточно. И это правильно: пустые строки нарушают работу Автофильтра. Значит Вам зачем-то надо отрывать столбец с формулами от расчетных данных слева...
Я храню на листе в таблице только данные, и не разрываю диапазон.

Чем отличается
ThisComponent.CurrentSelection.SpreadSheet – это и есть ссылка на активный лист.

[eeigor]

Но, как я понял, здесь есть одно большое НО. Если я не прав, то поправьте.

Да, есть такое "НО".
Автозаполнение действительно работает не с CurrentRegion, а только рядом с примыкающим полностью заполненным столбцом (который может и не участвовать в вычислениях, но задаёт "высоту прогона", служит, своего рода, ориентиром). Таким образом, автозаполнение легко может быть прервано из-за присутствия пустой ячейки слева. Тогда Ваш макрос имеет смысл.

[Tigrik]

Тогда Ваш макрос имеет смысл.

И, все-таки, Ваша подсказка с крестиком, мне, сейчас, очень и очень помогает.
У меня уже готовы все расчётные таблицы, но я экспериментирую с различными формулами в столбцах. Изменяю первую ячейку в столбце и на крестик - ориентируясь на высоту соседних столбов формулы перезаписываются в этом столбце. До кнопки с макросом тоже недалеко тянуться, но так оперативнее.
Спасибо.

---
Так, мысли вслух.
Буквально вчера, 40 тысяч строк по 15 столбцов заполнялись формулами около 5 минут. А, сейчас, 1 столбец на эти же 40 тысяч строк - почти в два раза дольше - 9 с половиной минут. Не понятно!
Наверное, Calc бедолага устал к вечеру.

[economist]

Calc и Excel быстро скисают на 25k+ строках. Для скорости - нужен другой инструмент, Python + Pandas.  

Для примера и контраста. Вот такой код на Python за-ВПР()-ит две таблицы по 50 тыс строк на 40 столбцов за 10 секунд (в итоговой таблице будет 40+40=80 столбцов) и даже успеет сохранить их в ODS:

Код Выделить Развернуть


import pandas as pd
t1 = pd.read_excel('D:/1.ods')
t2 = pd.read_excel('D:/2.ods')
t3 = pd.merge(t1, t2, how='left', on='ИНН')
t3.to_excel('D:/3.ods')


[Tigrik]

economist, Благодарю.
Я, уже, для себя, взял это на заметку.
Сначала, я хочу более-менее доработать и "обкатать" алгоритм для этой своей задачи в условиях Basic+Calc (раз уж взялся - осталось немного).
Затем, при наличие возможности и времени, обратится по Вашей рекомендации к Python + Pandas (давно хотел попробовать на "вкус" Питона - так, по-любительски).
И эту же задачу, для сравнения, решить на этой связке.

[Tigrik]

Таким образом, автозаполнение легко может быть прервано из-за присутствия пустой ячейки слева.

Небольшое уточнение.
Этот механизм автозаполнения работает и тогда, когда соседний "направляющий" столбец находится справа - главное, чтобы он был не ПУСТОЙ (любое содержание) до строки в диапазоне автозаполнения.
Вчера, при экспериментах в своих таблицах, приходилось "обнулять" некоторые столбцы - этот механизм не работал (что и понятно) - либо растягивать пустую ячейку (или ряд пустых ячеек) на весь диапазон, либо программно.