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

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

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 &  ")  "