alexandrainst / php-xlsx-fast-editor
PHP 库,用于在现有的 Excel 工作簿上执行基本的快速读写操作
2.2.0
2024-07-02 12:53 UTC
Requires (Dev)
- ext-simplexml: *
- ext-tokenizer: *
- ext-xmlwriter: *
- phpstan/phpstan: ^1.11
- phpstan/phpstan-strict-rules: ^1.6
- squizlabs/php_codesniffer: ^3.10
README
PHP 库,用于在现有的 Excel 工作簿上执行基本的快速读写操作。
它使用快速简单的低级 ZIP & XML 操作处理 XLSX/XLSM 文档(Microsoft Excel 2007+,Office Open XML 工作簿),无需任何库依赖,同时最大限度地减少意外副作用。
理由
如果您需要高级的 Excel 文档操作,例如处理样式,请检查 PhpSpreadsheet 库(以前称为 PHPExcel),但对于简单地从现有的 Excel 工作簿中读取和写入基本值,PhpSpreadsheet
的速度慢了十倍以上,并且有破坏某些不受支持的 Excel 功能(如某些注释和图表)的风险。
还有库可以从头开始创建新的 Excel 文档或仅读取一些值,但没有明显的用于编辑的库。
php-xlsx-fast-editor
解决了快速读取、写入和编辑现有 Excel 文档的需求,同时降低了破坏任何东西的风险。
请注意,要创建新文档,您可以只需提供空白或模板 Excel 文档作为输入。
使用
通过 Composer
composer require alexandrainst/php-xlsx-fast-editor
或手动
require 'vendor/alexandrainst/XlsxFastEditor/autoload.php';
示例
<?php use alexandrainst\XlsxFastEditor\XlsxFastEditor; use alexandrainst\XlsxFastEditor\XlsxFastEditorException; try { $xlsxFastEditor = new XlsxFastEditor('test.xlsx'); // Workbook / worksheet methods $nbWorksheets = $xlsxFastEditor->getWorksheetCount(); $worksheetName = $xlsxFastEditor->getWorksheetName(1); $worksheetId1 = $xlsxFastEditor->getWorksheetNumber('Sheet1'); // If you want to force Excel to recalculate formulas on next load: $xlsxFastEditor->setFullCalcOnLoad($worksheetId1, true); // Direct read/write access $fx = $xlsxFastEditor->readFormula($worksheetId1, 'A1'); $f = $xlsxFastEditor->readFloat($worksheetId1, 'B2'); $i = $xlsxFastEditor->readInt($worksheetId1, 'C3'); $s = $xlsxFastEditor->readString($worksheetId1, 'D4'); $h = $xlsxFastEditor->readHyperlink($worksheetId1, 'B4'); $d = $xlsxFastEditor->readDateTime($worksheetId1, 'F4'); $xlsxFastEditor->deleteRow($worksheetId1, 5); $xlsxFastEditor->writeFormula($worksheetId1, 'A1', '=B2*3'); $xlsxFastEditor->writeFloat($worksheetId1, 'B2', 3.14); $xlsxFastEditor->writeInt($worksheetId1, 'C3', 13); $xlsxFastEditor->writeString($worksheetId1, 'D4', 'Hello'); $xlsxFastEditor->writeHyperlink($worksheetId1, 'B4', 'https://example.net/'); // Only for cells with an existing hyperlink // Read as array $table = $xlsxFastEditor->readArray($worksheetId1); $s = $table['B'][2]; $table = $xlsxFastEditor->readArrayWithHeaders($worksheetId1); $s = $table['columnName'][2]; // Regex search & replace operating globally on all the worksheets: $xlsxFastEditor->textReplace('/Hello/i', 'World'); // Navigation methods for existing rows $row = $xlsxFastEditor->getFirstRow($worksheetId1); $row = $xlsxFastEditor->getRow($worksheetId1, 2); $row = $row->getPreviousRow(); $row = $row->getNextRow(); $row = $xlsxFastEditor->getLastRow($worksheetId1); $xlsxFastEditor->getHighestColumnName($worksheetId1); // Methods for rows $rowNumber = $row->number(); $cell = $row->getCellOrNull('D2'); $cell = $row->getCellOrNull('D'); // Navigation methods for existing cells $cell = $row->getFirstCell(); $cell = $cell->getPreviousCell(); $cell = $cell->getNextCell(); $cell = $row->getLastCell(); // Methods for cells $cellName = $cell->name(); $columnName = $cell->column(); $fx = $cell->readFormula(); $f = $cell->readFloat(); $i = $cell->readInt(); $s = $cell->readString(); $h = $cell->readHyperlink(); $d = $cell->readDateTime(); $cell->writeFormula('=B2*3'); $cell->writeFloat(3.14); $cell->writeInt(13); $cell->writeString('Hello'); $cell->writeHyperlink('https://example.net/'); // Only for cells with an existing hyperlink // Iterators for existing rows and cells foreach ($xlsxFastEditor->rowsIterator($worksheetId1) as $row) { foreach ($row->cellsIterator() as $cell) { // $cell->... } } $xlsxFastEditor->save(); // If you do not want to save, call `close()` instead: // $xlsxFastEditor->close(); } catch (XlsxFastEditorException $xlsxe) { die($xlsxe->getMessage()); }
提示
- ℹ️ 迭代器(
$x->rowsIterator()
,$row->cellsIterator()
)和导航方法($cell->getNextCell()
等)比通过直接访问($x->readString($worksheetId, 'A1')
等),通过名称($row->getCellOrNull('A')
)或通过编号($x->getRow($worksheetId, 1)
)访问多个行或单元格要快得多。
要求
PHP 7.4+,带有 ZIP 和 XML 扩展。有关详细信息,请参阅 composer.json
。
致谢
最初由 Alexandre Alapetite 为 Alexandra Institute 编写,2023。许可 GNU LGPL。