PhpSpreadsheet官網

PhpSpreadsheet安裝

composer require phpoffice/phpspreadsheet

使用composer安裝時一定要下載php對應的版本，下載之前使用php -v檢查當前php版本

簡單使用

<?php
require 'vendor/autoload.php';use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;// 創建Spreadsheet對象
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();// 動態表頭
$headers = ['ID', '姓名', '性別', '職業', '新增字段', '新增字段2', '新增字段3', '新增字段4', '新增字段5', '新增字段6', '新增字段7', '新增字段8', '新增字段9', '新增字段10', '新增字段11', '新增字段12', '新增字段13', '新增字段14', '新增字段15', '新增字段16', '新增字段17', '新增字段18', '新增字段19', '新增字段20', '新增字段21', '新增字段22', '新增字段23', '新增字段24', '新增字段25', '新增字段26', '新增字段27', '新增字段28', '新增字段29', '新增字段30'];// 動態數據
$data = [[1, '李云龍', '男', '軍人', '1427141544485854854485', '字段1', '字段2', '字段3', '字段4', '字段5', '字段6', '字段7', '字段8', '字段9', '字段10', '字段11', '字段12', '字段13', '字段14', '字段15', '字段16', '字段17', '字段18', '字段19', '字段20', '字段21', '字段22', '字段23', '字段24', '字段25', '字段26', '字段27', '字段28', '字段29', '字段30'],[2, '蘇乞兒', '男', '乞丐', '值2', '字段1', '字段2', '字段3', '字段4', '字段5', '字段6', '字段7', '字段8', '字段9', '字段10', '字段11', '字段12', '字段13', '字段14', '字段15', '字段16', '字段17', '字段18', '字段19', '字段20', '字段21', '字段22', '字段23', '字段24', '字段25', '字段26', '字段27', '字段28', '字段29', '字段30'],[3, '周星馳', '男', '導演', '值3', '字段1', '字段2', '字段3', '字段4', '字段5', '字段6', '字段7', '字段8', '字段9', '字段10', '字段11', '字段12', '字段13', '字段14', '字段15', '字段16', '字段17', '字段18', '字段19', '字段20', '字段21', '字段22', '字段23', '字段24', '字段25', '字段26', '字段27', '字段28', '字段29', '字段30'],[4, '林允兒', '女', '演員', '值4', '字段1', '字段2', '字段3', '字段4', '字段5', '字段6', '字段7', '字段8', '字段9', '字段10', '字段11', '字段12', '字段13', '字段14', '字段15', '字段16', '字段17', '字段18', '字段19', '字段20', '字段21', '字段22', '字段23', '字段24', '字段25', '字段26', '字段27', '字段28', '字段29', '字段30'],
];// 設置表頭和數據
$allData = array_merge([$headers], $data);
$sheet->fromArray($allData, null, 'A1');// 設置表頭樣式
$styleArray = ['alignment' => ['horizontal' => 'center','vertical' => 'center',],'font' => ['name' => '宋體',// 'bold' => true,// 'size' => 22]
];$headerRange = 'A1:' . chr(ord('A') + count($headers) - 1) . '1';
$sheet->getStyle($headerRange)->getNumberFormat()  // 先獲取NumberFormat對象->setFormatCode(NumberFormat::FORMAT_TEXT)->applyFromArray($styleArray);// 保存文件
$writer = new Xlsx($spreadsheet);
$writer->save('dynamic_example.xlsx');?>

在thinkphp項目中使用

// 如果將該擴展直接安裝到thinkphp6項目中提示找不到Class 'PhpOffice\\PhpSpreadsheet\\Spreadsheet' not found，將該擴展放置項目根目錄的extend文件夾中（參考下圖）,隨后打開下行代碼
// require_once app()->getRootPath() . 'extend/PhpSpreadsheet/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Cell\DataType;public function export_data($data){// 獲取所有要查詢的字段和名稱$header_arr = [];$fields_arr = [];foreach($data['export_data'] as $key=>$val){array_push($header_arr, $val['label']);array_push($fields_arr, $val['field']);} // 文件存儲目錄$public = app()->getRootPath().'public/';$path = 'uploads/export_data/';if(!file_exists($path)){mkdir($path, 0777);}// 文件名$res_file = $file_name . '_all.xlsx';$finalFile = $public . $path . $res_file;$currentRow = 1; // 當前寫入行$spreadsheet = new Spreadsheet();$sheet = $spreadsheet->getActiveSheet();// 3. 計算列范圍 - 安全方法$lastColumn = Coordinate::stringFromColumnIndex(count($header_arr));$headerRange = 'A:' . $lastColumn;// $sheet->setTitle('Sheet1');// 如果是第一次運行，創建新文件并寫入頭部if (!file_exists($finalFile)) {$spreadsheet = new Spreadsheet();$sheet = $spreadsheet->getActiveSheet();$sheet->getStyle($headerRange)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_TEXT);// 寫入頭部$sheet->fromArray([$header_arr], null, 'A1');$currentRow = 2;// 設置表頭樣式$styleArray = ['alignment' => ['horizontal' => 'center','vertical' => 'center',],// 'font' => [// 'name' => '宋體',// 'bold' => true,// 'size' => 22// ]];// $headerRange = 'A1:' . chr(ord('A') + count($header_arr) - 1) . '1';$sheet->getStyle($headerRange)->applyFromArray($styleArray);// 保存文件$writer = new Xlsx($spreadsheet);$writer->save($finalFile);// echo "創建新文件并寫入頭部\n";}// 分批導出并追加數據$totalBatches = 3; // 假設總共3批for ($batch = 1; $batch <= $totalBatches; $batch++) {// 加載現有文件$reader = IOFactory::createReader('Xlsx');$spreadsheet = $reader->load($finalFile);$sheet = $spreadsheet->getActiveSheet();// 獲取當前最后一行$currentRow = $sheet->getHighestRow() + 1;// 生成測試數據 - 實際應用中從數據庫獲取$data = [];$perBatch = 10000;$start = ($batch - 1) * $perBatch + 1;$end = $batch * $perBatch;for ($i = $start; $i <= $end; $i++) {$data[] = [$i,"姓名{$i}",($i % 2) ? '男' : '女',"職業{$i}"];}// 追加數據$stringContent = array_map(function($row) use ($sheet, $currentRow) {return array_map(function($cell) {return (string)$cell; // 只返回值，不返回類型}, $row);}, $data);// 先設置數據$sheet->fromArray($stringContent, null, "A{$currentRow}");// 部分列強制文本格式$forceTextColumns = ['CI', 'AQ', 'AH']; // 需要強制文本的列for ($row = $currentRow; $row <= $highestRow; $row++) {foreach ($forceTextColumns as $col) {$cell = $sheet->getCell($col.$row);$cell->setValueExplicit($cell->getValue(),DataType::TYPE_STRING);$sheet->getStyle($col.$row)->getNumberFormat()->setFormatCode('@');}}// 保存文件$writer = new Xlsx($spreadsheet);$writer->save($finalFile);echo "批次 {$batch} 數據已追加到 {$finalFile} (行 {$currentRow}-" . ($currentRow + count($data) - 1) . ")\n";}echo "所有數據已導出到 {$finalFile}\n";
}