Чтение Excel-файлов с помощью PHPExcel

Главная > php> Чтение Excel-файлов с помощью PHPExcel

Чтение Excel-файлов с помощью PHPExcel

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

Зачем это нужно

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

Шаблон

Для примера с шаблоном рассмотрим ситуацию, когда в интернет-магазине происходит отсылка заказа. При этом зачастую товары отсылаются по почте. Почта, как впрочем, и другие учреждения, требуют сопроводительную документацию. В данном случае речь идет о форме сопроводительного бланка к посылке (ф.116).

В экселе это выглядит так

Как видите, в таблице уже есть данные «От кого», «Адрес отправителя» и «Предъявленный документ». Нам нужно вписать следующие данные: «Сумма» (цифрами и прописью), «Кому», «Адрес получателя».
Для чтения данных в библиотеке PHPExcel существует класс PHP_Excel_IOFactory. В этом классе для загрузки есть статичный метод load()

PHPExcel PHP_Excel_IOFactory::load( string $pFilename)

Этот метод получает имя xls-файла, который нужно загрузить, а возвращает объект класса PHPExcel, с уже загруженными данными. После чего можно делать изменения в таблице с помощью вызова нужных методов класса PHPExcel. Подробнее, об этих методах можете почитать в предыдущей статье.
f116.php

set_include_path(get_include_path() . PATH_SEPARATOR .
'PhpExcel/Classes/');
//массив со сформированными данными
$data = array(
'index'=>'456787',
'fio'=>'Иванову Ивану Ивановичу',
'city'=>'г.Москва',
'address'=>'ул.Ленина, д.1 кв.1',
'summ'=>'1000',
'summ_'=>'Одна тысяча'
);
include_once 'PHPExcel/IOFactory.php';
$objPHPExcel = PHPExcel_IOFactory::load("template.xls");
$objPHPExcel->setActiveSheetIndex(0);
$aSheet = $objPHPExcel->getActiveSheet();
//индекс
$index = str_split($data['index'],1);
$aSheet->setCellValue('D23',$index[0]);
$aSheet->setCellValue('E23',$index[1]);
$aSheet->setCellValue('F23',$index[2]);
$aSheet->setCellValue('G23',$index[3]);
$aSheet->setCellValue('H23',$index[4]);
$aSheet->setCellValue('I23',$index[5]);
$aSheet->setCellValue('D52',$index[0]);
$aSheet->setCellValue('E52',$index[1]);
$aSheet->setCellValue('F52',$index[2]);
$aSheet->setCellValue('G52',$index[3]);
$aSheet->setCellValue('H52',$index[4]);
$aSheet->setCellValue('I52',$index[5]);
//сумма прописью
$aSheet->setCellValue('C16',
mb_convert_encoding($data['summ_'].' рублей',
'utf-8', 'windows-1251'));
//сумма цифрами
$aSheet->setCellValue('L21', $data['summ']);
$aSheet->setCellValue('L49', $data['summ']);
//город
$aSheet->setCellValue('C25',
mb_convert_encoding($data['city'],'utf-8',
'windows-1251'));
$aSheet->setCellValue('C54',
mb_convert_encoding($data['city'],'utf-8',
'windows-1251'));
//улица
$aSheet->setCellValue('C26',
mb_convert_encoding($data['address'],'utf-8',
'windows-1251'));
$aSheet->setCellValue('C55',
mb_convert_encoding($data['address'],'utf-8',
'windows-1251'));
//кому
$aSheet->setCellValue('D27',
mb_convert_encoding($data['fio'],'utf-8','windows-1251'));
$aSheet->setCellValue('D56',
mb_convert_encoding($data['fio'],'utf-8','windows-1251'));
//создаем объект класса-писателя
include("PHPExcel/Writer/Excel5.php");
$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
//выводим заголовки
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="print.xls"');
header('Cache-Control: max-age=0');
//выводим в браузер таблицу с бланком
$objWriter->save('php://output');

В результате в браузер будет выведен файл с заполненным бланком

Бланк можно выводить на печать и отправлять посылку

Небольшое замечание по поводу mb_convert_encoding(). Эту функцию стоит использовать только, если кодировка данных отлична от utf-8.

Итератор

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

Чудо-рейтинг во всей красе

Поставим самую простую задачу: вывести все данные, которые находятся в таблице.
В этом случае нам понадобятся два класса из библиотеки PHPExcel. Это класс

PHPExcel_Worksheet_RowIterator
который предназначен для представления итераций строк, и класс

PHPExcel_Worksheet_CellIterator

который предназначен для представления итераций ячеек. Оба эти класса являются потомками класса IteratorIterator, который имеет очень удобный функционал для вывода класса в цикле foreach как массив.
Получить объекты обоих классов можно через методы

PHPExcel_Worksheet_Row PHPExcel_Worksheet::getRowIterator()

PHPExcel_Worksheet_CellIterator PHPExcel_Worksheet_Row::getCellIterator()

Вот код, реализующий этот функционал.
iterator.php

<html>
<head>
<title>Iterator</title>
<meta http-equiv="content-type"
content="text/html;charset=utf-8"/>
</head>
<body>
<?php
set_include_path(get_include_path() .
PATH_SEPARATOR . 'PhpExcel/Classes/');
include_once 'PHPExcel/IOFactory.php';
$objPHPExcel = PHPExcel_IOFactory::load("rate.xls");
$objPHPExcel->setActiveSheetIndex(0);
$aSheet = $objPHPExcel->getActiveSheet();
echo '<table cellpadding="0" cellspacing="0">';
//получим итератор строки и пройдемся по нему циклом
foreach($aSheet->getRowIterator() as $row){
echo "<tr>\r\n";
//получим итератор ячеек текущей строки
$cellIterator = $row->getCellIterator();
//пройдемся циклом по ячейкам строки
foreach($cellIterator as $cell){
//и выведем значения
echo "<td>".$cell->getCalculatedValue()."</td>";
}
echo "<tr>\r\n";
}
echo '</table>';
?>
</body>
</html>

В результате будет отображен рейтинг в html-таблице.

Рейтинг в формате html

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

Читайте все статьи цикла:

Создание Excel-файлов с помощью PHPExcel
Чтение Excel-файлов с помощью PHPExcel
Часто задаваемые вопросы по PHPExcel

Популярность: 83%

Интересное из других блогов:

2leep.com

И не забывайте комментировать статью.

Метки: Excel, php, PHPExcel, xls

php, Технологии | web-junior 17 Января 2010 года

Добавляйся в группу во вконтакте, чтобы самым первым узнавать все новости сайта

Отзывов: 151 на «Чтение Excel-файлов с помощью PHPExcel»

1 2 3 ... 8 Следующие »

Автор: VICTOR_81, 24 февраля 2010 в 12:23
использует Firefox 3.5.8 на Windows Vista
Спасибо Автору +5 )

Ответить
- Автор: web-junior, 24 февраля 2010 в 12:27
  использует Firefox 3.6 на Windows XP
  Пожалуйста, заходите еще =)
  
  Ответить
Автор: VICTOR_81, 24 февраля 2010 в 13:01
использует Firefox 3.5.8 на Windows Vista
Подскажите где взять php_zip??
И как правильно его подцепить к php
А то файл xml создал и прочитать не могу (
Fatal error: Class ‘ZipArchive’ not found in Z:\home\Buch\www\1\Classes\PHPExcel\Reader\Excel2007.php on line 217

Ответить
- Автор: web-junior, 24 февраля 2010 в 13:13
  использует Firefox 3.6 на Windows XP
  Обычно расширение php_zip поставляется вместе с самим php.
  Для того, чтобы убедиться, есть ли у вас это расширение, нужно зайти в папку ext, которая находится в той папке, куда был установлен php, и поискать там файл php_zip.dll. Если этот файл присутствует, вам нужно его подключить. В файле php.ini нужно найти строку
  ;extension=php_zip.dll
  и удалить в этой строке ; в самом начале. После чего нужно перезапустить веб-сервер.
  
  Ответить
  - Автор: VICTOR_81, 24 февраля 2010 в 14:27
    использует Firefox 3.5.8 на Windows Vista
    Этой библиотеки небыло.
    Нашел отдельно закачал как вы и сказали. не помогло. Мало того закачал на сервер, думал может действительно что то не то у меня.
    
    Там та же ошибка(( Печально
    
    Ответить
    - Автор: web-junior, 24 февраля 2010 в 15:19
      использует Firefox 3.6 на Windows XP
      Если у вас не получается установить это расширение по моим объяснениям, то пожалуйста обратитесь к официальному руководству, которое можно найти здесь или здесь (на английском)
      
      Ответить
Автор: Yurgen, 9 марта 2010 в 18:00
использует Internet Explorer 8.0 на Windows 7
Спасибо!

Ответить

Автор: Александр, 20 марта 2010 в 18:29

использует

Opera 10.50 на

Windows 7

Здравствуйте!
Я переписываю парсер xls файла с Spreadsheet_Excel_Writer на PHPExcel и возник вопросик.
Полазив по просторам сети, я так и не нашел, то как можно получать содержимое конкретных ячеек в цикле ( например А:2, Е:6;А:3, Е:7). Находил что можно получать содержимое отдельных строк с помощью переопределения метода readCell, но дальше дело не пошло.
Спасибо!

Ответить

Автор: web-junior, 21 марта 2010 в 10:55

использует

Firefox 3.5.8 на

Windows XP

Добрый день!
Если вам нужно вывести в цикле все ячейки, то для этого есть итераторы строк и ячеек, которые описаны в этой статье.
Если вам нужно получить несколько произвольных ячеек в цикле, то это может выглядеть так
…
include_once ‘PHPExcel/IOFactory.php’;
//загружаем файл
$objPHPExcel = PHPExcel_IOFactory::load(“rate.xls”);
//устанавливаем активный лист
$objPHPExcel->setActiveSheetIndex(0);
//получаем лист
$aSheet = $objPHPExcel->getActiveSheet();
//проходим в цикле по ячейкам столбца С
for($i=1;$i< =10;$i++){
echo $aSheet->getCell(‘C’.$i)->getValue().”\r\n”;
}
…

Ответить

Автор: Сергей, 14 июля 2010 в 12:01

использует

Firefox 3.6.6 на

Windows XP

а как пройти в цикле например по первой строке?

Ответить

Автор: web-junior, 19 июля 2010 в 13:38

использует

Firefox 3.5.9 на

Windows XP

Проще простого – это пройтись циклом по итератору строк. Приблизительно это будет выглядеть так:

...
//получаем итератор строк
$row = $aSheet->getRowIterator();
for($row->rewind();$row->valid();$row->next()){
//после первой строки выходим   
 if($row->key()>1)break;
 
//получим итератор ячеек текущей строки
$cellIterator = $row->current()->getCellIterator();
//пройдемся циклом по ячейкам строки
foreach($cellIterator as $cell){
//и выведем значения
 
echo $cell->getColumn().'  '.$cell->getCalculatedValue();
}
 
}
...

Ответить

Автор: Сергей, 19 июля 2010 в 13:56
использует Firefox 3.6.6 на Windows XP
Спасибо.
Автор: Gogita, 7 сентября 2011 в 13:20
использует Opera 11.51 на Windows Vista
Да присутствует в том же папке, где файл с вышеуказанным кодам
Автор: web-junior, 7 сентября 2011 в 14:26
использует Firefox 6.0.1 на GNU/Linux x64
А он корректно открывается?
Автор: Gogita, 7 сентября 2011 в 14:46
использует Opera 11.51 на Windows Vista
Да открывается. Я просто замучился и вас замучил.
Автор: web-junior, 7 сентября 2011 в 15:46
использует Firefox 6.0.1 на GNU/Linux x64
В ошибке текст ‘Could not open rate2.xls for reading! File does not exist.’ означает что файл не существует. Стоит наверное отталкиваться от этого в устранении неисправности.
Автор: Gogita, 7 сентября 2011 в 16:09
использует Opera 11.51 на Windows Vista
Я уже незнаю что сказать. Сколько не пробовал выдает адноитоже ошибку.
Я бы дал вам мой скайп и видеозванком показал бы все как есть, но навернаа эта не возможна.
Автор: Максим, 1 декабря 2011 в 10:52
использует Chromium 12.0.742.112 на Ubuntu 10.10
Добрый день. А как вытащить из таблицы определенные столбцы, прописанные идентификатором в первой строке?

Автор: web-junior, 1 декабря 2011 в 14:07

использует

Firefox 8.0 на

GNU/Linux x64

Здравствуйте.
Если вам нужно пройтись по столбцу, то можете использовать этот код:

include_once 'PHPExcel.php';
$objReader = new PHPExcel_Reader_Excel5();
$objReader->setReadDataOnly(false);
$objPHPExcel = $objReader->load("rate.xls");
$objPHPExcel->setActiveSheetIndex(1);
$sheet = $objPHPExcel->getActiveSheet();
//название столбцов
$columns = array('A', 'B');
 
//проходим циклом по столбцам
foreach($columns as $column){
  echo "<strong>Column $column</strong><br/>";
  $row = 1;
  while(true){
    //если дошли до конца, то выходим
    if(!$sheet->cellExists($column.$row)){
      break;
    }
 
    echo $sheet->getCell($column.$row)->getCalculatedValue().'<br/>';
    $row++;
  }
}

Автор: Gogita, 7 сентября 2011 в 12:01
использует Opera 11.51 на Windows Vista
Здравствуйте.
Вот пишу как вы указали выше:

set_include_path(get_include_path() . PATH_SEPARATOR . 'PhpExcel/Classes/'); include_once 'PhpExcel/Classes/PHPExcel/IOFactory.php'; //загружаем файл $objPHPExcel = PHPExcel_IOFactory::load('rate2.xls'); //устанавливаем активный лист $objPHPExcel->setActiveSheetIndex(0); //получаем лист $aSheet = $objPHPExcel->getActiveSheet(); //проходим в цикле по ячейкам столбца С for($i=1;$igetCell('C'.$i)->getValue()."\r\n"; }

Но выдает ошибку:

Fatal error: Uncaught exception ‘Exception’ with message ‘Could not open rate2.xls for reading! File does not exist.’ in Z:\home\phpsaiti.ge\www\table\PhpExcel\Classes\PHPExcel\Reader\Excel5.php:509 Stack trace: #0 Z:\home\phpsaiti.ge\www\table\PhpExcel\Classes\PHPExcel\IOFactory.php(257): PHPExcel_Reader_Excel5->canRead(‘rate2.xls’) #1 Z:\home\phpsaiti.ge\www\table\PhpExcel\Classes\PHPExcel\IOFactory.php(192): PHPExcel_IOFactory::createReaderForFile(‘rate2.xls’) #2 Z:\home\phpsaiti.ge\www\table\3_1_1.php(18): PHPExcel_IOFactory::load(‘rate2.xls’) #3 Z:\home\phpsaiti.ge\www\table\index.php(619): include(‘Z:\home\phpsait…’) #4 {main} thrown in Z:\home\phpsaiti.ge\www\table\PhpExcel\Classes\PHPExcel\Reader\Excel5.php on line 509

В чем может быть ошибка?

Ответить
- Автор: web-junior, 7 сентября 2011 в 12:55
  использует Firefox 6.0.1 на GNU/Linux x64
  Добрый день.
  А файл rate2.xls присутствует?
  В этом месте
  
  //загружаем файл $objPHPExcel = PHPExcel_IOFactory::load(‘rate2.xls’);
  
  rate2.xls замените на имя своего файла.

Автор: Марья, 1 апреля 2010 в 06:25
использует Firefox 3.0.18 на Windows XP
Вопрос, наверное, глупый, но у меня вылезает ошибка:
Fatal error: Class ‘PHPExcel_IOFactory’ not found in /home/www/soroka78/htdocs/mylo/print_blank/print_blank.php on line 195

Подключение библиотеки сделала, как в Вашем примере. В чем может быть дело?

Ответить
- Автор: web-junior, 1 апреля 2010 в 09:32
  использует Firefox 3.5.8 на Windows XP
  Внимательно проверьте существование файла PHPExcel/IOFactory.php в папке Classes. Попробуйте написать полный путь к этому файлу в операторе include_once
  
  Ответить
  - Автор: Марья, 1 апреля 2010 в 16:21
    использует Firefox 3.0.19 на Windows XP
    Спасибо! Даже неловко. Я первым дело именно это и проверила и просмотрела. А после вашей наводки увидела ошибку.
    Только вот теперь вылезла другая ошибка, которая не нравится мне гораздо больше:
    Fatal error: Allowed memory size of 20971520 bytes exhausted (tried to allocate 135885 bytes) in /home/www/soroka78/htdocs/mylo/print_blank/PHPExcel/Classes/PHPExcel/Reader/Excel5/Escher.php on line 341
    Это значит, мощностей сервера не хватает, да?
    
    Ответить
    - Автор: web-junior, 1 апреля 2010 в 18:02
      использует Firefox 3.5.8 на Windows XP
      Да, на виртуальных хостингах зачастую установлено ограничение на использование памяти, из-за чего и происходит подобная ошибка.
      
      Ответить
      - Автор: Марья, 1 апреля 2010 в 20:32
        использует Firefox 3.0.19 на Windows XP
        Ясно. Очень жаль.
        Спасибо вам огромное за полезный материал и отзывчивость.
        А может вы знаете еще какой-нибудь класс функций для работы с Excel? Мне надо бланки автоматически заполнять. Т.е. интересует именно чтение+редактирование, а не просто создание.
      - Автор: web-junior, 2 апреля 2010 в 13:27
        использует Firefox 3.5.8 на Windows XP
        есть еще в PEAR пару пакетов. Еще есть PHP Excel Reader, который судя по всему обладает подобной функциональностью
Автор: Марья, 3 апреля 2010 в 06:13
использует Firefox 3.0.15 на Windows Server 2003
Спасибо! Буду искать.

Ответить
Автор: Людмила, 14 апреля 2010 в 10:56
использует Firefox 3.6.3 на Windows XP
Здравствуйте! У меня возникла проблема с кодировкой с вашими примерами! В первом примере выводятся какие-то кракозябки, вопросики, ромбики… А вот во втором не печатаются вообще русские буквы. Сама таблица строится хорошо, за исключением русского языка! Да и к тому же ругается на
header(‘Content-Type: application/vnd.ms-excel’);
header(‘Content-Disposition: attachment;filename=”print.xls”‘);
header(‘Cache-Control: max-age=0′);

Закомментирую я эти строчки, но зачем их тогда вообще ставить?
Заранее благодарю…

Ответить
- Автор: web-junior, 15 апреля 2010 в 10:37
  использует Firefox 3.5.8 на Windows XP
  Добрый день!
  Если есть проблемы с кодировкой, то это значит, что у вас каким-то образом случилась кодировка отличная от utf-8. С utf-8 кириллица работает нормально.
  
  Функция header будет ругаться только в том случае, если до этого уже были посланы заголовки: был вывод, отправлены заголовки сессии, куки и др.
  Эти строчки нужны, чтобы браузеры понимали, что мы им шлем excel-файл, а не что-то другое.
  
  Ответить
Автор: Евгений, 20 апреля 2010 в 12:09
использует Google Chrome 4.1.249.1045 на Windows XP
Здравствуйте.
Спасибо за статью.
Скажите можно ли с помощью этой библиотеки получить формат ячеек, точнее нужен отступ в ячейке.
Спасибо.

Ответить
- Автор: web-junior, 20 апреля 2010 в 12:44
  использует Firefox 3.5.8 на Windows XP
  Добрый день!
  Среди всех классов стилей в библиотеке есть такой класс как PHPExcel_Style_Alignment, у которого есть метод getIndent(). Этот метод возвращает отступ в ячейке. Добраться до этого метода можно так:
  
  1
  $aSheet->getStyle('B6')->getAlignment()->getIndent();
  
  где $aSheet – это объект активного листа, а B6 – ячейка, у которой нужно получить отступ
  
  Ответить
  - Автор: Евгений, 20 апреля 2010 в 22:47
    использует Google Chrome 4.1.249.1045 на Windows XP
    Спасибо за помощь.
    Искал в классе PHPExcel_Style. Не увидел, думал такой возможности нет.
    
    Ответить

1 2 3 ... 8 Следующие »

RSS-лента комментариев. Адрес для трекбека

Ваш отзыв

« Создание Excel-файлов с помощью PHPExcel

А вы готовы к приходу twitter? Обзор api. »

web-junior.net

Все для начинающего веб-программиста