Ошибка базы данных: 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-отчетами.

Комментарии