大家好，又见面了，我是你们的朋友全栈君。
要读取大量数据，需要 ReadFilter，指定读取范围，下面是我的 ReadFilter 类
代码语言：javascript复制<?php
namespace commonmodels;
class MyExcelReadFilter implements PhpOfficePhpSpreadsheetReaderIReadFilter
{
private $startRowNo; // 如 1
private $endRowNo; // 如 1000
private $startColNo; // 如 1
private $endColNo; // 如 50
public function __construct($startRowNo= 1, $endRowNo = 1000, $startColNo = 1, $endColNo = 50)
{
$this->startRowNo = $startRowNo;
$this->endRowNo = $endRowNo;
$this->startColNo = $startColNo;
$this->endColNo = $endColNo;
}
public function setRows($startRowNo, $rowCount)
{
$this->startRowNo = $startRowNo;
$this->endRowNo = $startRowNo $rowCount - 1;
}
public function setColumns($startColNo, $columnCount)
{
$this->startColNo = $startColNo;
$this->endColNo = $startColNo $columnCount - 1;
}
public function setColumnsByLetter($startColLetter, $endColLetter)
{
$this->startColNo = self::calcColNo($startColLetter);
$this->endColNo = self::calcColNo($endColLetter);
}
/**
* @inheritDoc
*/
public function readCell($column, $row, $worksheetName = '')
{
$colNo = self::calcColNo($column);
if ($row >= $this->startRowNo && $row <= $this->endRowNo &&
$colNo >= $this->startColNo && $colNo <= $this->endColNo) {
return true;
}
return false;
}
/**
* @param $colLetter string Excel表格中的列号，例如 'A'、'Z'、'AA'、'AZ'
* @return float|int 返回整数列号，从 1 开始，即 'A' 列 对应 1 列
*/
public static function calcColNo(string $colLetter)
{
$len = strlen($colLetter);
$colNo = 0; $weight = 1;
for ($i = $len-1; $i >= 0; --$i) {
$colNo = (ord($colLetter[$i]) - ord('A') 1) * $weight;
$weight *= 26;
}
return $colNo;
}
/**
* @param int $colNo 整数列号，从 1 开始
* @return string 返回 Excel表格形式的列号，例如 'A'、'Z'、'AA'、'AZ'，1列 对应 'A'列
*/
public static function calcColLetter(int $colNo)
{
$letters = range('A', 'Z'); // $letters[0] = 'A', $letters[25] = 'Z'
$colLetter = ''; $base = 26;
do {
--$colNo; // 改成基于下标 0
$r = $colNo % $base;
$colLetter = $letters[$r] . $colLetter;
$colNo = intval($colNo/$base); // 必须强制取整
} while ($colNo > 0);
return $colLetter;
}
}参考 (34条消息) 使用phpspreadsheet导出数据时内存溢出处理_x554462的博客-CSDN博客_phpspreadsheet内存溢出x
https://blog.csdn.net/x554462/article/details/89102800写了 FileCache 类 （经过测试发现 FileCache 可以一定程度避免内存溢出，但读取速度比较慢）
代码语言：javascript复制<?php
namespace commonmodels;
use PhpOfficePhpSpreadsheetReaderException;
class MyExcelFileCache implements PsrSimpleCacheCacheInterface
{
const CACHE_LINES = 3000;
private $cacheKeyDict = []; // 键字典
private $cache = []; // 一次性读取的数据装入该缓存，避免短期重复打开文件
private $fileHandleDict = []; // 文件指针字典
private $cacheDir; // 存放文件 cache 的目录
private $filePrefix; // 唯一性前缀保证文件名唯一性
/**
* 递归删除缓存目录
* @param $path string
*/
private function delCacheDir(string $path)
{
if (is_dir($path)) { // 确保 $path 是目录
foreach (scandir($path) as $f) { // 扫描该目录下每个 目录或文件
if (is_dir($path . $f)) { // 是子目录
if ($f != '.' && $f != '..') {
$this->delCacheDir($path . $f . '/'); // 递归删除子目录内部
@rmdir($path. $f . '/'); // 删除子目录本身
}
} else { // 是文件
@unlink($path. $f); // 删除文件
}
}
}
}
/**
* @param $key string
* @return string 根据 key 生成文件名
*/
private function getFilenameByKey($key)
{
$a = explode('.', $key);
$end = array_pop($a);
$dir = $this->cacheDir . implode('_', $a);
if (!is_dir($dir)) {
mkdir($dir, 0777, true);
}
$line = '';
$len = strlen($end);
for ($i = 0; $i < $len; $i ) {
if (is_numeric($end[$i])) $line.= $end[$i];
}
$suf = intval(round($line / self::CACHE_LINES));
return $dir . '/' . $this->filePrefix . $suf;
}
/**
* 根据键换算出文件名，然后到字典中查询文件指针
* @param $key string
* @return mixed|resource 文件指针
* @throws Exception
*/
private function getFileHandleByKey(string $key)
{
$filename = $this->getFilenameByKey($key);
if (!array_key_exists($filename, $this->fileHandleDict)) { // 字典中没有对应文件指针
$fp = fopen($filename, 'w '); // 新建文件
if (!$fp) {
throw new Exception('生成缓存文件失败（MyExcelFileCache）');
}
$this->fileHandleDict[$filename] = $fp; // 向字典添加新建的指针
}
return $this->fileHandleDict[$filename]; // 返回字典中的指针
}
private function removeKeyPrefix($key)
{
return preg_replace('/^phpspreadsheet./', '', $key); // 移除前缀 phpspreadsheet.
}
public function __construct($cacheDir)
{
$this->cacheDir = rtrim($cacheDir, '/') . '/';
$this->filePrefix = uniqid();
}
public function __destruct()
{
$this->clear();
}
/**
* @inheritDoc
*/
public function get($key, $default = null)
{
$key = $this->removeKeyPrefix($key);
if ($this->has($key)) { // 文件缓存的键字典中有该键 （即缓存过的）
$seek = $this->cacheKeyDict[$key]; // 取出在文件缓存中的定位信息
if (array_key_exists($key, $this->cache) && $this->cache[$key]['seek'] == $seek) { // cache数组中是否已经提取过
return $this->cache[$key]['data']; // 已经提取过，直接返回
}
$fp = $this->getFileHandleByKey($key); // 到文件指针字典中查询出文件指针
$this->cache = [];
fseek($fp, 0); // 定位到文件开头
while (!feof($fp)) {
$data = fgets($fp); // 读取一行
$data = json_decode(trim($data), 1); // 解码成关联数组
if ($data['key'] == $key && $data['seek'] == $seek) { // 该行对应这次查询的键，保存到 $default
$default = unserialize($data['data']);
}
$this->cache[$data['key']] = [ // 读取过程中，把所有行都缓存到 cache数组，避免短期反复打开该文件
'data' => unserialize($data['data']),
'seek' => $data['seek']
];
}
}
return $default;
}
/**
* @inheritDoc
*/
public function set($key, $value, $ttl = null)
{
$key = $this->removeKeyPrefix($key);
if ($this->has($key) && $this->get($key) == $value) return true; // 之前已经缓存过
$fp = $this->getFileHandleByKey($key); // 取出文件指针 （内容从未缓存或内容已经变更）
fseek($fp, 0, SEEK_END); // 定位到文件尾部
$seek = ftell($fp); // 获得文件指针位置
$this->cacheKeyDict[$key] = $seek; // 在字典登记该键 （键对应键，值为数据在文件缓存中的起始位置）
fwrite($fp, json_encode([
'key' => $key,
'data' => serialize($value),
'seek' => $seek
]).PHP_EOL); // 在文件缓存写入数据 （json编码的关联数组）
unset($value); // “释放”已经写入缓存的变量
return true;
}
/**
* @inheritDoc
*/
public function delete($key)
{
$key = $this->removeKeyPrefix($key);
unset($this->cacheKeyDict[$key]); // 从键字典释放该键
return true;
}
/**
* @inheritDoc
*/
public function clear()
{
$this->cacheKeyDict = []; // 键字典清空
foreach ($this->fileHandleDict as $fileHandle) { // 文件指针字典中所有文件关闭
isset($fileHandle) && fclose($fileHandle);
}
$this->delCacheDir($this->cacheDir); // 递归删除缓存目录
return true;
}
/**
* @inheritDoc
*/
public function getMultiple($keys, $default = null)
{
$result = [];
foreach ($keys as $key) $result[$key] = $this->get($key, $default);
return $result;
}
/**
* @inheritDoc
*/
public function setMultiple($values, $ttl = null)
{
foreach ($values as $key => $value) {
$this->set($key, $value);
}
return true;
}
/**
* @inheritDoc
*/
public function deleteMultiple($keys)
{
foreach ($keys as $key) $this->delete($key);
return true;
}
/**
* @inheritDoc
*/
public function has($key)
{
$key = $this->removeKeyPrefix($key);
return array_key_exists($key, $this->cacheKeyDict); // 缓存键字典中是否有该键
}
}然后是自己的 3种 读取方式的类（直接读取、trunk方式循环读取、带FileCache读取）。这个类不完全通用，因为是针对特定问题处理的。
代码语言：javascript复制<?php
namespace commonmodels;
use PhpOfficePhpSpreadsheetIOFactory;
use PhpOfficePhpSpreadsheetReaderXlsx;
use PhpOfficePhpSpreadsheetSettings;
class MyExcelUtility
{
const CHUNK_SIZE = 250;
public static function excelDateToDate(int $nDate, $format = 'Y-m-d')
{
return date($format, ($nDate-25569) * 86400); // Excel bug， 1900 年是闰年
}
public static function excelDateToUnixStamp(int $nDate)
{
return ($nDate-25569) * 86400;
}
/**
* 获得第一个 sheet 总的行数和列数
* @param $reader Xlsx IOFactory 创建的实例（根据后缀名创建不同类的实例）
* @param $filePath string
* @return array ['totalRows' => .., 'totalColumns' => ...]
*/
public static function getSheetInfo($reader, $filePath)
{
$info = $reader->listWorkSheetInfo($filePath);
return ['totalRows' => intval($info[0]['totalRows']), 'totalColumns' => intval($info[0]['totalColumns'])];
}
public static function read($filePath, $maxRowNo, $maxColNo, $inputFileType = 'Xlsx', $calcColNos = [3, 4])
{
$reader = IOFactory::createReader($inputFileType); // 创建指定类型的 reader
$reader->setReadDataOnly(true); // 非只读似乎有问题 Xlsx.php 884行 Illegal offset type 警告错误 github 已fix
$filterSubnet = new MyExcelReadFilter(1, $maxRowNo, 1, $maxColNo);
$reader->setReadFilter($filterSubnet); // 设定读取范围
Settings::setLibXmlLoaderOptions(LIBXML_DTDLOAD|LIBXML_DTDATTR|LIBXML_PARSEHUGE); // 添加 PARSEHUGE 似乎不会溢出了 2021.10.11
$spreadsheet = $reader->load($filePath);
$result = [];
for ($rowNo = 1; $rowNo <= $maxRowNo; $rowNo ) {
for ($colNo = 1; $colNo <= $maxColNo; $colNo ) {
if (in_array($colNo, $calcColNos))
$result[$rowNo][$colNo] = $spreadsheet->getSheet(0)
->getCell(MyExcelReadFilter::calcColLetter($colNo) . $rowNo)
->getCalculatedValue(); // 都当作计算单元格读取可能比较慢，所以这里对这些列单独处理
else
$result[$rowNo][$colNo] = $spreadsheet->getSheet(0)
->getCell(MyExcelReadFilter::calcColLetter($colNo) . $rowNo)
->getValue();
}
}
$spreadsheet->__destruct();
$spreadsheet = null;
unset($spreadsheet); // 及时释放 $spreadsheet 对象有助于防止内存溢出
$reader = null;
unset($reader);
return $result;
}
public static function readWithTrunk($filePath, $maxRowNo, $maxColNo, $inputFileType = 'Xlsx',
$calcColNos = [3, 4], $chunkSize = self::CHUNK_SIZE,
$nonEmptyCheck = [10, 2])
{
$result = [];
$endLoop = false;
for ($startRowNo = 1; $startRowNo <= $maxColNo; $startRowNo = $chunkSize) {
$reader = IOFactory::createReader($inputFileType);
$reader->setReadDataOnly(true);
$filterSubnet = new MyExcelReadFilter();
$reader->setReadFilter($filterSubnet);
$filterSubnet->setColumnsByLetter('A', 'AZ');
$filterSubnet->setRows($startRowNo, $chunkSize);
$spreadsheet = $reader->load($filePath);
for ($rowNo = 1; $rowNo <= $maxRowNo; $rowNo ) {
for ($colNo = 1; $colNo <= $maxColNo; $colNo ) {
if (in_array($colNo, $calcColNos))
$result[$rowNo][$colNo] = $spreadsheet->getSheet(0)
->getCell(MyExcelReadFilter::calcColLetter($colNo) . $rowNo)
->getCalculatedValue(); // 都当作计算单元格读取可能比较慢，所以这里对这些列单独处理
else
$result[$rowNo][$colNo] = $spreadsheet->getSheet(0)
->getCell(MyExcelReadFilter::calcColLetter($colNo) . $rowNo)
->getValue();
if ($rowNo >= $nonEmptyCheck[0] && $colNo == $nonEmptyCheck[1] && empty($result[$rowNo][$colNo])) { // 最长的非空列已经是空值
$endLoop = true;
goto endLoop1;
}
}
}
endLoop1:
$spreadsheet->__destruct();
$spreadsheet = null;
unset($spreadsheet); // 及时释放 $spreadsheet 对象有助于防止内存溢出
$reader = null;
unset($reader);
if ($endLoop) break; // 不用继续批量读取
}
return $result;
}
public static function readWithFileCache($filePath, $maxRowNo, $maxColNo, $inputFileType = 'Xlsx',
$calcColNos = [3, 4], $cacheDir = '/tmp/excel_cache')
{
Settings::setCache(new MyExcelFileCache($cacheDir));
$reader = IOFactory::createReader($inputFileType); // 创建指定类型的 reader
$reader->setReadDataOnly(true);
$filterSubnet = new MyExcelReadFilter(1, $maxRowNo, 1, $maxColNo);
$reader->setReadFilter($filterSubnet); // 设定读取范围
$spreadsheet = $reader->load($filePath);
$result = [];
for ($rowNo = 1; $rowNo <= $maxRowNo; $rowNo ) {
for ($colNo = 1; $colNo <= $maxColNo; $colNo ) {
if (in_array($colNo, $calcColNos))
$result[$rowNo][$colNo] = $spreadsheet->getSheet(0)
->getCell(MyExcelReadFilter::calcColLetter($colNo) . $rowNo)
->getCalculatedValue(); // 都当作计算单元格读取可能比较慢，所以这里对这些列单独处理
else
$result[$rowNo][$colNo] = $spreadsheet->getSheet(0)
->getCell(MyExcelReadFilter::calcColLetter($colNo) . $rowNo)
->getValue();
}
}
// $spreadsheet->__destruct(); // 不注释这句会造成 能读取完，但出现段错误
// $spreadsheet = null;
// unset($spreadsheet);
// $reader = null;
// unset($reader);
return $result;
}
public static function getHeaderRanger($result, $maxHeaderRows, $maxColNo) // 表头不超过 $maxHeaderRows 行 $maxColNo 列
{
$lastColNo = $maxColNo; $longestRowNo = -1;
while ($lastColNo >= 1) {
for ($rowNo = 1; $rowNo <= $maxHeaderRows; $rowNo ) {
if (!empty($result[$rowNo][$lastColNo])) {
$longestRowNo = $rowNo; break;
}
}
if ($longestRowNo > 0) break;
--$lastColNo;
}
return ['longestRowNo' => $longestRowNo, 'lastColNo' => $lastColNo];
}
}测试代码
代码语言：javascript复制public function actionXlsx1()
{
$xlsxFile = User::getFilePath('test1.xlsx', '', true, '@backend/runtime');
$start = microtime(true);
$result = MyExcelUtility::read($xlsxFile, 500, 50);
$end = microtime(true);
for ($i = 1; $i <= 5; $i ) {
for ($j = 1; $j <= 20; $j ) {
echo $result[$i][$j]."t";
}
echo PHP_EOL;
}
echo number_format($end - $start, 10). " sec for readn"; // 1000段错误，无法得到结果 500 -> 29.4952
}
public function actionXlsx2()
{
$xlsxFile = User::getFilePath('test1.xlsx', '', true, '@backend/runtime');
$start = microtime(true);
$result = MyExcelUtility::readWithTrunk($xlsxFile, 1000, 50, 'Xlsx', [3,4], 400);
$end = microtime(true);
for ($i = 1; $i <= 5; $i ) {
for ($j = 1; $j <= 20; $j ) {
echo $result[$i][$j]."t";
}
echo PHP_EOL;
}
echo number_format($end - $start, 10). " sec for read with trunkn"; // 1000/400->40.4761 1000/240->39.2139 1000/500->39.5506
}
public function actionXlsx3()
{
$xlsxFile = User::getFilePath('test1.xlsx', '', true, '@backend/runtime');
$start = microtime(true);
$result = MyExcelUtility::readWithFileCache($xlsxFile, 1000, 50);
$end = microtime(true);
for ($i = 1; $i <= 5; $i ) {
for ($j = 1; $j <= 20; $j ) {
echo $result[$i][$j]."t";
}
echo PHP_EOL;
}
echo number_format($end - $start, 10). " sec for read with file cachen"; // 51.2165
}总结：
在3个readXxx函数中，如果没有 $reader->setReadDataOnly(true) ，即设置为只读，在 Xlsx.php 文件的 884行 会出现 Illegal offset type 信息的php警告错误
仔细核对了github上的代码，发现已经fix了，哈哈，就是 $comment[‘authorId’] 是一个对象，作为数组的键会出这个错误，github上已经前面加了 (int)强制类型转换了
对于带FileCache的读取函数，读取完成后不能使用 $spreadsheet->__destruct(); 去释放内存，如果这么做，会出现段错误 （内存越界之类）。FileCache实例对象是被动被reader使用的，即读取前设置而已（观察缓存文件发现每个缓存文件是几十MB）。
直接读取方式还是比较容易出现段错误的，我的例子中，虚拟机ubuntu18.04 内存8GB php7.2 控制台命令方式 50列 1000行就出现段错误，500行不会。500行耗时29.4952秒。
Trunk方式读取，也就是循环读取释放的方式，参考Reading large File increases Execution time & Memory (E.g file with 500000 records) · Issue #629 · PHPOffice/PhpSpreadsheet · GitHub
https://github.com/PHPOffice/PhpSpreadsheet/issues/629
，不会出现段错误，而且在3种里面似乎效率最高。测试中，1000行分400行一次，耗时40.4761秒，分240行一次，39.2139秒，分500行一次，39.5506秒，基本上无差别，这似乎表明，时间并不是耗费在重复打开解压同一个文件上（Xlsx等带x的Office文件都是zip文件），而是耗费在解析这些单元格上。（10000行，400行一次，耗时473.7826秒）
带FileCache读取，1000行耗时51.2165秒（10000行耗时598.8875秒，一旦开启LIBXML_PARSEHUGE 非常非常慢，但不开启对20000行会报错），没有速度优势，不过可以避免内存耗尽。另外，如果需要纵向的大量数据的公式计算，也只能用这种方式。
————————————————————————————————-
2021.10.11 读取超大表格时（19444×50），不管哪种读取方式，都会出现 PHP Warning ‘yiibaseErrorException’ with message ‘simplexml_load_string(): Entity: line 2: parser error : internal error: Huge input lookup’
in /home/zime/PhpstormProjects/gxlq_pig/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Reader/Xlsx.php:623 这表明 phpspreadsheet 内部用 simplexml_load_string 函数来读取 xml，查阅 php.net 上该函数
PHP: simplexml_load_string – Manual PHP: 预定义常量 – Manual
可以发现，它有 options 参数，其中的 LIBXML_BIGLINES 和 LIBXML_PARSEHUGE 比较吸引人，我们的表格不超过60000行（从 Excel 2007 开始，”大网格”将每个工作表的最大行数从 65,536 增加到超过 100 万，将列数从 256 (IV) 增加到 16,384 (XFD)），所以，第一个参数不是关键，关键是第二个参数。直接去修改源码不现实，如何把参数传入呢？
Documentation (phpoffice.github.io)
答案是Settings::setLibXmlLoaderOptions ，默认的参数可以用对应 get 方法获得 （默认 12=4 8，即 LIBXML_DTDLOAD|LIBXML_DTDATTR）。经试验，使用 LIBXML_PARSEHUGE 选项就不会出现该错误了。 simplexml_load_string 内部使用了 libxml/libxml2 库（C语言写的解析xml的库），查看
Libxml2 set of examples (xmlsoft.org)
上的一些信息，虽然还没有细究，感觉使用 LIBXML_PARSEHUGE 选项，内部很可能也是类似 trunk 读取方式的。初步理解，就是 LIBXML_PARSEHUGE 选项下，trunk 方式读取xml构建节点树，而不管是哪种读取模式，都要先构建xml节点树，
在我们的trunk模式下，如果使用了huge模式，那么trunk size应该尽可能大，共20000行，估测 20000行（50列）trunk size 大约 120秒，内存大约 1.6GB，改成10000行一次大约165秒，内存1.36GB。这里内存包含了其他读写的。
—————————————————-2021.10.13
读取上万行的 Excel Xlsx文件，还是放弃 PHP 吧，性能太差，菜刀就是“菜”刀，我最后还是靠golang搞成csv来解决问题，读取2万行，每一行至少执行三个INSERT数据库操作，不超过5分钟，实际读取Excel不超过半分钟。
golang 利用 execelize 包读取成 csv （很简单的程序）
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/192571.html原文链接：https://javaforall.cn
