php парсинг excel файла

Разбираем xlsx в PHP без готовых библиотек

В первую очередь опишу проблему, которая заставила в тысячный раз вернуться к обсосанному со всех сторон вопросу: бестолковые менеджеры — без консультации с программистами — пообещали заказчику загрузку данных на сайт из xls(x).

Все бы ничего, но хостер заказчика дает 64мб памяти под выполнение и плевать он хотел на то, что у клиента Exсel файлы вообще без форматирования весят по 10-15мб, что при загрузке его PHPExel съедает (на тест-сервере) что-то около 500мб памяти.
Решение под катом с трудом дотянуло до 5мб.

Предусловия:
1. Имеется Exсel документ листов так в 10-20 с данными о товарах в интернет-каталоге. В каждом листе шапка — «название», «цена» и т.п. + воз доп. характеристик в 40 столбцов — и собственно данные в количестве «у-экселя-сантиметровый-скроллер»;
2. никакого CSV использовать нельзя. Все данные у заказчика уже в Exel и пересохранять их он не собирается… пообещали тут и все;
3. Spreadsheet_Excel_Writer откинут по причине неуниверсальности, хотя написано про него много хорошего. Жду комментариев по memory tests;
4. что удивительно, универсальных решений гугль не предложил. Неужели никто не сталкивался с такой проблемой на PHP *nix, удивился я.

Решение:
После перебора различных способов, вежливо предоставленных гуглом, решили почитать спецификации (эхх, учил меня отец. ). Увидев там ключевые слова основан на Open XML и используется сжатие ZIP быстро позвонили заказчику и перевели разговор в русло xlsx и только: «Ну вы же понимаете! 21 век все-таки! Зачем нам хвататься за старое! Нужно одной ногой стоять в будущем!»

Далее алгоритм таков: принимаем файл, распаковываем его и внимательно смотрим на получившееся.
Полную инвентаризацию надо будет на досуге провести, а сейчас же нам наиболее интересно содержимое директории [xl], конкретно — /xl/worksheets/ и файл /xl/sharedStrings.xml.
В файле /xl/workbook.xml лежит описание листов, но т.к. задачи собрать названия листов не стояло, этот пункт я пропущу. При необходимости разобраться в нем не составит труда.

и так далее в том же духе. Представляет собой текстовые данные в ячейках исходного документа. Со всех листов! Пока просто соберем эти данные в массив.

На выходе получаем многомерный массив, с которым уже можно свободно работать, а можно и сразу в базу лить данные — это личное дело каждого.

Напоследок скажу, что толком в спецификации xlsx не разбирался, а только выполнил поставленную задачу с конкретными xlsx документами. Куда-то ведь должны писаться формулы и изображения (t=«i»?). Когда столкнусь с такой задачей — непременно опишу, а пока представляю нетребовательный к системе алгоритм для сбора текстовых данных из xslx. Надеюсь, будет востребован, т.к. в поисках подобного не встречал.

P.S. Только расставляя метки наткнулся на Работа с большими файлами экселя. Хабрить надо было, а не гуглить — много бы времени сэкономил.

Источник

Реализация быстрого импорта из Excel на PHP

Что использовать в качестве инструмента?

Проблему пользователей при импорте адресных баз сняли. Но тут возникает уже проблема непосредственно разработки.

Наша боль, как разработчиков

Обратила на себя наше внимание библиотека PHPExcel. Ее мы использовали еще несколько лет назад в сервисе sms-рассылок SMS24X7.ru. Петя Соколов (Petr_Sokolov), наш талантливый разработчик, написал обертку для этой библиотеки, исправляющую ряд ее недостатков и багов.

Библиотека, безусловно, интересная и развитая. Но для Печкина ее использовать стало невозможно уже через пару лет, когда выросли и мы и наши клиенты — ее катастрофическая требовательность к ресурсам и огромное время парсинга файлов. Например, нередки случаи загрузки на сервис адресных баз > 100 000 строк со сложной структурой. А если файл уже 500 000 строк и “весит” больше 30Мб?

И тут нас отпустило.

В процессе поисков мы наткнулись на коммерческую библиотеку libxl, увидев результаты “кустарного benchmark” на Stackoverflow.

Библиотека написана на C++, а благодаря великолепному объектно-ориентированному расширению для PHP от Ilia Alshanetsky, легка в освоении и интеграции (например, переписать наше текущее решение с PHPExcel на LibXL заняло около 3 часов). Что очень классно, учитывая, что, к сожалению, документации от разработчика расширения нет и необходимо пользоваться расширением Reflection.

Процесс установки очень прост.

В результате компиляции вы получите файл excel.so в папке /usr/lib/php5/20090626/. Теперь достаточно создать файл /etc/php5/conf.d/excel.ini с содержимым.

Проверим установился ли модуль и перезагрузим веб-сервер.

В коде все тоже очень просто. Подгружаете файл и читаете необходимые ячейки. Например, вот так:

Полученные результаты производительности

Отсутствие потребности в оперативной памяти (в процессе загрузки файла и его чтения) приятно порадовало.

А вот и прирост скорости загрузки excel-файла и его чтения на различных размерах адресных баз.

Данные тесты проводились на xlsx-файлах с N подписчиками в один стоблец с email. Реальные же адресные базы еще больше и сложнее и преимущество в скорости и потреблении памяти выглядит еще значительнее.

Стоимость библиотеки 199$ за девелоперскую лицензию, но, поверьте, это того стоит. Безусловно рекомендуем всем, кто сталкивается с проблемой импорта Excel-файлов на свой сервис.

Источник

Работа с Excel средствами PHP

PHPExcel — библиотека для создания и чтения данных из файлов формата OpenXML (который используется в MS Excel 2007). С ее помощью можно считывать из файлов, записывать в файлы, форматировать содержимое, работать с формулами и т.д. Для работы PHPExcel требуется версия PHP 5.2 или выше, с установленными библиотеками Zip, XML и GD2.

Установка PHPExcel

Первым делом библиотеку необходимо скачать. Для этого переходим на официальный сайт библиотеки и скачиваем архив PHPExcel-1.7.8.zip. После распаковки мы получим несколько файлов и папок:

Файлы — это различные описания по предыдущим версиям, лицензионное соглашение и очень краткая инструкция по установке. Далее, в папке Classes, содержится непосредственно сама библиотека PHPExcel — эту папку необходимо скопировать в корень нашего скрипта.

В папке Documentation содержится документация по библиотеке на английском языке. В папке Tests — примеры по использованию библиотеки.

Создание Excel-файла

Итак, давайте создадим файл makeexcel.php и начинаем работать с ним. Для начала нам необходимо подключить главный файл библиотеки PHPExcel.php (который находится в папке Classes) и создать объект класса PHPExcel:

Настройки листа книги Excel

Документ Excel состоит из книг, а каждая книга в свою очередь, состоит из листов. Далее лист состоит из набора ячеек, доступ к которым осуществляется по координатам. То есть у нас есть столбцы, которые имеют буквенные имена (А, В, С и т.д) и есть строки, которые пронумерованы. Значит, что бы получить доступ к первой ячейке нужно указать код А1. Точно также мы с помощью библиотеки будем получать доступ к каждой ячейке.

Итак, первым делом необходимо выбрать активный лист, на который мы будем выводить данные и получить объект этого листа:

Если Вы захотите указать активным какой то другой лист, то вначале его необходимо создать, при помощи метода:

Затем, по аналогии, указываем индекс и получаем объект активного листа.

Далее при помощи метода setTitle(‘Прайс лист’) задаем название нашего листа.

Если нужно, можно при печати выводить шапку и подвал листа:

Обратите внимание на передаваемые параметры:

Затем указываем настройки шрифта по умолчанию:

Наполнение документа данными

Для начала давайте зададим ширину столбцов (в символьных единицах), которые нам понадобятся:

Теперь заполним несколько ячеек текстом:

Далее давайте в ячейку D4 запишем текущую дату:

Стилизация данных

Теперь, по аналогии, применим стили к остальным ячейкам:

Сохранение документа

Осталось только сохранить наш документ:

Если нужно вывести документ в браузер

Первый заголовок указывает браузеру тип открываемого контента — это документ формата Excel. Второй — говорит браузеру, что документ необходимо отдать пользователю на скачивание под именем simple.xlsx.

Добавление формул

Формула Excel — это математическое выражение, которое создается для вычисления результата и которое может зависеть от содержимого других ячеек. Формула в ячейке Excel может содержать данные, ссылки на другие ячейки, а также обозначение действий, которые необходимо выполнить.

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

PHPExcel тоже поддерживает добавление формул в ячейки. Установить формулу можно так:

Чтение Excel-файла

Самый простой вариант — считать все таблицы (на всех листах) и записать данные в трехмерный массив:

Источник

Php парсинг excel файла

PHP XLS Excel Parser

Probably, the fastest possible and the most efficient parser for XLS excel files for PHP!

Note: this parser works only with older XLS files that were used in Microsoft Excel 95 (BIFF5) and 97-2003 (BIFF8).
It will not work with the newer ones, XLSX!

At least PHP 5.6 32-bit is required. Untested with PHP versions prior to 5.6.
Works best with PHP 7.x 64-bit (faster, more memory efficient than PHP 5.6).

Also, this parser uses my PHP MSCFB Parser. Grab a copy of MSCFB.php if you don’t have one here: https://github.com/arti9m/PHP-MSCFB-Parser and put it in your PHP include directory or in the same directory where MSXLS.php is. MSCFB is «required-once» inside MSXLS, so there’s no need to include/require it manually.

Alternatively, if you know sheet name, you can use it with the same method to select sheet:

Leave out sheet index/name to select the first available valid sheet:

Note: The first valid worksheet is selected automatically when the file is opened or when Parsing mode is changed.

There are two modes which the parser can work in: Array mode and Row-by-row mode. By default, Array mode is used.

In this mode, empty rows and cells are ignored. Boolean excel cells are parsed as true or false. If excel internally represents a whole number as float (which is often the case), it will be parsed as float type.

$excel->cells is a two-dimentional array. Its first dimension represents rows and its second dimension represents columns, both have zero-based numeration. See Rows and columns numeration for more information.

Cell numeration in the returned row is zero-based. See Rows and columns numeration for more information.

One advantage of Row-by-row mode is that it allowes many settings to be changed that affect which cells are proccessed and how. Please refer to Reading settings part of Methods (functions) subsection for more information.

Debug mode enables output (echo) of all error and warning messages. To enable Debug mode, set the 2nd parameter to true in the constructor:

It is also possible to show errors from MSCFB helper class. To do this, set the 4th parameter to true in the constructor:

Warning! PHP function name in which error occured is displayed alongside the actual message. Do not enable Debug mode in your production code since it may pose a security risk! This warning applies both to MSXLS class and MSCFB class.

Temporary files and memory

If XLS file was saved as a Compound File (which is almost always the case), then MSXLS must use a temporary PHP stream resource to store Workbook stream that is extracted from the Compound File. It is stored either in memory or as a temporary file, depending on data size. By default, data that exceeds 2MiB (PHP’s default value) is stored as a temporary file. XLS file may sometimes be stored as a Workbook stream itself, in which case a temporary file or stream is not needed and not created.

You can control when a temporary file is used instead of memory by specifying the threshold in bytes as the 3rd parameter to the constructor. If Workbook stream size (in bytes) is less than this value, it will be stored in memory.

You can instruct PHP not to use a temporary file (thus always storing Workbook stream in memory) by setting this parameter to zero:

Set this parameter to null to use default value:

Note: MSCFB helper class may also need to use a temporary stream resource. It will behave the same way as described above, and will also use that 3rd parameter as its memory limiter.

Note: temporary files are automatically managed (created and deleted) by PHP.

4. Additional information

Rows and columns numeration

A Compound File, or Microsoft Binary Compound File, is a special file format which is essentially a FAT-like container for other files.

Workbook stream, or just Workbook is a binary bytestream that essentially represents excel BIFF file.

Excel file format is known as BIFF, or Binary Interchangeable File Format. There are several versions exist which differ in how they store excel data from version to version. This parser supports BIFF version 5, or BIFF5, which is the file format used in Excel 95, and BIFF version 8 (BIFF8), which is used in Excel 97-2003 versions. The biggest difference between BIFF5 and BIFF8 is that they store strings differently. In BIFF5, strings are stored inside cells in locale-specific 8-bit codepage (for example, CP1252), while BIFF8 has a special structure called SST (Shared Strings Table), which stores unique strings inside itself in UTF16 little-endian encoding, and a reference to SST entry is stored in a cell.

Workbook stream consists of Workbook Globals substream and one or more Sheet substreams. Workbook Globals contains information about the file such as BIFF5 encoding, encryption, sheets information and much more (we do not actually need much more). Sheet substreams, or Sheets represent actual sheets that are created in Excel. They can be Worksheets, Charts, Visual Basic modules and some more, but only regular Worksheets can be parsed.

Excel keeps track of cells starting with first non-empty row and non-empty column, ending with last non-empty row and non-empty column. All other cells are completely ignored by this parser like they don’t exist at all.

What happens when I open XLS file

Note: during every stage extensive error checking is performed. See Error handling for more info.

After Workbook stream is accessed, the output encoding is set to mb_internal_encoding() return value. Then get_data() method is executed: the script extracts information such as sheet count, codepage, sheets byte offsets, etc.

After that, either the first non-empty worksheet will be selected and ready for parsing and all other sheets information will be available to the user, or some error will be created (for example, when no non-empty worksheet was found).

By default, Array parsing mode is active.

Attempts to invoke a Row-by-row-mode related method that is suitable for Array mode only (and vice versa) will create an error, disabling any further actions most of the time.

If no errors occured, it is now possible to select and setup parsing mode.

After a worksheet is parsed, you can select another worksheet for parsing (if any) with select_sheet() method. When you are finished parsing a file, it is a good idea to free memory manually, especially if something else is going on in your script later on. free() method and unset() function called one after another is the best way to do it.

5. Public properties and methods

get_data() — Checks XLS file for errors and encryption, gathers information such as CODEPAGE for BIFF5, SST location for BIFF8. Gathers information about all sheets in the file. Also executes select_sheet() to select first valid worksheet for parsing. This method is called automatically when XLS file is opened. Invoking it manually makes sence only if BIFF5 codepage was detected incorrectly and you cannot see sheet names (and you really need them). In this case, encoding settings must be configured with set_encodings() after file opening and get_data() should be called manually after it.

get_biff_ver() — returns version of excel file. 5 is BIFF5 (Excel 95 file), 8 is BIFF8 (Excel 97-2003 file).

get_codepage() — returns CODEPAGE string. Relevant only for BIFF5 files, in which strings are encoded using a specific codepage. In BIFF8, all strings are unicode (UTF-16 little endian).

get_sheets() — returns array of structures with sheets information. See the code below.

get_active_sheet() — returns currently selected sheet info in the same structure that get_valid_sheets() array consists of.

get_filename() — returns a file name string originally supplied to the constructor.

get_filesize() — returns size of the file supplied to the constructor (in bytes).

get_margins($which = ‘all’) — returns currently set margins for the selected worksheet. Margins are set automatically when the sheet is selected. Margins can be set manually with set_margins() method. They define what rows and columns are read by read_next_row() method.

$enable parameter enables encoding conversion of BIFF5 strings.

$from is source encoding string, for example ‘CP1252’. Leaving it out or setting it to null resets this parameter to detected internal BIFF5 codepage.

$to is target encoding string, for example ‘UTF-8’. Leaving it out or setting it to null resets this parameter to the value returned by mb_internal_encoding() PHP function.

$use_iconv — If true, iconv() function will be used for convertion. Otherwise, mb_convert_encoding() will be used.

set_output_encoding($enc = null) — sets output encoding which excel strings should be decoded to.
$enc is target encoding string. If parameter set to null or left out, a value returned by mb_internal_encoding() function will be used.

$sheet must be either a sheet number or a sheet name. Use get_valid_sheets() to get those, if needed.
-1 or leaving out the parameter will select the first valid worksheet.

read_next_row() — parses next row and returns array of parsed cells. Works only in Row-by-row mode.

free_stream() — Close Workbook stream, free memory associated with it and delete temporary files.

free_sst() — re-initialize SST structure (Shared Strings Table, used by Array mode).

free_rows_map() — re-initialize rows map storage used by Row-by-row mode.

free_sst_maps() — re-initialize SST offsets map and SST lengths storage used by Row-by-row mode.

3. Reading settings (mostly for Row-by-row mode)

Note: this is the only setting that also works in Array mode.

set_active_row($row_number) — set which row to read next.
$row_number is zero-based excel row number and it must not be out of bounds set by set_margins() method.

last_read_row_number() — returns most recently parsed row number.
Valid only if called immediately after read_next_row().

next_row_number() — returns row number that is to be parsed upon next call of read_next_row().
Returns -1 if there is no more rows left to parse.

use_empty_cols($set = false) — whether or not to parse empty columns to empty value.

use_empty_rows($set = false) — whether or not to parse empty rows.

Note: if empty columns parsing is disabled (it is disabled by default), read_next_row() will return -1 when an empty row is encountered. If empty columns parsing is enabled with use_empty_cols(true), it will return array of cells filled with empty value.

set_float_to_int($tf = false) — whether or not to parse excel cells with whole float numbers to integers. Often whole numbers are stored as float internally in XLS file, and by default they are parsed as floats. This setting allows to parse such numbers as integer type. Note: cells with numbers internally stored as integers are always parsed as integers.

4. Constructor and destructor

$filename — path to XLS file.

$debug — if evaluates to true, enables Debug mode.

$debug_MSCFB — if evaluates to true, enables Debug mode in MSCFB helper class.

__destruct() — execute free() method, thus closing all opened streams, deleting temporary files and erasing big structures.

If Debug mode is enabled, errors and warnings are printed (echoed) to standart output automatically.

7. Security considerations

There are extensive error checks in every function that should prevent any potential problems no matter what file is supplied to the constructor. The only potential security risk can come from the Debug mode, which prints a function name in which an error or a warning has occured, but even then I do not see how such information can lead to problems with this particular class. It’s pretty safe to say that this code can be safely run in (automated) production of any kind. Same applies to MSCFB class.

8. Performance and memory

The MSXLS class has been optimized for fast parsing and data extraction, while still performing error checks for safety. It is possible to marginally increase performance by leaving those error checks out, but I would strongly advise against it, because if a specially crafted mallicious file is supplied, it becomes possible to cause a memory hog or an infinite loop.

The following numbers were obtained on a Windows machine (AMD Phenom II x4 940), with a 97.0 MiB test XLS file (96.2 MiB Workbook stream) using WAMP server. XLS file consists entirely of unique strings. Default temporary file settings is used.

Note: Disabling temporary files does not decrease script execution time by any significant margin. In fact, the execution time is increased sometimes.

Note: It took 1.65 seconds and 12.0 MiB of memory to parse a real-life XLS pricelist of 13051 entries in Array mode in PHP 7.0.10. That XLS file was 3.45 MiB in size.

9. More documentation

All code in MSXLS.php file is heavily commented, feel free to take a look at it. To understand how XLS file is structured, please refer to MS documentation, or to OpenOffice.org’s Documentation of MS Compound File (also provided as a PDF file in this repository).

About

Probably, the fastest possible and the most efficient parser for XLS excel files for PHP!

Источник