Главная > 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 2 3 4 5 ... 9

  1. Автор: LazyLizard, 7 июля 2010 в 19:21

    использует Firefox 3.0.14 Firefox 3.0.14 на Windows Vista Windows Vista

    А вот такой вопрос.

    Есть файл, в котором активный лист неизвестен. И надо установить активным лист с названием, содержащим слово ‘connect’. Это как сделать?

    Спасибо.

    Ответить

    • Автор: web-junior, 8 июля 2010 в 12:36

      использует Firefox 3.5.9 Firefox 3.5.9 на Windows XP Windows XP

      В библиотеке есть класс PHPExcel_WorksheetIterator, который является итератором листов в excel-файле. Можно пройтись циклом по итератору, проверяя имя каждого листа. Найдя нужный, запомнить его индекс и установить активным лист обычным способом.
      Реализуется это приблизительно так

      set_include_path(get_include_path() .
PATH_SEPARATOR . 'PhpExcel/Classes/');
include_once 'PHPExcel/IOFactory.php';
$objPHPExcel = PHPExcel_IOFactory::load("file.xls");
 
//получаем итератор листов
$wIterator = new PHPExcel_WorksheetIterator($objPHPExcel);
$sheetIndex = -1;
 
//пройдемся циклом по итератору
for($wIterator->rewind();$wIterator->valid();$wIterator->next()){
    //имя текущего листа
    $name = $wIterator->current()->getTitle();
 
    //если нашли нужное, то запоминаем индекс и выходим
    if(preg_match('/connect/',$name)){
        $sheetIndex = $wIterator->key();
        break;
    }
}
 
//если не нашли
if($sheetIndex==-1){
    die('Нет листа с именем connect');
}
    //устанавливаем индекс
    $objPHPExcel->setActiveSheetIndex($sheetIndex);
    $aSheet = $objPHPExcel->getActiveSheet();
      Ответить

      • Автор: LazyLizard, 8 июля 2010 в 12:44

        использует Firefox 3.0.14 Firefox 3.0.14 на Windows Vista Windows Vista

        спасибо! вечером попробую, но идея ясна.

        Ответить

  2. Автор: Ray, 1 октября 2010 в 12:19

    использует Opera 10.51 Opera 10.51 на Windows 7 Windows 7

    Добрый день! Спасибо за статью, помогло :)
    при чтении .xslx вылазит след. бяка:
    Warning: simplexml_load_string() [function.simplexml-load-string]: namespace error : Namespace prefix w10 for type on wrap is not defined in Z:\home\exel\www\Classes\PHPExcel\Reader\Excel2007.php on line 1061

    что ен так?

    Ответить

    • Автор: web-junior, 1 октября 2010 в 15:45

      использует Firefox 3.5.12 Firefox 3.5.12 на Windows XP Windows XP

      Здравствуйте!
      Скажите пожалуйста, какой версией php/PHPExcel вы пользуетесь, поскольку у меня на версиях 5.3(5.2)/1.7.4 работает без проблем.

      Ответить

      • Автор: Ray, 1 октября 2010 в 18:21

        использует Opera 10.51 Opera 10.51 на Windows 7 Windows 7

        PHP 5.2.14
        PHPExel 1.7.4
        как думаете, с чем может быть связанна такая ошибка?
        причем код работает – результат выводит, но при этом еще кучу варнингов )
        на .xls файлах все нормально работает.

        Ответить

  3. Автор: Garry, 29 октября 2010 в 19:04

    использует Google Chrome 5.0.375.29 Google Chrome 5.0.375.29 на GNU/Linux GNU/Linux

    О! То что надо! Небольшое доходчивое объяснение с примерами и ссылками! Спасибо!

    Ответить

    • Автор: web-junior, 29 октября 2010 в 19:57

      использует Firefox 3.6.10 Firefox 3.6.10 на Windows 7 Windows 7

      Пожалуйста! Заходите еще!

      Ответить

  4. Автор: Garry, 1 ноября 2010 в 15:20

    использует Google Chrome 5.0.375.29 Google Chrome 5.0.375.29 на GNU/Linux GNU/Linux

    Вопрос: как узнать, какой ширины/высоты ячейка? Т.е. ячейка может быть результатом объединения с несколькими ячейками и ожет занимать, допустим, 3-столбца или 5 строк.
    А то возникает следующая ситуация: Заголовок – ячейка, объединение 3-х столбцов, а при загрузке файла все равно расценивается как ячейка шириной в один столбец, а потом добавляется 2 пустых ячейки.

    Ответить

  5. Автор: Garry, 1 ноября 2010 в 16:39

    использует Google Chrome 5.0.375.29 Google Chrome 5.0.375.29 на GNU/Linux GNU/Linux

    И в догонку: что-то как-то непонятно работают getStyle и т.д. Ничего не возвращает и возникает ошибка.
    Что бы было проще:

    $objPHPExcel->setActiveSheetIndex($sheetIndex);
    $aSheet = $objPHPExcel->getActiveSheet();
    $rowIterator = $aSheet->getRowIterator();
    foreach($rowIterator as $row) {
    $cellIterator = $row->getCellIterator();
    foreach($cellIterator as $cell) {
    // проверка стиля (жирный шрифт или нет)
    }
    }

    Ответить

  6. Автор: memo, 23 ноября 2010 в 11:52

    использует Google Chrome 7.0.517.44 Google Chrome 7.0.517.44 на Windows 7 Windows 7

    Автору спс за развитие русско-язычного блога, хоть с кем то можно поделится своими мучениями:)

    Необходимо получить уровень группировки столбца, используя:

    $objPHPExcel->getActiveSheet()->getColumnDimension(‘D’)->getOutlineLevel();

    получаю 0.

    Ответить

    • Автор: web-junior, 24 ноября 2010 в 19:51

      использует Firefox 3.6.12 Firefox 3.6.12 на Windows 7 Windows 7

      Пожалуйста. Заходите, делитесь своими мучениями, будем совместно пытаться их решить =)

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

      1
2
3
4
5

      set_include_path(get_include_path() . PATH_SEPARATOR . 'PhpExcel/Classes/');
include_once 'PHPExcel/IOFactory.php';
$objPHPExcel = PHPExcel_IOFactory::load("template.xls");
$objPHPExcel->setActiveSheetIndex(0);
var_dump($objPHPExcel->getActiveSheet()->getColumnDimension('M')->getOutlineLevel());
      Ответить

  7. Автор: memo, 28 ноября 2010 в 19:40

    использует Google Chrome 7.0.517.44 Google Chrome 7.0.517.44 на Windows 7 Windows 7

    Спасибо, помогло:)

    Я подгружал документ не правильно

    В место $objPHPExcel = PHPExcel_IOFactory::load(“Tests/06largescale.xlsx”);

    $objPHPExcel = PHPExcel_IOFactory->load(“Tests/06largescale.xlsx”);

    Ответить

  8. Автор: MaRos, 29 ноября 2010 в 18:28

    использует Internet Explorer 7.0 Internet Explorer 7.0 на Windows XP Windows XP

    Как читать ячейку в которой записана дата, и получить дату, у меня на выходе число ?

    Ответить

    • Автор: web-junior, 29 ноября 2010 в 22:08

      использует Firefox 3.6.12 Firefox 3.6.12 на Windows 7 Windows 7

      Здравствуйте.
      Число – это внутреннее представление даты в Excel. Отформатировать и вывести дату строкой можно с помощью метода PHPExcel_Style_NumberFormat::toFormattedString, например, так:

      1
2
3
4
5
6
7
8
9
10
11
12

      include_once 'PHPExcel/IOFactory.php';
$objPHPExcel = PHPExcel_IOFactory::load("template.xls");
$objPHPExcel->setActiveSheetIndex(0);
$objWorksheet = $objPHPExcel->getActiveSheet();
$cell = $objWorksheet->getCell('K2');
 
$cellData = PHPExcel_Style_NumberFormat::toFormattedString(
    $cell->getCalculatedValue(),
    PHPExcel_Style_NumberFormat::FORMAT_DATE_DATETIME
);
 
var_dump($cellData);

      т.е. в метод toFormattedString нужно передать значение и желаемый формат. Класс PHPExcel_Style_NumberFormat имеет достаточно большое количество констант для различных форматов. И дат в том числе. Посмотрите подробнее в документации.

      В свое время, на phpexcel.codeplex.com разгорались дискуссии между разработчиками по этому поводу. Предлагалось для ячейки (класс PHPExcel_Cell) кроме методов getValue() и getCalculatedValue() ввести еще метод getFormattedValue(), который как раз и занимался бы форматирование дат, процентов, валют и т.д. Но, судя по всему, разработчики не достигли соглашения в этом вопросе и не стали реализовывать эту функциональность.

      Ответить
1 2 3 4 5 ... 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="">

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