Php and excel files

Разбираем 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 лежит описание листов, но т.к. задачи собрать названия листов не стояло, этот пункт я пропущу. При необходимости разобраться в нем не составит труда.

. Наименование  Описание  Изображение  URL  !Классификация  !Бренд  ~1ф, 220-240 В, 50 Гц  . 

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

 $xml = simplexml_load_file(PATH . '/upload/xls_data/xl/sharedStrings.xml'); $sharedStringsArr = array(); foreach ($xml->children() as $item) < $sharedStringsArr[] = (string)$item->t; > 

/xl/worksheets/
Это директория с файлами типа «sheet1.xml» с описанием данных листов. Конкретно в каждом файле нас интересует содержимое и его детей .

Методом сопоставлений и экспериментов было выяснено, что атрибут [t=«s»] у ячейки (судя по всему type=string) является указанием на то, что значение берем из файла sharedStrings.xml. Указатель — значение — номер элемента из $sharedStringsArr. Если не указан — берем само значение за значение ячейки.

 $handle = @opendir(PATH . '/upload/xls_data/xl/worksheets'); $out = array(); while ($file = @readdir($handle)) < //проходим по всем файлам из директории /xl/worksheets/ if ($file != "." && $file != ".." && $file != '_rels') < $xml = simplexml_load_file(PATH . '/upload/xls_data/xl/worksheets/' . $file); //по каждой строке $row = 0; foreach ($xml->sheetData->row as $item) < $out[$file][$row] = array(); //по каждой ячейке строки $cell = 0; foreach ($item as $child) < $attr = $child->attributes(); $value = isset($child->v)? (string)$child->v:false; $out[$file][$row][$cell] = isset($attr['t']) ? $sharedStringsArr[$value] : $value; $cell++; > $row++; > > > var_dump($out); 

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

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

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

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

Источник

Обзор PHP расширений для чтения файлов Excel

В статье представлены различные PHP-расширения для чтения файлов XLS, XLSX, описаны их плюсы и минусы, а также примеры чтения.

PHPExcel

Огромная библиотека читает и формирует фалы xls, xlsx, csv.

Пример чтения файла в массив:

require_once __DIR__ . '/PHPExcel-1.8/Classes/PHPExcel/IOFactory.php'; // Файл xlsx $xls = PHPExcel_IOFactory::load(__DIR__ . '/test.xlsx'); // Первый лист $xls->setActiveSheetIndex(0); $sheet = $xls->getActiveSheet(); foreach ($sheet->toArray() as $row)

SimpleXLSX

• Менее прожорлив к памяти.
• Не всегда может прочитать файл, например файл сформированный PHPExcel.

require_once __DIR__ . '/simple-xlsx/simplexlsx.class.php'; // Файл xlsx $xlsx = new SimpleXLSX(__DIR__ . '/test.xlsx'); // Первый лист $sheet = $xlsx->rows(1); foreach ($sheet as $row)

PHP-ExcelReader

require_once __DIR__ . '/phpExcelReader/Excel/reader.php'; $data = new Spreadsheet_Excel_Reader(); $data->setOutputEncoding('UTF-8'); // Файл xls $data->read(__DIR__ . '/test.xls'); // Первый лист $sheet = $data->sheets[0]['cells']; foreach ($sheet as $row)

PHP-Excel-Reader

Форк библиотеки «PHP Excel Reader» с SourceForge предназначенный для вывода таблицы в HTML.

Например файл example.xls выведет следующим образом:

 table.excel < border: 1px solid #CCCCCC; border-collapse:collapse; font-family:sans-serif; font-size:12px; margin: 0 auto; >table.excel thead th, table.excel tbody th < border: 1px solid #CCCCCC; text-align: center; vertical-align:bottom; >table.excel tbody th < text-align:center; width:20px; >table.excel tbody td < vertical-align:bottom; >table.excel tbody td dump(true, true); ?>

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

Nuovo Spreadsheet-Reader

Читает файлы XLSX, XLS, CSV и OpenOffice ods. Для чтения XLS используется предыдущая библиотека php-excel-reader.

require_once __DIR__ . '/spreadsheet-reader/php-excel-reader/excel_reader2.php'; require_once __DIR__ . '/spreadsheet-reader/SpreadsheetReader.php'; // Файл xlsx, xls, csv, ods. $Reader = new SpreadsheetReader(__DIR__ . '/test.xlsx'); // Номер листа. $Reader -> ChangeSheet(0); foreach ($Reader as $Row)

PHP-Spreadsheetreader

Откроет только файлы в формате XML Excel 2004.

$file = __DIR__ . '/test.xml'; require_once __DIR__ . '/SpreadsheetReader/SpreadsheetReader.php'; $reader = new SpreadsheetReader; // Файл xml $sheets = $reader->read(__DIR__ . '/test.xml'); // Выводим Первый лист foreach ($sheets[0] as $row)

Источник

5 Best PHP Excel Libraries in 2023

Excel is a spreadsheet application that allows users to input and manipulate data. It is part of the Microsoft Office suite of programs and is available for both Windows and Mac users. Excel files are saved with a .xlsx extension and can contain code, formulas, charts, and other spreadsheet elements. Excel is a powerful tool that can be used for a variety of purposes, from tracking household expenses to creating complex financial models.

If you’re looking for a library to help you work with Excel files in PHP, then you’re in luck. There are a number of great options to choose from, and we’ve compiled a list of the 5 best ones for you.

5 Best PHP Excel Libraries

phpspreadsheet

PhpSpreadsheet is a library that offers a powerful set of classes for reading and writing various spreadsheet file formats. No matter the spreadsheet application, PhpSpreadsheet has the power to read and write these files in a standalone way.

PHPSpreadsheet is the newest version of PHPExcel. It is not compatible with the old version, but it is much better because it follows stricter rules for code (namespaces, PSR compliance, use of latest PHP features, etc.).

Format	Reading	Writing
Open Document Format/OASIS (.ods)	✓	✓
Office Open XML (.xlsx) Excel 2007 and above	✓	✓
BIFF 8 (.xls) Excel 97 and above	✓	✓
BIFF 5 (.xls) Excel 95	✓
SpreadsheetML (.xml) Excel 2003	✓
Gnumeric	✓
HTML	✓	✓
SYLK	✓
CSV	✓	✓
PDF (using either the TCPDF, Dompdf or mPDF libraries, which need to be installed separately)	✓

require 'vendor/autoload.php'; use PhpOffice\PhpSpreadsheet\Spreadsheet; use PhpOffice\PhpSpreadsheet\Writer\Xlsx; $spreadsheet = new Spreadsheet(); $sheet = $spreadsheet->getActiveSheet(); $sheet->setCellValue('A1', 'Good morning'); $writer = new Xlsx($spreadsheet); $writer->save('my first excel file.xlsx');

Spout

Whether you need to read or write spreadsheet files in a PHP application, Spout is the library for you. It is capable of processing very large files while keeping memory usage low. So whether your file is 500KB or 500MB, Spout can handle it without breaking a sweat.

Your PHP version must be 7.2 or higher to install Spout. Additionally, the php_zip and php_xmlreader extensions must be enabled on your server.

use Box\Spout\Reader\Common\Creator\ReaderEntityFactory; $reader = ReaderEntityFactory::createReaderFromFile('/path/to/file.ext'); $reader->open($filePath); foreach ($reader->getSheetIterator() as $sheet) < foreach ($sheet->getRowIterator() as $row) < // do stuff with the row $cells = $row->getCells(); . > > $reader->close();

PHP Excel Templator

This is a PHP spreadsheet library that lets you export Excel documents from an Excel template. You don’t need to write code, create styles, and so on from scratch when utilizing the extension.

The extension allows you to insert variables into your templates, so you can quickly and easily create formatted tables with data. You can also change the cell styles on the fly, making it easy to get the exact look you want.

use alhimik1986\PhpExcelTemplator\PhpExcelTemplator; require_once('vendor/autoload.php'); // if you don't use framework PhpExcelTemplator::saveToFile('./template.xlsx', './exported_file.xlsx', [ '' => date('d-m-Y'), '' => 'Sales department', ]);

It supports both one-dimension and 2-dimension arrays, so you can quickly and easily create tables with data. You can also apply the same template to multiple sheets, making it easy to create an entire excel file using a single template.

SimpleXLSX

If you need to work with Microsoft Excel 2007 files in your PHP application, then you’ll want to check out the SimpleXLSX library. It makes it easy to read and write data from Excel XLSx files, without requiring any additional extensions.

use Shuchkin\SimpleXLSX; if ( $xlsx = SimpleXLSX::parse(‘book.xlsx’) ) < print_r( $xlsx->rows() ); > else

This makes it a great choice for applications that need to interoperate with Microsoft Office products. The library is also very lightweight, making it a good choice for applications with limited resources.

PHP_XLSXWriter

PHP_XLSXWriter is designed to be lightweight and has minimal memory usage, making it ideal for larger projects. Additionally, it supports currency/date/numeric cell formatting as well as basic cell styling.

The library supports basic features like PHP 5.2.1+ and UTF-8 encoding. It also allows you to format your cells in different ways, including currency and date formats, as well as numeric formatting.

$header = array( 'created'=>'date', 'product_id'=>'integer', 'quantity'=>'#,##0', 'amount'=>'price', 'description'=>'string', 'tax'=>'[$$-1009]#,##0.00;[RED]-[$$-1009]#,##0.00', ); $data = array( array('2015-01-01',873,1,'44.00','misc','=D2*0.05'), array('2015-01-12',324,2,'88.00','none','=D3*0.05'), ); $writer = new XLSXWriter(); $writer->writeSheetHeader('Sheet1', $header ); foreach($data as $row) $writer->writeSheetRow('Sheet1', $row ); $writer->writeToFile('file.xlsx');

You can also style your cells however you want. It can handle spreadsheets with over 100,000 rows without any problems.

Источник