1、下载composer包
在composer.json的require中加入,引入包文件
"phpoffice/phpspreadsheet": "^1.3",
2、封装类文件到项目中
<?php namespace common\helpers; use Yii; use PhpOffice\PhpSpreadsheet\Spreadsheet; use PhpOffice\PhpSpreadsheet\Cell\DataType; use PhpOffice\PhpSpreadsheet\IOFactory; class ExportHelper { /** * $columnNames = ['用户', '手机号', '反馈类型', '留言', '反馈时间']; * $columnData[] = ['张三','15880xxxxxx','','','']; * param $rowList = ['title' => '反馈' . date('His') . '.xlsx','columnName' => $columnNames,'columnData' => $columnData]; */ public static function exportData($rowList=[]) { // var_dump($rowList);die; set_time_limit(0); // Create new Spreadsheet object $spreadsheet = new Spreadsheet(); // Set document properties $spreadsheet->getProperties()->setCreator('Maarten Balliauw') ->setLastModifiedBy('Maarten Balliauw') ->setTitle('Office 2007 XLSX Test Document') ->setSubject('Office 2007 XLSX Test Document') ->setDescription('Test document for Office 2007 XLSX, generated using PHP classes.') ->setKeywords('office 2007 openxml php') ->setCategory('Test result file'); // // $spreadsheet->setActiveSheetIndex(0); $activeSheet = $spreadsheet->setActiveSheetIndex(0); foreach ($rowList['columnName'] as $key => $value) { $activeSheet->setCellValueByColumnAndRow($key + 1, 1, $value); } $col = 0; foreach ($rowList['columnData'] as $row => $item) { foreach ($item as $col => $value) { Yii::info($value['val']); $activeSheet->getCellByColumnAndRow($col + 1, $row + 2)->setValueExplicit($value['val'], DataType::TYPE_STRING); //$activeSheet->setCellValueByColumnAndRow($col + 1, $row + 2, "".$value['val']); } } $spreadsheet->getActiveSheet()->setTitle('数据'); // Set active sheet index to the first sheet, so Excel opens this as the first sheet $spreadsheet->setActiveSheetIndex(0); $filename = $rowList['title']; // Redirect output to a client’s web browser (Xls) header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment;filename=' . $filename); header('Cache-Control: max-age=0'); // If you're serving to IE 9, then the following may be needed header('Cache-Control: max-age=1'); // If you're serving to IE over SSL, then the following may be needed header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified header('Cache-Control: cache, must-revalidate'); // HTTP/1.1 header('Pragma: public'); // HTTP/1.0 $writer = IOFactory::createWriter($spreadsheet, 'Xlsx'); $writer->save('php://output'); exit; } }
3、使用(参数可以参考封装类的提示)
$rowList = [ 'title' => '反馈' . date('His') . '.xlsx', 'columnName' => $columnNames, 'columnData' => $columnData ]; ExportHelper::exportData($rowList); //记得引入命名空间
这样子,直接调用封装的类后,之后php将数据下载成excel就简单啦