[文檔](http://www.hmoore.net/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; } } ```