php如何处理excel文件大小,PHP如何切割excel大文件（附完整代码）-php教程

本篇文章次要讲述的是行使phpspreadsheet切割excel年夜文件，具备肯定的参考代价，感兴味的冤家能够理解一下，心愿对你有所启示。

行使phpspreadsheet能够轻松的解析excel文件，然而phpspreadsheet的内存耗费也是比拟年夜的，我试过解析快要5M的纯文字excel内存应用量就会超越php默许的最年夜内存128M。

当然这能够用调理内存巨细的办法来处理，然而正在并发量年夜的时分就比拟风险了。以是明天引见下一种办法，行使phpspreadsheet对excel文件进行切割，这是个拿工夫换空间的办法以是普通对时效性要求低的需要能够应用。

办法：

先放个phpspreadsheet官网提供的一个性能readCell，咱们就能够行使这个性能来进行切割。

起首对excel文件进行预读，次要是猎取一切的工作表和工作表上面的数据行数，这个阶段readCell办法不断前往的都是false，咱们只要要记载readCell出去的工作表及数据行数。

而后就是对猎取到的记载进行剖析，确定每一局部数据需求装几何行原始excel的数据，需求留意的是为了不内容混杂，没有要讲两个工作表的内容切到一同。

最初就是轮回剖析的数据以及再次行使readCell猎取每一局部数据，留意每一次读取文件后都要行使disconnectWorksheets办法清算phpspreadsheet的内存。

通过我本人的测试发现，行使该办法解析5M的excel文件，均匀只要要21M的内存就能够搞定！

代码

namespace CutExcel;

require_once 'PhpSpreadsheet/autoload.php';

/**

* 预读过滤类

* @author wangyelou

* @date 2018-07-30

*/

class MyAheadreadFilter implements \PhpOffice\PhpSpreadsheet\Reader\IReadFilter

{

public $record = array();

private $lastRow = '';

public function readCell($column, $row, $worksheetName = '')

{

if (isset($this->record[$worksheetName]) ) {

if ($this->lastRow != $row) {

$this->record[$worksheetName] ++;

$this->lastRow = $row;

}

} else {

$this->record[$worksheetName] = 1;

$this->lastRow = $row;

}

return false;

}

}

/**

* 解析过滤类

* @author wangyelou

* @date 2018-07-30

*/

class MyreadFilter implements \PhpOffice\PhpSpreadsheet\Reader\IReadFilter

{

public $startRow;

public $endRow;

public $worksheetName;

public function readCell($column, $row, $worksheetName = '')

{

if ($worksheetName == $this->worksheetName && $row >= ($this->startRow+1) && $row <= ($this->endRow+1)) {

return true;

}

return false;

}

}

/**

* 切割类

* @author wangyelou

* @date 2018-07-30

*/

class excelCut

{

public $cutNum = 5;

public $returnType = 'Csv';

public $fileDir = '/tmp/';

public $log;

/**

* 切割字符串

* @param $str

* @return array|bool

*/

public function cutFromStr($str)

{

try {

$filePath = '/tmp/' . time() . mt_rand(1000, 9000) . $this->returnType;

file_put_contents($filePath, $str);

if (file_exists($filePath)) {

$result = $this->cutFromFile($filePath);

unlink($filePath);

return $result;

} else {

throw new Exception('文件写入谬误');

}

} catch (Exception $e) {

$this->log = $e->getMessage();

return false;

}

}

/**

* 切割文件

* @param $file

* @return array|bool

*/

public function cutFromFile($file)

{

try {

$cutRules = $this->readaheadFromFile($file);

$dir = $this->getFileDir($file);

$returnType = $this->returnType ? $this->returnType : 'Csv';

$results = array();

//初始化读

$myFilter = new MyreadFilter();

$inputFileType = \PhpOffice\PhpSpreadsheet\IOFactory::identify($file);

$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);

$reader->setReadDataOnly(true);

$reader->setReadFilter($myFilter);

foreach ($cutRules as $sheetName => $rowIndexRange) {

//读

list($myFilter->startRow, $myFilter->endRow, $myFilter->worksheetName) = $rowIndexRange;

$spreadsheetReader = $reader->load($file);

$sheetData = $spreadsheetReader->setActiveSheetIndexByName($myFilter->worksheetName)->toArray(null, false, false, false);

$realDatas = array_splice($sheetData, $myFilter->startRow, ($myFilter->endRow - $myFilter->startRow + 1));

$spreadsheetReader->disconnectWorksheets();

unset($sheetData);

unset($spreadsheetReader);

//写

$saveFile = $dir . $sheetName . '.' . $returnType;

$spreadsheetWriter = new \PhpOffice\PhpSpreadsheet\Spreadsheet();

foreach ($realDatas as $rowIndex => $row) {

foreach ($row as $colIndex => $col) {

$spreadsheetWriter->getActiveSheet()->setCellValueByColumnAndRow($colIndex+1, $rowIndex+1, $col);

}

}

$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheetWriter, $returnType);

$writer->save($saveFile);

$spreadsheetWriter->disconnectWorksheets();

unset($spreadsheetWriter);

$results[] = $saveFile;

}

return $results;

} catch (Exception $e) {

$this->log = $e->getMessage();

return false;

}

}

/**

* 预读文件

*/

public function readaheadFromFile($file)

{

if (file_exists($file)) {

//猎取统计数据

$myFilter = new MyAheadreadFilter();

$inputFileType = \PhpOffice\PhpSpreadsheet\IOFactory::identify($file);

$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);

$reader->setReadDataOnly(true); //只读数据

$reader->setReadFilter($myFilter);

$spreadsheet = $reader->load($file);

//$sheetData = $spreadsheet->getActiveSheet()->toArray(null, false, false, false);

list($fileName,) = explode('.', basename($file));

$datas = array();

$averageNum = ceil(array_sum($myFilter->record) / $this->cutNum);

foreach ($myFilter->record as $sheetName => $count) {

for ($i=0; $i

$datas[$fileName . '_' . $sheetName . '_' . $i] = array($i*$averageNum, ($i+1)*$averageNum-1, $sheetName);

}

}

return $datas;

} else {

throw new Exception($file . ' not exists');

}

}

/**

* 创立目次

* @param $file

* @return bool|string

*/

protected function getFileDir($file)

{

$baseName = basename($file);

list($name) = explode('.', $baseName);

$fullName = $name .'_'. time() . '_' . mt_rand(1000, 9999);

$path = $this->fileDir . $fullName . '/';

mkdir($path, 0777);

chmod($path, 0777);

if (is_dir($path)) {

return $path;

} else {

$this->log = "mkdir {$path} failed";

return false;

}

}

}

相干教程：PHP视频教程

以上就是PHP若何切割excel年夜文件(附完好代码)的具体内容，更多请存眷资源魔其它相干文章！

本网站(www.ziyuanmo.com)刊载的所有内容,包括文字、图片、音频、视频、软件、程序、以及网页版式设计等均由用户发表。

虚拟收费资源付款后概不退款，请考虑在三后支付！如有侵权可联系邮箱：ziyuanmo@admin.com，会在第一时间解决！

资源猫 » PHP如何切割excel大文件(附完整代码)-php教程

分享到：