Создание xls(excel) файлов с помощью Spreadsheet_Excel

Опубликовано admin - вс, 06/03/2018 - 16:17

Продолжая обзор возможностей php при работе с excel документами, после того как в предыдущей статье ма завершили рассматривать чтение файлов excel с помощью пакета pear Spreadsheet_Excel_Reader, следует обратится к одному из оптимальных и бесплатных вариантов создания xls файлов с помощью пакета pear Spreadsheet_Excel_Writer. В отличие от пакета Spreadsheet_Excel_Reader пакет Spreadsheet_Excel_Writer имеет стабильную версию, которую Вы можете получить на сайте pear.php.net. Следует иметь ввиду, что данный пакет рассчитан на excel 5 версии. При изучении методов автор использовал последнюю версию OpenOffice.

Основные возможности Spreadsheet_Excel_Writer:

создание excel документов в формате Excel 5 (BIFF5);
отправка заголовков браузеру о формате Excel;
создание нескольких листов Excel в одном файле xls;
создание и заполнение столбцов и строк файла excel;
объединение столбцов и строк;
изменение цвета шрифтов,выбор и установка различных шрифтов;
сохранение xls файлов;
установление размера строк и столбцов;
и много другое. Смотрите ниже подробно методы пакета.

Для того чтобы вывести простой ecxel файл через браузер необходимо выполнить следующий код:

<?php require_once 'Spreadsheet/Excel/Writer.php'; // Создаём новый класс, содержащий лист в exсel файле $workbook = new Spreadsheet_Excel_Writer(); // отправляем HTTP заголовок $workbook->send('test.xls'); // Обязательно устанавливайте версию exсel, среди доступных значений только 8(Excel 97/2000) $workbook->setVersion(8); // Создаём новый рабочий лист exсel файла $worksheet =& $workbook->addWorksheet('My first worksheet'); // Устанавливаем поддержку русской кодировки $worksheet->setInputEncoding('utf-8'); // Данные в листе - первый параметр ответственен за строку, второй за столбец // третий за значение столбца $worksheet->write(0, 0, 'Название первого столбца'); $worksheet->write(0, 1, 'Название второго столбца'); $worksheet->write(1, 0, 'Ваш первый столбец первой строки'); $worksheet->write(1, 1, 'Ваш второй столбец первой строки'); $worksheet->write(2, 0, 'Ваш первый столбец второй строки'); $worksheet->write(2, 1, 'Ваш второй столбец второй строки'); $worksheet->write(3, 0, 'Третья строка'); $worksheet->write(3, 1, 2009); // Данные код выполняет отправку заголовка $workbook->close(); ?>

Помимо методов, используемых только в данном примере, существует дополнительные методы о которых и пойдёт речь далее.

Метод close () закрывает файл exсel, в случае успешного закрытия возвращает true, иначе PEAR_Error

"PHP"

mixed Workbook::close ( )

Объект создаёт новый лист в excel файле, возвращает ссылку на объект нового листа и true или PEAR_Error. Название не может превышать 32 знака.

"PHP"

object reference Workbook::&addWorksheet (string  $name='' )

"PHP"

object reference Workbook::&addFormat ( array $properties=array() )

Объект добавляет новый формат к excel файлу. Доступные значения:

Align (void Format::setAlign ( string $location ))

Bold (void Format::setBold ( integer $weight=1 ))

Bottom (void Format::setBottom (integer $style))

Top (void Format::setTop ( integer $style ))

Left (void Format::setLeftColor ( mixed $color ))

Right (void Format::setRight ( integer $style ))

Border (void Format::Border ( integer $style ))

BorderColor (void Format::BorderColor ( mixed $color ))

BottomColor (void Format::BottomColor ( mixed $color ))

TopColor (void Format::TopColor ( mixed $color ))

RightColor (void Format::RightColor ( mixed $color ))

LeftColor (void Format::LeftColor ( mixed $color ))

FgColor (void Format::FgColor ( mixed $color ))

BgColor (void Format::BgColor ( mixed $color ))

Color (void Format::Color ( mixed $color ))

Pattern (void Format::Pattern ( mixed $color ))

Underline (void Format::Underline ( mixed $color ))

TextRotation (void Format::TextRotation ( mixed $color ))

Size (void Format::Size ( mixed $color ))

NumFormat (void Format::NumFormat ( mixed $color ))

Script (void Format::Script ( mixed $color ))

Эти свойства можно устанавливать с помощью массива свойств, например:

"PHP"

$upper_right_side_brick =& $workbook->addFormat(array('right' => 5, 'top' => 5, 'size' => 15,
                                                      'pattern' => 1, 'bordercolor' => 'blue',
                                                      'fgcolor' => 'red'));

Так же свойства можно устанавливать по отдельности, например:

// Установить свойства по отдельности $upper_left_side_brick =& $workbook->addFormat(); $upper_left_side_brick->setLeft(5); $upper_left_side_brick->setTop(5); $upper_left_side_brick->setSize(15); $upper_left_side_brick->setPattern(1); $upper_left_side_brick->setBorderColor('blue'); $upper_left_side_brick->setFgColor('red');

Следующий метод выравнивает данные в ячейке таблицы, допустимыми значениями являются:

Для горизонтального выравнивания (только одно): left, center, right, fill, justify, merge, equal_space.

Для вертикального выравнивания (только одно): top, vcenter, bottom, vjustify, vequal_space.

"PHP"

void Format::setAlign (string $location)

Метод setVAlign() устанавливает вертикальное выравнивание данных в ячейке таблицы, допустимыми значениями являются: top, vcenter, bottom, vjustify, vequal_space. equal_space.

"PHP"

void Format::setVAlign (string $location)

Метод setHAlign() устанавливает вертикальное выравнивание данных в ячейке таблицы, допустимыми значениями являются: left, center, right, fill, justify, merge, equal_space.

"PHP"

void Format::setHAlign (string $location)

Метод setMerge() аналог свойства setAlign('merge')

"PHP"

void Format::setMerge ()

Метод setBold выделяет текст в ячейках. Допустимые значения: от 100 до 1000.

"PHP"

void Format::setBold (integer $weight=1)

Метод setBottom() устанавливает нижнюю границу ячейки.

"PHP"

void Format::setBottom (integer $style)

Метод setTop() устанавливает верхнюю границу ячейки.

"PHP"

void Format::setTop (integer $style)

Метод setRight() устанавливает правую границу ячейки.

"PHP"

void Format::setRight (integer $style)

Метод setLeft() устанавливает левую границу ячейки.

"PHP"

void Format::setLeft (integer $style)

Метод setBorder() устанвливает стиль грницы ячейки.

"PHP"

void Format::setBorder (integer $style)

Метод setBorderColor() устанавливает цвет границы ячеек.

"PHP"

void Format::setBorderColor (mixed $color)

Метод setBottomColor() устанавливает цвет нижней границы ячейки.

"PHP"

void Format::setBottomColor (mixed $color)

Метод setTopColor() устанавливает цвет верхней границы ячейки.

"PHP"

void Format::setTopColor (mixed $color)

Метод setRightColor() устанавливает цвет правой границы ячейки.

"PHP"

void Format::setRightColor (mixed $color)

Метод setLeftColor() устанавливает цвет левой границы ячейки.

"PHP"

void Format::setLeftColor (mixed $color)

Метод setFgColor() устанавливает цвет фона переднего плана ячейки.

"PHP"

void Format::setFgColor (mixed $color)

Следующий метод устанавливает цвет фона ячейки.

"PHP"

void Format::setBgColor (mixed $color)

Метод setColor() устанавливает цвет текста ячейки.

"PHP"

void Format::setColor (mixed $color)

Метод setPattern() устанавливает фон, чтобы более точно понять назначение данного метода используйте его вместе с методами setBgColor и setFgColor.

"PHP"

void Format::setPattern (integer $arg=1)

Метод устанавливает подчеркивание текста в ячейке.

"PHP"

void Format::setUnderline (integer  $underline )

Метод устанавливает стиль Italic для текса в ячейке.

"PHP"

void Format::setItalic ( )

Метод устанавливает размер текста в ячейке.

"PHP"

void Format::setSize ( )

Метод устанавливает ориентацию текста в ячейке, допустимые значения - 0, 90, 270 и -1.

"PHP"

void Format::setTextRotation ( )

Метод устанавливает числовой формат.

"PHP"

Format::setNumFormat.

Допустимы следующие значения:

0	Десятичное число	Количество нулей, определяющие количество цифр, которые будут отображены.
0.00	Десятичное число	Количество нулей, после точки, определяющие количество десятичных цифр, которые будут отображены.
#.##		Количество определенных знаков, после точки, определяющие количество десятичных цифр, которые будут отображены.
0%		Количество нулей, определяющие количество цифр, которые будут отображены.
0.000%		Количество нулей, после точки, определяющие количество десятичных цифр, которые будут отображены.
$#.#;[Red]($#.#)	Валюта	Подробно об этом смотрите на pear.php.net
??/??	Дробь	Подробно об этом смотрите на pear.php.net
# ??/??	Дробь	Подробно об этом смотрите на pear.php.net
0.00E+#	Scientific	Подробно об этом смотрите на pear.php.net
D-MMM-YY	Date	Подробно об этом смотрите на pear.php.net
D/M/YYYY h:mm:ss	Date/Time	Подробно об этом смотрите на pear.php.net
h:mm:ss AM/PM	Time	Подробно об этом смотрите на pear.php.net

Устанавливает перечеркивание шрифта

"PHP"

void Format::setStrikeOut ()

Устанавливает оконтуривание шрифта

"PHP"

void Format::setOutLine ()

Устанавливает тип сценария шрифта.

"PHP"

void Format::setScript (integer  $script )

Устанавливает семейство шрифта. Допустимыми значениями могут быть Times New Roman, Arial, Courier.

"PHP"

void Format::setFontFamily (string $font_family)

Все вышеперечисленные методы связанны с шрифтом в ячейке документа excel.

В пакете Spreadsheet_Excel_Writer сущетсвуют ещё несколько свойств для работы с листами excel документа.

Например следующей метод устанавливает, временную папку в которой будет сохранятся OLE файл, на сайте разработчика советует использовать данный метод в тех случаях, когда у Вас не достаточно прав для сохранения файла во временную директорию, используемую по умолчанию.

"PHP"

boolean Workbook::&setTempDir (string $dir)

Так же на сайте pear.php.net содержится пример использования данного свойства.

"PHP"

<?php
require_once 'Spreadsheet/Excel/Writer.php';
$workbook = new Spreadsheet_Excel_Writer('test.xls');
$worksheet =& $workbook->addWorksheet('My first worksheet');
$workbook->setTempdir('/home/xnoguer/temp');
$worksheet->write(0, 0, "did this work?");
$workbook->close();
?>

Следующий метод, позволяет устанавливать RGB цвета в цветовой палитре.

"PHP"

integer Workbook::setCustomColor (integer $index,
integer  $red,integer  $green,integer  $blue)

Если Вам необходимо вернуть листы книги как объекты, используйте следующее Метод.

"PHP"

arrayWorkbookworksheets">array Workbook::worksheets ()

Если Вам необходимо изменить ранее установленное названия листа книги, то используйте следующий метод.

"PHP"

getName">string Worksheet::getName ( )

Ранее уже упоминалось о необходимости установления правильной кодировки для excel файла, тем не менее укажем этот метод ещё раз.

"PHP"

void Worksheet::setInputEncoding (string $encoding)

Пример "Как установить кодировку в exсel файле?".

"PHP"

<?php
require_once 'Spreadsheet/Excel/Writer.php';
$workbook = new Spreadsheet_Excel_Writer();
$workbook->setVersion(8); // Обязательно указывайте версию файла для кодировки

$worksheet =& $workbook->addWorksheet('Russian text');
$workbook->send('test.xls');
$worksheet->setInputEncoding('utf-8');  // Устанавливаем кодировку 

$russian = "Привет";
$worksheet->write(1, 0, $russian);
$workbook->close();
?>

Если Вы используете несколько листов в excel файле, то с помощью следующего метода можно выбрать любой лист.

"PHP"

void Worksheet::select ( )

Не следует путать выбор листов в процессе написания кода и выбор активного листа при открытии excel файла, для установки активного листа в последнем случае необходимо использовать следующий метод.

"PHP"

void Worksheet::activate ( )

Ещё одним методом, при помощи которого можно выбирать листы, является метод activate(), это Метод пригодится в том случае, если существует множество листов и Вам необходимо выбрать один из них в качестве первого листа. Синтаксис следующий.

"PHP"

void Worksheet::setFirstSheet ( )

Метод protect запрещает модификацию листа. Его синтаксис следующий.

"PHP"

void Worksheet::protect (string $password)

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

"PHP"

<?php
require_once 'Spreadsheet/Excel/Writer.php';
$workbook = new Spreadsheet_Excel_Writer();
$workbook->setVersion(8);
$worksheet =& $workbook->addWorksheet('Russian text');
$workbook->send('test.xsl');
$worksheet->setInputEncoding('utf-8');
$russian = "Привет";
$worksheet->setColumn(1,0,50); //Устанавливаем ширину ячейки
$worksheet->write(1, 0, $russian,$format_out);
$workbook->close();
?>

Метод writeCol позволяет установить массив переменных в качестве содержимого ячейки.

"PHP"

mixed Worksheet::writeCol (integer $row,integer $col,array $val,mixed $format = null)
integer  $row // Строка
integer  $col // Столбец

array $val // Значение к записи в ячейку
mixed  $format // Необязательное значение устанавливающее формат записи

Метод writeRow позволяет установить массив переменных в качестве содержимого ячейки для строки.

"PHP"

mixed Worksheet::writeRow (integer $row,integer $col,array $val,mixed $format = null)
integer  $row // Строка
integer  $col // Столбец

array $val // Значение к записи в ячейку
mixed  $format // Необязательное значение устанавливающее формат записи

Метод setSelection позволяет выделить ячейку или группу ячеек.

"PHP"

void Worksheet::setSelection (integer $first_row ,integer $first_column ,integer $last_row ,integer $last_column)
integer  $row // Строка
integer  $col // Столбец

array $val // Значение к записи в ячейку
mixed  $format // Необязательное значение устанавливающее формат записи

Метод setSelection позволяет выделить ячейку или группу ячеек.

"PHP"

void Worksheet::setSelection (integer $first_row ,integer $first_column ,integer $last_row ,integer $last_column)
integer  $first_row - первая строка выбранного квадранта
integer  $first_column - первый столбец выбранного квадранта
integer  $last_row - последняя строка выбранного квадранта
integer  $last_column - последний столбец выбранного квадранта

Чтобы выделить определенный раздел в листе файла excel, используйте Метод freezePanes().

"PHP"

void Worksheet::freezePanes (array $panes 
array $panes  - Массив может содержит следующие ключи и их значения:
0 => Вертикальное объединение ячеек,
1 => Горизонтальное объединение ячеек
2 => Видимость ячеек сверху 
3 => Видимость ячеек расположенных с левой строны листа
4 => Активная панель

Чтобы выделить определенный раздел в листе файла excel, используйте Метод thawPanes().

"PHP"

void Worksheet::thawPanes (array $panes 
array $panes  - Массив может содержит следующие ключи и их значения:
0 => Вертикальное объединение ячеек,
1 => Горизонтальное объединение ячеек
2 => Видимость ячеек сверху 
3 => Видимость ячеек расположенных с левой стороны листа
4 => Активная панель

Метод setPortrait () устанавливает ориентацию листа.

"PHP"

void Worksheet::setPortrait ()

Метод setLandscape() устанавливает ориентацию как "ландшафт".

"PHP"

void Worksheet::setLandscape ()

Метод setLandscape() устанавливает тип листа. Ex. 1 = US Letter, 9 = A4.

"PHP"

void Worksheet::setPaper (integer  $size=0 )

Метод setHeader() позволяет установить заголовок листа.

"PHP"

void Worksheet::setHeader(string $string,float  $margin=0.5)

Метод setFooter() позволяет уставноить заголовок листа в низу страницы.

"PHP"

void Worksheet::setFooter(string $string,float  $margin=0.5)

Метод centerHorizontally() позволяет отцентрировать страницу по горизонтали.

"PHP"

void Worksheet::centerHorizontally (integer  $center=1 )

Метод centerVertically() позволяет отцентрировать страницу по вертикали.

"PHP"

void Worksheet::centerVertically (integer  $center=1 )

Метод setMargins() устнавливает отступы для страницы.

"PHP"

void Worksheet::setMargins (float  $margin )

Метод setMargins_LR() устанавливает правый и левый отступы для страницы.

"PHP"

void Worksheet::setMargins_LR (float  $margin )

Метод setMargins_TB() устанавливает верхний и нижний отступы для страницы.

"PHP"

void Worksheet::setMargins_TB (float $margin)

Метод setMarginLeft() устанавливает верхний и нижний отступы для страницы.

"PHP"

void Worksheet::setMarginLeft (float $margin=0.75)

Метод setMarginRight() устанавливает верхний и нижний отступы для страницы.

"PHP"

void Worksheet::setMarginRight (float $margin=0.75)

Метод setMarginTop() устанавливает верхний и нижний отступы для страницы.

"PHP"

void Worksheet::setMarginTop (float $margin=1)

Метод setMarginBottom() устанавливает верхний и нижний отступы для страницы.

"PHP"

void Worksheet::setMarginBottom (float $margin=1)

Метод RepeatRows() устанавливает строки, которые будут повторены при печати.

"PHP"

void Worksheet::repeatRows (integer $first_row,integer  $last_row=NULL)

Метод repeatColumns() устанавливает столбцы, которые будут повторены при печати.

"PHP"

void Worksheet::repeatColumns (integer $first_col ,integer $last_col=NULL)

Метод printArea() устанавливает область, которая будет напечатана.

"PHP"

void Worksheet::printArea (integer $first_row,integer  $first_col,integer  $last_row,integer  $last_col)

Метод hideGridlines() устанавливает градиент, используемый при печати.

"PHP"

void Worksheet::hideGridlines ()

Метод printRowColHeaders() позволяет выбрать показывать или нет, при печати, заголовки ячеек и столбцов.

"PHP"

void Worksheet::printRowColHeaders (integer $print=1)

Метод fitToPages() позволяет выбрать показывать или нет, при печати, номера строк, данный метод не работает в OpenOffice.

"PHP"

void Worksheet::fitToPages (integer $width ,integer $height)

Метод setHPagebreaks() позволяет установить, при печати файла, отображение разрыва страниц.

"PHP"

void Worksheet::setHPagebreaks (array $breaks)

Метод setHPagebreaks() позволяет установить, при печати файла, отображение вертикального разрыва страниц.

"PHP"

void Worksheet::setVPagebreaks (array $breaks)

Метод setZoom() устанавливает zoom для страницы.

"PHP"

void Worksheet::setZoom (integer $scale=100)

Метод setPrintScale() устанавливает масштаб страницы при печати страницы.

"PHP"

void Worksheet::setPrintScale (integer $scale=100)

Метод write() записывает данные в excel файл.

"PHP"

void Worksheet::write (integer $row,integer  $col,mixed  $token,mixed  $format=0)

Пример write():

"PHP"

<?php
require_once 'Spreadsheet/Excel/Writer.php';
$workbook = new Spreadsheet_Excel_Writer();
$workbook->setVersion(8);
$worksheet =& $workbook->addWorksheet('Russian text');
$workbook->send('test.xsl');
$worksheet->setInputEncoding('utf-8');
$russian = "Привет";
$worksheet->write(1, 0, $russian); // Записываем данные в первый столбец первой строки

$workbook->close();
?>

Следующие свойства схожи с предыдущим по своему назначению,поэтому подробно мы их описывать не будем, за подробностями обращайтесь на http://pear.php.net , ниже приведенные свойства так или иначе записывают данные в excel файл и отличаются лишь форматом.

"PHP"

void Worksheet::writeNumber (integer $row,integer  $col,float $num,mixed  $format=0)
void Worksheet::writestring (integer $row,integer  $col,string  $str,mixed  $format=0)
void Worksheet::writeNote (integer $row,integer  $col,string  $note)
void Worksheet::writeBlank (integer $row,integer  $col,mixed  $format)
integer WorksheetwriteFormula (integer  $row,integer  $col,string  $formula,mixed  $format=0 )
void Worksheet::writeUrl (integer $row,integer  $col,string  $url,string  $string='',mixed  $format=0 )

Метод setRow() используется для установления высоты строки.

"PHP"

void Worksheet::setRow (integer  $row ,integer  $height ,mixed  $format=0 )

Метод mergeCells() используется для объединения ячеек.

"PHP"

void Worksheet::mergeCells (integer $first_row,integer  $first_col,integer  $last_row,integer  $last_col)

Метод minsertBitmap используется для установления изображения в файл excel.

"PHP"

void Worksheet::insertBitmap (int $row, int $col, str $bitmap, int $x=0, 
int $y=0, int $scale_x=1,integer $scale_y=1)

Конструктор Spreadsheet_Excel_Writer создаёт лист в excel файле.

"PHP"

object The Spreadsheet_Excel_Writer::Spreadsheet_Excel_Writer (string $filename='')

К сожалению пакет содержит недокументированные методы, о значении которых трудно догадаться, но при желании Вы можете их изучить самостоятельно. Среди таких методов находятся:

"PHP"

void Worksheet::hideScreenGridlines ( )
void Worksheet::setMerge (integer $first_row,integer  $first_col,integer  $last_row,integer  $last_col)

На этом наша статья о пакете Spreadsheet_Excel_Writer заканчивается, в заключение необходимо отметить, что некоторые примеры указанные на сайте http://pear.php.net работают не в полной мере, а некоторые методы не реализуются полностью, видимо это связано с редким обновлением пакета, поэтому будьте внимательны при написании кода. Успехов Вам.

Оценка

1390 просмотров

Взаимосвязанные материалы

# 1 . Создание xls(excel) файлов с помощью Spreadsheet_Excel_Writer. ( воскресенье, июня 3, 2018 - 16:17 ),

# 2 . Как с помощью php перенести данные из xls(exel) в mysql?. ( воскресенье, июня 3, 2018 - 16:13 ),

Порой для работы требуется извлекать данные из файлов xls в базу данных, что обычными средствами php не возможно. читать...

# 3 . Рисуем графики на PHP ( воскресенье, июня 3, 2018 - 11:47 ),
Графики на страницах с помощью PHP необходимы во многих случаях - для создания опросов и голосований, статистики. Иногда можно использовать Google Api для создания графиков, иногда PHP. Исходя из этого, мы рассмотрим сегодня примеры различных графиков на PHP и Javascript. читать...

# 4 . jQuery Nivo Slider ( вторник, января 17, 2012 - 21:29 ),

Nivo Slider обладает множеством настроек для создания эффектного рекламного ролика на сайте. читать...

# 5 . PHP - jQuery календарь и новости! ( понедельник, декабря 12, 2011 - 21:01 ),

Заказчики меня часто спрашивают сделать вывод новостей на jQuery. читать...

Создание xls(excel) файлов с помощью Spreadsheet_Excel_Writer.

Теги

Взаимосвязанные материалы

Комментарии

Не могли бы Вы сообщить