Содержание


Чтение и запись данных Excel из приложений PHP

Использование поддержки XML

Comments

Пакет Microsoft Office 2003 для операционной системы Microsoft Windows® открыл для разработчиков целый ряд новых возможностей, с которыми они не сталкивались прежде. Конечно, в вашем распоряжении имелся набор новых функций, однако наиболее значимым преимуществом стало добавление поддержки файловых XML-форматов. В Office 2003 вы можете сохранить электронную таблицу Microsoft Excel в XML-формате и использовать ХМL-файл так же, как и его двоичный эквивалент. То же самое касается и Microsoft Word.

Почему же XML-формат имеет такое большое значение? Потому, что на протяжении многих лет истинная мощь Excel или Word была заблокирована использованием файлов в двоичных форматах, а доступ к этим файлам извне можно было получить только с помощью программ-конвертеров. Теперь же файлы Excel или Word можно читать и записывать с помощью таких инструментов, как XSLT (Extensible Stylesheet Language Transformation) или DOM (XML Document Object Model), встроенных в язык программирования PHP.

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

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

В этой статье приведен пример простого 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. Данные для импорта
Рисунок 1. Данные для импорта

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

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

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

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

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

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

Рисунок 3. Выбор входного XML-файла Excel
Рисунок 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="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 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" 
  ss:ExpandedRowCount="5" 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>
  <Row>
  <Cell><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-таблицы
Рисунок 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. База данных
Рисунок 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
Рисунок 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
Рисунок 7. Выгруженный XML в браузере Firefox

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

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

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

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

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

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

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

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


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


Похожие темы


Комментарии

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

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=40
Zone=Open source, XML
ArticleID=834287
ArticleTitle=Чтение и запись данных Excel из приложений PHP
publish-date=09102012