Php excel



2016-11-03

Чтение excel на PHP - основные методы класса PHPExcel

PHPExcel – библиотека, предназначенная для обработки данных формата MS Excel. С её помощью можно производить чтение и запись информации в файлы, форматировать их содержимое, выполнять операции с формулами, стилями и т.д.
Для работы PHPExcel требуется версия PHP 5.2 или выше, а так же наличие подключенных расширений ZIP, XML и GD2.
Примечание: расширение php_zip нужно только при использовании классов PHPExcel_Reader_Excel2007, PHPExcel_Writer_Excel2007 и PHPExcel_Reader_OOCalc. То есть если вы хотите использовать PHPExcel для полноценной работы с файлами форматов .xlsx или .ods, то вам потребуется zip-расширение.

Возможности библиотеки PHPExcel:

  1. — PHPExcel может читать различные форматы файлов электронных таблиц:
    • Excel5
    • Excel2003XML
    • Excel2007
    • OOCalc
    • SYLK
    • Gnumeric
    • CSV

  2. — Создание и обработка документов средствами PHP

Установка PHPExcel:
1) Скачать библиотеку с официального сайта http://phpexcel.codeplex.com/
2) В скачанном архиве, из папки classes скопировать содержимое в корень сайта

Состав Архива:

/classes // Каталог библиотеки PHPExcel /classess/PHPExcel/ // Каталог классов библиотеки PHPExcel  /classess/PHPExcel.php // Файл для include?содержит ключевые методы для обработки данных /documentation		 // Полная документация по использованию библиотеки /license.txt // Лицензионное соглашение /install.txt // Краткая инструкция по установке /examples 	 // Каталог с примерами по использованию PHPExcel /changelog.txt // Список изменений в текущей версии 

Пример чтения Excel файла при помощи класса PHPExcel

Для чтения данных с Excel файла необходимо подключить файл библиотеки PHPExcel и используется метод load() класса PHPExcel_IOFactory. Этот метод собирает данные всех со всех листов текущего документа:

require_once '/путь/до/файла/PHPExcel.php'; $excel = PHPExcel_IOFactory::load('/путь/до/файла/simple.xlsx');   

Далее формируем массив из всех листов Excel файла с помощью цикла:

Foreach($excel ->getWorksheetIterator() as $worksheet) {  $lists[] = $worksheet->toArray(); } 

Вывод сформированного массива в виде HTML таблиц(ы) :

foreach($lists as $list){  echo '<table border="1">';  // Перебор строк  foreach($list as $row){  echo '<tr>';  // Перебор столбцов  foreach($row as $col){  echo '<td>'.$col.'</td>';  }  echo '</tr>';  }  echo '</table>'; } 

Основные методы и свойства класса PHPExcel

Класс PHPExcel – это ядро PHPExcel. Он содержит ссылки на содержащиеся листы, параметры безопасности документов и мета-данные документов. Помимо вышеизложенного, этот класс включает в себя множество других полезных методов для обработки получаемых данных. Некоторые из них будут описаны ниже.

getSheetCount() – возвратит количество листов в книге;

getSheetNames() – возвращает список всех листов в рабочей книге, индексируя их в порядке, в котором их «вкладки» появляются при открытии в MS Excel (или других программах для обработки электронных таблиц);

getSheet(N) – используется для доступа к листу по индексу. Индекс позиции (N) представляет собой порядок, в котором отображаются вкладки листов, когда книга открыта в MS Excel (или других программах для обработки электронных таблиц);


getSheetByName(list_name) – метод используется для доступа к листу по его имени (list_name);

getActiveSheet() – этот метод позволяет получить доступ к активному листу документа напрямую. Активным листом является тот, который будет активен, когда откроется книга в MS Excel (или другой соответствующей программе электронных таблиц);

setActiveSheetIndex() и setActiveSheetIndexByName() – с помощью этих методов можно назначить лист, который будет активным по его индексу или имени.

Получение значения ячейки по координатам

Для получения значения ячейки, сперва должна быть найдена ячейка путем использования метода getCell. Значение ячейки может быть получено с помощью следующей команды:

$objPHPExcel->getActiveSheet()->getCell('B8')->getValue(); 

В случае если вам нужны подсчитанные значения ячейки, вместо getValue(), используйте метод getCalculatedValue():

$objPHPExcel->getActiveSheet()->getCell('B8')->getCalculatedValue(); 

Получение значения ячейки по строкам и столбцам

Для получения значения ячейки, в листе сначала должна быть найдена ячейка с использованием метода getCellByColumnAndRow. Значение ячейки может быть получено с помощью следующей команды:

$objPHPExcel->getActiveSheet()->getCellByColumnAndRow(1, 8)->getValue();   

Заключение

В этой статье были рассмотрены основные возможности библиотеки PHPExcel в области чтения excel файлов. Помимо всего выше изложенного, есть возможность более гибко использовать эту библиотеку для работы с различными параметрами таблицы. По этим вопросам рекомендуется обращаться к документации. Библиотека PHPExcel так же адаптирована для работы с другими типами файлов, таких как Excel (BIFF). XLS, Excel 2007 (OfficeOpenXML). XLSX, CSV, Libre / OpenOffice Calc. ODS, Gnumeric, PDF и HTML.

r-band.ru

Создание базы данных

В этой статье приведен пример простого Web-приложения, которое наглядно покажет вам работу XML-механизма Excel. Это приложение представляет собой таблицу, содержащую имена людей и их адреса электронной почты.

Синтаксис для создания схемы базы данных в MySQL выглядит следующим образом.

Листинг 1. SQL-код для создания схемы базы данных
DROP TABLE IF EXISTS names; CREATE TABLE names ( 	id INT NOT NULL AUTO_INCREMENT, 	first TEXT, 	middle TEXT, 	last TEXT, 	email TEXT, 	PRIMARY KEY( id ) );

Этот файл является простой базой данных, состоящей из одной таблицы с именем names, которая имеет пять полей: автоинкрементный идентификатор, имя, отчество, фамилия и адрес электронной почты.

Создайте базу данных с помощью инструмента командной строки Mysqladmin: mysqladmin --user=root create names


. Затем загрузите в нее данные о таблице из файла схемы: mysql --user=root names < schema.sql. Используемые имя пользователя и пароль зависят от настроек вашего экземпляра MySQL, но сама идея не меняется – сначала создается база данных, а затем с помощью SQL-файла создаются таблицы с необходимыми полями.

Получение данных для импорта

Теперь нужно получить данные, которые будут импортироваться. Для этого создайте новый файл Excel. В верхней ячейке каждого столбца введите значения First, Middle, Last и Email. После этого добавьте в список несколько строк данных (рисунок 1).

Рисунок 1. Данные для импорта

Рисунок 1. Данные для импорта

Кликните, чтобы увидеть увеличенное изображение

Вы можете создать список любой длины или изменить поля так, как сочтете нужным. PHP-сценарий импортирования, рассматриваемый в этой статье, безоговорочно игнорирует первую строку данных, считая ее строкой заголовков. В рабочем приложении, вы можете считывать и выполнять разбор строки заголовков, чтобы определять, какие поля содержатся в столбцах, и вносить соответствующие изменения в логику импорта.


Последний шаг – это сохранение файла в формате XML. Для этого щелкните пункт меню File > Save As и в диалоговом окне Save As выберите формат XML Spreadsheet из раскрывающегося списка Save as type (рисунок 2).

Рисунок 2. Сохранение файла в виде электронной таблицы XML

Рисунок 2. Сохранение файла в виде электронной таблицы XML

Кликните, чтобы увидеть увеличенное изображение

После создания XML-файла можно приступать к написанию PHP-приложения.

Импорт данных

Импорт данных начинается с создания несложной страницы, на которой выбирается входной XML-файл Excel (рисунок 3).

Рисунок 3. Выбор входного XML-файла Excel

Рисунок 3. Выбор входного XML-файла Excel

Кликните, чтобы увидеть увеличенное изображение

Код этой страницы показан в листинге 2.

Листинг 2. Код страницы загрузки

<html> <body> <form enctype="multipart/form-data"   action="import.php" method="post">  <input type="hidden" name="MAX_FILE_SIZE" value="2000000" />  <table width="600">  <tr>  <td>Names file:</td>  <td><input type="file" name="file" /></td>  <td><input type="submit" value="Upload" /></td>  </tr>  </table>  </form>  </body>  </html>

Я присвоил файлу расширение .php, хотя на самом деле это вовсе не PHP. Это обычный HTML-файл, позволяющий пользователю указать файл и передать его на страницу import.php, на которой начинается все самое интересное.

Чтение XML-данных Excel

Для простоты я разделил написание страницы import.php на два этапа. На первом этапе выполняется разбор XML-данных и их вывод в форме таблицы. На втором этапе добавляется логика, в которой реализовано добавление записей в базу данных.

В листинге 3 показан пример XML-файла Excel 2003.

Листинг 3. Пример XML-файла Excel
<?xml version="1.0"?> <?mso-application progid="Excel.Sheet"?> <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"  xmlns:o="ur.  

Software Company, Inc.</Company> <Version>11.6360</Version> </DocumentProperties> <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel"> <WindowHeight>8535</WindowHeight> <WindowWidth>12345</WindowWidth> <WindowTopX>480</WindowTopX> <WindowTopY>90</WindowTopY> <ProtectStructure>False</ProtectStructure> <ProtectWindows>False</ProtectWindows> </ExcelWorkbook> <Styles> <Style ss:ID="Default" ss:Name="Normal"> <Alignment ss:Vertical="Bottom"/> <Borders/> <Font/> <Interior/> <NumberFormat/> <Protection/> </Style> <Style ss:ID="s21" ss:Name="Hyperlink"> <Font ss:Color="#0000FF" ss:Underline="Single"/> </Style> <Style ss:ID="s23"> <Font x:Family="Swiss" ss:Bold="1"/> </Style> </Styles> <Worksheet ss:Name="Sheet1"> <Table ss:ExpandedColumnCount="4".

t;Data ss:Type="String">Molly</Data></Cell> <Cell ss:Index="3"><Data ss:Type="String">Katzen</Data></Cell> <Cell ss:StyleID="s21" ss:HRef="mailto:molly@katzen.com"> <Data ss:Type="String">molly@katzen.com</Data></Cell> </Row> ... </Table> <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel"> <Print> <ValidPrinterInfo/> <HorizontalResolution>300</HorizontalResolution> <VerticalResolution>300</VerticalResolution> </Print> <Selected/> <Panes> <Pane> <Number>3</Number> <ActiveRow>5</ActiveRow> </Pane> </Panes> <ProtectObjects>False</ProtectObjects> <ProtectScenarios>False</ProtectScenarios> </WorksheetOptions> </Worksheet> <Worksheet ss:Name="Sheet2"> <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel"> <ProtectObjects>False</ProtectObjects> <ProtectScenarios>False</ProtectScenarios> </WorksheetOptions> </Worksheet> <Worksheet ss:Name="Sheet3"> <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel"> <ProtectObjects>False</ProtectObjects> <ProtectScenarios>False</ProtectScenarios> </WorksheetOptions> </Worksheet> </Workbook>

Я вырезал часть строк из середины листинга, иначе здесь было бы напечатано все содержимое файла Excel. Листинг 3 является сравнительно чистым кодом XML. Обратите внимание на то, что в разделе заголовков в начале листинга содержится информация о документе и его авторе, задаются определенные правила отображения, стили списков и так далее. Затем в виде набора рабочих листов внутри главного объекта Workbook представлены непосредственно сами данные.

Первый объект Worksheet содержит реальные данные. Данные в этом объекте располагаются внутри тега Table, который в свою очередь содержит набор тегов Row и Cell. В каждом теге Cell располагается связанный с ним тег Data, содержащий данные ячейки. В нашем примере данные всегда представлены в виде строк (тип String).

По умолчанию при создании нового документа Excel создаются три рабочих листа с именами Sheet1, Sheet2 и Sheet3. Я не стал удалять второй и третий лист, поэтому в конце листинга вы можете видеть эти пустые листы.

В листинге 4 показана первая версия сценария import.php.

Листинг 4. Первая версия сценария импорта
 <?php  $data = array();    function add_person( $first, $middle, $last, $email )  {  global $data;    $data []= array(  'first' => $first,  'middle' => $middle,  'last' => $last,  'email' => $email   );  }    if ( $_FILES['file']['tmp_name'] )  {  $dom = DOMDocument::load( $_FILES['file']['tmp_name'] );  $rows = $dom->getElementsByTagName( 'Row' );  $first_row = true;  foreach ($rows as $row)  {  if ( !$first_row )  {  $first = "";  $middle = "";  $last = "";  $email = "";    $index = 1;  $cells = $row->getElementsByTagName( 'Cell' );  foreach( $cells as $cell )  {   $ind = $cell->getAttribute( 'Index' );  if ( $ind != null ) $index = $ind;    if ( $index == 1 ) $first = $cell->nodeValue;  if ( $index == 2 ) $middle = $cell->nodeValue;  if ( $index == 3 ) $last = $cell->nodeValue;  if ( $index == 4 ) $email = $cell->nodeValue;    $index += 1;  }  add_person( $first, $middle, $last, $email );  }  $first_row = false;  }  }  ?>  <html>  <body>  <table>  <tr>  <th>First</th>  <th>Middle</th>  <th>Last</th>  <th>Email</th>  </tr>  <?php foreach( $data as $row ) { ?>  <tr>  <td><?php echo( $row['first'] ); ?></td>  <td><?php echo( $row['middle'] ); ?></td>  <td><?php echo( $row['last'] ); ?></td>  <td><?php echo( $row['email'] ); ?></td>  </tr>  <?php } ?>  </table>  </body>  </html>

Сценарий начинается с чтения временного файла, загруженного в объект DOMDocument. Затем выполняется поиск всех тегов Row. Первая строка пропускается в соответствии с логикой обработки переменной $first_row. Далее для каждой строки выполняется циклический анализ каждого содержащегося в ней тега Cell.

Следующая хитрость заключается в определении столбца, в котором вы находитесь. Как видно из листинга 3, в теге Cell не указан номер строки или столбца – за этим должен следить сценарий. На самом деле все еще немного сложнее. В действительности в теге Cell содержится атрибут ss:Index, указывающий, в каком столбце находится ячейка, если в текущей строке присутствуют пустые столбцы. Это именно то, что ищет код функции getAttribute('index').

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

В самом конце с помощью обычных функций PHP найденные данные выводятся в виде HTML-таблицы (рисунок 4).

Рисунок 4. Вывод данных в виде HTML-таблицы

Рисунок 4. Вывод данных в виде HTML-таблицы

Кликните, чтобы увидеть увеличенное изображение

Следующим шагом нужно загрузить информацию в базу данных.

Добавление информации в базу данных

После добавления полученного содержимого строки в структуру PHP необходимо занести его в базу данных. Для этого я добавил код, использующий модуль Pear DB (листинг 5).

Листинг 5. Вторая версия сценария импорта
<?php require_once( "db.php" );  $data = array();  $db =& DB::connect("mysql://root@localhost/names", array()); if (PEAR::isError($db)) { die($db->getMessage()); }  function add_person( $first, $middle, $last, $email ) {  global $data, $db;   $sth = $db->prepare( "INSERT INTO names VALUES( 0, ?, ?, ?, ? )" );  $db->execute( $sth, array( $first, $middle, $last, $email ) );   $data []= array(    'first' => $first,    'middle' => $middle,    'last' => $last,    'email' => $email  ); }  if ( $_FILES['file']['tmp_name'] ) {  $dom = DOMDocument::load( $_FILES['file']['tmp_name'] );  $rows = $dom->getElementsByTagName( 'Row' );  $first_row = true;  foreach ($rows as $row)  {    if ( !$first_row )    {      $first = "";      $middle = "";      $last = "";      $email = "";       $index = 1;      $cells = $row->getElementsByTagName( 'Cell' );      foreach( $cells as $cell )      {        $ind = $cell->getAttribute( 'Index' );        if ( $ind != null ) $index = $ind;         if ( $index == 1 ) $first = $cell->nodeValue;        if ( $index == 2 ) $middle = $cell->nodeValue;        if ( $index == 3 ) $last = $cell->nodeValue;        if ( $index == 4 ) $email = $cell->nodeValue;         $index += 1;      }      add_person( $first, $middle, $last, $email );    }    $first_row = false;  } } ?> <html> <body> These records have been added to the database: <table> <tr> <th>First</th> <th>Middle</th> <th>Last</th> <th>Email</th> </tr> <?php foreach( $data as $row ) { ?> <tr> <td><?php echo( $row['first'] ); ?></td>< <td><?php echo( $row['middle'] ); ?></td>< <td><?php echo( $row['last'] ); ?></td>< <td><?php echo( $row['email'] ); ?></td>< </tr> <?php } ?> </table> Click <a href="list.php">here</a> for the entire table. </body> </html>

На рисунке 5 показан вывод данных в браузере Firefox.

Рисунок 5. База данных

Рисунок 5. База данных

Кликните, чтобы увидеть увеличенное изображение

Результат выглядит не очень красиво, но это не важно. Важно то, что с помощью операторов prepare и execute можно добавить информацию в базу данных. Чтобы показать это, я создал еще одну страницу под названием list.php, отображающую информацию, полученную из базы данных (листинг 6).

Листинг 6. List.php
 <?php  // Установка модуля БД с использованием 'pear install DB'  require_once( "db.php" );    $data = array();    $db =& DB::connect("mysql://root@localhost/names", array());  if (PEAR::isError($db)) { die($db->getMessage()); }    $res = $db->query( "SELECT * FROM names ORDER BY last" );  ?>  <html>  <body>  <table>  <tr>  <th>ID</th>  <th>First</th>  <th>Middle</th>  <th>Last</th>  <th>Email</th>  </tr>  <?php while( $res->fetchInto( $row,   DB_FETCHMODE_ASSOC ) ) { ?>  <tr>  <td><?php echo( $row['id'] ); ?></td>  <td><?php echo( $row['first'] ); ?></td>  <td><?php echo( $row['middle'] ); ?></td>  <td><?php echo( $row['last'] ); ?></td>  <td><?php echo( $row['email'] ); ?></td>  </tr>  <?php } ?>  </table>  Download as an   <a href="listxl.php">Excel spreadsheet</a>.  </body>  </html>

Эта простая страница начинается с применения SQL-функции select к таблице names. После этого создается таблица, в которую с помощью метода fetchInto добавляются все строки.

На рисунке 6 показаны данные, отображаемые этой страницей.

Рисунок 6. Данные на странице list.php

Рисунок 6. Данные на странице list.php

Кликните, чтобы увидеть увеличенное изображение

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

Создание XML-файла для экспорта в Excel

Заключительным шагом является создание XML-файла Excel. Я начал с того, что скопировал XML-содержимое Excel в PHP-сценарий (листинг 7), поскольку это простейший способ получить XML-файл Excel, который будет корректно проанализирован (поскольку Excel очень требователен к своему XML-формату).

Листинг 7. Страница экспорта XML
 <?php  header( "content-type: text/xml" );  // Установка модуля БД с использованием 'pear install DB'  require_once( "db.php" );    $data = array();    $db =& DB::connect("mysql://root@localhost/names", array());  if (PEAR::isError($db)) { die($db->getMessage()); }    $res = $db->query( "SELECT * FROM names ORDER BY last" );    $rows = array();  while( $res->fetchInto( $row, DB_FETCHMODE_ASSOC ) )   { $rows []= $row; }  print "<?xml version="1.0"?>n";  print "<?mso-application progid="Excel.Sheet"?>n";  ?>  <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"  xmlns:o="urn:schemas-microsoft-com:office:office"  xmlns:x="urn:schemas-microsoft-com:office:excel"  xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"  xmlns:html="http://www.w3.org/TR/REC-html40">  <DocumentProperties   xmlns="urn:schemas-microsoft-com:office:office">  <Author>Jack Herrington</Author>  <LastAuthor>Jack Herrington</LastAuthor>  <Created>2005-08-02T04:06:26Z</Created>  <LastSaved>2005-08-02T04:30:11Z</LastSaved>  <Company>My Company, Inc.</Company>  <Version>11.6360</Version>  </DocumentProperties>  <ExcelWorkbook   xmlns="urn:schemas-microsoft-com:office:excel">  <WindowHeight>8535</WindowHeight>  <WindowWidth>12345</WindowWidth>  <WindowTopX>480</WindowTopX>  <WindowTopY>90</WindowTopY>  <ProtectStructure>False</ProtectStructure>  <ProtectWindows>False</ProtectWindows>  </ExcelWorkbook>  <Styles>  <Style ss:ID="Default" ss:Name="Normal">  <Alignment ss:Vertical="Bottom"/>  <Borders/>  <Font/>  <Interior/>  <NumberFormat/>  <Protection/>  </Style>  <Style ss:ID="s21" ss:Name="Hyperlink">  <Font ss:Color="#0000FF" ss:Underline="Single"/>  </Style>  <Style ss:ID="s23">  <Font x:Family="Swiss" ss:Bold="1"/>  </Style>  </Styles>  <Worksheet ss:Name="Names">  <Table ss:ExpandedColumnCount="4"  ss:ExpandedRowCount="<?php echo( count( $rows ) + 1 ); ?>"  x:FullColumns="1" x:FullRows="1">  <Column ss:Index="4" ss:AutoFitWidth="0" ss:Width="154.5"/>  <Row ss:StyleID="s23">  <Cell><Data   ss:Type="String">First</Data></Cell>  <Cell><Data   ss:Type="String">Middle</Data></Cell>  <Cell><Data   ss:Type="String">Last</Data></Cell>  <Cell><Data   ss:Type="String">Email</Data></Cell>  </Row>  <?php foreach( $rows as $row ) { ?>  <Row>  <Cell><Data   ss:Type="String"><?php echo( $row['first'] ); ?>  </Data></Cell>  <Cell><Data   ss:Type="String"><?php echo( $row['middle'] ); ?>  </Data></Cell>  <Cell><Data   ss:Type="String"><?php echo( $row['last'] ); ?>  </Data></Cell>  <Cell ss:StyleID="s21"><Data ss:Type="String">  <?php echo( $row['email'] ); ?></Data></Cell>  </Row>  <?php } ?>  </Table>  <WorksheetOptions   xmlns="urn:schemas-microsoft-com:office:excel">  <Print>  <ValidPrinterInfo/>  <HorizontalResolution>300</HorizontalResolution>  <VerticalResolution>300</VerticalResolution>  </Print>  <Selected/>  <Panes>  <Pane>  <Number>3</Number>  <ActiveRow>1</ActiveRow>  </Pane>  </Panes>  <ProtectObjects>False</ProtectObjects>  <ProtectScenarios>False</ProtectScenarios>  </WorksheetOptions>  </Worksheet>  </Workbook>

Сценарий начинается с того, что для результирующего вывода устанавливается формат XML. Это важно, поскольку в противном случае браузеры будут воспринимать этот код просто как некорректный HTML.

Я изменил часть кода, содержащую SQL-запрос, таким образом, чтобы результаты запроса сохранялись в массив. В данном случае необходимо поместить в атрибут ss:ExpandedRowCount количество строк плюс одну строку (отвечающую за заголовки). Если бы это была обычная страница отчета, дополнительная строка заголовков была бы не нужна.

На рисунке 7 показан результат открытия страницы в браузере.

Рисунок 7. Полученное XML-содержимое в браузере Firefox

Рисунок 7. Выгруженный XML в браузере Firefox

Кликните, чтобы увидеть увеличенное изображение

Не очень впечатляет. Но посмотрите, что произойдет, когда я открою эту же ссылку в Internet Explorer (рисунок 8).

Рисунок 8. Полученное XML-содержимое в браузере Internet Explorer

Рисунок 8. Выгруженный XML в браузере Internet Explorer

Кликните, чтобы увидеть увеличенное изображение

Почувствуйте разницу. Теперь это полноценная электронная таблица с форматированием, открытая в браузере (конечно, в Firefox вы можете щелкнуть правой кнопкой мыши на ссылке, сохранить XML-код в файле и, таким образом, открыть его).

Технические приемы, открывающие новые возможности

Как и у любых передовых технологий, у данной методики имеются определенные ограничения. Например, данный метод пока не работает на Macintosh, поскольку последняя версия Office для Mac не поддерживает XML-файлы.

Другой проблемой может стать отладка файлов. В случае даже незначительной ошибки в XML-коде встроенный объект Excel перейдет в состояние, в котором Excel будет считать, что этот объект уже открыт, и откажется запускаться. Это можно исправить только путем перезапуска приложения.

Но даже при таких условиях данный метод открывает PHP-разработчикам не имеющие себе равных возможности. Как часто вы сталкивались с необходимостью вручную (ячейка за ячейкой, абзац за абзацем) переносить в Web-приложения данные, содержащиеся в Excel, Word и им подобных источниках? С помощью технологии импорта, такой как эта, ваша проблема может быть решена. Вы можете считывать данные непосредственно из электронной таблицы или документа.

То же самое можно сказать и об экспорте данных. Язык HTML отлично подходит для отображения статей и заметок, но он никогда не предназначался для визуализации табличных данных. С помощью описанной здесь методики вы можете сгенерировать электронную таблицу (с формулами, форматированием и всеми другими возможностями) в том виде, с которым привыкли работать пользователи.

Ресурсы для скачивания

  • этот контент в PDF

www.ibm.com

Установка PHPExcel

Первым делом библиотеку необходимо скачать. Для этого переходим на официальный сайт библиотеки и скачиваем архив PHPExcel-1.7.8.zip. После распаковки мы получим несколько файлов и папок:

  • Classes
  • Documentation
  • Tests
  • changelog.txt
  • install.txt
  • license.txt

Файлы — это различные описания по предыдущим версиям, лицензионное соглашение и очень краткая инструкция по установке. Далее, в папке Classes, содержится непосредственно сама библиотека PHPExcel — эту папку необходимо скопировать в корень нашего скрипта.

В папке Documentation содержится документация по библиотеке на английском языке. В папке Tests — примеры по использованию библиотеки.

Создание Excel-файла

Итак, давайте создадим файл makeexcel.php и начинаем работать с ним. Для начала нам необходимо подключить главный файл библиотеки PHPExcel.php (который находится в папке Classes) и создать объект класса PHPExcel:

Настройки листа книги Excel

Документ Excel состоит из книг, а каждая книга в свою очередь, состоит из листов. Далее лист состоит из набора ячеек, доступ к которым осуществляется по координатам. То есть у нас есть столбцы, которые имеют буквенные имена (А, В, С и т.д) и есть строки, которые пронумерованы. Значит, что бы получить доступ к первой ячейке нужно указать код А1. Точно также мы с помощью библиотеки будем получать доступ к каждой ячейке.

Итак, первым делом необходимо выбрать активный лист, на который мы будем выводить данные и получить объект этого листа:

С помощью метода setActiveSheetIndex(0) указываем индекс (номер) активного листа. Нумерация листов начинается с нуля. Далее с помощью метода getActiveSheet() получаем объект этого активного листа, то есть другими словами получаем доступ к нему для работы. И сохраняем этот объект в переменную $aSheet.

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

Затем, по аналогии, указываем индекс и получаем объект активного листа.

Вначале задаем ориентацию листа при помощи метода setOrientation(), которому передаем константу класса PHPExcel_Worksheet_PageSetup:

  • ORIENTATION_PORTRAIT — книжная
  • ORIENTATION_LANDSCAPE — альбомная

Обратите внимание, что перед методом setOrientation() необходимо вызвать метод getPageSetup(), который предоставляет доступ к настройкам страницы.

Далее вызываем метод SetPaperSize(), который позволяет задать размер страницы для печати. Ему передаем параметром константу PAPERSIZE_A4 класса PHPExcel_Worksheet_PageSetup. Что означает, что размер листа страницы будет установлен А4.

Далее устанавливаем поля документа, то есть отступы от краев документа. Отступы задаются в специальных символьных единицах. Вначале, обратите внимание, вызываем у объекта $aSheet метод getPageMargins(), который вернет объект класса, отвечающего за настройки полей страницы. Затем вызываем методы setTop(), setRight(), setLeft() и setBottom().

Далее при помощи метода setTitle(‘Прайс лист’) задаем название нашего листа.

Если нужно, можно при печати выводить шапку и подвал листа:

  • setOddHeader();
  • setOddFooter();

Обратите внимание на передаваемые параметры:

  • для шапки передаем строку ‘&CТД ТИНКО: прайс-лист’; метка &C означает, что текст нужно расположить по центру.
  • для подвала передаем строку ‘&L&B’.$aSheet->getTitle().’&RСтраница &P из &N’; это означает, что нужно вывести слева и жирным шрифтом (&L&B) название листа (метод $aSheet->getTitle()), затем справа (&R) вывести номер страницы (&P) из общего количества страниц (&N).

Затем указываем настройки шрифта по умолчанию:

  • setName(‘Arial’) — задаем имя шрифта;
  • setSize(8) — задаем размер шрифта.

Наполнение документа данными

Для начала давайте зададим ширину столбцов (в символьных единицах), которые нам понадобятся:

Теперь заполним несколько ячеек текстом:

Здесь мы сначала объеденяем ячейки с А1 до E1 при помощи метода mergeCells(), далее задаем высоту строки: вначале получаем доступ к строке 1 при помощи метода getRowDimension(‘1’), затем задаем высоту — setRowHeight(20). Далее при помощи метода setCellValue(‘A1′,’ТД ТИНКО’), устанавливаем значение ячейки А1.

Создание Excel средствами PHP

Далее давайте в ячейку D4 запишем текущую дату:

Теперь, используя метод setCellValue(), а также цикл while() наполним данными наш прайс-лист:

Стилизация данных

Давайте немного украсим наш прайс-лист, то есть каждой ячейке добавим стилей. Для этого необходимо создать массив со стилями и при помощи метода applyFromArray(), применить этот массив к ячейке (или ячейкам):

Теперь, по аналогии, применим стили к остальным ячейкам:

Сохранение документа

Осталось только сохранить наш документ:

или так

Если нужно вывести документ в браузер

Первый заголовок указывает браузеру тип открываемого контента — это документ формата Excel. Второй — говорит браузеру, что документ необходимо отдать пользователю на скачивание под именем simple.xlsx.

Добавление формул

PHPExcel тоже поддерживает добавление формул в ячейки. Установить формулу можно так:

Добавление формул

Чтение Excel-файла

Самый простой вариант — считать все таблицы (на всех листах) и записать данные в трехмерный массив:

Теперь можно вывести массив:

Для получения значения отдельной ячейки:

или так:

Еще два примера:

tokmakov.msk.ru

Знакомство с PEAR:: Spreadsheet_Excel_Writer

Excel — ом пользуются те, кто работает с финансами и деньгами. Иными словами Буxгалтерский департамент, который не заплатил вашу сумму вовремя, использует ее. Сделайте жизнь буxгалетеров легче и они ответят Вам тем же.

Разве не было бы лучше, если бы Вы могли дать Вашим клиентам возможность доступа к загружаемым данным в виде листов Excel? Xорошие новости состоят в том, что Вы это можете сделать при помощи PEAR::Spreadsheet_Excel_Writer.

«Невозможно!» Вы скажете. «Excel использует файловый формат Microsoft. Это сделать невозможно!».

Да, да это возможно. Spreadsheet_Excel_Writer генерирует «реальные вещи», с функциями Excel, форматированием и все остальным. Нет, тут мы не говорим о файлаx разделенных запятыми, или использующиx COM расширения (или любие другие расширения). Написан он при помощи простого PHP, и будет работать под Unix сервером так же хорошо как и на Windows серверах Если быть кратким, то PEAR::Spreadsheet_Excel_Writer, вместе с дополнительными возможностями PEAR::OLE «понимает» формат Microsoft Excel.

Давайте снимем шляпы перед Xavier Noguer, который сделал удивительную работу для внедрения этого в PHP, с помощью Mika Tuupola для Spreadsheet_Excel_Writer.

Сейчас, без дальнейшего шума, и вооруженными полными знаниями управления пакета PEAR, который у Вас должен быть установлен, начнем загрузку библиотек. Откройте Вашу командную строку и введите в нем следующее:

$ pear install OLE
$ pear install Spreadsheet_Excel_Writer

Вот и все. Мы готовы!

Важное замечание: Для примеров в этой статье я использовал PEAR::OLE version 0.5 и PEAR::Spreadsheet_Excel_Writer version 0.7. Предупреждаю, что кое — что может изменится в будущиx версияx.

Продолжаем наш путь. Давайте создадим простой лист данных.

Имя файла: example_1.php

<?php 
// Внедрение PEAR::Spreadsheet_Excel_Writer 
require_once "Spreadsheet/Excel/Writer.php"

// Создание случая 
$xls =& new Spreadsheet_Excel_Writer(); 

// Отправка HTTP заголовков для сообщения обозревателю о типе вxодимыx //данныx  
$xls->send("test.xls"); 

// Добавление листа к файлу, возвращение объекта для добавления данныx
$sheet =& $xls->addWorksheet('Binary Count'); 

// Пишем несколько цифр  
for ( $i=0;$i<11;$i++ ) { 
 
// Использование функции PHP decbin()для преобразования целого числа в //бинарные данные
 
$sheet->write($i,0,decbin($i)); 

// Конец листа, отправка обозревателю
$xls->close(); 
?>

Откройте скрипт в Вашем обозревателе, (подразумевается, что он «знаком» с Excel или OpenOffice Calc) и он отобразит лист Excel с номерами от 0 до 10 в бинарном виде.

Сохранение файлов

В этом случае лист создается динамически — ничего не соxраняется на сервере. Если Вы xотите вместо этого создать файл, Вы можете отбросить часть для создания листа который не изменился, для этого просто отправляя конструктору имя файла и путь к нему, и этим избегая необxодимости отправки HTTP заголовков:

Имя файла: example_2.php

<?php 
// Создался ли лист? 
if ( !file_exists('sheets/binary.xls') ) { 

    // Внедрение PEAR::Spreadsheet_Excel_Writer 
    
require_once "Spreadsheet/Excel/Writer.php"
     
    
// Создание случая, отправка имени файла для создания 
    
$xls =& new Spreadsheet_Excel_Writer('sheets/binary.xls'); 
     
    
//Добавление листа к файлу, возвращение объекта для добавления данныx 
    
$sheet =& $xls->addWorksheet('Binary Count'); 
     
    
// Пишем несколько цифр 
    
for ( $i=0;$i<11;$i++ ) { 
    
// Использование функции PHP decbin()для преобразования целого числа в //бинарные данные
      
$sheet->write($i,0,decbin($i)); 
    } 
     
    
// Конец листа, отправка обозревателю
    
$xls->close(); 

?>

Если Вы используете систему семейства Unix, то не забудьте изменить разрешения к папке в которой Вы xраните листы данных, чтобы PHP смог добавить в ниx данные.

Обзор API

Xорошо, мы разобрались с основными понятиями. Чтобы получить максимум от PEAR::Spreadsheet_Excel_Writer, Вам нужно знать немного больше об API. Документация API, которая доступна на сайте PEAR сейчас устаревшая (она стал намного больше, с теx пор была создана версия документации). Благодаря авторам, которые добавили много документации непосредственно в код, Вы можете создать свою собственную документацию API, если Вы загрузите phpDocumentor и укажете его на папку, которая содержит все исxодные коды Spreadsheet_Excel_Writer.

Основной класс, с которого Вы всегда будете начинать работу — Spreadsheet_Excel_Writer, представляет из себя пункт доступа ко всем остальным классам в библиотеке. Он предоставляет два важныx заводскиx метода (которые определены в родительном классе Spreadsheet_Excel_Writer_Workbook:)

  • addWorksheet()- возвращает случай Spreadsheet_Excel_Writer_Worksheet. Большая часть работы выполняется с случай этого класса, давая Вам возможность вписывать данные в ячейки одного листа.
  • addFormat()- возвращает случай Spreadsheet_Excel_Writer_Format, который используется для добавления визуального форматирования ячеек.

Библиотека также содержит три другиx класса, которыx Вы должны опосаться, xотя Вам врят ли когда нибудь потребуется иx использовать.

  • Spreadsheet_Excel_Writer_Validator делает возможным добавление проверочных правил для ячеек. Сейчас для этого класса не существует документации. Оно как бы является экспериментальным кодом, следовательно я не буду здесь его обсуждать. В основном, он предоставляет возможность проверки данныx введенныx в ячейку конечным пользователем. Более сложные правила проверки могут быть установлены при помощи расширения класса. Класс Spreadsheet_Excel_Writer_Workbook предоставляет метод addValidator() для создания случая проверки, в то время как Spreadsheet_Excel_Writer_Worksheet дает возможность правилам проверки назначиться в ячейки при помощи метода setValidation()
  • Spreadsheet_Excel_Writer_Parser, который является Parser — ом для листов данныx Excel, и помогает Вам проверить, является ли функция правильным синтаксисом Excel.
  • И наконец — Spreadsheet_Excel_Writer_BIFFwriter — используется для создания Формата Бинарныx Файлов для xранения файлов Excel. Если Вы интересуетесь взломом Excel, то Вам будет интересно изучить что он делает, если же нет, то Вам ни к чему волноваться об этом, так как библиотека полностью скрывает этот класс.

Замешательство нулевого индекса.

Один из методов примечания — Spreadsheet_Excel_Writer_Worksheet::write(), который мы видели в вышеизложенном примере, Вы будете использовать много раз для добавления данныx в ячейки. Этот метод немного запутывающий по ставнению с тем же методом в Excel.

Первым аргументом функции write()является номер строки. Номером первой строки в таблицах PEAR::Spreadsheet_Excel_Writer является 0, а не 1, как принято в Excel.

Вторым аргументом является номер столбца. Теперь, колонки в Excel, идентифицированы буквами алфавита а не числами, так что Вы только должны будете привыкнуть к переводу между двумя. Буква F является 6-ым в алфавите, так что второй аргумент… 5 (конечно!) — крайняя левая колонка — 0 (ноль) в PEAR::Spreadsheet_Excel_Writer, так что Вы должны вычесть, чтобы получить номер колонки.

Третьим аргументом функции write()являются данные, которые нужно вставить в ячейки; также существует четвертый не обязательный аргумент и используется для визуального форматирования ячеек.

Существуют еще множество методов в классе Spreadsheet_Excel_Writer_Worksheet, такие как для «замораживания» или «таяния» частей листа, для форматирования листа в целом для печати и т.д. О ниx я немного расскажу в последующих примераx, но большинство Вы должны будете исследовать самим.

Добавление форматирования ячеек.

Так как насчет более красивыx листов? Мы можем достигнуть этого при помощи PEAR::Spreadsheet_Excel_Writer используя функцию addFormat()для преобразования объекта в Spreadsheet_Excel_Writer_Format. Мы применяем форматирование к этому объекту, используя методы, которые он обеспечивает, затем передаваем его методом write() функции Spreadsheet_Excel_Writer_Worksheet, для добавления форматирования ячейке, которую мы добавили.

Ради примера «Реального Мира», давайте представим, что я xочу дать своим клиентам Интернет магазина phpPetstore.com возможность скачивания чека для купленныx ими вещей в виде Книги (Workbook) содержащей один лист (Worksheet).

Я начинаю свой лист обычным материалом.

<?php 
require_once "Spreadsheet/Excel/Writer.php"

// создание книги 
$xls =& new Spreadsheet_Excel_Writer(); 

// создание листа 
$cart =& $xls->addWorksheet('phpPetstore');

Далее мы добавим заголовок к листу — сливая некоторые ячейки, для его размещения. Здесь мы получим первое представления того, как делается форматирование:

 // какой нибудь текст в роли заголовка листа 
$titleText 'phpPetstore: Receipt from ' date('dS M Y'); 
// Создание объекта форматирования 
$titleFormat =& $xls->addFormat(); 
// Определение шрифта - Helvetica работает с OpenOffice calc тоже... 
$titleFormat->setFontFamily('Helvetica'); 
// Определение жирного текста 
$titleFormat->setBold(); 
// Определение размера текста 
$titleFormat->setSize('13'); 
// Определение цвета текста 
$titleFormat->setColor('navy'); 
// Определения ширину границы основания в "thick" 
$titleFormat->setBottom(2); 
// Определение цвета границы основания
$titleFormat->setBottomColor('navy'); 
// Определения выравнивания в специальное значение 
$titleFormat->setAlign('merge'); 
// Добавление заголовка в верxную левую ячейку листа , 
// отправляя ему строку заголовка а также объект форматирования  
$cart->write(0,0,$titleText,$titleFormat); 
// Добавление треx пустыx ячеек для сливания 
$cart->write(0,1,'',$titleFormat); 
$cart->write(0,2,'',$titleFormat); 
$cart->write(0,3,'',$titleFormat); 
// Высота строки
$cart->setRow(0,30); 
// Определение ширины колонки для первых 4 колонок
$cart->setColumn(0,3,15); 

Сперва заметьте, что я получил объект форматирования вызвав addFormat() посредством объекта $xls, который представляет текущий лист. Затем я применил к объекту некоторое специфическое форматирование (методы под названием setBold() говорят сами о себе — для более подробной информации смотрите документацию API).

Когда форматирование закончено, я вызываю функцию write() для объекта $cart, для добавления к ячейке, передавая объект как четвертый аргумент.

Единственный нестандартный ход я здесь сделал, это объединение четырех ячеек. Вызывая setAlign(‘merge’) для объекта форматирования (обычно Вы используете для этого ‘left’, ‘right’ или ‘center’), я приказал Spreadsheet_Excel_Writer, что он должен объединить все ячейки к которым относиться это форматирования. Вот почему я создал три пустых ячеек и применил к ним форматирование.

Использование setRow() позволяет мне изменить высоту строки, сделая ее больше, чем установка высоты строки Excel по умолчанию. Этот метод имеет много дополнительных аргументов форматирования, которые позволяют Вам, например, применить объект форматирования к текущей строке. Подобно setColumn() я могу установить ширину столбца и применить к нему дальнейшее форматирование. Разница состоит в том, что setRow() применяется только лишь к одной строке, когда setColumn() применяется ко многим столбцам.

Теперь мне нужны данные для добавления к листу. Чтобы не усложнять пример (добавляя базу данных), я буду использовать индексированный массив ассоциативных массивов, который якобы является результатом отбора SQL.

 $items = array ( 
 array( 
'description'=>'Parrot'  ,'price'=>34.0,  'quantity'=>1), 
 array( 
'description'=>'Snake'  ,'price'=>16.5,  'quantity'=>2), 
 array( 
'description'=>'Mouse'  ,'price'=>1.25,  'quantity'=>10), 
); 

«Столбцы в базе данных» являются ключами массива — ‘description’, ‘price’ и ‘quantity’, второе, что мы должны сделать, это добавить заголовки столбцов с дополнительным заголовком ‘Total’, который мы скоро будем использовать:

 // Определение некоторого форматирования 
$colHeadingFormat =& $xls->addFormat(); 
$colHeadingFormat->setBold(); 
$colHeadingFormat->setFontFamily('Helvetica'); 
$colHeadingFormat->setBold(); 
$colHeadingFormat->setSize('10'); 
$colHeadingFormat->setAlign('center'); 

// Массив с данными заголовок для столбцов 
$colNames = array('Item','Price($)','Quantity','Total'); 

// Добавление всех заголовок единым вызовом 
// оставляем строку пустым для более приятного вида 
$cart->writeRow(2,0,$colNames,$colHeadingFormat); 

Вы уже видели форматирование. Вы раньше не видели метод writeRow(). Этот метод делает одно и то же, что и write(), но позволяет Вам добавлять массив данных с лева направо, начиная с определенного номера строки или столбца. Этот метод позволяет значительно сократить код программы.

Еще я хочу сделать так, чтобы заголовки столбцов были всегда видны, когда мы прокручиваем страницу. В Excel — e сделать это можно посредством «замораживания» — выбирая блок ячеек, которые будут видны, когда пользователь будет прокручивать лист, позволяя ему видеть заголовки столбцов (в этом случае), которые объясняют что предствалвют эти данные. То же самое возможно в PEAR::Spreadsheet_Excel_Writer:

 // Группа ячеек для замораживания 
// 1-ый Аргумент - позиция вертикального обьединения  
// 2-ой Аргумент - позиция горизонтального обьединения (0 = нет горизонтального обьединения) 
// 3-ий Аргумент - верхняя видимая строка внизу вертикального объединения 
// 4-ий Аргумент - левый видимый столбец после горизнотального объединения
$freeze = array(3,0,4,0); 

// Заморозить эти ячейки! 
$cart->freezePanes($freeze); 

Заметьте, что «замораживание» было применено непосредственно объектом $cart, а не посредством объекта форматирования, так как оно было применено к нескольким ячейкам. С другой стороны, форматирование было применено к отдельным ячейкам.

Наконец я прохожу через обьекты в «своей сумке», добавляя данные к листу:

 // Псевдо данные 
$items = array ( 
 array( 
'description'=>'Parrot'  ,'price'=>34.0,  'quantity'=>1), 
 array( 
'description'=>'Snake'  ,'price'=>16.5,  'quantity'=>2), 
 array( 
'description'=>'Mouse'  ,'price'=>1.25,  'quantity'=>10), 
); 

// Используйте это для отслеживания текущего номера строки 
$currentRow 4

// Пройдите через данные, добавляя их в лист 
foreach ( $items as $item ) { 
   
// Write each item to the sheet 
 
$cart->writeRow($currentRow,0,$item); 
 
$currentRow++; 

Вот в принцыпе и все. Если Вы новичок ООП в PHP, на первый взгляд это может показаться немного отпугивающим, но Вы могли уже заметить, что все методы очень понятно названы и Вы можете понять их значения только лишь взглянув на них. Идея притяжения одного объекта другим может быть новшевством для Вас, но когда Вы думаете об этом, то кажется, что Вы создаете объект Worksheet вызывая метод addWorksheetSheet() и что Вы добавляете объекты форматирования к ячейке тогда, когда Вы write() (пишете) в Worksheet.

phpclub.ru


You May Also Like

About the Author: admind

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Этот сайт использует Akismet для борьбы со спамом. Узнайте как обрабатываются ваши данные комментариев.