[Решено] Calc: Как лучше перебрать все пустые ячейки в нескольких диапазонах?

Автор eeigor, 29 ноября 2020, 17:36

0 Пользователи и 1 гость просматривают эту тему.

eeigor

rami, работает, спасибо.
Вот как вы это ищете?!

Открыл-увидел много свойств у CurrentController, что есть в Параметрах...

На форуме OpenOffice автор дополнительно вызывал Doc.calculateall(). Но LO Calc не обновил-таки параметры.
Ubuntu 18.04 LTS • LibreOffice 7.5.1.2 Community

eeigor

Ещё раз спасибо всем. Эта ветка была очень полезной.

UPD:
Необходимо добавить.
Несмотря на то, что кодом выше удавалось изменить синтаксис формулы в файле настроек, и в Параметрах значение обновлялось в списке выбора, но всё-равно потребовалась перезагрузка LO.
На Windows 10 вскрылась и другая ошибка:
при установленном параметре синтаксиса формулы "Excel R1C1" перестала работать строка в коде:

oCell = ThisComponent.Sheets.getByName("Имя_листа").getCellRangeByName("Имя_диапазона")
Ubuntu 18.04 LTS • LibreOffice 7.5.1.2 Community

sokol92

Думаю, что это (серьезный) баг с именем листа, про который следует сообщить (если не было прецедентов).

Я использую синтаксис "Excel R1C1" исключительно для отладки формул и всегда восстанавливаю значение по умолчанию.
Владимир.

eeigor

Цитата: sokol92 от  3 декабря 2020, 15:08
Я использую синтаксис "Excel R1C1" исключительно для отладки формул и всегда восстанавливаю значение по умолчанию.
И я пришёл к такому же выводу. Будет время, подготовлю пример и напишу багрепорт, но я не делал этого раньше.
Ubuntu 18.04 LTS • LibreOffice 7.5.1.2 Community


eeigor

Ubuntu 18.04 LTS • LibreOffice 7.5.1.2 Community

kompilainenn

Поддержать разработчиков LibreOffice можно тут, а наш форум вот тут

eeigor

Ubuntu 18.04 LTS • LibreOffice 7.5.1.2 Community

kompilainenn

Цитата: eeigor от 30 марта 2021, 20:17Вроде как что-то исправили...
убедительная просьба скачать ежедневную сборку и проверить, действительно ли исправлено, если да - прошу отписаться в баг репорте
Цитата: eeigor от 30 марта 2021, 20:17В этом есть капля и нашего труда.
далеко не капля, спасибо за баг репорт
Поддержать разработчиков LibreOffice можно тут, а наш форум вот тут

eeigor

OFFTOPIC
Однако, это решение появилось в процессе обсуждения поставленной задачи.

Цитата: sokol92 от  2 декабря 2020, 16:41Формулу можно задавать в синтаксисах Calc, Excel A1 и Excel R1C1 (последний стиль подходит для данной задачи).
' Возвращает формулу Calc, преобразованную из одного синтаксиса в другой.
' Параметры:
' oDoc       документ Calc
' oCell      ячейка, формула для которой задается
' Formula    текст формулы
' FromSyntax синтаксис исходной формулы: 0-Calc; 1-Excel A1; 2-Excel R1C1
' ToSyntax   синтаксис целевой формулы (значения как у FromSyntax)
Function ConvertFormula(Byval oDoc, Byval oCell, Byval Formula As String, Byval FromSyntax As Long, Optional ByVal ToSyntax As Long) As String
  Dim oFP, arr
  If IsMissing(ToSyntax) Then
    ToSyntax=0
  End If
 
  If FromSyntax=ToSyntax Or left(Formula, 1)<>"=" Then
    ConvertFormula=Formula
  Else
    oFP=oDoc.createInstance("com.sun.star.sheet.FormulaParser")
    oFP.formulaConvention=FromSyntax
    arr=oFP.parseFormula(mid(Formula,2), oCell.cellAddress)
    oFP.formulaConvention=ToSyntax
    ConvertFormula="=" & oFP.printFormula(arr, oCell.cellAddress)
  End If
End Function


По собственному опыту скажу, что наиболее часто при программировании в MS Excel используется стиль R1C1 (это действительно удобно!). Однако там есть и соответствующее свойство FormulaR1C1.

В LO Calc такого свойства нет, но проблема очень легко решатся. Используя код от коллег @sokol92 (см. ConvertFormula) и @Lupp (см. setRCformula) я предлагаю довольно простое, но не очевидное решение:

<...>
   Dim oCell As Object
   Dim sFormulaR1C1$: sFormulaR1C1 = "=VLOOKUP(RC[-1];colours;2;0)"  'зд. формула в стиле R1C1 - только для примера
<...>
   oCell.Tokens = TokenizeFormulaR1C1(sFormulaR1C1, oCell)
<...>

Function TokenizeFormulaR1C1(sFormulaR1C1$, oCell As Object) As Object
   Dim oParser As Object
   oParser = ThisComponent.CreateInstance("com.sun.star.sheet.FormulaParser")
   oParser.FormulaConvention = com.sun.star.sheet.AddressConvention.XL_R1C1

   TokenizeFormulaR1C1 = oParser.parseFormula(sFormulaR1C1, oCell.CellAddress)
End Function


Вот эта строка всё и определяет:
   oCell.Tokens = TokenizeFormulaR1C1(sFormulaR1C1, oCell)
Да-да, мы задаём формулу в стиле R1C1 через свойство Tokens, поскольку свойства FormulaR1C1 нет. И не надо.
Неверно выразился: токены содержат всю информацию о формуле, позволяющую представить формулу в любом стиле.

При этом свойство ячейки Formula будет автоматически заполнено текстом формулы с учётом параметра Formula syntax (grammar) в стиле Calc A1. А больше ничего не требуется. Не надо вычислять диапазоны/ячейки: всё "относительно" и, соответственно, одинаково.

@sokol92, как оказалось, нет нужды предварительно удалять знак равенства в формуле при её разборе (см. метод <FormulaParser>.parseFormula), а также незачем "сшивать" формулу с новым синтаксисом (см. метод <FormulaParser>.printFormula): Calc это и так успешно делает через последовательность токенов.

UPDATED1:
Впрочем, удобства ради можно заменить строку
   oCell.Tokens = TokenizeFormulaR1C1(sFormulaR1C1, oCell)
вызовом процедуры:
   Call SetFormulaR1C1(sFormulaR1C1, oCell)
скрывающей работу с токенами.

Sub SetFormulaR1C1(sFormulaR1C1$, oCell As Object)
   Dim oParser As Object
   oParser = ThisComponent.CreateInstance("com.sun.star.sheet.FormulaParser")
   oParser.FormulaConvention = com.sun.star.sheet.AddressConvention.XL_R1C1

   oCell.Tokens = oParser.parseFormula(sFormulaR1C1, oCell.CellAddress)
End Sub


Прим. Причём свойство ячейки Formula всегда, вне зависимости от выбранного синтаксиса, хранит текст формулы в стиле Calc A1. Этот параметр влияет только на отображение формулы на листе. То есть текст формулы на листе формируется из последовательности токенов с учётом параметра Formula syntax (grammar).

UPDATED2:
И вот таким способом можно получить отображаемую формулу (выберите ячейку с формулой на листе и выполните процедуру TestGetDisplayedFormula):
Function GetDisplayedFormula$(oCell As Object) As String
   On Local Error GoTo HandleErrors

   GetDisplayedFormula = oCell.Formula
   If oCell.Type <> com.sun.star.table.CellContentType.FORMULA Then
       Exit Function
   End If

   Dim nSyntax%
   nSyntax = GetFormulaSyntax()
   'With com.sun.star.sheet.AddressConvention
   '    ' 0=.OOO (Calc A1)|1=.XL_A1 (Excel A1)|2=.XL_R1C1 (Excel R1C1)
   '    If nSyntax = .OOO Then
   '        GetDisplayedFormula = oCell.Formula
   '    Else  'Excel ReferenceStyle
           Dim oParser As Object
           oParser = ThisComponent.createInstance("com.sun.star.sheet.FormulaParser")
           oParser.FormulaConvention = nSyntax
           GetDisplayedFormula = "=" & oParser.printFormula(oCell.Tokens, oCell.CellAddress)
   '    End If
   'End With
   Exit Function

HandleErrors:
   Msgbox "Error " & Err & " in line " & Erl & ": " & Error _
    , MB_ICONSTOP, "macro:GetDisplayedFormula$()"
End Function

Function GetFormulaSyntax%()
''' Returns: Formula syntax grammar.

   Dim sNodePath As String
   GlobalScope.BasicLibraries.LoadLibrary("Tools")
   sNodePath = "/org.openoffice.Office.Calc/Formula/Syntax"
   GetFormulaSyntax = GetRegistryKeyContent(sNodePath).getByName("Grammar")
End Function

Sub TestGetDisplayedFormula()
   Dim oCell As Object
   oCell = ThisComponent.CurrentSelection
   MsgBox GetDisplayedFormula(oCell)
End Sub


UPDATED2:
С использованием предложенного кода мой инспектор активной ячейки выдаёт много интересного.
Обратите внимание: даже имена одной и той же формулы не совпадают в данном примере, а не только нотация.
Ubuntu 18.04 LTS • LibreOffice 7.5.1.2 Community

sokol92

Здравствуйте, Игорь! Спасибо за произведенное исследование!

Методы getTokens и setTokens являются для меня новыми, так как они не упомянуты в документации по сервису SheetCell.

Что касается отображаемой формулы в ячейке - то это свойство FormulaLocal.
Владимир.

eeigor

Цитата: sokol92 от  4 января 2022, 18:11Что касается отображаемой формулы в ячейке - то это свойство FormulaLocal.
Да, действительно: для примера выше (см. скриншот) FormulaLocal содержит:
   =MULTIPLE.OPERATIONS($I$3;$I$1;$I5;$J$1;J$3)
Точно в той же нотации (Calc A1), как и для свойства Formula.

Почему-то у меня перестал работать флажок: Use English function names (?). И, кстати, давно заметил...
Не использовал русские наименования... Полагаю, что имена функций должны быть по-русски. MULTIPLE.OPERATIONS это что, русский эквивалент имени TABLE?
Но из последовательности Tokens генерируется именно это наименование: MULTIPLE.OPERATIONS (не вижу связи с FormulaLocal), а не TABLE.

P.S. В процедуре GetDisplayedFormula() я закомментировал блок строк, где: If nSyntax = .OOO Then
потому что правильность отображаемой формулы зависит не только от текущего синтаксиса, но и от имени используемой в ней функции (в примере выше это MULTIPLE.OPERATIONS). Выходит, что даже в стиле Calc A1 отображение может отличаться от текста, хранимого в свойстве Formula, а от текста, хранимого в свойстве FormulaLocal, отличаться может используемой нотацией (синтаксисом).
Ubuntu 18.04 LTS • LibreOffice 7.5.1.2 Community

sokol92

Флажок "Use English function names" не работает, если параметр языка интерфейса (Настройки языка / Языки / Пользовательский интерфейс) изменен на Aнглийский (США), что выглядит естественным, поскольку имена функций и так английские.
По поводу MULTIPLE.OPERATIONS на форуме дискуссия ведется в другой теме:)
Владимир.

eeigor

Владимир, спасибо! Вопрос снят.
Замечу только, что токены собирают формулу с учетом локали (FormulaLocal), но и с учетом синтаксиса (стиля ссылок).
Ubuntu 18.04 LTS • LibreOffice 7.5.1.2 Community

sokol92

Цитата: eeigor от  4 января 2022, 19:09токены собирают формулу с учетом локали

Можно и без учета локали (в локализации en_US, или, как написал Михаил, "канонической"). Для этого у сервиса FormulaParser есть свойство CompileEnglish. Вот уточненная функция для перевода формул (обратите внимание на два последних параметра):

' Возвращает формулу Calc, преобразованную из одного синтаксиса в другой.
' Параметры:
' oDoc        документ Calc.
' oCell       ячейка, формула для которой задается.
' Formula     текст формулы.
' FromSyntax  синтаксис исходной формулы: 0-Calc; 1-Excel A1; 2-Excel R1C1.
' ToSyntax    синтаксис целевой формулы (значения как у FromSyntax).
' FromEnglish исходная формула задана в локализации en_US (умолчание False).
' ToEnglish   возвращаемая формула в локализации en_US (умолчание - равно FromEnglish). 
Function ConvertFormula(Byval oDoc, Byval oCell, Byval Formula As String, Byval FromSyntax As Long, Optional ByVal ToSyntax As Long, _
                        Optional ByVal FromEnglish As Boolean, Optional ByVal ToEnglish As Boolean) As String
   Dim oFP, arr
   If IsMissing(ToSyntax) Then ToSyntax=0
   If IsMissing(FromEnglish) Then FromEnglish=False
   If IsMissing(ToEnglish) Then ToEnglish=FromEnglish

   If FromSyntax=ToSyntax Or left(Formula, 1)<>"=" Then
     ConvertFormula=Formula
   Else
     oFP=oDoc.createInstance("com.sun.star.sheet.FormulaParser")
     oFP.formulaConvention=FromSyntax
     oFP.CompileEnglish=FromEnglish
     arr=oFP.parseFormula(mid(Formula,2), oCell.cellAddress)
     oFP.formulaConvention=ToSyntax
     oFP.CompileEnglish=ToEnglish
     ConvertFormula="=" & oFP.printFormula(arr, oCell.cellAddress)
   End If
End Function


Пример (выделена ячейка A1).
Sub Test
 msgbox ConvertFormula(ThisComponent, ThisComponent.CurrentSelection, "=Int(RC[2])", 2, 0, True, False)
End Sub


возвращает: =ЦЕЛОЕ(C1).
Владимир.