alexandrainst/php-xlsx-fast-editor

PHP 库,用于在现有的 Excel 工作簿上执行基本的快速读写操作

2.2.0 2024-07-02 12:53 UTC

This package is auto-updated.

Last update: 2024-09-10 09:07:07 UTC


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 AlapetiteAlexandra Institute 编写,2023。许可 GNU LGPL