Чтение и запись электронных таблиц в PHP

Янв 10

Электронные таблицы в PHP. Чтение.

Преодолеваем языковой барьер

Когда возникает необходимость работать с данными в различных форматах, PHP трудно превзойти. Язык позволяет работать не только с SQL и XML, но и (приналичии соответствующих расширений) с форматами вроде Ogg/Vorbis, ZIP-архивами, и заголовками EXIF. Поэтому нет ничего удивтельного в том, что PHP также может читать и писать электронные таблицы Microsoft Excel, пусть и с небольшой помощью PEAR.

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

Собираем все в кучу

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

PHP-ExcelReader, в данный момент не поддерживается, но доступен по адресу http://sourceforge.net/projects/phpexcelreader/
Spreadsheet_Excel_Writer, поддерживается силами Xavier Noguer и доступен на http://pear.php.net/package/Spreadsheet_Excel_Writer

Для работы последнего пакета вам понадобится скачать и установить пакет PEAR OLE, взять его можно с http://pear.php.net/package/OLE

Пакеты можно уставить либо вручную, либо при помощи установщика PEAR:

shell> pear install Spreadsheet_Excel_Writer

Теперь, возможно, вам придется кое-что подкорректировать, чтобы заставить работать PHP-ExcelReader.

Как говорилось выше, пакет больше не поддерживается, поэтому простого подключения reader.php при помощи include() в ваш скрипт недостаточно. Чтобы заставить его работать, извлеките файл oleread.inc из архива в директорию, доступную для подключения, соблюдая вложенность каталогов – Spreadsheet/Excel/Reader/OLERead.php. После этой процедуры все должно заработать (текущая версия PHP-ExcelReader на момент написания статьи от 5 июля 2007 г., с другими может не работать).

Играем с числами

После того, как все компоненты готовы, посмотрим на них в действии. Допустим, что у нас имеется следующая таблица:

Теперь напишем простой PHP-скрипт, который читает эту таблицуи отображает количество рабочих листов, строк и столбцов в них:

< ?php
//подключаем класс
include 'reader.php';

//инициализуем объект
$excel = new Spreadsheet_Excel_Reader();

//читаем данные таблицы
$excel->read('Book1.xls');

//выводим колиество листов, строк и столбцов
echo "Number of sheets: " . sizeof($excel->sheets) . "\n";
for ($x=0; $x<sizeof ($excel->sheets); $x++) {
  echo "Number of rows in sheet " . ($x+1) . ": " . $excel->sheets[$x]["numRows"] . "\n";
  echo "Number of columns in sheet " . ($x+1) . ": " . $excel->sheets[$x]["numCols"] . "\n";
}
?>

Результат работы скрипта:

Как же это получилось? Это было не очень сложно, потому что PHP-ExcelReader делает за вас всю грязную работу. В первом шаге мы подключили файл с пакетом и создали объект класса Spreadsheet_Excel_Reader. Этот объект имеет метод read(), принимающий в качестве аргумента путь к файлу Excel и преобразующий его данные так, чтобы с ними можно было работать средствами PHP.

Каждый файл Excel имеет как минимум один рабочий лист. Листы представлен свойством sheets, представленным в виде массива, нумерованного с нуля. Например, первый лист доступен как $obj->sheets[0], второй- как $obj->sheets[1] и т.д.

Для каждого листа PHP-ExcelReader создает в массиве несколько ключей с информацией о листе. Например, numRows содержит количество строк, а numCols – количество столбцов.

Читаем данные

PHP-ExcelReader хранит данные ячеек каждого листа в соответствующем массиве cells, вложенном в массив рабочего листа. Получить отдельную ячейку можно по ее координатам, например, ячейка 5C может быть получена так: $obj->sheets[0]['cells'][5][3]. Нумерация строк и столбцов начинается с 1.

Теперь напишем скрипт, отображающий данные таблицы Excel в виде HTML-таблицы:

<html>
  <head>
    <style type="text/css">
    table {
    	border-collapse: collapse;
    }
    td {
    	border: 1px solid black;
    	padding: 0 0.5em;
    }
    </style>
  </head>
  <body>
    <table>
    < ?php
    //подключаем класс
    include 'reader.php';

    //инициализуем объект
    $excel = new Spreadsheet_Excel_Reader();

   //читаем данные таблицы
   $excel->read('Book1.xls');    

   //выводим данные таблицы Excel в виде HTML-таблицы
    $x=1;
    while($x< =$excel->sheets[0]['numRows']) {
      echo "\t<tr>\n";
      $y=1;
      while($y< =$excel->sheets[0]['numCols']) {
        $cell = isset($excel->sheets[0]['cells'][$x][$y]) ? $excel->sheets[0]['cells'][$x][$y] : '';
        echo "\t\t<td>$cell</td>\n";
        $y++;
      }
      echo "\t</tr>\n";
      $x++;
    }
    ?>
    </table>
  </body>
</html>

Как видите, нет ничего сложного в том, чтобы вывести данные рабочего листа в HTML-таблицу. Все, что нам нужно – это два цикла: один цикл пробегает по строкам, а другой – по столбцам, а также немного PHP-кода для получения значения ячейки.

Результат:

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

И перезапустим наш скрипт:

Конвертирование таблиц

Используя мощь PHP-ExcelReader, можно легко передать данные из файла Excel в базу данных MySQL:

< ?php
include 'reader.php';

$excel = new Spreadsheet_Excel_Reader();

$excel->read('Book2.xls');

//пробуем соединиться
try {
   $pdo = new PDO('mysql:dbname=test;host=localhost', 'user', 'pass');
} catch (PDOException $e) {
   die("ERROR: Could not connect: " . $e->getMessage());
}

// передаем данные из ячеек в SQL-запрос
$sql = "INSERT INTO data (country, sales) VALUES (?, ?)";
if ($stmt = $pdo->prepare($sql)) {
  $x=2;
  while($x< =$excel->sheets[0]['numRows']) {
    $stmt->bindParam(1, $excel->sheets[0]['cells'][$x][1]);
    $stmt->bindParam(2, $excel->sheets[0]['cells'][$x][2]);
    if (!$stmt->execute()) {
      echo "ERROR: Could not execute query: $sql. " . print_r($pdo->errorInfo());
    }
    $x++;
  }
} else {
  echo "ERROR: Could not prepare query: $sql. " . print_r($pdo->errorInfo());
}

//закрываем соединение
unset($pdo);
?>

Скормим скрипту следующую таблицу:

После того, как скрипт отработает, вы увидите данные таблице в базе:
mysql> SELECT * FROM data; +----------------+-------+ | country | sales | +----------------+-------+ | United States | 7583 | | United Kingdom | 4359 | | France | 45885 | | Germany | 3833 | | Spain | 8738 | | Italy | 5239 | | Greece | 38282 | +----------------+-------+ 7 rows in set (0.01 sec)

Автор – Vikram Vaswani

Оригинал – http://devzone.zend.com/article/3336-Reading-and-Writing-Spreadsheets-with-PHP

Связанные записи

Отзывов (46) на «Электронные таблицы в PHP. Чтение.»

Onwave пишет:
22 Янв 2009 в 16:00
Чтобы заставить его работать, извлеките файл oleread.inc из архива в директорию, доступную для подключения, соблюдая вложенность каталогов – Spreadsheet/Excel/Reader/OLERead.php

Куда извлечь?! Я новичек, поясните, в какую директорию извлеч…На примере денвера?!

[Ответить]
0utPunk пишет:
22 Янв 2009 в 16:52
К сожалению, я не помню, что и где в денвере находится. Объясню на общем примере навскидку.

1.Найдите в файле php.ini директиву include_path, в ней указаны каталоги, откуда php будет подключать библиотеки.
У меня она вылядит так:
include_path = «.:/php/includes:/usr/local/lib/php»

2.В указанных в директиве каталогах ищем каталог PEAR.

3. В каталоге PEAR создаем нашу «матрешку» – Spreadsheet/Excel/Reader/OLERead.php.

Если каталога PEAR нигде не обнаружено, идем на http://pear.php.net/package/PEAR/download, качаем отуда архив, открываем его, видим каталог PEAR-< номер версии>, распаковываем его содержимое в каталог, указанный в include_path и выполняем третий шаг.

[Ответить]
Паша пишет:
24 Сен 2009 в 15:37
Автору большое спасибо!!! за отличную статью, правда есть пару вопросов по подключении библиотеке на сервере!

[Ответить]
0utPunk Reply:
сентября 24, 2009 at 18:48
Задавайте, постараюсь ответить.

[Ответить]
icebergcap пишет:
19 Окт 2009 в 11:54
Есть следующий вопрос – как быть с кодировками?
вот здесь можно глянуть http://karetti.ru/ex.php

[Ответить]
0utPunk Reply:
октября 19, 2009 at 12:59
Вам стоит посмотрить в сторону iconv

[Ответить]
Demokrat пишет:
27 Окт 2009 в 15:11
Здравствуйте! У меня проблема с выводом данных строк и столбцов, выводит пустой лист без данныx, из за чего это может быть?
Подскажите.Заранее спасибо!

[Ответить]
0utPunk пишет:
27 Окт 2009 в 15:56
Может быть у вас ошибка в коде, может быть с кодировкой что-то. В любом случае без конкретных данных сложно судить.

[Ответить]
Demokrat пишет:
27 Окт 2009 в 16:08
Да нет все проверил поэтапно, первое выводит отлично все! т.е
читает эту таблицуи отображает количество рабочих листов, строк и столбцов в них…

А вот в следующем небольшая проблемка, может библиотеку нужно поместить в другое место она у меня в своей директории.
Подскажите!

[Ответить]
Demokrat пишет:
27 Окт 2009 в 16:10
выводит былый лист без данных. Т.е я сделал так

table {
border-collapse: collapse;
}
td {
border: 1px solid black;
padding: 0 0.5em;
}

read(’1.xls’);

//выводим данные таблицы Excel в виде HTML-таблицы
$x=1;
while($xsheets[0]['numRows']) {
echo «\t\n»;
$y=1;
while($ysheets[0]['numCols']) {
$cell = isset($excel->sheets[0]['cells'][$x][$y]) ? $excel->sheets[0]['cells'][$x][$y] : »;
echo «\t\t$cell\n»;
$y++;
}
echo «\t\n»;
$x++;
}
?>

[Ответить]
Demokrat пишет:
27 Окт 2009 в 16:29
Администратор вы где? подскажите?

[Ответить]
0utPunk пишет:
27 Окт 2009 в 18:02
Попробуйте вызвать var_dump(), передав ей $excel->sheets, и по результату ориентируйтесь.

[Ответить]
xen пишет:
29 Окт 2009 в 00:47
Огромное тебе автор человеческое спасибо)))) Статья просто замечательная.

[Ответить]
ILVIR пишет:
29 Окт 2009 в 00:53
КТо нибудь разобрался с кодировками? подскажите плиз а то русские символы выводит непонятными символами((

[Ответить]
0utPunk пишет:
29 Окт 2009 в 08:58
xen, всегда пожалуйста!
ILVIR, вам нужна функция iconv
Применяется так: $string = iconv($in_charset, $out_charset, $string);

[Ответить]
Саня пишет:
29 Окт 2009 в 15:31
Здравствуйте администратор! а как правильно подключить библеотеку? на локальной машине

[Ответить]
ILVIR пишет:
29 Окт 2009 в 18:21
Я ее пробовал и пробую)) А какие кодировки нужно вводить вместо $in_charset, $out_charset? никак не хочет читать русские символы

Вот типа так:
$cell = isset($excel->sheets[0]['cells'][$x][$y]) ? $excel->sheets[0]['cells'][$x][$y] : »;
$cell1 = iconv(«windows-1251″, «utf-8″, $cell);
echo «\t\t$cell1\n»;
И другие кодировки пробовал, и местами менял, не выходит пока что)

[Ответить]
professor пишет:
29 Окт 2009 в 21:57
попробуй вот это напеши

$Excel->setOutputEncoding(‘cp1251′); // устанавливаем кодировку

[Ответить]
ILVIR пишет:
29 Окт 2009 в 22:41
professor, спасибо большое заработало)))

[Ответить]
0utPunk пишет:
29 Окт 2009 в 22:42
Еще для определения кодировки, если она неизвестна с самого начала, бывает полезна функция mb_detect_encoding()

[Ответить]
0utPunk пишет:
29 Окт 2009 в 22:46
Саня, прочитайте второй комментарий.

[Ответить]
professor пишет:
30 Окт 2009 в 14:22
ILVIR
скинь хоть ты премер чтоб люди уже не парелись

[Ответить]
ILVIR пишет:
30 Окт 2009 в 16:41
ПОжалуйста, вот моя таблица:

setOutputEncoding(‘cp1251′);
//читаем данные таблицы
$excel->read(‘excel1.xls’);

//выводим данные таблицы Excel в виде HTML-таблицы
$x=1;
while($xsheets[0]['numRows']) {
echo «\t\n»;
$y=1;
while($ysheets[0]['numCols']) {
$cell = isset($excel->sheets[0]['cells'][$x][$y]) ? $excel->sheets[0]['cells'][$x][$y] : »;
// $cell1 = iconv(«utf-8″, «windows-1251″, $cell);
echo «\t\t$cell\n»;
$y++;
}
echo «\t\n»;
$x++;
}
?>

Demokrat, в 2 строках с while поставь знаки <=

[Ответить]
professor пишет:
31 Окт 2009 в 09:51
А теперь мона попробовать вывести не тока данные но и цвет ячейки совмещенные ячейки заголовки ну и тому подобное

у кого есть мысли

[Ответить]
Робота з Excel таблицями засобами PHP | Блоґ Виспянського Ігоря пишет:
01 Ноя 2009 в 23:29
[...] є перекладена в двох частинах на російську мову: «Электронные таблицы в PHP. Чтение» і «Электронные таблицы в PHP. [...]
Demikrat пишет:
03 Ноя 2009 в 16:23
ILVIR, поставил <= не помогает. выдает ошибку.
может ты подробней опишишь что нужно сделать?

[Ответить]
0utPunk пишет:
03 Ноя 2009 в 18:06
Demikrat, пости сюда кусок кода, который изменил

[Ответить]
professor пишет:
04 Ноя 2009 в 21:50
все пашет сам пробовал
http://www.shmeller.net/files/prof/test/index.php

вот что тут

table {
border-collapse: collapse;
}
td {
border: 1px solid black;
padding: 0 0.5em;
}

setOutputEncoding(‘CP1251′);

//читаем данные таблицы
$excel->read(‘Book1.xls’);

//выводим данные таблицы Excel в виде HTML-таблицы
$x=1;
while($x sheets[0]['numRows']) {
echo «\t\n»;
$y=1;
while($y sheets[0]['numCols']) {
$cell = isset($excel->sheets[0]['cells'][$x][$y]) ? $excel->sheets[0]['cells'][$x][$y] : »;
echo «\t\t$cell\n»;
$y++;
}
echo «\t\n»;
$x++;
}
?>

[Ответить]
professor пишет:
04 Ноя 2009 в 21:53
давайте лучше дольше развивать
допустим мне нуна чтобы не просто данные считались но и цвет ячейки и обледененные ячейки, границы таблицы из xls

[Ответить]
Demikrat пишет:
09 Ноя 2009 в 12:54
Приветствую всех!
Описываю код текста каторый набирал в пхп (выводит белый лист без ошибки) может у меня проблема с какой нибудь библиотекой?
но хотя првый пример работает нормально т.е читает эту таблицу и отображает количество рабочих листов, строк и столбцов в них
- – - – - – — – - – - – - – — – - – - – — – - – - –
// в чем может быть проблема в этом коде??? Я уже начитаю нервничать!

table {
border-collapse: collapse;
}
td {
border: 1px solid black;
padding: 0 0.5em;
}

read(’1.xls’);

//выводим данные таблицы Excel в виде HTML-таблицы
$x=1;
while($xsheets[0]['numRows']) {
echo «\t\n»;
$y=1;
while($ysheets[0]['numCols']) {
$cell=isset($excel->sheets[0]['cells'][$x][$y]) ? $excel->sheets[0]['cells'][$x][$y] : »;
echo «\t\t$cell\n»;
$y++;
}
echo «\t\n»;
$x++;
}
?>

[Ответить]
0utPunk пишет:
09 Ноя 2009 в 15:44
Выложите код на http://pastie.org и дайте сюда ссылку, а то парсер вордпресса часть кода съедает.

[Ответить]
Demikrat пишет:
09 Ноя 2009 в 16:59
Добавил код туда который у меня описан выше…джу от Вас ответа

[Ответить]
0utPunk пишет:
09 Ноя 2009 в 17:56
А где ссылка?

[Ответить]
Demokrat пишет:
10 Ноя 2009 в 00:18
http://pastie.org/690524
вот ссылка

[Ответить]
0utPunk пишет:
11 Ноя 2009 в 09:58
Следующие фрагменты кода:
< ?php $x< =$excel->sheets[0]['numRows'] $y< =$excel->sheets[0]['numCols']
должны быть без пробелов и выглядеть так

Потому что дескрипторы PHP, равно как и оператор <= (меньше или равно) всегда должны быть БЕЗ ПРОБЕЛОВ. У меня в посте так, потому что парсер вордпресса глючит.

[Ответить]
Demikrat пишет:
12 Ноя 2009 в 12:15
Здравствуйте 0utPunk, исправил, но проблема так же пока остала… может еще куда нибудь глянуть? у вас так же не вывоводит если есть пробелы, в коде т.е выводит белый лист.. просто я подазреваю на то что у меня может быть неработает правильно библиотека OLE-1.0.0RC1

[Ответить]
0utPunk пишет:
12 Ноя 2009 в 22:14
Попробуйте сделать error_reporting(E_ALL); в начале скрипта и запустить, может, что и покажет.

[Ответить]
shvendel пишет:
15 Ноя 2009 в 02:48
Demikrat, та же самая беда
Мне кажется дело не в коде…..

[Ответить]
Demikrat пишет:
24 Ноя 2009 в 16:11
Администратор? как решить такую проьблему? если она есть не только у меня?

[Ответить]
Dreamer пишет:
12 Янв 2010 в 11:00
Здравствуйте!
А как-то можно сделать следующее: записать уже в ранее созданный файл данные, там чтоб выполнилась формула(в соответствующих ячейках она уже записана) и затем прочитать всю информацию из файла .xls. Искал в функциях, как создать объект Spreadsheet_Excel_Writer_Worksheet c уже готового файла но не нашёл. Может есть какой-то способ. Подскажите пожалуйста

[Ответить]
Dreamer пишет:
12 Янв 2010 в 12:27
Или может кто знает как перенести формулу из одного xls файла в другой xls файл? Можно ли как-то извлечь формулу из xls- файла?

[Ответить]
Cep}|{uo пишет:
21 Янв 2010 в 09:18
Библиотека понимает не все форматы эксель, тестил на одном файле, читает видит листы, но данных не видит.

[Ответить]
maxander пишет:
25 Янв 2010 в 07:53
Помогите ни как не могу разобраться почему меня за места русской кадировки ерунда какая то. Функцией incov и mb_string не могу воспользоваться не включают её. Решил самопальной воспользоваться.

function utf8($string){
for ($c=0;$c<strlen($string);$c++){
$i=ord($string[$c]);
if ($i >2)&5;
$new_i=$new_c1*256+$new_c2;
if ($new_i==1025){
$out_i=168;
} else {
if ($new_i==1105){
$out_i=184;
} else {
$out_i=$new_i-848;
}
}
@$out .= chr($out_i);
$byte2 = false;
}
if (($i>>5)==6) {
$c1 = $i;
$byte2 = true;
}
}
return $out;
}

require_once ‘reader.php’;
$data = new Spreadsheet_Excel_Reader();
$data->setOutputEncoding(‘CP1251′);
$data->read(‘test.xls’);
for ($i = 7; $i sheets[0]['numRows']; $i++)
{
echo $test=$data->sheets[0]['cells'][$i][1];
}

Получается вот что:
B5AB
B5AB
B5AB
B5AB
B5AB
B5AB
B5AB
B5AB

[Ответить]
Никита пишет:
10 Фев 2010 в 02:09
echo iconv(‘UNICODELITTLE’,'UTF-8′,$stroka); – вот это помогает и все по русски…

выдрано из этого класса:
демо: http://440hz.ru/trash/excel/
скачать: http://440hz.ru/trash/excel/excel.tar.gz

[Ответить]
Максим пишет:
11 Июн 2010 в 09:27
Спасибо огромное за статью – очень пригодилась. А кто-нибудь встречал reader для формата xlsx?

[Ответить]
ISQman пишет:
26 Июл 2010 в 10:46
для документов типа *.xlsx есть на перле решение, но оно грязное, сам вот не могу толком разобраться, также у меня проблема с объединением ячеек в прайс-листах (объединение по вертикали (rowspan)), колспаны победил и быстро, а вот с рядами никак, также пробовал весь лист засунуть в базу данных, что тоже приятно.
если у кого найдётся решение, или хотите перекинуться мыслишками по поводу *.xlsx – isqman@bk.ru или 424174772 ася или ISQman в скайпе.

[Ответить]

0utPunk's Blog

Электронные таблицы в PHP. Чтение.

Преодолеваем языковой барьер

Собираем все в кучу

Играем с числами

Читаем данные

Конвертирование таблиц

Связанные записи

Отзывов (46) на «Электронные таблицы в PHP. Чтение.»

Ваш отзыв

RSS

Рубрики

Свежие комментарии

Архивы

Сертификаты

Кнопы

0utPunk's Blog

Электронные таблицы в PHP. Чтение.

Преодолеваем языковой барьер

Собираем все в кучу

Играем с числами

Читаем данные

Конвертирование таблиц

Связанные записи

Отзывов (46) на «Электронные таблицы в PHP. Чтение.»

Ваш отзыв

RSS

Рубрики

Свежие комментарии

Метки

Архивы

Сертификаты

Кнопы