maxidebox@list.ru | +7 (929) 701 21 71
Чтение и запись данных Excel из приложений PHP

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

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

Узнайте, как использовать поддержку XML в PHP для чтения данных, выгруженных из Microsoft® Excel® 2003 в XML-формате, и как экспортировать данные в XML-формате из PHP-приложений для использования в электронных таблицах Excel.

Пакет 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. Данные для импорта

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

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

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

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

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

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

Рисунок 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-таблицы

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

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

После добавления полученного содержимого строки в структуру 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. База данных

Результат выглядит не очень красиво, но это не важно. Важно то, что с помощью операторов 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

Несмотря на не очень красивый вид страницы, общая идея занесения информации в базу данных должна быть понятна. Это, в свою очередь, послужит основой сценария, генерирующего 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

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

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

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

Голосов: 218 | Просмотров: 732