Целью данной статьи является помощь тем, кому необходимо сгенерировать Excel документ, а также построить в нем график по нужным столбцам, познакомиться со способами форматирования текста, вывода формул и русских надписей при помощи скриптового языка Perl.
К примеру, необходимо из БД MYSQL, в которой содержится информация об отелях и их рейтинге, вывести в документ имена этих отелей с их рейтингом и построить график , по полученным столбцам, сделать форматированную шапку для столбцов из русского текста.
Сделать это можно следующим образом:
1. Подготовить бинарный файл-подложку, для отображения графиков в Excel (если Вам интересно просто создание Excel файлов без графиков можете сразу начинать со второго пункта).
2. Вставить ссылку на этот файл непосредственно в программе скрипте, которая нам сгенерирует не просто заполненный Excel, но и график по необходимым.
1. Для вставки в Excel не только данных, но и графиков необходимо подготовить шаблон - бинарный файл. По-другому пока perl генерировать Excel документы с графиками не может, об этом можно прочитать здесь. Т.е. функция для вставки графика использует заранее подготовленный бинарник.
Для начала готовим Excel файл с настроенным графиком( в примере используется файл указанный здесь).
Для этого располагаем в нем два столбца (или столько сколько необходимо) в том же положении, в котором потом они будут в конечном файле и набираем их тестовыми данными , как показано ниже:
Располагаем график и ссылаемся в нем на эти столбцы. Настраиваем цвет и тип графика. При этом в самом графике, например в заголовке, могут использоваться русские названия.
График может быть таким:
2. Устанавливаем модуль для работы с Excel:
cpan[1]> make install clean Spreadsheet::WriteExcel
3. После распаковки модуля Spreadsheet-WriteExcel-2.26 в папке charts вызываем команду для полученного Excel файла:
# chartex -c mychart article.xls
Так мы получили бинарник mychart01.bin подоснову для Excel документа с графиком.
1. Создаем Excel файл
my $workbook = Spreadsheet::WriteExcel->new("/home/masha/ExampleExcel_with_Chart.xls");
2. Подключаемся к файлу хранящему шаблон слов для шапки и для имен листов. Делаем шапку из русских, форматированных слов. Их надо хранить в отдельном файле в формате utf8.
Здесь располагается куча комбинаций, примеров и разных кодировок и разных языков /root/.cpan/build/Spreadsheet-WriteExcel-2.26/examples
my $file = '/home/masha/article_pattern.txt';
open FH, '<:encoding(utf8)', $file or die "Couldn't open $file: $!\n";
Считываем данные в массив:
while (<FH>) {
next if /^#/; # Ignore the comments in the sample file.
chomp;
push(@array,$_);
}
3. Создаем первый лист и указываем имя листа:
my $worksheet1=$workbook->add_worksheet('Hotels_Rating');
Имя листа необходимо задавать по-английски, т.к. именно в данном примере с графиком он используется в функции store_formula() и не может быть русским. В целом можно создавать Excel файлы с листами на русском языке.
4. Создаем формат вывода текста в ячейках таблицы: по-центру, жирным, подчеркнутым и т.д. И заполним пару ячеек с помощью этого формата текстом на русском языке из шаблонного файла:
my $format = $workbook->add_format(valign=>'vcenter',align=>'left');
$format->set_bold();
$format->set_underline();
$worksheet1->write('B1',$array[0],$format);
$worksheet1->write('C1',$array[1],$format);
$worksheet1->write('B6',$array[2],$format);
5. Делаем выборку из базы:
my $dbh=DBI->connect('dbi:mysql:example:localhost', 'root', '') or die "Can't connect";
my $sth=$dbh->prepare('SELECT * FROM hotels;');
$sth->execute();
6. Сохраняем полученные данные в столбцы документа и отключаемся от базы:
my $rows = $dbh->selectall_arrayref($sth);
$worksheet1->select();
$worksheet1->write_col('B2',$rows);
$sth->finish();
$dbh->disconnect();
7. Добавляем график:
my $chart = $worksheet1->embed_chart('B9','/home/masha/mychart01.bin');
$worksheet1->store_formula("=Hotels_Rating!A1");
Функция store_formula - позволяет пересчитать данные на графике заново в зависимости от текущих в документе Excel
8. Сохраняем Excel
$workbook->close();
Исходный текст программы: createxls_charts.pl.
Пример Excel документа с графиком: article.xls.
Шаблон с русскими именами для заголовков столбцов: article_pattern.txt.
Bin файл полученный из article.xls как описано выше: mychart01.bin.