加载大文件 · PHP笔记

[文档](https://ihavenolimitations.xyz/a173512/php_note/1690644) [使用教程](https://xlswriter-docs.viest.me/zh-cn/an-zhuang) 下载dell [GitHub Release](https://github.com/viest/php-ext-xlswriter/releases) [PECL](https://pecl.php.net/package/xlswriter) 例子： ### 导出文件 ``` $config = ['path' => '/home/viest'];//如果路径下有相同命名的文件，新文件会覆盖老文件 $excel = new \Vtiful\Kernel\Excel($config); // fileName 会自动创建一个工作表，你可以自定义该工作表名称，工作表名称为可选参数 $filePath = $excel->fileName('tutorial01.xlsx', 'sheet1') ->header(['Item', 'Cost']) ->data([ ['Rent', 1000], ['Gas', 100], ['Food', 300], ['Gym', 50], ]) ->output(); ``` ### **加载excel大文件** ``` $config = ['path' => './']; $excel = new \Vtiful\Kernel\Excel($config); // 打开示例文件 $sheetList = $excel->openFile('test.xlsx') ->sheetList(); dump_export($sheetList);//array ( 0 => 'Sheet1', 1 => 'Sheet2', 2 => 'Sheet3', ) foreach ($sheetList as $sheetName) { if ($sheetName=="Sheet1") { // 通过工作表名称获取工作表数据 $sheetData = $excel ->openSheet($sheetName) ->getSheetData(); dump_export($sheetData); break; } } ``` ## **PhpSpreadsheet分批加载（实测没有用）** 分批加载没批加载2条数据 ``` $inputFileName = '1.xlsx'; $chunkSize = 2; // 设置每次读取的行数 $data = []; $startRow = 1; $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx'); $reader->setReadDataOnly(true); // 只读取数据，不读取格式 $spreadsheet = $reader->load($inputFileName); do { $worksheet = $spreadsheet->getActiveSheet(); // $end_Column = $worksheet->getHighestColumn();//BT $highestRow = $worksheet->getHighestRow(); $endRow = min($startRow + $chunkSize - 1, $highestRow); $rows = $worksheet->rangeToArray('A' . $startRow . ':BT' . $endRow, null, true, true); // $rows = $worksheet->toArray("无",true,true,false,false); $data = array_merge($data, $rows); $startRow += $chunkSize; } while ($startRow <= $highestRow); ``` 还可以用过滤器返回指定的行和列 ``` $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx'); $reader->setReadDataOnly(true); // 只读取数据，不读取格式 # 打开文件、载入excel表格 $spreadsheet = $reader->load('1.xlsx'); # 获取活动工作薄 $sheet = $spreadsheet->getActiveSheet(); // # 获取单元格值和坐标 // $cellC1 = $sheet->getCell('B2'); // echo '值: ', $cellC1->getValue(),PHP_EOL; // echo '坐标: ', $cellC1->getCoordinate(),PHP_EOL;//B2 # 获取总列数（即最大的列坐标） $total_Column = $sheet->getHighestColumn(); // dump($total_Column); # 列数改为数字显示//B $total_ColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($total_Column); // dump( $total_ColumnIndex);//指定列的索引 # 获取总行数 $total_row = $sheet->getHighestRow(); // dump($total_row);//3 // 定义每个“区块”要读取的行数 $chunkSize = 2; // 设置每次读取的行数 $total_row = $sheet->getHighestRow(); //创建我们的读取筛选器的新实例 $chunkFilter = new ChunkReadFilter(); //使用读取过滤器 $reader->setReadFilter($chunkFilter); /** 循环以“区块大小”块读取我们的工作表 **/ for ($startRow = 2; $startRow <= $total_row; $startRow += $chunkSize) { // dump($startRow); // dump($chunkSize); /** 告诉读取筛选器我们想要此迭代的行 **/ $chunkFilter->setRows($startRow,$chunkSize); /** 只加载与过滤器匹配的行 **/ $spreadsheet = $reader->load('1.xlsx'); # 获取活动工作薄 $worksheet = $spreadsheet->getActiveSheet(); // $rows = $worksheet->toArray(); $endRow = min($startRow + $chunkSize - 1, $total_row); $rows = $worksheet->rangeToArray('A' . $startRow . ':BT' . $endRow, null, true, true); dump($rows); } /** Define a Read Filter class implementing \PhpOffice\PhpSpreadsheet\Reader\IReadFilter */ class ChunkReadFilter implements \PhpOffice\PhpSpreadsheet\Reader\IReadFilter { public $startRow = 0; public $endRow = 0; /** 设置要读取的行的列表 */ public function setRows($startRow, $chunkSize) { $this->startRow = $startRow; $this->endRow = $startRow + $chunkSize; } public function readCell($columnAddress, $row, $worksheetName = '') { // 仅读取标题行和指定的行 if (($row == 1) || ($row >= $this->startRow && $row < $this->endRow)) { return true; } return false; } } ```