Php прочитать excel файл
This commit does not belong to any branch on this repository, and may belong to a fork outside of the repository.
- Open with Desktop
- View raw
- Copy raw contents Copy raw contents
Copy raw contents
Copy raw contents
PHPExcel Developer Documentation
Reading and writing to file
As you already know from part REF _Ref191885438 \w \h 3.3 REF _Ref191885438 \h Readers and writers, reading and writing to a persisted storage is not possible using the base PHPExcel classes. For this purpose, PHPExcel provides readers and writers, which are implementations of PHPExcel_Writer_IReader and PHPExcel_Writer_IWriter.
The PHPExcel API offers multiple methods to create a PHPExcel_Writer_IReader or PHPExcel_Writer_IWriter instance:
Direct creation via PHPExcel_IOFactory. All examples underneath demonstrate the direct creation method. Note that you can also use the PHPExcel_IOFactory class to do this.
Creating PHPExcel_Reader_IReader using PHPExcel_IOFactory
There are 2 methods for reading in a file into PHPExcel: using automatic file type resolving or explicitly.
Automatic file type resolving checks the different PHPExcel_Reader_IReader distributed with PHPExcel. If one of them can load the specified file name, the file is loaded using that PHPExcel_Reader_IReader. Explicit mode requires you to specify which PHPExcel_Reader_IReader should be used.
You can create a PHPExcel_Reader_IReader instance using PHPExcel_IOFactory in automatic file type resolving mode using the following code sample:
A typical use of this feature is when you need to read files uploaded by your users, and you don’t know whether they are uploading xls or xlsx files.
If you need to set some properties on the reader, (e.g. to only read data, see more about this later), then you may instead want to use this variant:
You can create a PHPExcel_Reader_IReader instance using PHPExcel_IOFactory in explicit mode using the following code sample:
Note that automatic type resolving mode is slightly slower than explicit mode.
Creating PHPExcel_Writer_IWriter using PHPExcel_IOFactory
You can create a PHPExcel_Writer_Iwriter instance using PHPExcel_IOFactory:
Excel 2007 (SpreadsheetML) file format
Excel2007 file format is the main file format of PHPExcel. It allows outputting the in-memory spreadsheet to a .xlsx file.
Reading a spreadsheet
You can read an .xlsx file using the following code:
You can set the option setReadDataOnly on the reader, to instruct the reader to ignore styling, data validation, … and just read cell data:
Read specific sheets only
You can set the option setLoadSheetsOnly on the reader, to instruct the reader to only load the sheets with a given name:
Read specific cells only
You can set the option setReadFilter on the reader, to instruct the reader to only load the cells which match a given rule. A read filter can be any class which implements PHPExcel_Reader_IReadFilter. By default, all cells are read using the PHPExcel_Reader_DefaultReadFilter.
The following code will only read row 1 and rows 20 – 30 of any sheet in the Excel file:
Writing a spreadsheet
You can write an .xlsx file using the following code:
By default, this writer pre-calculates all formulas in the spreadsheet. This can be slow on large spreadsheets, and maybe even unwanted. You can however disable formula pre-calculation:
Office 2003 compatibility pack
Because of a bug in the Office2003 compatibility pack, there can be some small issues when opening Excel2007 spreadsheets (mostly related to formula calculation). You can enable Office2003 compatibility with the following code:
Office2003 compatibility should only be used when needed Office2003 compatibility option should only be used when needed. This option disables several Office2007 file format options, resulting in a lower-featured Office2007 spreadsheet when this option is used.
Excel 5 (BIFF) file format
Excel5 file format is the old Excel file format, implemented in PHPExcel to provide a uniform manner to create both .xlsx and .xls files. It is basically a modified version of PEAR Spreadsheet_Excel_Writer, although it has been extended and has fewer limitations and more features than the old PEAR library. This can read all BIFF versions that use OLE2: BIFF5 (introduced with office 95) through BIFF8, but cannot read earlier versions.
Excel5 file format will not be developed any further, it just provides an additional file format for PHPExcel.
Excel5 (BIFF) limitations Please note that BIFF file format has some limits regarding to styling cells and handling large spreadsheets via PHP.
Reading a spreadsheet
You can read an .xls file using the following code:
You can set the option setReadDataOnly on the reader, to instruct the reader to ignore styling, data validation, … and just read cell data:
Read specific sheets only
You can set the option setLoadSheetsOnly on the reader, to instruct the reader to only load the sheets with a given name:
Read specific cells only
You can set the option setReadFilter on the reader, to instruct the reader to only load the cells which match a given rule. A read filter can be any class which implements PHPExcel_Reader_IReadFilter. By default, all cells are read using the PHPExcel_Reader_DefaultReadFilter.
The following code will only read row 1 and rows 20 to 30 of any sheet in the Excel file:
Writing a spreadsheet
You can write an .xls file using the following code:
Excel 2003 XML file format
Excel 2003 XML file format is a file format which can be used in older versions of Microsoft Excel.
Excel 2003 XML limitations Please note that Excel 2003 XML format has some limits regarding to styling cells and handling large spreadsheets via PHP.
Reading a spreadsheet
You can read an Excel 2003 .xml file using the following code:
Read specific cells only
You can set the option setReadFilter on the reader, to instruct the reader to only load the cells which match a given rule. A read filter can be any class which implements PHPExcel_Reader_IReadFilter. By default, all cells are read using the PHPExcel_Reader_DefaultReadFilter.
The following code will only read row 1 and rows 20 to 30 of any sheet in the Excel file:
Symbolic LinK (SYLK)
Symbolic Link (SYLK) is a Microsoft file format typically used to exchange data between applications, specifically spreadsheets. SYLK files conventionally have a .slk suffix. Composed of only displayable ANSI characters, it can be easily created and processed by other applications, such as databases.
SYLK limitations Please note that SYLK file format has some limits regarding to styling cells and handling large spreadsheets via PHP.
Reading a spreadsheet
You can read an .slk file using the following code:
Read specific cells only
You can set the option setReadFilter on the reader, to instruct the reader to only load the cells which match a given rule. A read filter can be any class which implements PHPExcel_Reader_IReadFilter. By default, all cells are read using the PHPExcel_Reader_DefaultReadFilter.
The following code will only read row 1 and rows 20 to 30 of any sheet in the SYLK file:
Open/Libre Office (.ods)
Open Office or Libre Office .ods files are the standard file format for Open Office or Libre Office Calc files.
Reading a spreadsheet
You can read an .ods file using the following code:
Read specific cells only
You can set the option setReadFilter on the reader, to instruct the reader to only load the cells which match a given rule. A read filter can be any class which implements PHPExcel_Reader_IReadFilter. By default, all cells are read using the PHPExcel_Reader_DefaultReadFilter.
The following code will only read row 1 and rows 20 to 30 of any sheet in the Calc file:
CSV (Comma Separated Values)
CSV (Comma Separated Values) are often used as an import/export file format with other systems. PHPExcel allows reading and writing to CSV files.
CSV limitations Please note that CSV file format has some limits regarding to styling cells, number formatting, .
Reading a CSV file
You can read a .csv file using the following code:
Setting CSV options
Often, CSV files are not really “comma separated”, or use semicolon (;) as a separator. You can instruct PHPExcel_Reader_CSV some options before reading a CSV file.
Note that PHPExcel_Reader_CSV by default assumes that the loaded CSV file is UTF-8 encoded. If you are reading CSV files that were created in Microsoft Office Excel the correct input encoding may rather be Windows-1252 (CP1252). Always make sure that the input encoding is set appropriately.
Read a specific worksheet
CSV files can only contain one worksheet. Therefore, you can specify which sheet to read from CSV:
Read into existing spreadsheet
When working with CSV files, it might occur that you want to import CSV data into an existing PHPExcel object. The following code loads a CSV file into an existing $objPHPExcel containing some sheets, and imports onto the 6th sheet:
Writing a CSV file
You can write a .csv file using the following code:
Setting CSV options
Often, CSV files are not really “comma separated”, or use semicolon (;) as a separator. You can instruct PHPExcel_Writer_CSV some options before writing a CSV file:
Write a specific worksheet
CSV files can only contain one worksheet. Therefore, you can specify which sheet to write to CSV:
By default, this writer pre-calculates all formulas in the spreadsheet. This can be slow on large spreadsheets, and maybe even unwanted. You can however disable formula pre-calculation:
Writing UTF-8 CSV files
A CSV file can be marked as UTF-8 by writing a BOM file header. This can be enabled by using the following code:
Decimal and thousands separators
If the worksheet you are exporting contains numbers with decimal or thousands separators then you should think about what characters you want to use for those before doing the export.
By default PHPExcel looks up in the server's locale settings to decide what characters to use. But to avoid problems it is recommended to set the characters explicitly as shown below.
English users will want to use this before doing the export:
German users will want to use the opposite values.
Note that the above code sets decimal and thousand separators as global options. This also affects how HTML and PDF is exported.
PHPExcel allows you to read or write a spreadsheet as HTML format, for quick representation of the data in it to anyone who does not have a spreadsheet application on their PC, or loading files saved by other scripts that simply create HTML markup and give it a .xls file extension.
HTML limitations Please note that HTML file format has some limits regarding to styling cells, number formatting, .
Reading a spreadsheet
You can read an .html or .htm file using the following code:
HTML limitations Please note that HTML reader is still experimental and does not yet support merged cells or nested tables cleanly
Please note that PHPExcel_Writer_HTML only outputs the first worksheet by default.
Writing a spreadsheet
You can write a .htm file using the following code:
Write all worksheets
HTML files can contain one or more worksheets. If you want to write all sheets into a single HTML file, use the following code:
Write a specific worksheet
HTML files can contain one or more worksheets. Therefore, you can specify which sheet to write to HTML:
Setting the images root of the HTML file
There might be situations where you want to explicitly set the included images root. For example, one might want to see
You can use the following code to achieve this result:
By default, this writer pre-calculates all formulas in the spreadsheet. This can be slow on large spreadsheets, and maybe even unwanted. You can however disable formula pre-calculation:
Embedding generated HTML in a web page
There might be a situation where you want to embed the generated HTML in an existing website. PHPExcel_Writer_HTML provides support to generate only specific parts of the HTML code, which allows you to use these parts in your website.
- generateHTMLHeader()
- generateStyles()
- generateSheetData()
- generateHTMLFooter()
Here's an example which retrieves all parts independently and merges them into a resulting HTML page:
Writing UTF-8 HTML files
A HTML file can be marked as UTF-8 by writing a BOM file header. This can be enabled by using the following code:
Decimal and thousands separators
See section PHPExcel_Writer_CSV how to control the appearance of these.
PHPExcel allows you to write a spreadsheet into PDF format, for fast distribution of represented data.
PDF limitations Please note that PDF file format has some limits regarding to styling cells, number formatting, .
PHPExcel’s PDF Writer is a wrapper for a 3rd-Party PDF Rendering library such as tcPDF, mPDF or DomPDF. Prior to version 1.7.8 of PHPExcel, the tcPDF library was bundled with PHPExcel; but from version 1.7.8 this was removed. Instead, you must now install a PDF Rendering library yourself; but PHPExcel will work with a number of different libraries.
Currently, the following libraries are supported:
The different libraries have different strengths and weaknesses. Some generate better formatted output than others, some are faster or use less memory than others, while some generate smaller .pdf files. It is the developers choice which one they wish to use, appropriate to their own circumstances.
Before instantiating a Writer to generate PDF output, you will need to indicate which Rendering library you are using, and where it is located.
Writing a spreadsheet
Once you have identified the Renderer that you wish to use for PDF generation, you can write a .pdf file using the following code:
Please note that PHPExcel_Writer_PDF only outputs the first worksheet by default.
Write all worksheets
PDF files can contain one or more worksheets. If you want to write all sheets into a single PDF file, use the following code:
Write a specific worksheet
PDF files can contain one or more worksheets. Therefore, you can specify which sheet to write to PDF:
By default, this writer pre-calculates all formulas in the spreadsheet. This can be slow on large spreadsheets, and maybe even unwanted. You can however disable formula pre-calculation:
Decimal and thousands separators
See section PHPExcel_Writer_CSV how to control the appearance of these.
Generating Excel files from templates (read, modify, write)
Readers and writers are the tools that allow you to generate Excel files from templates. This requires less coding effort than generating the Excel file from scratch, especially if your template has many styles, page setup properties, headers etc.
Here is an example how to open a template file, fill in a couple of fields and save it again:
С помощью PHPExcel можно производить чтение и запись информации в файлы, форматировать их содержимое,
выполнять операции с формулами, стилями и т.д.
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:
1. PHPExcel может читать различные форматы файлов электронных таблиц:
- Excel5
- Excel2003XML
- Excel2007
- OOCalc
- SYLK
- Gnumeric
- CSV
2. Создание и обработка документов средствами PHP
Состав Архива:
Пример чтения 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.
Лучший способ сказать автору «СПАСИБО» рассказать друзьям или поделиться ссылкой!
Другие статьи:
В этой статье Мы покажем Вам, как сделать так, чтобы шапка сайта выезжала при прокрутке страницы вверх, но при этом скрывалась при прокрутке страницы вниз, чтобы не загораживать нам просмотр самой страницы.
Всплывающая фиксированная навигация для сайта jQuery, CSS - инструкция, примеры
DateTimePicker – удобный и легкий в использовании плагин для быстрой подстановки даты и времени в поля ввода.
Плагин DateTimePicker для сайта - инструкция, настройка, инициализация
В этой статье будет рассмотрено решение этой задачи с использованием плагина Masked Input, который отличается простотой и удобством в использовании.
Плагин маски ввода для input - jquery maskedinput - инструкция, настройка, инициализация
Метод, который мы рассмотрим ниже, позволяет размещать информацию на страницах любого пользователя или группы,к которому имеет доступ авторизованный пользователь.
Публикация на стене Вконтакте средствами API - кросспостинг
Текстовый редактор TinyMCE в визуальном режиме при стандартной конфигурации обрезает некоторый теги и свойства.
TinyMCE вырезает теги - исключения для тегов TinyMCE
Научимся вставлять теги в выделенный фрагмент текста, вставлять теги на место селектора в тексте и получать средствами JS HTML код отредактированного текста и текст без HTML сущностей.
Cвойства и методы TinyMCE - вставка тегов, вывод значений
Как вы уже поняли, редактор является платформенным Javascript редактором HTML кода, т.е. WYSIWYG редактором для сайтов и веб приложений. Разработчиком этого полезного продукта является компания Moxiecode Systems AB.
Установка и настройка TinyMCE - инициализация и параметры
В статье мы: познакомимся с каруселью; научимся интегрировать ее на свой сайт; познакомимся с основными параметрами инициализации;рассмотрим самые популярные свойства и методы классов jCarousel
Установка и настройка jCarousel - параметры и методы
Любой backend программист php сталкивается с задачей автоматизации почтовой рассылки на веб сайтах и многие,кто сталкивался с данной задачей впервые, вставали перед ключевым вопросом: "Писать или не писать smtp клиент с нуля?".
Привет, Хабр!
Мне по работе часто приходится импортировать Excel-документы средствами PHP.
Для этого я использую библиотеку PHPExcel, которая на сегодняшний день является весьма удобным инструментом.
Но есть несколько «скользких» моментов, связанных с чтением данных из ячеек, о которых я хочу рассказать хабрачитателям, а также разобрать функцию, решающую эти проблемы.
1. Адресация ячеек
- колонка и ряд в виде строки: «A1»
- колонка буквой, ряд числом: («A», 1)
- колонка и ряд числом: (1, 1)
Но в 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 позволяет производить импорт и экспорт данных в excel. Применять различные стили оформления к отчетам. В общем, все на высоте. Даже есть возможность работы с формулами. Только необходимо учитывать, что вся работа (чтение и запись) должна вестись в кодировке utf-8.
Установка библиотеки
С помощью библиотеки PHPExcel можно записывать данные в следующие форматы:
- Excel 2007;
- Excel 97 и поздние версии;
- PHPExcel Serialized Spreadshet;
- HTML;
- PDF;
- CSV.
Импорт данных из PHP в Excel
Рассмотрим пример по формированию таблицы умножения.
Рассмотрим еще на примере три полезные инструкции:
- $sheet->getColumnDimension('A')->setWidth(40) – устанавливает столбцу “A” ширину в 40 единиц;
- $sheet->getColumnDimension('B')->setAutoSize(true) – здесь у столбца “B” будет установлена автоматическая ширина;
- $sheet->getRowDimension(4)->setRowHeight(20) – устанавливает четвертой строке высоту равную 20 единицам.
Также обратите внимание на следующие необходимые для работы с отчетом методы:
- Методы для вставки данных в ячейку:
- setCellValue([$pCoordinate = 'A1' [, $pValue = null [, $returnCell = false]]]) — принимает три параметра: координату ячейки, данные для вывода в ячейку и третий параметр эта одна из констант типа boolean: true или false (если передать значение true, то метод вернет объект ячейки, иначе объект рабочего листа);
- setCellValueByColumnAndRow([$pColumn = 0 [, $pRow = 1 [, $pValue = null [, $returnCell = false]]]]) — принимает четыре параметра: номер столбца ячейки, номер строки ячейки, данные для вывода в ячейку и четвертый параметр действует по аналогии с третьим параметром метода setCellValue().
- getCell([$pCoordinate = 'A1']) — принимает в качестве параметра координату ячейки;
- getCellByColumnAndRow([$pColumn = 0 [, $pRow = 1]]) — принимает два параметра в виде номеров столбца и строки ячейки.
Как мы видим, вышеприведенные методы являются парными. Поэтому мы можем работать с ячейками используя строковое или числовое представление координат. Что конечно же является дополнительным преимуществом в работе.
Оформление отчета средствами PHP в Excel
Очень часто возникает необходимость выделить в отчете некоторые данные. Сделать выделение шрифта или применить рамку с заливкой фона для некоторых ячеек и т.д. Что позволяет сконцентрироваться на наиболее важной информации (правда может и наоборот отвлечь). Для этих целей в библиотеке PHPExcel есть целый набор стилей, которые можно применять к ячейкам в excel. Есть конечно в этой библиотеке небольшой “минус” – нельзя применить стиль к нескольким ячейкам одновременно, а только к каждой индивидуально. Но это не создает дискомфорта при разработке web-приложений.
Назначить стиль ячейке можно тремя способами:
- Использовать метод applyFromArray, класса PHPExcel_Style. В метод applyFromArray передается массив со следующими параметрами:
- fill — массив с параметрами заливки;
- font — массив с параметрами шрифта;
- borders — массив с параметрами рамки;
- alignment — массив с параметрами выравнивания;
- numberformat — массив с параметрами формата представления данных ячейки;
- protection — массив с параметрами защиты ячейки.
- pCellStyle – данный параметр является экземпляром класса PHPExcel_Style;
- pRange – диапазон ячеек.
Заливка
Значением параметра fill является массив со следующими необязательными параметрами:
- type — тип заливки;
- rotation — угол градиента;
- startcolor — значение в виде массива с параметром начального цвета в формате RGB;
- endcolor — значение в виде массива с параметром конечного цвета в формате ARGB;
- color — значение в виде массива с параметром начального цвета в формате RGB.
Или можно использовать следующие методы:
Вставка изображений
Довольно редко, но бывает полезным произвести вставку изображения в отчет. Это может быть логотип, схема и т.д. Для работы нам понадобятся следующие методы:
- setPath([$pValue = '', [$pVerifyFile = true]]) — данный метод принимает два параметра. В качестве первого параметра указывается путь к файлу с изображением. А второй параметр имеет смысл указывать, если необходимо осуществлять проверку существования файла (может принимать одно из значений true или false).
- setCoordinates([$pValue = 'A1'])) — принимает на вход один параметр в виде строки с координатой ячейки.
- setOffsetX([$pValue = 0]) — принимает один параметр со значением смещения по X от левого края ячейки.
- setOffsetY([$pValue = 0]) — принимает один параметр со значением смещения по Y от верхнего края ячейки.
- setWorksheet([$pValue = null, [$pOverrideOld = false]]) — этот метод принимает на вход два параметра. Первый является обязательным, а второй нет. В качестве первого параметра указывается экземпляр класса PHPExcel_Worksheet (текущий активный лист). Если в качестве значения второго параметра передать true, то если лист уже был назначен ранее – произойдет его перезапись и соответственно изображение удалится.
Код демонстрирующий алгоритм вставки изображения приведен ниже:
Вот так выглядит отчет со вставленным изображением:
Шрифт
В качестве значения параметра font указывается массив, который содержит следующие необязательные параметры:
- name — имя шрифта;
- size — размер шрифта;
- bold — выделять жирным;
- italic — выделять курсивом;
- underline — стиль подчеркивания;
- strike — перечеркнуть;
- superScript — надстрочный знак;
- subScript — подстрочный знак;
- color — значение в виде массива с параметром цвета в формате RGB.
Или воспользоваться следующими методами:
Рамка
В качестве значения параметра borders указывается массив, который содержит следующие необязательными параметры:
- тип рамки — (top|bootom|left|right|diagonal|diagonaldirection);
- style — стиль рамки;
- color — значение в виде массива с параметром цвета в формате RGB.
Так же можно прибегнуть к использованию следующих методов:
$PHPExcel_Style->getBorders()->getLeft()->applyFromArray(array(‘style’ =>PHPExcel_Style_Border::BORDER_DASHDOT,’color’ => array(‘rgb’ => ’808080')));
$PHPExcel_Style->getBorders()->getRight()->applyFromArray(array(‘style’ =>PHPExcel_Style_Border::BORDER_DASHDOT,’color’ => array(‘rgb’ => ’808080')));
$PHPExcel_Style->getBorders()->getTop()->applyFromArray(array(‘style’ =>PHPExcel_Style_Border::BORDER_DASHDOT,’color’ => array(‘rgb’ => ’808080')));
$PHPExcel_Style->getBorders()->getBottom()->applyFromArray(array(‘style’ =>PHPExcel_Style_Border::BORDER_DASHDOT,’color’ => array(‘rgb’ => ’808080')));
$PHPExcel_Style->getBorders()->getDiagonal()->applyFromArray(array(‘style’ => PHPExcel_Style_Border::BORDER_DASHDOT,’color’ => array(‘rgb’ => ’808080')));
$PHPExcel_Style->getBorders()->setDiagonalDirection(array(‘style’ =>PHPExcel_Style_Border::BORDER_DASHDOT,’color’ => array(‘rgb’ => ’808080'))).
Выравнивание
Значением параметра alignment является массив, который принимает на вход четыре необязательных параметра:
- horizontal — константа горизонтального выравнивания;
- vertical — константа вертикального выравнивания;
- rotation — угол поворота текста;
- wrap — разрешить перенос текста;
- shrinkToFit — изменять ли размер шрифта при выходе текста за область ячейки;
- indent — отступ от левого края.
Или использовать следующие методы:
Формат представления данных
Параметр numberformat представляет собой массив, который включает только один параметр: code — формат данных ячейки.
А можно и воспользоваться методом:
Защита ячеек
В качестве значения параметра protection выступает массив, который содержит два необязательных параметра:
- locked — защитить ячейку;
- hidden — скрыть формулы.
Или использовать следующие методы:
Теперь мы знаем, какие есть настройки стилей и какие присутствуют параметры у каждого стиля. Сейчас мы к ячейкам таблицы применим стиль оформления, но проделаем это тремя способами. Первый способ заключается в создании массива настроек, который в качестве параметра мы передадим в метод applyFromArray, класса PHPExcel_Style.
Далее мы применим созданный нами стиль к ячейкам excel.
Сейчас применим тот же стиль, но используя другую методику.
Вот что у нас получилось:
Для получения данных о стиле конкретной ячейки необходимо использовать один из следующих методов, который вернет экземпляра класса PHPExcel_Style:
- getStyleByColumnAndRow([$pColumn = 0 [, $pRow = 1]]) – применяется если требуется обратиться к ячейке по числовым координатам. Методу необходимо передать два параметра в виде номеров столбца и строки ячейки;
- getStyle([pCellCoordinate = 'A1']) – используется для обращения по строковой координате ячейки. Методу требуется передать один параметр, это строковое представление координаты.
А теперь рассмотрим третий способ назначения стиля ячейкам путем дублирования стиля. Пример использования представлен ниже (предполагается, что к ячейке “B2” применен некий стиль и мы его хотим продублировать для диапазона ячеек “F2:F10”):
Добавление комментариев
Я думаю, что не часто кто-то пользуется возможностью добавления комментариев к ячейкам, но это сугубо мое личное мнение, однако такая возможность имеется. Добавить комментарий к ячейке довольно просто, что видно из примера ниже:
Следует заметить, что при повторном вызове метода createTextRun() новый комментарий добавится к уже существующему, а не заменит его. Следует отметить, что данный метод возвращает объект класса PHPExcel_RichText_Run, у которого имеются методы для установки и получения параметров шрифта:
- getFont() – возвращает объект класса для работы со шрифтами PHPExcel_Style_Font.
- setFont([$pFont = null]))]) – данному методу требуется передать в качестве параметра объект класса PHPExcel_Style_Font.
Вот какой комментарий мы должны получить:
Вставка ссылки
Вставка ссылок в ячейку тоже не вызывает каких-либо затруднений, что можно видеть из нижеописанного примера:
Чтение данных из Excel
Формировать отчеты и применять к ним стили это конечно отлично. Но на этом возможности библиотеки PHPExcel не заканчиваются. Ну что же, посмотрим на что она еще способна. А способна она еще и читать данные из файлов формата *.xls / *.xlsx.
С помощью библиотеки PHPExcel можно читать следующие форматы:
- Excel 2007;
- Excel 5.0/Excel 95;
- Excel 97 и поздние версии;
- PHPExcel Serialized Spreadshet;
- Symbolic Link;
- CSV.
Для работы нам понадобятся объекты двух классов:
- PHPExcel_Worksheet_RowIterator – используется для перебора строк;
- PHPExcel_Worksheet_CellIterator – используется для перебора ячеек.
Для демонстрации выведем данные из таблицы с информацией об автомобилях.
Пример чтения файла представлен ниже:
Первый вариант
Второй вариант
В первом варианте мы производим чтение данных, из ячеек используя итераторы. А во втором, мы используем индексную адресацию для обращения и получения данных из ячеек листа. Получить данные о количестве строк и столбцов, можно воспользовавшись следующими методами класса PHPExcel_Worksheet:
- getHighestColumn() – возвращает символьное представление последнего занятого столбца в активном листе. Обратите внимание: не индекс столбца, а его символьное представление (A, F и т.д.);
- getHighestRow() – возвращает количество занятых строк в активном листе.
Другие полезные методы
Возможностей по работе с отчетами формата excel с использованием PHP как мы видим, достаточно много. Но мы рассмотрим еще несколько полезных методов, которые могут оказаться весьма полезны в работе:
- getMergeCells() – с помощью данного метода принадлежащего классу PHPExcel_Worksheet можно получить информацию обо всех объединенных ячейках в листе;
- setPreCalculateFormulas([$pCellStyle = true]) – данный метод необходимо использовать если требуется произвести расчет формул в листе (он имеется у двух классов: PHPExcel_Writer_Excel5 и PHPExcel_Writer_Excel2007). В рассматриваемый метод передается параметр типа boolean: true или false (если передать значение true, то расчет формул произойдет перед сохранением файла автоматически, иначе расчета формул не последует). Использование данного метода может оказаться полезным если созданный файл потребуется загрузить, к примеру на Google Drive. Ведь в таком случае расчет формул не будет произведен автоматически указанным сервисом и здесь вся ответственность ложиться на нас;
- stringFromColumnIndex([$pColumnIndex = 0]) – данный метод позволяет определить по номеру столбца его символьное представление, для этого в качестве параметра необходимо передать его номер;
- columnIndexFromString([$pString = 'A']) – с помощью данного метода можно определить номер столбца по его символьному представлению, для этого в качестве единственного параметра необходимо передать его обозначение.
Примечание: Методы stringFromColumnIndex и columnIndexFromString примечательны тем, что их можно использовать без создания объекта класса. Пример использования представлен ниже:
С помощью продемонстрированных возможностей, можно формировать и считывать любые отчеты в виде файлов, формата excel. А также были продемонстрированы почти все возможные методы для работы со стилями.
Читайте также: