LXF81:OOo Basic

Материал из Linuxformat.

OOo Basic. Макросы в Calc

часть 2 Держите таблицы на расстоянии вытянутой руки и работайте с данными из консоли — просто следуйте за Марком Бэйном!

Со времен разностной машины Чарльза Бэббиджа и до появления табличного процессора Calc люди стараются изобретать средства автоматизации зубодробительных вычислений. Один из способов избежать монотонной работы — использование электронных таблиц, легко управляющихся с нудными столбцами цифр. Благодаря комбинации OOo Basic и Calc возможно не только автоматизировать выполнение сложных задач, но и, как я продемонстрирую, манипулировать данными прямо из командной строки. Как и в прошлый раз, первый шаг — создание документа. Код для открытия нового пустого документа Writer:

sub main
 loadNewFile
end sub
sub loadNewFile
 dim doc as object, desk as object, myFile as string, Dummy()
 myFile = "private:factory/sWriter"
 desk = CreateUnoService("com.sun.star.frame.Desktop")
 doc = desk.loadComponentFromUrl(myFile,"_blank", 0,Dummy())
end sub

Просмотрев код, вы увидите, что тип открываемого файла определяется строкой

myFile = "private:factory/sWriter"

Теперь надо бы знать, что подставить вместо swriter. Для открытия таблицы необходимо сделать замену на sCalc:

myFile = "private:factory/sCalc"

Будьте ленивее

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

sub main
 loadNewFile("sCalc")
end sub
sub loadNewFile (filetype as string)
 dim doc as object, desk as object, myFile as string, Dummy()
 myFile = "private:factory/" & filetype
 desk = CreateUnoService("com.sun.star.frame.Desktop")
 doc = desk.loadComponentfromurl(myFile,"_blank",0,Dummy())
end sub

Благодаря тому, что наша процедура принимает тип файла в качестве аргумента, она становится гораздо более гибкой. Что еще более важно, можно обойтись всего одной процедурой для открытия и документа Writer, и таблицы Calc. При желании можно даже задать тип по умолчанию, используя опциональный параметр и метод isMissing:

sub loadNewFile (optional filetype as string)
if isMissing(filetype) then
 filetype = "sCalc"
end if

Хорошо, теперь мы умеем открывать пустую таблицу — а как насчет записи в ячейку? Следующая процедура поможет это сделать:

sub writeToCell
 dim sheet as object, cell as object
 sheet = thisComponent.sheets(0)
 cell = sheet.getCellByPosition(0,0)
 cell.string = "Hello World"
end sub

Запомните, что эту процедуру необходимо вызывать из процедуры Main.

Не мешает просмотреть процедуру writeToCell, чтобы как следует понять ее работу. thisComponent мы уже видели (когда рассматривали OOo Basic и документ Writer): это просто ссылка на текущий документ (в нашем случае — на таблицу). Далее мы выбираем лист, с которым будем работать; sheet(0) является первым листом (или Sheet1) в Calc. Sheet(1) будет ссылаться на второй лист, и так далее. Наконец, мы выбираем нужную ячейку с помощью метода getCellByPosition, который требует в качестве входных параметров номер столбца и номер строки. Position(0,0) ссылается на ячейку A1, (1,0) соответствует B1, (0,2) — А2, и так далее.

Все это здорово, но порядок ваших листов может меняться; что если вы хотите обращаться к ним по именам? Нет проблем — вместо sheets можно использовать метод getByName:

sheet=thisComponent.sheets.getByName("Sheet1")

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

sub simple_maths
 dim sheet as object, cell as object
 sheet = thisComponent.sheets.getByName("Sheet1")
 cell = sheet.getCellByPosition(0,0)
 cell.value = 10
 cell = sheet.getCellByPosition(0,1)
 cell.value = 10
 cell = sheet.getCellByPosition(0,2)
 cell.formula ="= A1+A2"
end sub

Особой пользы тут нет, но зато этот пример показывает, что загружать данные в таблицу и затем совершать над ними операции очень просто. Можно улучшить процедуру, разрешив ввод чисел в процедуру в качестве аргументов:

sub simple_maths(numbA as double, numbB as double)
 dim sheet as object, cell as object
 sheet=thisComponent.sheets.getByName("Sheet1")
 cell=sheet.getCellByPosition(0,0)
 cell.value=numbA
 cell=sheet.getCellByPosition(0,1)
 cell.value=numbB
 cell=sheet.getCellByPosition(0,2)
 cell.formula="=A1+A2"
end sub

Теперь надо немного изменить процедуру Main:

simple_maths(12.5,35.7)

Пример, конечно, тривиальный: было бы куда быстрее вбить цифры в таблицу вручную. Но ведь это только начало — вы можете приняться за любые усложнения обработки данных согласно вашим потребностям. По-вашему, 2 числа — это слишком мало: а вдруг понадобится передать 10, 100 или 1000 значений? К счастью, в процедуру очень легко передать массив:

sub main
 loadNewFile
 simple_maths_array(array(45,67,89,34))
end sub
sub simple_maths_array(numbers)
 dim sheet as object, cell as object, r as integer, sum as double
 sheet = thisComponent.sheets.getByName("Sheet1")
 sum = 0
 for r = 0 to ubound(numbers)
  sum = sum + numbers(r)
  cell = sheet.getCellByPosition(0,r)
  cell.value = numbers(r)
 next
 cell = sheet.getCellByPosition(0,r+1)
 cell.value = sum
end sub

Процедура simple_maths_array заполняет первую колонку Sheet1 содержимым массива, а затем внизу подсчитывает сумму всех элементов.

Записав данные в таблицу, вы заинтересуетесь: можно ли использовать данные из существующей таблицы? Естественно, можно, а то бы я и упоминать об этом не стал. Следующая процедура открывает существующую таблицу (~/test.ods) и отображает содержимое ячейки A1 листа Sheet1:

sub dataFromExistingFile
 dim doc as object, desk as object, sheet as object, cell as object
 dim url as string, contents as double, Dummy()
 desk = CreateUnoService("com.sun.star.frame.Desktop")
 url=file://~/test.ods
 doc = desk.loadComponentfromurl(url,"_blank",0,Dummy())
 sheet = thisComponent.sheets.getByName("Sheet1")
 cell = sheet.getCellByPosition(0,0)
 contents = cell.value
 msgbox(contents)
end sub

Тут вы, видимо, спросите: а что будет, если ячейка содержит текст, а не число? Наверное, команда contents = cell.value вызовет ошибку, и процедура не выполнится? А вот и нет: если ячейка содержит текст, то параметр value будет установлен в ноль, таким образом, проблема будет устранена.

Немного математики

Пока что мы всего-навсего писали и читали данные из ячеек. Пора заняться чем-нибудь поинтереснее. Как насчет использования встроенных математических функций OpenOffice.org Calc? Допустим, мы хотим посчитать сумму, среднее чисел и стандартное отклонение. Это можно сделать, используя сервис FunctionAccess:

sub usingOOoFunctions(iArray)
 dim service as object, sheet as object, cell as object
 service = createUnoService( "com.sun.star.sheet.FunctionAccess" )
 sheet = thisComponent.sheets.getByName("Sheet1")
 cell = sheet.getCellByPosition(0,0)
 cell.value = service.callFunction( "STDEV", iArray )
end sub

Как всегда, не забудьте изменить процедуру Main, чтобы новая процедура смогла выполниться:

usingOOoFunctions(array(45,67,89,34))

Не сомневаюсь, что вы немедля найдете кучу недостатков у usingOOoFunctions — на данный момент она умеет считать только стандартное отклонение, использует только Sheet1 и пишет только в ячейку A1. Но, используя входные параметры, ее можно сделать весьма адаптивной:

sub usingOOoFunctions( fType as string, sName as string, _c as integer,r as integer, iArray )
 dim service as object, sheet as object, cell as object
 service = createUnoService( "com.sun.star.sheet.FunctionAccess" )
 sheet = thisComponent.sheets.getByName(sName)
 cell = sheet.getCellByPosition(c,r)
 cell.value = service.callFunction( fType, iArray )
end sub

Модифицируйте Main следующим образом:

usingOOoFunctions("STDEV","Sheet1", 1, 1, array(45,67,89,34))

Возникает серьезный вопрос: как обрабатывать неверные операции или входные данные? Например, что произойдет при попытке выполнить

usingOOoFunctions("SQRT","Sheet1", 1, 1, array(-1))

• то есть извлечь квадратный корень из «-1»? (Надеюсь, вы в курсе, что так делать нельзя [ну, по крайней мере, на множестве действительных чисел, — прим.ред.].) Ошибочные ситуации можно отсечь, написав следующий код:

if (fType <> "SQRT" and iArray(0) <> -1 ) then

• но тогда выходит, что вы обязаны предусмотреть все возможные комбинации функций и их аргументов, способные вызвать ошибку.

Самым эффективным решением будет написание обработчика ошибок. Рассмотрим пример (он завершится аварийно):

function dummy as double
 dim service as object
 service = createUnoService( "com.sun.star.sheet.FunctionAccess" )
 dummy = service.callFunction( "SQRT", array(-1) )
end function

Запустите ее с

msgbox (dummy)

На последней строке функция начнет ругаться, но это можно предотвратить, вставив в ее начало выражение, которое при наличии ошибки просто отошлет к выполнению следующей строки кода. Вы, видимо, заявите (довольно верно), что нам незачем продолжать выполнение кода — лучше изящно выйти вон. Значит, потребуется добавить кое-какой код для правильной обработки ошибки:

function dummy as double
 dim service as object
 on error goto errorFound
 service = createUnoService( "com.sun.star.sheet.FunctionAccess" )
 dummy = service.callFunction( "SQRT", array(-1) )
 exit function
 errorFound:
 msgbox("Invalid input. Result set to -1")
 dummy=-1
end function

Теперь функция не продолжит выполнение, а перескочит на метку errorFound: (двоеточие означает, что данная лексема является меткой). Заметим, что exit function стоит ДО кода обработки ошибки, иначе этот код будет выполняться всегда, хоть бы ошибки и не было — а нам- то надо, чтобы ошибка обрабатывалась, только если она действительно возникла.

Функция не есть процедура

В приведенных примерах мы использовали функции и процедуры. Вы спросите: а в чем разница? Функция и процедура — почти одно и то же, только функция еще и возвращает результат. Это значит, что, определяя функцию, вы должны указать, какой тип результата она возвратит. Вот простой пример, который вам все объяснит.

Сначала установим значение переменной с помощью процедуры:

dim sheet as object, cell as object
sub main
 loadNewFile
 sheet=thisComponent.sheets(0)
 cell=sheet.getCellByPosition(0,0)
 simple_sub
end sub
sub simple_sub
 cell.value = 1
end sub

Теперь сделаем то же самое, но уже с помощью функции:

dim sheet as object, cell as object
sub main
 loadNewFile
 sheet=thisComponent.sheets(0)
 cell=sheet.getCellByPosition(0,0)
 cell.value = simple_function
end sub
function simple_function as integer
 simple_function = 1
end function

Заметили? Процедура записывает напрямую в ячейку, а функция возвращает значение, а уж оно затем записывается в ячейку.

Следует заметить еще одно: некоторые переменные (sheet и cell) объявлены глобальными. Это значит, что они доступны из любой функции и процедуры. Если переменная определена внутри процедуры, то она существует только на время выполнения функции или процедуры (их часто называют областью видимости переменной). Вещь полезная, но из-за этого вы должны быть очень внимательны, назначая имена переменным:

dim sheet_number as integer
dim sheet as object, cell as object
sub main
 loadNewFile
 set_sheetnumber
 sheet = thisComponent.sheets(sheet_number)
 cell = sheet.getCellByPosition(0,0)
 cell.value = sheet_number
end sub
sub set_sheetnumber
 sheet_number = 1
end sub

Число 1 записывается в ячейку А1 листа Sheet2.

Если бы мы вставили строку dim sheet_number as integer в процедуру set_sheetnumber в вышеописанном примере, то создалась бы новая переменная sheet_number, доступная только из процедуры set_sheetnumber. Хотя имена в обеих процедурах совпадают, это две разных переменных, содержащих разные значения.

Теперь мы можем легко и просто читать и записывать любую ячейку на любом листе таблицы. Значит, настало время заняться именами листов. Они не оригинальны — Sheet1, Sheet2, Sheet3 — и не информативны. К тому же их всего три.

sub changeSheetNames
 dim sheet as object
 sheet = thisComponent.createInstance("com.sun.star.sheet.Spreadsheet")
         thisComponent.Sheets.insertByName("MySheet", Sheet)
         thisComponent.sheets.removebyname("Sheet1")
         thisComponent.sheets.removebyname("Sheet2")
         thisComponent.sheets.removebyname("Sheet3")
end sub

Легко и просто — но малость ограниченно. Было бы действительно полезно передавать имена листов в качестве массива — смотрите:

dim i as integer
for i = 0 to ubound(sheetNames)
 sheet = thisComponent.createInstance("com.sun.star.sheet.Spreadsheet")
         thisComponent.Sheets.insertByName(sheetNames(i), Sheet)
next

Слушай мою команду…

Наконец, мы можем объединить все, что мы рассмотрели в этом руководстве (плюс кое-что из прошлого выпуска). Следующий код запускает команды интерпретатора (в нашем случае это df и du), сохраняет результаты в файле, а затем загружает их в таблицу.

Выполняем:

const tmpFile as string = "/tmp/myfile.tmp"
const bshFile as string = "/tmp/runme.bsh"
sub main
 theFullWorks
end sub
function buildCommand (ipCommand as string) as string
 buildCommand = "rm -f " & tmpFile & ";" _
 & ipCommand & " | sed s/'\t'/' '/g >" & tmpFile & ";" _
 & "while [ ""$(grep ' ' " & tmpFile & ")"" != """" ];" _
 & "do cat " & tmpFile & " | sed s/' '/' '/g > " & tmpFile & "1;" _
 & "mv " & tmpFile & "1 " & tmpFile & ";" & "done"
end function
sub theFullWorks
 dim command as string
 loadNewFile
 changeSheetNames (array("Disk Space Usage","File Usage"))
 command = buildCommand("df|grep -v Filesystem")
 reportSheet(command,"Disk Space Usage")
 command = buildCommand("du /| sort -nr")
 reportSheet(command,"File Usage")
end sub
sub reportSheet (command as string, sheetName as string)
 dim sheet as object, cell as object
 dim iNumber As Integer, oNumber As Integer, iLine As String
 dim i as integer, c as integer
 iNumber = Freefile
 oNumber = Freefile
 Open bshFile For output As #oNumber
 print #oNumber,command
 close #oNumber
 shell("bash -c """ & bshFile & """",,,true)
 i = 1
 sheet=thisComponent.sheets.getByName(sheetName)
 Open tmpFile For Input As #iNumber
 While not EOF(iNumber)
 dim cArray
 Line Input #iNumber, iLine
 cArray = split(iLine)
 for c=0 to ubound(cArray)
  cell=sheet.getCellByPosition(c,i)
  cell.string=cArray(c)
 next
 i = i + 1
 wend
 Close #iNumber
end sub

Чтобы просмотреть команду, выполняемую оболочкой, используйте msgbox.

Большая часть кода вполне понятна, но несколько мест выглядят слегка пугающе. Например, что означают & и зачем они нужны? С их помощью строятся команды, предназначенные для выполнения командным интерпретатором Linux. Если вы хотите увидеть, что именно будет выполняться, просто добавьте msgbox следующим образом:

Sub main
 dim command as string
 command = buildCommand("df|grep –v Filesystem")
 msgbox(command)
end sub

Код, который мы рассмотрели, выглядит достаточно просто, но вы, наверное, согласитесь: основываясь на нем, можно затевать достаточно сложную и мощную работу.

Полезные советы

• И спользуйте CreateUnoService для доступа к различным интерфейсам OpenOffice.org (или Универсальным Сетевым Объектам)
• Если вам лень все время писать thisComponent, можете заменить его псевдонимом:

dim doc as object
doc = thisComponent

• Помните разницу между функцией и процедурой — функция выполняет код и возвращает значение. Процедура выполняет код, но не возвращает никакого результата.
• Заметив, что какой-либо участок вашего кода неоднократно повторяется, обдумайте, можно ли вынести его в процедуру или функцию.
• Если вы пишете код для выполнения командным интерпретатором, отлаживайте его методом просмотра в msgbox

Объекты, которые вам нужны

Доступ к Универсальным Сетевым Объектам OpenOffice.org можно получить с помощью метода CreateUnoService. Эти объекты обычно называют «Сервисы».