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

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

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

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

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

Шаблон

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

f116

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

Как видите, в таблице уже есть данные «От кого», «Адрес отправителя» и «Предъявленный документ». Нам нужно вписать следующие данные: «Сумма» (цифрами и прописью), «Кому», «Адрес получателя».
Для чтения данных в библиотеке 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-файлов – это случай, когда нужно использовать данные таблиц в приложении.
Для примера, возьмем таблицу с чудо-рейтингом, который был описан в прошлой статье.

example-31

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

Поставим самую простую задачу: вывести все данные, которые находятся в таблице.
В этом случае нам понадобятся два класса из библиотеки 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-таблице.

rate

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

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

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

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


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

2leep.com

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

Метки: , , ,

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

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

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

1 ... 6 7 8 9

  1. Автор: Юрий, 28 января 2012 в 21:25

    использует Google Chrome 16.0.912.77 Google Chrome 16.0.912.77 на Windows Server 2003 Windows Server 2003

    Здравствуйте. У меня такая проблема.:
    Есть файл blank.xls, до 22 строки он заполнен, т.е. шапка, есть кнопки скрыть и показать (относиться к пустым ячейкам) При чтении файла и записи в него некоторых строк, после шапки, после того как файл сохранён, я его открываю и проблема: Объединённые ячейки стают раздельными, слетают, кнопки и макросыю.
    Подскажите пожалуйста как решить?

    Код:
    load($inputFileName);
    $objPHPExcel->setActiveSheetIndex(0);
    $aSheet = $objPHPExcel->getActiveSheet();
    //$strForReplace = mb_convert_case(‘привет мир’, MB_CASE_TITLE, “windows-1251″);
    $strForReplace2 = mb_convert_encoding(‘Текст в cp1251′,’utf-8′,’windows-1251′);
    $aSheet->setCellValue(‘E23′,$strForReplace2);
    //создаем объект класса-писателя
    include(“PHPExcel/Writer/Excel5.php”);
    $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
    //сохраняем в тот-же файл
    $objWriter->save(‘blaniris2011-3.xlsx’);
    ?>

    Ответить

  2. Автор: Татьяна, 8 февраля 2012 в 21:08

    использует Google Chrome 16.0.912.77 Google Chrome 16.0.912.77 на Windows XP Windows XP

    Необходимо вывести русский текст по буквам. Если $fio – русский текст, то
    $aSheet->setCellValue(‘C24′, $fio); // выводится весь текст по русски – ОК
    $aSheet->setCellValue(‘C24′, $fio[1]); // не выводится ничего
    $aSheet->setCellValue(‘C24′, substr($fio,1,1)); // не выводится ничего
    причем если $fio англ.текст, то выводится в обоих случаях правильно.

    Ответить

    • Автор: web-junior, 8 февраля 2012 в 21:57

      использует Firefox 10.0 Firefox 10.0 на GNU/Linux x64 GNU/Linux x64

      Если $fio – текст в кодировке утф-8, то попробуйте использовать mb_substr вместо substr

      Ответить

  3. Автор: Татьяна, 9 февраля 2012 в 07:08

    использует Google Chrome 16.0.912.77 Google Chrome 16.0.912.77 на Windows XP Windows XP

    Спасибо большое, получилось.

    Ответить

  4. Автор: Михаил, 16 февраля 2012 в 12:58

    использует Opera 11.61 Opera 11.61 на Windows XP Windows XP

    Здравствуйте! Очень полезная статья. Только не понятно как в цикле заполнить xls. Особенно интересна адресация ячеек цифрами, т.к. количество столбцов заранее не заданно!

    Ответить

    • Автор: web-junior, 17 февраля 2012 в 15:03

      использует Firefox 10.0 Firefox 10.0 на GNU/Linux x64 GNU/Linux x64

      Добрый день.
      Ячейки можно указывать цифрами так:
      $aSheet->getCellByColumnAndRow( [string $pColumn = 0], [string $pRow = 1])
      для получения ячейки или
      $aSheet->setCellValueByColumnAndRow( [string $pColumn = 0], [string $pRow = 1], [mixed $pValue = null], [bool $returnCell = false])
      для установки значения

      Ответить

  5. Автор: Михаил, 20 февраля 2012 в 06:12

    использует Opera 11.61 Opera 11.61 на Windows XP Windows XP

    Большое спасибо!

    Ответить

  6. Автор: Михаил, 22 февраля 2012 в 04:23

    использует Opera 11.61 Opera 11.61 на Windows XP Windows XP

    А есть возможность установить значение ячеек массивом?

    Ответить

    • Автор: web-junior, 23 февраля 2012 в 15:27

      использует Firefox 10.0 Firefox 10.0 на GNU/Linux x64 GNU/Linux x64

      Есть. Используйте вот этот метод
      $aSheet->fromArray( [array $source = null], [mixed $nullValue = null], [string $startCell = 'A1'], [boolean $strictNullComparison = false])

      Ответить

  7. Автор: Михаил, 24 февраля 2012 в 07:21

    использует Opera 11.61 Opera 11.61 на Windows XP Windows XP

    И ещё раз спасибо! Интесно ещё очень назначение параметров и адресация ячеек числами для функции fromArray.

    Ответить

    • Автор: web-junior, 24 февраля 2012 в 11:02

      использует Firefox 10.0 Firefox 10.0 на GNU/Linux x64 GNU/Linux x64

      Адресация используется очень простая. Значения ячеек передаются многомерным массивом вот так

      1
2
3
4
5
6

       $data = array(
	      array('CellA1', 'CellB1', 'CellC1'),
	      array('CellA2', 'CellB2', 'CellC2'),
	      array('CellA3', 'CellB3', 'CellC3'),
            );
            $aSheet->fromArray($data);

      Назначение параметров следующее:
      array $source – массив со значениями ячеек
      mixed $nullValue – значения в исходном массиве, которые устанавливаются как пустые ячейки
      string $startCell – ячейка, с которой начинаем заполнение. По умолчанию это А1
      boolean $strictNullComparison – включить проверку нулевых значений в исходном массиве

      Ответить

  8. Автор: Михаил, 24 февраля 2012 в 11:46

    использует Opera 11.61 Opera 11.61 на Windows XP Windows XP

    как задать startCell числами….?

    Ответить

    • Автор: web-junior, 24 февраля 2012 в 12:08

      использует Firefox 10.0 Firefox 10.0 на GNU/Linux x64 GNU/Linux x64

      Скорее всего только буквенно-цифровое

      Ответить
1 ... 6 7 8 9

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

Ваш отзыв

Вы можете использовать следующие теги: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre lang="" line="" escaped="" highlight="">

Нажимая на кнопку "Добавить" вы принимаете правила комментирования