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

OpenOffice.org
OOo: Таблицы на автоматике OOo: Работаем автоматом

OpenDocument Раскройте потенциал OpenOffice.org

Содержание 1 OOo: Таблицы на автоматике 1.1 Электронная таблица ODF изнутри 1.2 Средняя загрузка канала 1.3 Скрипты 1.4 Плюсы и минусы метода

OOo: Таблицы на автоматике

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

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

Стандартное решение таких задач – импорт необработанных данных в приложения вроде OOCalc или Gnumeric, ввод соответствующих формул вручную, создание диаграммы, форматирование всего этого по своему вкусу и отправка результата адресату. Это вполне приемлемо для однократного задания; но если вашему боссу что ни день подавай новую версию электронной таблицы со свежими данными?

К счастью, благодаря формату OpenDocument (см. врезку), нет причин зря тратить время на босса – любителя редактировать электронные таблицы с красивыми диаграммами. Если ваши данные и требуемая таблица имеют постоянную структуру, достаточно написать небольшие скрипты на Perl и языке оболочки для автоматической генерации новой электронной таблицы ODF в том же формате, независимо от исходных данных. В результате получится файл, который можно мигом перевести в формат Excel, если другого выбора нет, и никто не узнает, что вы не корпели над его созданием вручную полдня.

Основная процедура проста: если у вас еще нет шаблона, то создайте электронную таблицу в OOo со всеми необходимыми формулами и диаграммами и сохраните ее для дальнейшего использования. Во-вторых, подправьте скрипт с LXFDVD: он преобразует исходные данные в формат ODF, открывает файл с шаблоном, вводит данные в соответствующие места таблицы и сохраняет результат в новом файле.

К концу данного урока я покажу вам, как все это сделать, мы обсудим все плюсы и минусы такой процедуры. Однако сперва необходимо кратко рассмотреть формат электронной таблицы OpenDocument.

Электронная таблица ODF изнутри

Любой ODF-файл – все равно, текст, электронная таблица или презентация – это просто Zip-архив, содержащий несколько XML-файлов, макросы и изображения, но, к счастью, большинство их мы можем игнорировать. Важные данные находятся в фай­ле content.xml. Если документ – это электронная таблица, то каж­дый ее лист – двумерная таблица (ячейки которой содержат чис­ла, строки и формулы), расположенная в этом файле.

Каждая строка этих таблиц – элемент с именем <table:table-row>. Отдельная ячейка обозначена как <table:table-cell>, и внут­ри нее числовые значения хранятся в двух атрибутах с именами office:value-type и office:value; формулы же хранятся в атрибутах table:formula. Диапазоны ячеек заключаются в квадратные скоб­ки, типа [.B17:.D19].

Объекты в ODF­-архивах расположены в отдельном катало­ге с именем Objects, где имеются подкаталоги с именами Object 1, Object 2 и т. д. Каждая диаграмма в электронной таблице ODF хранится в виде элемента <draw:object>, описанного в файле content.xml своего собственного подкаталога Object n. Точнее, в этом втором файле находятся несколько атрибутов, описываю­щих внешний вид электронной таблицы, плюс один XML-элемент с именем <table:table>, содержащий копию, в немного другом формате, всех ячеек, связанных с диаграммой. Именно эту копию ODF­-приложение использует для отрисовки.

Средняя загрузка канала

Допустим, ваши данные – это описание загрузки каналов связи двух серверов, вычисляемые каждый час за последние сутки. Данные могут поступать от скрипта Perl или Awk, из базы данных или из автоматически генерируемого письма, лишь бы формат их не менялся – 24 строки текста с тремя колонками, разделенными табуляциями: временная отметка плюс загрузка сервера 1 и сервера 2 в Мбит/с:

# Листинг 1: Исходные данные в ASCII­формате
Время суток        BW 1        BW2
Полночь            4.5         6.4
                   6.3         6.3
                   3.1         6.1
                   1.85        5.87
                   и так да лее...

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

Во-первых, распакуем ODS-файл. Внутри него мы найдем все числа в двух разных местах, как объяснено выше. После вставки данных из Листинга 1, исходный XML-текст для строки 17 нашей электронной таблицы внутри файла main/content.xml будет выглядеть так (обратите внимание на формулу в конце):

# Листинг 2
<table:table­row table:style­name=”ro1”><table:table­
cell office:value­type=”string”><text:p>Midnight</
text:p></ table:table­cell><table:table­cell office:value­
type=”float” office:value=”4.5”><text:p>4.5</text:p></
table:tablecell>< table:table­cell office:value­type=”float”
office:value=”6.4”><text:p>6.4</text:p></table:tablecell><
table:table­cell table:formula=”oooc:=SUM(B17:C17)/2” /></
table:table­row>

Текст интересующих нас ячеек выделен [в статье] синим цветом.

Для сравнения, ниже приведен исходный XML-код для первых трех точек графика и их метки, внутри Object/content.xml, и вновь содержимое ячеек выделено синим [в статье]:

# Листинг 3
<table:table­row><table:table­cell office:valuetype=”
string”><text:p>Midnight</text:p></table:tablecell>< table:table­
cell office:value­type=”float” office:value=”4.5”><text:p>4.5</
text:p></table:tablecell>< table:table­cell office:value­type=”float”
office:value=”6.4”><text:p>6.4</text:p></table:tablecell><
table:table­cell office:value­type=”float” office:value=”5.
45”><text:p>5.45</text:p></table:table­cell></table:table­row>

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

# Листинг 4
#> mkdir temp#> cp sample_spreadsheet.ods temp/sample.zip
#> cd temp
#> unzip sample.zip
#> ls ­l
content.xml
Object 1/content.xml
ObjectReplacements/Object 1
...прочие файлы опущены
#> rm “ObjectReplacements/Object 1”

Файл ObjectReplacements/Object 1 – двоичная версия диаграммы, созданной при последнем сохранении файла. OpenOffice.org будет отображать ее по умолчанию, если вы откроете файл заново, пока вы не заставите приложение перерисовать диаграмму, изменив значение любой ячейки. Необходимо удалить его из шаблона: в противном случае, невзирая на новые значения в таблице, будет отображаться исходная диаграмма, пока не будет изменено какое-нибудь значение.

В принципе, преобразовать в шаблоне два файла content.xml довольно просто: сложность таблицы или диаграммы роли не играет. Откройте их в любом текстовом редакторе, выберите все строки таблицы и замените их одной строкой-заполнителем. После этого основной файл должен выглядеть похоже на…

# Листинг 5
…много XML элемен тов
</table:table­row>MY_DATA_GOES_HERE</table:table>
…много дру гих XML элемен тов...

… а Object 1/content.xml;;; должен выглядеть так:

1. Листинг 6

…много XML элемен тов...
<table:table­rows>MY_CHART_GOES_HERE</table:tablerows>
…много дру гих XML элемен тов...

Будьте осторожны и удалите только элементы строк и ничего более, в противном случае вы повредите шаблон. По завершении, сохраните все в Tar-файле с именем ods_bw_template.tar. Помните, что это уже не полноценный ODF-файл, а просто шаблон, вот почему вам не следует использовать расширение .ods.

Скрипты

Создание другой электронной таблицы с новыми данными выполняется при помощи двух скриптов. Содержимое двух файлов content.xml редактирует скрипт ods_gen.pl, но вместо его прямого запуска мы используем его внутри обертки с именем ods_gen.sh, которую я описывал в начале. ods_gen.sh принимает два параметра:

# ods_gen.sh исходные_данные_1.txt ods_bw_шаблон.tar

Это файл исходных данных в формате, описанном ранее, и шаблон. После их копирования во временный каталог tmp_ods_gen и распаковки Tar-архива (строки 6–11 в коде ниже), ods_gen.sh вызывает ods_gen.pl дважды. В первый раз (строка 13) создается новый основной файл content.xml (отсюда аргумент main). Тот же скрипт, вызываемый с опцией chart в строке 16, обновляет файл content.xml, используемый для построения диаграммы. Завершающая часть, со строки 19 и до конца, это просто уборка: удаление временных файлов, сжатие всего в Zip и сохранение под именем $ODS_NAME.ods:

# Лис тинг 7: ods_gen.sh
1 #! /bin/bash
2
3 ODS_NAME=`date ‘+%Y%m%d%H%M’`
4 ODS_SCRIPT=’/home/marco/bin/ods_gen.pl’
5
6 echo Loading $1 into $ODS_NAME.ods with template $2
7 mkdir tmp_ods_gen
8 cp $1 tmp_ods_gen/data.txt
9 cp $2 tmp_ods_gen/template.tar
10 cd tmp_ods_gen
11 tar xf template.tar
12
13 $ODS_SCRIPT data.txt content.xml main > tmp_content_main.xml
14 mv tmp_content_main.xml content.xml
15
16 $ODS_SCRIPT data.txt ‘Object 1/content.xml’ chart > tmp_content_chart.xml
17 mv tmp_content_chart.xml ‘Object 1/content.xml’
18
19 rm template.tar data.txt
20
21 find . ­type f ­print0 | xargs ­0 zip ../$ODS_NAME > /dev/null
22
23 cd ..
24
25 rm ­rf tmp_ods_gen
26
27 mv $ODS_NAME.zip $ODS_NAME.ods

Всю работу выполняет скрипт ods_gen.pl. Он слишком велик, чтобы напечатать его в журнале, но мы включили его на LXFDVD и привели краткую блок-схему выше.

Важная часть начинается со строки 12, после инициализации ряда вспомогательных переменных: установите их в нужные вам значения. $ODS_MAIN_ROW_TEMPLATE и $ODS_CHART_ROW_TEMPLATE – это части из content.xml и Object 1/content.xml соответственно, уже описанные в Листингах 2 и 3. Заменить следует только числа и формулы, на строки MY_LABEL_STRING, MY_FIRST_NUMBER, MY_SECOND_NUMBER и MY_ODS_FORMULA в соответствующих местах. Если необходимо модифицировать этот скрипт для создания электронной таблицы с другим оформлением, начните отсюда – создайте собственный шаблон, откройте его, как описано выше, и скопируйте всю строку таблицы из каждого файла в эти две переменные.

Строки 20 и 46 одинаково важны: убедитесь, что они согласуются друг с другом, потому что здесь определяется формула, сначала в ODF, а затем в формате Perl. Значение в строке 20 содержит два местозаполнителя, RANGE_START и RANGE_END, потому что реальные адреса ячеек будут заменены позднее, в строках 39 и 40, на основании стартовых значений, определенных в строках с 21 по 23.

Строки скрипта с 30 по 34 читают файл с исходными данными, по строке за раз, загружая данные из трех колонок в $STRING, $NUM_1 и $NUM_2.

Когда ods_gen.pl создает новый XML-код, числа могут вставляться в исходном виде, но формулы требуется малость обработать. Если мы создаем главный файл content.xml, следует переделать исходные формулы с их абсолютными адресами ячеек, что и выполняется в строках с 38 по 40. При создании же XML-файла диаграммы необходимо вычислить значения формул (строки 46 и 47).

В обоих случаях в переменную $CURRENT_ROW в строках 37 или 44 предварительно записывается переменная из шаблона, соответствующая типу файла, который мы хотим сгенерировать (‘main’ [основной] или ‘chart’ [диаграмма]). Наконец, в строках с 50 до 54 мы выполняем все подстановки и добавляем строку в $TABLE_DATA.

Закрыв файл с данными, мы записываем все содержимое XML-файла, указанного как второй параметр, в переменную $XML_TEMPLATE, помещаем $TABLE_DATA на место переменных MY_DATA_GOES_HERE или MY_CHART_GOES_HERE и записываем все это в стандартный поток вывода. Вот, собственно, и все.

На рисунке показан результат: три различные версии электронной таблицы, каждая со своим набором данных и, очевидно, с различными диаграммами, созданными за две минуты путем троекратного запуска ods_gen.sh. Кусок формулы справа доказывает, что это действительно электронная таблица, созданная из редактируемых ячеек и формул. Окончательная версия ods_gen.pl должна обновлять дату (ячейка D14), но пусть это будет вашим домашним заданием. Теперь можно все утро валять дурака, а потом в пять секунд отправить новую электронную таблицу вашему боссу!

Плюсы и минусы метода

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

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

Выполнение работы данным способом имеет и другие преимущества. Оба скрипта на этих страницах вместе содержат менее 100 строк кода и требуют только Bash и Perl: они работают практически везде, даже если нет OpenOffice.org и/или вы не можете установить XML- библиотеки или другие утилиты. Вы запросто расширите их на большее число столбцов и диаграмм, и очень быстро перепишете их на любом предпочитаемом вами языке программирования. Единственное реальное ограничение – это точность (в особых случаях) и масштабируемость.

Вновь посмотрите на строки 20 и 46 Листинга 3: первая дает числа, которые OpenOffice.org отображает в ячейке столбца D. Вторую, заменяющую MY_ODS_FORMULA в Object 1/content.xml, OOo использует в качестве значений по оси Y при построении желтой линии диаграммы. Теоретически, при работе со сложными вычислениями с плавающей точкой, некоторые числа первого набора могут быть математически не эквивалентны своим аналогам из второго набора. В приведенном примере и в большинстве реальных сценариев отличие, если оно имеется, ничтожно, но об этом не вредно знать. LXF