Путешествие из Perl в Excel. Использование Spreadsheet::WriteExcel для формирования отчетов

СЕРГЕЙ СУПРУНОВ

Путешествие из Perl в Excel

Использование Spreadsheet::WriteExcel для формирования отчетов

Как было показано в статье «Внутренний веб-сервер» (журнал «Системный администратор», №10(23), октябрь 2004 г.), веб-сервер достаточно удобно использовать для быстрого развертывания несложных приложений, занимающихся сбором, обработкой и выдачей пользователям тех или иных данных. Один из немногих недостатков такого подхода, как было указано, заключается в том, что не всегда удается распечатать качественный отчет, который не стыдно было бы представить начальству или отослать по электронной почте, не вынуждая получателя заниматься копированием-вставкой тела письма и его предпечатной (или в общем плане – предварительной) обработкой.

Наиболее универсальным путем решения указанной проблемы является формирование XML-документа, работать с которым умеют многие программы. Однако в данной статье я хочу рассмотреть другой, менее универсальный, но более удобный для конечного пользователя способ – непосредственное формирование файла в формате Excel в CGI-сценарии. Дальнейшее изложение предполагает, что вы знакомы с языком Perl и его применением для CGI-сценариев, умеете настраивать веб-сервер, а также имеете представление о СУБД PostgreSQL (или какой-нибудь другой). Будет рассматриваться работа под FreeBSD, поэтому потребуется также знание базовых принципов UNIX-систем, хотя заставить все описанное работать под Windows, думаю, особого труда не составит. У пользователей Linux никаких проблем возникнуть также не должно.

Для решения поставленной задачи будем использовать имеющийся в коллекции CPAN (http://cpan.org) Perl-модуль Spreadsheet::WriteExcel, установить который во FreeBSD можно также и из коллекции портов (/usr/ports/textproc/p5-Spreadsheet-WriteExcel), что представляется мне более удобным.

На момент написания статьи актуальной была версия 2.11. Данная версия позволяет создавать файлы в формате BIFF8, поддерживающем Unicode. Следовательно, необходимо будет все текстовые строки преобразовывать в UTF8. Подробнее об этом будет сказано далее, при рассмотрении конкретных примеров. К сожалению, от этой версии мне не удалось добиться правильного отображения имени листа, содержащего русские символы. Работа с одной из предыдущих версий 0.43 (несмотря на значительный разрыв номеров версии, от 2.11 ее отделяет не более года), реализующей, по всей видимости, формат BIFF7 и использующей стандартную кодировку Windows, позволяющую сохранять данные в cp1251, никаких проблем ни с кодировкой данных, ни с кодировкой имени листа не выявила. Но поскольку Unicode – это, пожалуй, единственное решение проблем различных кодировок, то на пути к его безраздельному господству придется претерпеть некоторые неудобства.

Итак, что позволяет делать модуль WriteExcel? Он предоставляет скрипту на языке Perl ряд функций для записи данных в файл Excel. В качестве примера создадим простейший xls-документ с помощью следующего сценария:

Пример 1

#!/usr/bin/perl -w

# Spreadsheel::WriteExcel tests

# Стандартный модуль для осуществления перекодировок

use Encoding;

# Модуль для работы с файлами Excel

use Spreadsheet::WriteExcel;

# Подавление предупреждений о многобайтных символах

nowarnings “utf8”;

# Формируется HTTP-заголовок

print "Content-Type: text/html\n\n";

print '<H3>Spreadsheet::WriteExcel test</H3>';

# Создается xls-файл

my $workbook = Spreadsheet::WriteExcel->new('../public_html/excel/test.xls');

# Создается «рабочий лист» с именем Report

my $worksheet = $workbook->add_worksheet('Report');

# Объявляется формат ячеек, используемый в дальнейшем

my $tabformat = $workbook->add_format();

$tabformat->set_border();

# Пример вывода данных без перекодировки

$worksheet->write(1, 1, 'Русский текст с рамкой', $tabformat);

# Вывод ASCII-текста

$worksheet->write(‘B3’, 'English text.');

# Вывод данных с перекодировкой

$worksheet->write(3, 1, decode(‘koi8-r’, ' Русский текст с рамкой '), $tabformat);

# Ссылка на сформированный файл

print "<A href='/excel/test.xls'>Результат</A>";

Обратите внимание на строку Spreadsheet::WriteExcel->new(..), которая создает объект (соответствующий тому, что в терминах Excel именуется «книгой»), с которым в дальнейшем будет вестись вся работа. В качестве параметра конструктору new() передается имя создаваемого файла (если файл расположен вне текущего каталога, требуется указать полное имя, включающее абсолютный или относительный путь к файлу).

Данный файл должен существовать и быть доступным для записи сценарием либо сценарий должен иметь права на создание файла в указанном каталоге. Поскольку предоставлять сценариям право писать в один из каталогов дерева веб-сервера – не самая лучшая идея (так как в случае взлома сервера у злоумышленника будет больше шансов «нагадить»), то имеет смысл для этих целей создать специальную папку (например, /var/tmp/excel) или использовать /var/tmp. Хотя, с другой стороны, в этом случае мы не сможем создать ссылку на сформированный файл для непосредственного скачивания, и придется использовать сценарий, который будет отдавать файлы пользователю через http, например, такой:

Пример 2. Сценарий getexcel.cgi

#!/usr/local/bin/perl -w

# getexcel.cgi

use CGI;

$root = '/var/tmp/excel';

# Получаем имя файла из HTTP-запроса

$cgi = CGI->new();

$file = $cgi->param('file');

# Вырезаем небезопасные символы – имя файла может содержать только цифры и буквы в нижнем регистре.

# Расширение не указывается

$file =~ s/[^a-z0-9]//g;

$file = "$root/$file.xls";

# Открываем файл на чтение, устанавливаем двоичный режим

open(F, "$file") || &nosuchfile;

binmode(F);

# Формируем нужный заголовок

print "Content-Disposition: form/data; name=\"$file\";filename=\"$file\"\n";

print "Content-Type: application/vnd.ms-excel;name=\"file.xls\"\n";

print "Content-Transfer-Encoding: binary\n\n";

# Выводим содержимое файла

while(<F>) {

    print;

}

close(F);

#----------------------------------- subs

sub nosuchfile {

    print "Content-Type: text/html\n\n";

    print "<H3>Файл '$file' не найден.</H3>";

    return(-1);

}

Как компромисс можно заранее создать в одном из каталогов, доступных веб-серверу, нужные файлы и дать на них требуемые права записи, запретив, однако, запись в каталог. При этом можно даже разместить на них статические ссылки, которые всегда будут возвращать последний созданный отчет. Этот вариант выглядит более безопасным, по крайней мере для сервера, однако остается гипотетическая вероятность заполнения злоумышленником всего свободного пространства в текущем разделе (хотя если уж взлом состоялся, то наверняка «хакер» найдет себе более интересные занятия). К тому же, если кому-то удастся записать в такой файл, скажем, вирус, то можно навсегда потерять доверие ваших пользователей. Впрочем, указанные проблемы становятся актуальными лишь в случае, если злоумышленнику удастся проникнуть в систему с правами, достаточными для записи в эти файлы, но вопросы обеспечения безопасности сервера выходят за рамки данной статьи.

Еще один путь решения проблемы временных файлов – выдача данных, формируемых модулем Spreadsheet::WriteExcel, непосредственно в стандартный поток вывода вместо сохранения на диск. Начало примера 1 в этом случае будет выглядеть так:

#!/usr/bin/perl -w

# Spreadsheel::WriteExcel tests

use Encode;

use Spreadsheet::WriteExcel;

nowarnings “utf8”;

print "Content-Type: application/vnd.ms-excel\n\n";

my $workbook = Spreadsheet::WriteExcel->new('-');

. . .

В случае если скрипт, создающий файл, работает как CGI-сценарий и должен возвращать данные с использованием протокола HTTP, перед началом формирования выходного потока нужно будет передать браузеру заголовок с указанием mime-типа application/vnd.ms-excel, который позволит идентифицировать передаваемые данные именно как файл в формате Excel. Здесь используется минимальный заголовок, более подробный приведен выше, в getexcel.cgi. Это тоже хороший способ, но из недостатков следует указать на невозможность повторного использования уже сформированного файла. Необходимость в этом возникает нечасто, но все же возникает (например, если данные меняются реже, чем их запрашивают пользователи, то между изменениями данных разумно было бы отдавать пользователю уже сформированный отчет, а не создавать его заново). Обратите внимание, что в этом случае команды print (и прочие, осуществляющие вывод в стандартный поток) могут использоваться только для формирования HTTP-заголовка. Весь остальной вывод (вывод данных) должен осуществляться исключительно силами пакета Spreadsheet::WriteExcel.

Вернемся к нашему примеру 1. Объект, соответствующий файлу, у нас уже создан, теперь нужно создать лист (для этого используется метод add_worksheet) и можно приступать к записи данных. Для записи используется метод листа write, формат которого может быть одним из следующих:

$worksheet->write(cell, text[, format])

$worksheet->write(rownum, colnum, text[, format])

То есть адрес ячейки можно задавать как в формате «A1», так и номерами строки и столбца. Ячейке A1 соответствуют строка 0 и столбец 0.

Необязательный параметр format передает объект, создаваемый методом «книги» $workbook->add_format(). Он позволяет изменять принятое по умолчанию форматирование ячейки, для чего используются соответствующие методы данного объекта. В примере выше мы задаем видимую границу вокруг соответствующих ячеек с помощью метода set_border().

Поскольку версия 2.11 модуля WriteExcel подразумевает запись данных в формате Unicode, то все текстовые строки требуется преобразовать в формат UTF8. (Чтобы посмотреть на результат вывода текста без перекодировки, в пример 1 добавлена строка, выводящая в одну из ячеек русский текст непосредственно). Для этого я использую стандартный модуль Encode, функция которого decode(charset, string) осуществляет преобразование строки string, хранящейся с использованием кодировки charset, во «внутреннее представление Perl» (как сказано в man Encode). В моем случае «внутренне представление» совпадает с UTF8, поэтому дальнейших преобразований не требуется. Но тот же man Encode предупреждает, что может быть и по-другому, поэтому в общем случае может потребоваться использовать также функцию encode() для получения нужной кодировки. Ну и чтобы подавить вывод предупреждений «Wide character in print at…», которые формируются при попытке вывода оператором print многобайтных символов, используется прагма «no warnings “utf8”».

Еще одно замечание о Spreadsheet::WriteExcel – поскольку в самом модуле используется прагма Perl use strict, то даже если вы предпочитаете более вольный стиль, переменные, используемые им ($workbook, $worksheet, $tabformat) должны быть объявлены до использования, например, как my. В противном случае модуль выдаст сообщение «Can’t use an undefined value…» (невозможно использовать неопределенное значение), и xls-файл сформирован не будет (точнее, сам файл создастся, но будет пустым). Хотя аварийного завершения работы сценария в этом случае не происходит.

Для общего представления о модуле Spreadsheet::Write-Excel изложенного, думаю, достаточно. Перейдем к более содержательному примеру. Пусть у нас в базе данных есть таблица с тарифами на услуги доступа в Интернет по технологии ADSL, и требуется предоставить абонентам возможность как ознакомиться с ними на html-странице, так и загрузить файл в формате Excel, содержащий эту информацию. Конечно, тарифы меняются не так часто, и, вероятно, проще было бы решить поставленную задачу разработкой статической страницы и заранее созданным файлом Excel. Тем не менее, этот пример хорош как раз своей простотой.

Итак, в какой среде нам предстоит работать. База данных PostgreSQL, веб-сервер Apache с поддержкой CGI, Perl с установленным модулем Spreadsheet::WriteExcel. Все это работает под FreeBSD 5.3.

Таблица тарифов создана такой командой:

test=> create table trf_adsl (trfname varchar,

test(>                          payment numeric(7,2),

test(>                          traflimit numeric(5),

test(>                          overlimit numeric(5,2),

test(>                          comment varchar);

CREATETABLE

Назначения полей следующие:

Поле	Назначение
Trfname	Наименование тарифного плана
Payment	Ежемесячная абонентская плата (руб.)
Traflimit	Трафик (Мб), включенный в абонентскую плату
Overlimit	Стоимость трафика сверх лимита (руб/Мб)
Comment	Примечание

Для примера заполним таблицу несколькими записями:

test=> select * from trf_adsl;

trfname            | payment | traflimit  | overlimit  |          comment     ---------------------------+------------+------------+------------+--------------------------------------- "Свободный"      |    0.00 |      0     |    2.30    | Мин. предоплата - 300 руб. "Сотка"            |  200.00 |    100     |    2.15    | "Школьный"            |  250.00 |    120     |    2.05    | "Студенческий"      |  300.00 |    150     |    2.00    | До 1.06.2005 "Корпоративный"      | 1200.00 |    750     |    1.55    | (записей: 5)

Для простого вывода этих данных на html-страницу все достаточно банально:

Пример 3. Сценарий showtrf.cgi

#!/usr/local/bin/perl -w

# showtrf.cgi

use DBI;

$dbh = DBI->connect('dbi:Pg:dbname=test', 'serg', '');

$hres = $dbh->selectall_hashref('SELECT * FROM trf_adsl', 'traflimit');

print <<__ENDHTML__;

Content-Type: text/html

<LINK rel='stylesheet' type='text/css' href='/_serv_/main.css'>

<H2>Тарифы на услуги доступа в Интернет по ADSL</H2>

<TABLE border='1'><TR>

    <TH>Тарифный план

    <TH>Абонентская плата

    <TH>Включенный трафик

    <TH>Стоимость<BR>сверхлимитного<BR>трафика

    <TH>Примечание

</TR><TR>

__ENDHTML__

@sorted = keys %$hres;

@sorted = sort(@sorted);

foreach $payment (@sorted) {

    print "\t<TD>${$$hres{$payment}}{'trfname'}\n";

    print "\t<TD id='nr'>${$$hres{$payment}}{'payment'}\n";

    print "\t<TD id='nr'>${$$hres{$payment}}{'traflimit'}\n";

    print "\t<TD id='nr'>${$$hres{$payment}}{'overlimit'}\n";

    print "\t<TD>${$$hres{$payment}}{'comment'}\n";

    print "</TR><TR>\n";

}

print "</TR></TABLE>\n"; 

Результат тоже вполне обычен (рис. 1, внешний вид определяется подгружаемой таблицей стилей /__serv__/main.css).

Рисунок 1

Создать файл в формате Excel не намного сложнее. Приведу код сценария полностью, а ниже дам ряд пояснений:

Пример 4. Сценарий exceltrf.cgi

#!/usr/local/bin/perl -w

# exceltrf.cgi

# Подключение нужных модулей

useDBI;

use Encode;

use Spreadsheet::WriteExcel;

nowarnings “utf8”;

# Функция перекодировки из koi8-r в utf8

sub koi2utf {

    $text = shift;

    return decode(‘koi8-r’, $text);

}

# Запрос к базе данных

$dbh = DBI->connect('dbi:Pg:dbname=test', 'serg', '');

$hres = $dbh->selectall_hashref('SELECT * FROM trf_adsl', 'traflimit');

# Создаем «книгу» и лист с именем «ADSL»

$fn = 'tr' . time() . '.xls';

my $wb = Spreadsheet::WriteExcel->new('../public_html/excel/' . $fn);

my $ws = $wb->add_worksheet(‘ADSL');

# Задаем альбомное расположение листа

$ws->set_landscape();

# Формат текста, используемый для формирования заголовка

my $textfmt = $wb->add_format(font => 'Arial',

                              size => 18,

                              color => 'blue',

                              italic => 1);

$textfmt->set_merge();

# Формат «шапки» таблицы

my $headfmt = $wb->add_format();

$headfmt->set_bold();

$headfmt->set_align('center');

$headfmt->set_align('vcenter');

$headfmt->set_text_wrap();

$headfmt->set_border();

$headfmt->set_bottom(6);

my $mygray = $wb->set_custom_color(40, '#AAAAAA');

$headfmt->set_bg_color($mygray);

# Формат остальных ячеек таблицы

my $tabfmt  = $wb->add_format(num_format => '0.00', border => 1);

# Объединение ячеек A1-E1

$ws->merge_range('A1:E1',

    koi2utf('Тарифы на услуги доступа в Интернет по ADSL'),

    $textfmt);

# Вывод «шапки» таблицы

$ws->write('A3', koi2utf('Тарифный план'), $headfmt);

$ws->write('B3', koi2utf('Абонентская плата'), $headfmt);

$ws->write('C3', koi2utf('Включенный трафик'), $headfmt);

$ws->write('D3', koi2utf('Стоимость сверхлимитного трафика'), $headfmt);

$ws->write('E3', koi2utf('Примечание'), $headfmt);

# Начальные значения ширины столбцов

@rowidth = (15, 10, 10, 15, 15);

# Основную таблицу выводим, начиная с 4-й строки

$row = 3;

# Построчный вывод таблицы

@sorted = keys %$hres;

@sorted = sort(@sorted);

foreach $payment (@sorted) {

    $tmp = ${$$hres{$payment}}{'trfname'};

    if(length($tmp) > $rowidth[0]) { $rowidth[0] = length($tmp); }

    $ws->write($row, 0, koi2utf($tmp), $tabfmt);

    $tmp = ${$$hres{$payment}}{'payment'};

    if(length($tmp) > $rowidth[1]) { $rowidth[1] = length($tmp); }

    $ws->write($row, 1, koi2utf($tmp), $tabfmt);

    $tmp = ${$$hres{$payment}}{'traflimit'};

    if(length($tmp) > $rowidth[2]) { $rowidth[2] = length($tmp); }

    $ws->write($row, 2, koi2utf($tmp), $tabfmt);

    $tmp = ${$$hres{$payment}}{'overlimit'};

    if(length($tmp) > $rowidth[3]) { $rowidth[3] = length($tmp); }

    $ws->write($row, 3, koi2utf($tmp), $tabfmt);

    $tmp = ${$$hres{$payment}}{'comment'};

    if(length($tmp) > $rowidth[4]) { $rowidth[4] = length($tmp); }

    $ws->write($row, 4, koi2utf($tmp), $tabfmt);

    $row++;

}

# Устанавливаем уточненную ширину столбцов

for($i = 0; $i < 5; $i++) {

    $ws->set_column($i, $i, $rowidth[$i] + 3);

}

# Выводим ссылку, по которой можно добраться  до сформированного файла

print <<__ENDHTML__;

Content-Type: text/html

<A href="/excel/$fn">$fn</A>

__ENDHTML__

Для тестирования я выбрал сохранение формируемого файла в папке /excel дерева каталогов веб-сервера (см. путь в тексте примера), для которой установлены соответствующие права, позволяющие сценарию создавать в нем файлы. Почему на реальных системах так делать не следует, мы уже обсуждали. Имя файла формируется динамически с использованием значения текущего времени (см. переменную $fn). Сделано это для того, чтобы на стадии тестирования не тратить время на борьбу с ретивыми браузерами и прокси-серверами, норовящими сэкономить вам трафик за счет кэширования, а также чтобы сохранить историю экспериментов.

После создания рабочего листа $ws с помощью метода книги add_format() описываются несколько объектов, которые будут отвечать за форматирование ячеек. Свойства ячеек можно задавать как непосредственно в параметрах add_format (см. $textfmt и $tabfmt), так и с помощью методов объекта формата после его создания (см. $headfmt).

Заголовок и «шапка» таблицы формируются «вручную» с явным указанием ячеек, куда текст помещается. Метод merge_range() действует аналогично write(), но позволяет объединить несколько ячеек.

Далее в цикле выполняется выборка из хэша результатов запроса к БД, и соответствующие ячейки заполняются данными из таблицы trf_adsl. Здесь для метода write() удобнее использовать «раздельную адресацию», когда ячейка определяется номерами строки и столбца.

Переменная $tmp и вычисления длины нужны для того, чтобы установить ширину столбцов, позволяющую целиком видеть содержимое ячеек. Вообще проблема вычисления ширины столбца достаточно сложна, чтобы подходить к ней «академически». Я предпочитаю некоторое сочетание методов «на глазок» и «подогнать». Результат получается вполне приемлемым при минимуме затрат сил и времени. А именно, первоначальную ширину столбцов я определяю подбором по ширине заголовков (результат занесен в массив @rowidth). В дальнейшем пользуюсь тем, что в Excel ширина столбца задается в символах стандартного шрифта. Поскольку шрифт для основного текста я не меняю, то просто выполняется поиск наибольшей длины строки, которая будет заноситься в столбец, и если она превышает первоначальные значения в @rowidth, – производится корректировка. Ну и при установке ширины столбцов (цикл for в конце сценария) еще добавляю 3 символа «про запас».

Результат представлен на рис. 2.

Рисунок 2

Теперь осталось собрать все воедино. Итоговый файл я приводить не буду – он просто объединяет предыдущие два (showtrf.cgi и exceltrf.cgi) таким образом, чтобы при обращении к странице тарифы выводились на экран, и одновременно формировался бы файл, ссылка на который будет появляться на этой же странице. Файл с требуемым именем в итоговом варианте лучше создать заранее (можно даже пустой, например, командой touch), а с каталога, в котором он размещен, снять права на запись. Впрочем, различные способы решения проблемы «временных файлов» были указаны выше, и окончательный выбирать все же вам.

Вот, пожалуй, и все, что я хотел сказать в этой статье. Можно было бы подробней пройтись по методам записи в ячейки, форматированию, способам указания формата листа и т. д. Однако все это очень детально и понятно описано в справочном руководстве man Spreadsheet::WriteExcel (эту же справку можно получить и через perldoc – кому как удобнее), к тому же с массой наглядных примеров, так что я уже не смогу написать лучше.

Скажу лишь, что модуль содержит ряд специализированных методов вывода помимо универсального write (например, write_number, write_string, write_formula и т. д.), которые могут быть использованы для конкретизации типа заносимых в ячейку данных.

Существует возможность гибко управлять параметрами страницы (формат бумаги, ориентация листа, поля, масштаб и т. д.).

Список методов изменения формата ячеек также намного богаче, чем было показано в статье. В общем, можно сказать, что Spreadsheet::WriteExcel позволяет делать практически все, что вы можете сделать в Excel вручную (за исключением разве что макросов).

В заключение замечу, что описанный модуль в паре со Spreadsheet::ParseExcel (который позволяет извлекать данные из xls-файлов) можно использовать и для непосредственной обработки файлов Excel, например, чтобы сформировать какой-нибудь годовой отчет на основе ежемесячных. Возможно, это выглядит несколько громоздко, но все же позволяет сократить объем рутины, которую так или иначе приходится делать.