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

Опубликовано 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 файл через браузер необходимо выполнить следующий код:

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

Метод 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'));

 

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

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

Для горизонтального выравнивания (только одно): 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 работают не в полной мере, а некоторые методы не реализуются полностью, видимо это связано с редким обновлением пакета, поэтому будьте внимательны при написании кода. Успехов Вам.

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

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

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

# 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. читать...

Комментарии

На разработку сайта! Скидки до 20%!