Php прочитать excel файл
Узнайте, как использовать поддержку XML в PHP для чтения данных, выгруженных из Microsoft® Excel® 2003 в XML-формате, и как экспортировать данные в XML-формате из PHP-приложений для использования в электронных таблицах Excel.
Пакет Microsoft Office 2003 для операционной системы Microsoft Windows® открыл для разработчиков целый ряд новых возможностей, с которыми они не сталкивались прежде. Конечно, в вашем распоряжении имелся набор новых функций, однако наиболее значимым преимуществом стало добавление поддержки файловых XML-форматов. В Office 2003 вы можете сохранить электронную таблицу Microsoft Excel в XML-формате и использовать ХМL-файл так же, как и его двоичный эквивалент. То же самое касается и Microsoft Word.
Почему же XML-формат имеет такое большое значение? Потому, что на протяжении многих лет истинная мощь Excel или Word была заблокирована использованием файлов в двоичных форматах, а доступ к этим файлам извне можно было получить только с помощью программ-конвертеров. Теперь же файлы Excel или Word можно читать и записывать с помощью таких инструментов, как XSLT (Extensible Stylesheet Language Transformation) или DOM (XML Document Object Model), встроенных в язык программирования PHP.
В этой статье я покажу, как написать Web-приложение на PHP, которое использует эти форматы для чтения данных из электронной таблицы Excel и записи их в БД, а также для экспорта содержимого таблицы БД в электронную таблицу Excel.
Создание базы данных
В этой статье приведен пример простого Web-приложения, которое наглядно покажет вам работу XML-механизма Excel. Это приложение представляет собой таблицу, содержащую имена людей и их адреса электронной почты.
Синтаксис для создания схемы базы данных в MySQL выглядит следующим образом.
Листинг 1. SQL-код для создания схемы базы данных
Этот файл является простой базой данных, состоящей из одной таблицы с именем names, которая имеет пять полей: автоинкрементный идентификатор, имя, отчество, фамилия и адрес электронной почты.
Создайте базу данных с помощью инструмента командной строки Mysqladmin: mysqladmin --user=root create names. Затем загрузите в нее данные о таблице из файла схемы: mysql --user=root names Save As и в диалоговом окне Save As выберите формат XML Spreadsheet из раскрывающегося списка Save as type (рисунок 2).
Рисунок 2. Сохранение файла в виде электронной таблицы XML
После создания XML-файла можно приступать к написанию PHP-приложения.
Импорт данных
Импорт данных начинается с создания несложной страницы, на которой выбирается входной XML-файл Excel (рисунок 3).
Рисунок 3. Выбор входного XML-файла Excel
Код этой страницы показан в листинге 2.
Листинг 2. Код страницы загрузки
Я присвоил файлу расширение .php, хотя на самом деле это вовсе не PHP. Это обычный HTML-файл, позволяющий пользователю указать файл и передать его на страницу import.php, на которой начинается все самое интересное.
Чтение XML-данных Excel
Для простоты я разделил написание страницы import.php на два этапа. На первом этапе выполняется разбор XML-данных и их вывод в форме таблицы. На втором этапе добавляется логика, в которой реализовано добавление записей в базу данных.
В листинге 3 показан пример XML-файла Excel 2003.
Листинг 3. Пример XML-файла Excel
Я вырезал часть строк из середины листинга, иначе здесь было бы напечатано все содержимое файла Excel. Листинг 3 является сравнительно чистым кодом XML. Обратите внимание на то, что в разделе заголовков в начале листинга содержится информация о документе и его авторе, задаются определенные правила отображения, стили списков и так далее. Затем в виде набора рабочих листов внутри главного объекта Workbook представлены непосредственно сами данные.
Первый объект Worksheet содержит реальные данные. Данные в этом объекте располагаются внутри тега Table, который в свою очередь содержит набор тегов Row и Cell. В каждом теге Cell располагается связанный с ним тег Data, содержащий данные ячейки. В нашем примере данные всегда представлены в виде строк (тип String).
По умолчанию при создании нового документа Excel создаются три рабочих листа с именами Sheet1, Sheet2 и Sheet3. Я не стал удалять второй и третий лист, поэтому в конце листинга вы можете видеть эти пустые листы.
В листинге 4 показана первая версия сценария import.php.
Листинг 4. Первая версия сценария импорта
Сценарий начинается с чтения временного файла, загруженного в объект DOMDocument. Затем выполняется поиск всех тегов Row. Первая строка пропускается в соответствии с логикой обработки переменной $first_row. Далее для каждой строки выполняется циклический анализ каждого содержащегося в ней тега Cell.
Следующая хитрость заключается в определении столбца, в котором вы находитесь. Как видно из листинга 3, в теге Cell не указан номер строки или столбца – за этим должен следить сценарий. На самом деле все еще немного сложнее. В действительности в теге Cell содержится атрибут ss:Index, указывающий, в каком столбце находится ячейка, если в текущей строке присутствуют пустые столбцы. Это именно то, что ищет код функции getAttribute('index').
После определения индекса не остается ничего сложного. Значение ячейки помещается в локальный элемент, связанный с этим полем. Затем в конце строки вызывается функция add_person для добавления данных о человеке в результирующий набор
В самом конце с помощью обычных функций PHP найденные данные выводятся в виде HTML-таблицы (рисунок 4).
Рисунок 4. Вывод данных в виде HTML-таблицы
Следующим шагом нужно загрузить информацию в базу данных.
Добавление информации в базу данных
После добавления полученного содержимого строки в структуру PHP необходимо занести его в базу данных. Для этого я добавил код, использующий модуль Pear DB (листинг 5).
Листинг 5. Вторая версия сценария импорта
На рисунке 5 показан вывод данных в браузере Firefox.
Рисунок 5. База данных
Результат выглядит не очень красиво, но это не важно. Важно то, что с помощью операторов prepare и execute можно добавить информацию в базу данных. Чтобы показать это, я создал еще одну страницу под названием list.php, отображающую информацию, полученную из базы данных (листинг 6).
Листинг 6. List.php
Эта простая страница начинается с применения SQL-функции select к таблице names. После этого создается таблица, в которую с помощью метода fetchInto добавляются все строки.
На рисунке 6 показаны данные, отображаемые этой страницей.
Рисунок 6. Данные на странице list.php
Несмотря на не очень красивый вид страницы, общая идея занесения информации в базу данных должна быть понятна. Это, в свою очередь, послужит основой сценария, генерирующего XML-файл для экспорта в Excel.
Создание XML-файла для экспорта в Excel
Заключительным шагом является создание XML-файла Excel. Я начал с того, что скопировал XML-содержимое Excel в PHP-сценарий (листинг 7), поскольку это простейший способ получить XML-файл Excel, который будет корректно проанализирован (поскольку Excel очень требователен к своему XML-формату).
Сценарий начинается с того, что для результирующего вывода устанавливается формат XML. Это важно, поскольку в противном случае браузеры будут воспринимать этот код просто как некорректный HTML.
Я изменил часть кода, содержащую SQL-запрос, таким образом, чтобы результаты запроса сохранялись в массив. В данном случае необходимо поместить в атрибут ss:ExpandedRowCount количество строк плюс одну строку (отвечающую за заголовки). Если бы это была обычная страница отчета, дополнительная строка заголовков была бы не нужна.
На рисунке 7 показан результат открытия страницы в браузере.
Рисунок 7. Полученное XML-содержимое в браузере Firefox
Не очень впечатляет. Но посмотрите, что произойдет, когда я открою эту же ссылку в Internet Explorer (рисунок 8).
Рисунок 8. Полученное XML-содержимое в браузере Internet Explorer
Почувствуйте разницу. Теперь это полноценная электронная таблица с форматированием, открытая в браузере (конечно, в Firefox вы можете щелкнуть правой кнопкой мыши на ссылке, сохранить XML-код в файле и, таким образом, открыть его).
Но в PHPExcel нет универсальной функции получения ячейки любым из этих способов, есть только отдельные функции. Что ж, исправляем это упущение:
Сразу оговорюсь, что в приводимых мной примерах кода есть ссылки на $this, т.к. это методы моего класса-обертки над PHPExcel. В этом кусочке реализуются все три способа получения объекта ячейки.
2. Объединение ячеек
При чтении объединенных ячеек, PHPExcel возращает пустое значение для всех, кроме первой.
Т.е. для рисунка ниже значения B3 и C3 будут пустыми строками:
Мне всегда было неудобно такое поведение.
Гораздо удобнее (и логичнее!) для любой объединенной ячейки возвращать общее для них значение «mergedvalue».
Для этого при запросе значения нужно пройтись по всем объединенным диапазонам листа, и если заданная ячейка попадает в диапазон, то вернуть первую:
3. Даты
Как известно, Excel хранит даты как число дней с 1 января 1900 года. Поэтому при чтении ячейки B2 на скриншоте выше мы получим бесполезное 41044. Но есть и хорошая новость — в PHPExcel присутствует удобная функция PHPExcel_Shared_Date::ExcelToPHP(), которая превращает дату в формат php.
Осталось только применить эту функцию в нужный момент:
4. Формулы
В большинстве случаев стандартная функция $cell->getValue() корректно обрабатывает формулы и возвращает рассчитанное значение. Но бывают ситуации, когда формула ссылается на несуществующий лист или другой файл, который локально хранится у того, кто отправил вам excel-документ. Тогда getValue() возвратит ошибку, хотя визуально в экселе вы можете увидеть верное значение, если не было пересчета листа. Дело в том, что Excel сохраняет oldCalculatedValue, которое и используется, если не пересчитывать лист. На рисунке выше я показал это в ячейке B4 — она отображает старое значение, хотя ссылка в ней нерабочая.
PHPExcel, к счастью, тоже умеет хранить старое значение формулы. Это удобно использовать, когда getValue() не смогла отработать и вернула не значение, а саму формулу (первый символ " php">$val = $cell->getValue(); if((substr($val,0,1) === '=' ) && (strlen($val) > 1))< $val = $cell->getOldCalculatedValue(); >
Результат
В итоге мы получили функцию, которая позволяет универсально считывать значения ячеек:
Ложка дегтя
Важно отметить, что использование пунктов 2, 3 и 4 работает только в режиме ReadDataOnly = false. Это режим PHPExcel по умолчанию, когда он считывает всю мета-информацию о книге. Подходит для стандартных небольших документов, например счета, накладные и.т.д.
Включение ReadDataOnly = true может потребоваться для громоздких файлов, когда нужны только значения ячеек. По моей практике, такие файлы содержат отформатированные таблицы и там такой функционал не нужен.
PHPExcel — библиотека для создания и чтения данных из файлов формата OpenXML (который используется в MS Excel 2007). С ее помощью можно считывать из файлов, записывать в файлы, форматировать содержимое, работать с формулами и т.д. Для работы PHPExcel требуется версия PHP 5.2 или выше, с установленными библиотеками Zip, XML и GD2.
Установка PHPExcel
Первым делом библиотеку необходимо скачать. Для этого переходим на официальный сайт библиотеки и скачиваем архив PHPExcel-1.7.8.zip. После распаковки мы получим несколько файлов и папок:
- Classes
- Documentation
- Tests
- changelog.txt
- install.txt
- license.txt
Файлы — это различные описания по предыдущим версиям, лицензионное соглашение и очень краткая инструкция по установке. Далее, в папке Classes, содержится непосредственно сама библиотека PHPExcel — эту папку необходимо скопировать в корень нашего скрипта.
В папке Documentation содержится документация по библиотеке на английском языке. В папке Tests — примеры по использованию библиотеки.
Создание Excel-файла
Итак, давайте создадим файл makeexcel.php и начинаем работать с ним. Для начала нам необходимо подключить главный файл библиотеки PHPExcel.php (который находится в папке Classes) и создать объект класса PHPExcel:
Настройки листа книги Excel
Документ Excel состоит из книг, а каждая книга в свою очередь, состоит из листов. Далее лист состоит из набора ячеек, доступ к которым осуществляется по координатам. То есть у нас есть столбцы, которые имеют буквенные имена (А, В, С и т.д) и есть строки, которые пронумерованы. Значит, что бы получить доступ к первой ячейке нужно указать код А1. Точно также мы с помощью библиотеки будем получать доступ к каждой ячейке.
Итак, первым делом необходимо выбрать активный лист, на который мы будем выводить данные и получить объект этого листа:
С помощью метода setActiveSheetIndex(0) указываем индекс (номер) активного листа. Нумерация листов начинается с нуля. Далее с помощью метода getActiveSheet() получаем объект этого активного листа, то есть другими словами получаем доступ к нему для работы. И сохраняем этот объект в переменную $aSheet .
Если Вы захотите указать активным какой то другой лист, то вначале его необходимо создать, при помощи метода:
Затем, по аналогии, указываем индекс и получаем объект активного листа.
Вначале задаем ориентацию листа при помощи метода setOrientation() , которому передаем константу класса PHPExcel_Worksheet_PageSetup :
- ORIENTATION_PORTRAIT — книжная
- ORIENTATION_LANDSCAPE — альбомная
Обратите внимание, что перед методом setOrientation() необходимо вызвать метод getPageSetup() , который предоставляет доступ к настройкам страницы.
Далее вызываем метод SetPaperSize() , который позволяет задать размер страницы для печати. Ему передаем параметром константу PAPERSIZE_A4 класса PHPExcel_Worksheet_PageSetup . Что означает, что размер листа страницы будет установлен А4.
Далее устанавливаем поля документа, то есть отступы от краев документа. Отступы задаются в специальных символьных единицах. Вначале, обратите внимание, вызываем у объекта $aSheet метод getPageMargins() , который вернет объект класса, отвечающего за настройки полей страницы. Затем вызываем методы setTop() , setRight() , setLeft() и setBottom() .
Далее при помощи метода setTitle('Прайс лист') задаем название нашего листа.
Если нужно, можно при печати выводить шапку и подвал листа:
Обратите внимание на передаваемые параметры:
Затем указываем настройки шрифта по умолчанию:
- setName('Arial') — задаем имя шрифта;
- setSize(8) — задаем размер шрифта.
Наполнение документа данными
Для начала давайте зададим ширину столбцов (в символьных единицах), которые нам понадобятся:
Теперь заполним несколько ячеек текстом:
Здесь мы сначала объеденяем ячейки с А1 до E1 при помощи метода mergeCells() , далее задаем высоту строки: вначале получаем доступ к строке 1 при помощи метода getRowDimension('1') , затем задаем высоту — setRowHeight(20) . Далее при помощи метода setCellValue('A1','ТД ТИНКО') , устанавливаем значение ячейки А1.
Далее давайте в ячейку D4 запишем текущую дату:
С помощью констант, определенных в классе PHPExcel_Style_NumberFormat, можно задать формат ячейки: FORMAT_GENERAL (общий), FORMAT_TEXT (текст), FORMAT_NUMBER (число), FORMAT_NUMBER_00 (число с дробной частью), FORMAT_PERCENTAGE (процент), FORMAT_PERCENTAGE_00 (процент с дробной частью) и т.п.Теперь, используя метод setCellValue() , а также цикл while() наполним данными наш прайс-лист:
Стилизация данных
Давайте немного украсим наш прайс-лист, то есть каждой ячейке добавим стилей. Для этого необходимо создать массив со стилями и при помощи метода applyFromArray() , применить этот массив к ячейке (или ячейкам):
Теперь, по аналогии, применим стили к остальным ячейкам:
Сохранение документа
Осталось только сохранить наш документ:
Если нужно вывести документ в браузер
Первый заголовок указывает браузеру тип открываемого контента — это документ формата Excel. Второй — говорит браузеру, что документ необходимо отдать пользователю на скачивание под именем simple.xlsx.
Добавление формул
Формула Excel — это математическое выражение, которое создается для вычисления результата и которое может зависеть от содержимого других ячеек. Формула в ячейке Excel может содержать данные, ссылки на другие ячейки, а также обозначение действий, которые необходимо выполнить.
Использование ссылок на ячейки позволяет пересчитывать результат по формулам, когда происходят изменения содержимого ячеек, включенных в формулы. Формулы Excel начинаются со знака =. Скобки ( ) могут использоваться для определения порядка математических операции.
Примеры формул Excel: =27+36 , =А1+А2-АЗ , =SUM(А1:А5) , =MAX(АЗ:А5) , =(А1+А2)/АЗ .
PHPExcel тоже поддерживает добавление формул в ячейки. Установить формулу можно так:
Чтение Excel-файла
Самый простой вариант — считать все таблицы (на всех листах) и записать данные в трехмерный массив:
PHPExcel – библиотека, предназначенная для обработки данных формата MS Excel. С её помощью можно производить чтение и запись информации в файлы, форматировать их содержимое, выполнять операции с формулами, стилями и т.д.
Для работы PHPExcel требуется версия PHP 5.2 или выше, а так же наличие подключенных расширений ZIP, XML и GD2.
Примечание: расширение php_zip нужно только при использовании классов PHPExcel_Reader_Excel2007, PHPExcel_Writer_Excel2007 и PHPExcel_Reader_OOCalc. То есть если вы хотите использовать PHPExcel для полноценной работы с файлами форматов .xlsx или .ods, то вам потребуется zip-расширение.
Возможности библиотеки PHPExcel:
Состав Архива:
Пример чтения Excel файла при помощи класса PHPExcel
Для чтения данных с Excel файла необходимо подключить файл библиотеки PHPExcel и используется метод load() класса PHPExcel_IOFactory. Этот метод собирает данные всех со всех листов текущего документа:
Далее формируем массив из всех листов Excel файла с помощью цикла:
Вывод сформированного массива в виде HTML таблиц(ы) :
Основные методы и свойства класса PHPExcel
Класс PHPExcel – это ядро PHPExcel. Он содержит ссылки на содержащиеся листы, параметры безопасности документов и мета-данные документов. Помимо вышеизложенного, этот класс включает в себя множество других полезных методов для обработки получаемых данных. Некоторые из них будут описаны ниже.
getSheetCount() – возвратит количество листов в книге;
getSheetNames() – возвращает список всех листов в рабочей книге, индексируя их в порядке, в котором их «вкладки» появляются при открытии в MS Excel (или других программах для обработки электронных таблиц);
getSheet(N) – используется для доступа к листу по индексу. Индекс позиции (N) представляет собой порядок, в котором отображаются вкладки листов, когда книга открыта в MS Excel (или других программах для обработки электронных таблиц);
getSheetByName(list_name) – метод используется для доступа к листу по его имени (list_name);
getActiveSheet() – этот метод позволяет получить доступ к активному листу документа напрямую. Активным листом является тот, который будет активен, когда откроется книга в MS Excel (или другой соответствующей программе электронных таблиц);
setActiveSheetIndex() и setActiveSheetIndexByName() – с помощью этих методов можно назначить лист, который будет активным по его индексу или имени.
Получение значения ячейки по координатам
Для получения значения ячейки, сперва должна быть найдена ячейка путем использования метода getCell. Значение ячейки может быть получено с помощью следующей команды:
В случае если вам нужны подсчитанные значения ячейки, вместо getValue(), используйте метод getCalculatedValue():
Получение значения ячейки по строкам и столбцам
Для получения значения ячейки, в листе сначала должна быть найдена ячейка с использованием метода getCellByColumnAndRow. Значение ячейки может быть получено с помощью следующей команды:
Заключение
В этой статье были рассмотрены основные возможности библиотеки PHPExcel в области чтения excel файлов. Помимо всего выше изложенного, есть возможность более гибко использовать эту библиотеку для работы с различными параметрами таблицы. По этим вопросам рекомендуется обращаться к документации. Библиотека PHPExcel так же адаптирована для работы с другими типами файлов, таких как Excel (BIFF). XLS, Excel 2007 (OfficeOpenXML). XLSX, CSV, Libre / OpenOffice Calc. ODS, Gnumeric, PDF и HTML.
Читайте также: