PHPExcel – чтение данных из таблиц MS Office Excel средствами PHP

Суббота, 30 Октябрь 2010

 

Однажды мне понадобилось считать данные из табицы MS Office Excel, и вставить эти данные в генерируемые HTML страницы. Для этого пришлось писать скрипт, который бы был автономным, и не зависил от предустановленных расширений на сервере. Покопавшись в сети я обнаружил удобнейшую библиотеку для работы с файлами MS Office Excel - PHPExcel.

 

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

 

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

 

 
<?php
  function getXLS($xls){
    include_once 'Classes/PHPExcel/IOFactory.php';
    $objPHPExcel = PHPExcel_IOFactory::load($xls);
    $objPHPExcel->setActiveSheetIndex(0);
    $aSheet = $objPHPExcel->getActiveSheet();
 
    //этот массив будет содержать массивы содержащие в себе значения ячеек каждой строки
    $array = array();
    //получим итератор строки и пройдемся по нему циклом
    foreach($aSheet->getRowIterator() as $row){
      //получим итератор ячеек текущей строки
      $cellIterator = $row->getCellIterator();
      //пройдемся циклом по ячейкам строки
      //этот массив будет содержать значения каждой отдельной строки
      $item = array();
      foreach($cellIterator as $cell){
        //заносим значения ячеек одной строки в отдельный массив
        array_push($item, iconv('utf-8', 'cp1251', $cell->getCalculatedValue()));
      }
      //заносим массив со значениями ячеек отдельной строки в "общий массв строк"
      array_push($array, $item);
    }
    return $array;
  }
 
  $xlsData = getXLS('xls/xls.xls'); //извлеаем данные из XLS
?>
 

 

Для начала вам необходимо скачать библиотеку PHPExcel и разместить в нужной директории, затем вызвать функцию getXLS() передав ей в качестве параметра путь к xls файлу, который необходимо прочесть. Функция подключит библиотеку, сформирует массив и вернет его как результат своей работы.

 

 
<?php
include_once 'Classes/PHPExcel/IOFactory.php';
?>
 

 

В моем случае было необходимо считать файл с такой структурой ячеек:

 

xls

 

Обработав этот файл функция сформирует массив следующего вида:

 

 
<?php
 print_r($xlsData);
 //выведет такой массив:
 Array ( 
 [0] => Array ( [0] => Бесплатная Помощь Юристов Онлайн [1] => 306.jpg ) 
 [1] => Array ( [0] => Право собственности в порядке наследования [1] => 307.jpg ) 
 [2] => Array ( [0] => Консультация юриста по телефону [1] => 308.jpg ) 
 [3] => Array ( [0] => Оставление места ДТП. Ответственность [1] => 309.jpg ) 
 [4] => Array ( [0] => Вопросы Юристам Онлайн [1] => 310.jpg ) 
 [5] => Array ( [0] => Консультация: "Общая совместная собственность" [1] => 311.jpg ) 
 [6] => Array ( [0] => Консультация: "Как подать на развод в суд?" [1] => 312.jpg ) 
 [7] => Array ( [0] => Консультация: "Досудебные претензии" [1] => 313.jpg ) 
 [8] => Array ( [0] => Бесплатная Юридическая Помощь [1] => 314.jpg ) 
 [9] => Array ( [0] => Консультация: "Права собственников жилья" [1] => 315.jpg ) 
 [10] => Array ( [0] => Консультация: "Возврат прав" [1] => 316.jpg ) 
 [11] => Array ( [0] => Помощь Юристов Бесплатно [1] => 317.jpg ) 
 [12] => Array ( [0] => Консультация: "Права собственника" [1] => 318.jpg ) 
 [13] => Array ( [0] => Бесплатная Помощь Юристов [1] => 319.jpg ) 
 [14] => Array ( [0] => Помощь Юристов Онлайн [1] => 320.jpg ) 
 [15] => Array ( [0] => Консультация: "Совместная собственность на квартиру" [1] => 321.jpg ) 
 [16] => Array ( [0] => Консультация: "Наследование квартиры" [1] => 322.jpg ) 
 [17] => Array ( [0] => Юристы по земельным вопросам [1] => 323.jpg )
 ... 
 )
?>
 

 

Такая структура данных очень удобна для обработки и доступа.

 

Download

 

PS. И не забудьте подписатся на ленту новостей RSS

Метки:

Комментарии 

 
anonim Вторник, 15 Февраль 2011

У меня xls-файл в ANSI. Проект в юникоде. Конвертирую
array_push($ite m, iconv('cp1251', 'utf-8', $cell->getCalculatedVa lue()));
но на выходе получаю билеберду... ((
Подскажите, в чем может быть проблема?

 

 
 
anonim Вторник, 15 Февраль 2011

попробовал также
array_push($ite m, mb_convert_enco ding($cell->getCalculatedVa lue(), 'utf-8', 'cp1251'));
результат - тот же

 

 
 
anonim Вторник, 15 Февраль 2011

Что на выходе скиньте строчку с кракозябрами, или еще лучше ваш код на support@clevers cript.ru
А я подбиру кодировку, и еще страница вывода в какой кодировке?

 

 
 
anonim Пятница, 18 Февраль 2011

Как я уже написал, все скрипты в utf-8. Кракозябры не могу скинуть, т.к. уже заюзал другую библиотеку.
Код был полностью из вашего примера - я просто тестировал, за исключением той строки с конвертацией кодировки, что я привел выше.

 

 
 
anonim Пятница, 18 Февраль 2011

А работает у вас на сервере iconv ? Может локаль не настроена? Поделитесь какую библиотеку решили использовать?

 

 
 
anonim Пятница, 18 Февраль 2011

Работает и iconv и mb, локаль настроена.
Использую Spreadsheet_Exc el_Reader

 

 
 
anonim Пятница, 18 Февраль 2011

Спасибо, но все же скинте свой Exel файл с таблицами, а я попробую считать его, да и вашу страничку в utf-8 я через нее сделаю вывод в читаемом виде. Нужно разобратся, так как с момощью этого скрипта было сгенерировано огромное количество веб страниц с использованием данных из таблиц Exel.

 

 
 
anonim Суббота, 19 Февраль 2011

Вот: http://ifolder.ru/21983790
в архиве библиотека, скрипт и тестовая таблица

 

 
 
anonim Четверг, 12 Май 2011

Такая же ерунда, выводятся крякозяблы. Использую кодировку utf 8, а xsl кодировка win 1251. Выводится

Array ( [0] => Array ( [0] => ������ ������ ) [1] => Array ( ) [2] => Array ( [0] => �/� [1] => ���� [2] => �.�.�. [3] => ����� [4] => ��� [5] => �������� [6] => ������ [7] => ������ [8] => ���� ���������� [9] => ����������� ) [3] => Array ( [0] => 1 [1] => 40657 [2] => ������ �.�. [3] => ��������� [4] => 75 [5] => 10 [6] => ��������� � ����� [7] => ��������� [8] => 40657 [9] => �������� �.� ) [4] => Array ( [0] => 2 [1] => 40658 [2] => ������ �.�. [3] => ��������� [4] => 75 [5] => 11 [6] => ���� ����� � ������ [7] => � ������ [8] => [9] => ) [5] => Array ( [0] => [1] => [2] => [3] => [4] => [5] => [6] => [7] => [8] => [9] => ) [6] => Array ( [0] => [1] => [2] => [3] => [4] => [5] => [6] => [7] => [8] => [9] => ) [7] => Array ( [0] => [1] => [2] => [3] => [4] => [5] => [6] => [7] => [8] => [9] => ) )

 

 
 
anonim Пятница, 13 Май 2011

>>>>Вот: http://ifolder.ru/21983790
в архиве библиотека, скрипт и тестовая таблица

В index.php закомментировал перекодировку и все русские слова вывелись

foreach($cellIt erator as $cell){
//заносим значения ячеек одной строки в отдельный массив
//array_push($ite m, iconv('cp1251', 'utf-8', $cell->getCalculatedVa lue()));
array_push($ite m, $cell->getCalculatedVa lue());
}

выводит:

array(2) { [0]=> array(4) { [0]=> string(1) "1" [1]=> float(1) [2]=> string(40) "Привет, РњРёСЂ!" [3]=> string(21) "Привет, Мир!" } [1]=> array(4) { [0]=> string(1) "2" [1]=> float(2) [2]=> string(57) "Вторая строчка" [3]=> string(27) "Вторая строчка" } }

 

 
 
anonim Пятница, 13 Май 2011

>>>Владимир

Это в примере который вы скачали? в примере все корректно выводится, попробуйте также закомментироват ь строку с перекодировкой как выше.

foreach($cellIt erator as $cell){
//заносим значения ячеек одной строки в отдельный массив
//array_push($ite m, iconv('cp1251', 'utf-8', $cell->getCalculatedVa lue()));
array_push($ite m, $cell->getCalculatedVa lue());
}

 

 
 
anonim Среда, 18 Май 2011

Один нюанс: Если сделать как в примере, то скрипт пропустит пустые ячейки. Для того, чтобы этого не случилось нужно добавить:
Код:$cellIterator->setIterateOnlyExistingCells(false);

 

 
 
anonim Среда, 18 Май 2011

И еще одна засада. Метод load прогружает полностью файл. А если он состоит из нескольких страниц, и в каждой много данных - то во-первых кончается память, во-вторых по времени это выполняется долго. (для 2Мб файла мало 300Мб оперативки и 30 секунд на чтение). Спасает постраничное чтение данных, а именно:

Код:$inputFileType = PHPExcel_IOFactory::identify($inputFileName); // Определяем тип$objReader = PHPExcel_IOFactory::createReader($inputFileType); // Создаем ридер$objReader->setReadDataOnly(true); // Очень сильно влияет и на память и на время, это если нужны только данные... Плюс нужно смотреть ограничения...$worksheetNames = $objReader->listWorksheetNames($inputFileName); // Читаем имена страниц// Постранично читаем данные foreach ($worksheetNames as $wsName) { $objReader->setLoadSheetsOnly($wsName); $oExcel = $objReader->load($inputFileName); $oExcel->setActiveSheetIndexByName($wsName); $aSheet = $oExcel->getActiveSheet(); foreach ($aSheet->getRowIterator() as $rowId => $row) { $cellIterator = $row->getCellIterator(); $cellIterator->setIterateOnlyExistingCells(false); foreach($cellIterator as $cellId=>$cell) { // здесь код... } }}

В итоге используется всего порядка 40Мб памяти и укладывается в 30 секунд.

 

 
 
anonim Четверг, 19 Май 2011

>>>vonhamster

Спасибо большое за ваш совет, уверен он пригодится читателям!

 

 
 
anonim Пятница, 20 Май 2011

Крутой скрипт!
А вот в каком файле нужно делать правки по постраничному чтению текста? =)
Спасибо

 

 
 
anonim Пятница, 20 Май 2011

В index.php

 

 
 
anonim Понедельник, 27 Июнь 2011

Здраствуйте.
Если делать экспорт из 1с в xls, то не читаются русские символы. iconv и mb_convert_enco ding не работают.
Но если файл открыть в excel и просто сохранить все нормально читается.
Видимо 1с сохраняет в некорректной кодировке, подскажите?
Вот ссылка на файл xls http://farmasko.cravter.com.ua/2.xls
Вот ссылка на страницу вывода http://farmasko.cravter.com.ua/2.php

 

 
 
anonim Вторник, 15 Ноябрь 2011

Спасибо!

Так лучше (древовидная система отображения массива):
echo '';
print_r($xlsDat a);
echo '';

 

 
 
anonim Среда, 28 Декабрь 2011

было бы супер если бы тут была инструкция для чайников

 

 
 
anonim Вторник, 31 Январь 2012

Как можно получить данные из $xlsData. К примеру мне нужно значение именно: "Бесплатная Помощь Юристов Онлайн", которое хранится в (0,1).
Заранее спасибо.

 

 
 
anonim Четверг, 02 Февраль 2012

Nak0, не понял в чем сложность.
Array (
[0] => Array ( [0] => Бесплатная Помощь Юристов Онлайн [1] => 306.jpg )

echo $xlsData[0][0]; //выведет Бесплатная Помощь Юристов Онлайн
echo $xlsData[0][1]; //выведет 306.jpg

 

 
 
anonim Вторник, 20 Март 2012

Цитирую "И еще одна засада. Метод load прогружает полностью файл..."

Попробовал вставить код, получил ошибку "Call to undefined method PHPExcel_Reader _Excel5::listWo rksheetNames()"
Ругается на строку: "$worksheetNames = $objReader->listWorksheetNa mes($inputFileN ame); "

Может что-то не так делаю, а где не пойму...
У кого получилось?

 

 
 
anonim Пятница, 11 Май 2012

Добрый день! Помогите найти ошибку. Суть такая: пользователь вводит с формы числовые данные и они записываются в уже существующий файл. Ошибку выдает в этой строке:
$aSheet->setCellValue('H9', $_POST['seawater']);

Полный код:
set_include_pat h(get_include_p ath() . PATH_SEPARATOR . './PHPExcel/Classes/');

include('PHPExcel.php');
include_once 'PHPExcel/IOFactory.php';
$objPHPExcel = PHPExcel_IOFact ory::load('NABStatics1.xls');
$objPHPExcel->setActiveSheetI ndex(0);
$aSheet = $objPHPExcel->getActiveSheet( );

$aSheet->setCellValue('H9', $_POST['seawater']);
$aSheet->setCellValue('H10', $_POST['cargomass']);
$aSheet->setCellValue('H15', $_POST['forepeak']);
$aSheet->setCellValue('H16', $_POST['wbt1prb']);

include('PHPExcel/Writer/Excel2007.php');
$objWriter = PHPExcel_IOFact ory::createWrit er($objPHPExcel , 'Excel2007');
$objWriter->setOffice2003Compatibility(t rue);
$objWriter->save('NABStatics1.xls');

Заранее спасибо)

 

 
 
anonim Среда, 09 Январь 2013

[2013-01-09 14:09:30] Warning: stream_wrapper_ unregister() [function.stream-wrapper-unregister]: Unable to unregister protocol zip:// in /home/myday/public_html/includes/libraries/PHPExcel/Shared/ZipStreamWrappe r.php on line 69

Fatal error: Class 'ZipArchive' not found in /home/myday/public_html/includes/libraries/PHPExcel/Shared/File.php on line 53

 

 
 
anonim Понедельник, 11 Февраль 2013

Спасибо! Заработало!

 

 
 
anonim Воскресенье, 15 Сентябрь 2013

Подскажите пожалуйста, а как можно сформерованный массив обратно в excel записать тe берем данные из одного excel, заносим в массив, вносим небольшие изменения(места ми содерживое столбцов поменять или еще один столбец с из другого массива добавить) и как их обратно в excel занести?

 

 
 
anonim Воскресенье, 15 Сентябрь 2013

Решение проблемы с кодировкой: создать файл .htaccess в директории с исполняемым файлом и прописать AddDefaultChars et utf-8.

 

 
 
anonim Вторник, 29 Октябрь 2013

Спасибо. Работает хорошо :)

 

 
 
anonim Вторник, 10 Декабрь 2013

Скиньте кто-то готовую от форматированую структуру, а то здесь выдает все в один рядок(

 

 
 
anonim Пятница, 04 Апрель 2014

Подскажите начинающему! В примере сформированный массив выводится прям в браузере. Как мне в дальнейшем использовать эту информацию из сформированного массива? Например мне нужно извлечь только
[5] => Array ( [0] => Консультация: "Общая совместная собственность" [1] => 311.jpg ).
Как это реализовать? То, как написано в комментах выше, у меня выдает ошибку. Я вставил
Array (
[0] => Array ( [0] => Бесплатная Помощь Юристов Онлайн [1] => 306.jpg )

echo $xlsData[0][0]; //выведет Бесплатная Помощь Юристов Онлайн
в уже написанный код и вышла ошибка.
Скиньте пожалуйста код, а то могу не туда дописать! Спасибо!

 

 
 
anonim Пятница, 04 Апрель 2014

А как сделать так, чтобы картинки, которые в примере под номерами 306.jpg 307jpg ... тоже отображались? Или может какой-то другой способ, главное, сделать выборку картинок. Спасибо!

 

 
 
anonim Среда, 07 Май 2014

а что делать если строк 10к+?

 

 
 
anonim Среда, 18 Июнь 2014

если 10к+ строк, то можно попробовать такое решение http://noxls.net/

 

 
 
anonim Воскресенье, 07 Декабрь 2014

Тут не помещается. Вот ссылка на мой вопрос: http://www.cleverscript.ru/?away=http://www.cyberforum.ru/php-beginners/thread1321902.html#post6951558 . Помогите пожалуйста - в классах я не силен

 

 
 
anonim Пятница, 15 Май 2015

пасиб те аффтор.молодца! твою "штуковину" прикрутил к своему движку на yii за секунды,все пашет на ура и как надо,все оч просто..шеф рад и это главное))

 

 
 
prudcky Пятница, 29 Апрель 2016

в скрипте убрать функцию iconv
foreach($cellIt erator as $cell){
//заносим значения ячеек одной строки в отдельный массив
array_push($ite m, $cell->getCalculatedVa lue());
}

 

 
 
Владимир Четверг, 19 Январь 2017

Проблема с "кракозаброй/знаками вопроса" у меня решилась так:
В строке: array_push($ite m, iconv('utf-8', 'cp1251', $cell->getCalculatedVa lue()));
cp1251 заменил на Windows-1251, в итоге:
array_push($ite m, iconv('utf-8', 'windows-1251', $cell->getCalculatedVa lue()));

Может кому поможет, спасибо за внимание)

 

 
 
IvanI Пятница, 18 Август 2017

"Решение проблемы с кодировкой: создать файл .htaccess в директории с исполняемым файлом и прописать AddDefaultChars et utf-8." - Помогло, спасибо !!

 

 
 
IvanI Пятница, 18 Август 2017

$cellIterator->setIterateOnlyE xistingCells(fa lse);

Существенное замечание !

 

 

Добавить комментарий