Библиотека сайта rus-linux.net
Ошибка базы данных: Table 'a111530_forumnew.rlf1_users' doesn't exist
Создание Excel-файлов в Perl
Оригинал: Generating Native Excel Files in Perl
Автор: Mike Diehl
Дата: 11 августа 2008
Перевод: Александр Тарасов aka oioki
Дата перевода: 9 декабря 2008
За много лет мне приходилось создавать тысячи отчетов для клиентов и коллег. Обычно я пользовался веб-программой, генерирующей отчеты в HTML-формате или в формате, где разделителем служит запятая (CSV). HTML-формат намного симпатичнее и обычно этого пользователям хватает. Данные в формате CSV можно с легкостью импортировать в табличный процессор Open Office Calc или Excel. С помощью этих программ пользователи выполняют форматирование исходных данных и затем отправляют окончательный отчет куда нужно. Причем зачастую те, кто должны периодически отправлять отчеты одного и того же типа, неоднократно выполняют это форматирование, по сути каждый раз выполняя вручную одну и ту же работу. Они рады, что у них есть данные и не сильно беспокоятся о том, насколько красиво они представлены. Но есть решение лучше.
Perl-модуль Spreadsheet::WriteExcel позволяет генерировать данные,
форматировать их и выводить в родной формат табличного процессора Excel. Мне
остается лишь дать пользователям гиперссылку на отчеты.
Рассмотрим простой CGI-скрипт, использующий модуль Spreadsheet::WriteExcel. Код, представленный в этой статье, основан на программе, написанной мной некоторое время назад для пользователей. Он не такой красивый (или корректный) какой должен быть. Это лишь пример того, чего можно достичь с помощью модуля Spreadsheet::WriteExcel. Однако уверяю вас, результаты работы даже такой программы впечатляют!
Вот основа скрипта:
#====================================================
#!/usr/bin/perl
use DBI;
use CGI;
use Spreadsheet::WriteExcel;
$dbh = DBI->connect("dbi:Pg:dbname=test;host=db.example.com", "postgres", "passwd") ||
die "Can't connect to database.\n";
$cgi = new CGI;
print $cgi->header("application/excel");
#====================================================
В этом куске кода мы загружаем DBI-модуль - впоследствии нам придется запрашивать данные у СУБД PostgreSQL. Затем мы загружаем CGI-модуль, упрощающий написание CGI-скриптов, а мы ведь любим писать легко, ведь так? Затем мы подключаемся к базе данных и получаем ее handle-идентификатор для дальнейшего использования. Аналогично, получаем идентификатор CGI, для дальнейшей работы с модулем CGI.
Эта программа решает единственную задачу - возвращает файл в формате Excel, поэтому MIME-тип задается жестко. Если ваш скрипт содержит форму извлечения данных, или выпадающий список, в котором нужно выбрать отчет, тогда вызов header() стоит переместить в более подходящее для этого место.
Пора генерировать наш отчет.
#====================================================
my $workbook = Spreadsheet::WriteExcel->new("-");
my $worksheet = $workbook->add_worksheet("Cover Sheet");
my $bold = $workbook->add_format();
$bold->set_bold();
$worksheet->write(5, 0, "Division Number:", $bold);
$worksheet->write(5, 1, $division_number);
#====================================================
В этой части мы создаем идентификатор типа Spreadsheet::WriteExcel и сохраняем его в переменной $workbook. Здесь мы передаем конструктору объекта не имя файла, а просто "-". Это означает, что результирующая электронная таблица будет выведена не в файл, а на стандартный вывод STDOUT. Наш отчет будет содержать простой титульный лист, поэтому давайте сейчас добавим лист. Мне нужно вывести надпись "Division Number" полужирным шрифтом, для этого нужно проделать несколько шагов. Во-первых, создадим новый объект типа "формат", с помощью метода add_format(). Затем этому формату можно приписывать различные атрибуты; в нашем случае нужно сделать его полужирным. Мы могли бы сделать буквы красными на зеленом фоне, наклонный шрифт и выравнивание справа. Но зачем? В нашем случае мы просто хотим выделить надпись, поэтому мы всего лишь сделали ее полужирной.
Наконец, мы помещаем данные в наш лист, с помощью метода write(). Сначала создаем полужирную метку "Division Number", в первом столбце шестой строки. Обратите внимание, что в Excel строки и столбцы нумеруются с единицы, а в Spreadsheet::WriteExcel - с нуля. Еще одно замечание - порядок индексации - "строка, столбец", а не "горизонтальный индекс, вертикальный индекс". Когда я впервые воспользовался Spreadsheet::WriteExcel, я этого не понял, и удивлялся, почему мои таблицы выглядели столь забавно.
Переменная $division_number не пуста, она содержит целочисленное значение, которое должно быть где-либо определено.
Теперь добавим второй лист и разместим на нем какие-либо настоящие данные.
#====================================================
$worksheet = $workbook->add_worksheet("Customer Details");
my $current = $workbook->add_format();
my $overdue = $workbook->add_format();
$current->set_color('green');
$current->set_num_format('$0.00');
$overdue->set_color('red');
$overdue->set_num_format('$0.00');
$col=0;
foreach $i ("Customer Name", "Customer Number","Phone Number","Amount Due") {
$worksheet->write(0, $col++, $i, $bold);
}
$sth = $dbh->prepare("select name, number, phone, due from customers where
division=$division_number order by name");
$sth->execute();
my $row=1;
while ($a = $sth->fetchrow_hashref()) {
$worksheet->write($row,0, $a->{name}, $bold);
$worksheet->write($row,1, $a->{number});
$worksheet->write($row,1, $a->{phone});
if ($a->{due} > 1000) {
$worksheet->write($row,1, $a->{due},$overdue);
} else {
$worksheet->write($row,1, $a->{due}, $current);
}
$row++;
}
#====================================================
Теперь код выглядит действительно интересно. После создания нового листа, следующим шагом мы создаем еще два формата. Один формат будет красным - для клиентов-должников, и зеленым - для обычных клиентов. Затем, денежные суммы мы хотим отображать со знаком доллара, поэтому устанавливаем соответствующий формат ячейки.
Далее следует шапка таблицы. Я ленив и не люблю добавлять столбцы вручную, поэтому шапка таблицы печатается в цикле for. Кроме того, добавить новый столбец не представляет никаких сложностей - нужно лишь добавить его в список цикла for.
Теперь создаем запрос к базе данных. В данном случае мы запрашиваем список клиентов - имя, номер, номер телефона и величину долга.
Имя выводим полужирным шрифтом, далее следует номер клиента и телефонный номер, все это обычным шрифтом.
Последний столбец немного интереснее. В последнем столбце мы проверяем, задолжал ли клиент нам более чем $1000. Если это так, мы раскрашиваем его ячейку в красный цвет, тем самым выделяя его. Иначе раскрашиваем в зеленый формат $current.
После того, как мы вывели всю строку, переходим на следующую, с помощью оператора $row++ и забираем следующую запись из нашего набора данных.
Мы почти закончили.
#==================================================== $dbh->finish(); $workbook->close(); exit; #====================================================
Всегда нужно убираться за собой, а именно завершать работу с идентификатором БД, вызывая метод finish(). Закрытие идентификатора таблицы методом close() приводит к очистке буферов и выдаче готовой таблицы на стандартный вывод STDOUT.
Конечно, если бы мы писали генератор CSV-файла, то программа уместилась бы в 10 строк. Но тогда бы пользователю пришлось совершать дополнительную работу, а именно импортирование данных и форматирование получившейся таблицы. В то же время написанная нами только что программа проста, а результаты ее работы намного симпатичнее. На рисунке показан результат работы скрипта, после того как я вручную изменил ширину некоторых столбцов. Модуль Spreadsheet::WriteExcel позволяет выполнять и другие, более сложные варианты форматирования, например, помещение формул в ячейку. Модуль настолько прост в использовании, а отзывы пользователей положительны, что я даже не хочу больше заниматься CSV-отчетами.