composer require phpoffice/phpspreadsheet
导出excl
<?php class Export { // 表格坐标 private $cellIndex = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ']; // 默认配置 public $config = [ 'bold' => true, // 加粗 'size' => 12, // 文字大小 'column' => 4, // 设置列数 'title' => '默认导出', // 表格标题 'name' => '特抱抱', // 文件名 'type' => 'Xls', // 导出格式 ]; // 默认表头 public $tableHeader = [ 'php', 'vue', 'java', 'go', ]; // 默认数据 public $tableDefaultData = [ [ '天下第一', 'Vue牛逼', 'java牛逼', 'go牛逼', ], ]; /** * @param array $tableHeader 表头 * @param array $tableDefaultData 需要导出数据 */ public function __construct($tableHeader, $tableDefaultData) { $this->tableHeader = $tableHeader; $this->config['column'] = count($tableHeader); $this->tableDefaultData = $tableDefaultData; } /** * 创建表格 createTable */ public function createTable($tablname) { $spreadsheet = new Spreadsheet(); $worksheet = $spreadsheet->getActiveSheet(); //居中 $styleArray = [ 'alignment' => [ 'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER, ], ]; // 设置基本属性 $worksheet->setTitle($this->config['title']); $worksheet->getStyle($this->getColumn())->applyFromArray($styleArray) ->getFont() ->setBold($this->config['bold']) ->setName('Verdana') ->setSize($this->config['size']); foreach ($this->tableHeader as $index => $name) { $worksheet->setCellValue($this->cellIndex[$index] . '1', $name); } $baseRow = 2; foreach ($this->tableDefaultData as $index => $data) { $i = $index + $baseRow; for ($k = 0; $k <= $this->config['column'] - 1; $k++) { $item = $data[$k]; $worksheet->setCellValue($this->cellIndex[$k] . $i, ' ' . $item); // 中文设置表格宽度 if (preg_match("/[\x7f-\xff]/", $data[$k])) { $worksheet->getColumnDimension($this->cellIndex[$k])->setWidth(strlen($item)); } else { // 非中文自动设置宽度 $worksheet->getColumnDimension($this->cellIndex[$k])->setAutoSize(true); } } } $worksheet->calculateColumnWidths(); self::downloadExcel($spreadsheet, $tablname, 'Xls'); } /** * 文件下载 downloadExcel * * @param data $spreadsheet * @param string $filename * @param string $format * @return void */ private function downloadExcel($spreadsheet, $filename, $format) { // $format只能为 Xlsx 或 Xls if ($format == 'Xlsx') { header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); } elseif ($format == 'Xls') { header('Content-Type: application/vnd.ms-excel'); } header("Content-Disposition: attachment;filename=" . urlencode($filename) . '.' . strtolower($format)); //. date('Y-m-d') header('Cache-Control: max-age=0'); $objWriter = IOFactory::createWriter($spreadsheet, $format); $objWriter->save('php://output'); exit; } private function getColumn($row = 1) { $index = $this->cellIndex[$this->config['column']]; return 'A' . $row . ':' . $index . $row; } private function autoFitColumnWidthToContent($sheet, $fromCol, $toCol) { if (empty($toCol)) { //not defined the last column, set it the max one $toCol = $sheet->getColumnDimension($sheet->getHighestColumn())->getColumnIndex(); } for ($i = $fromCol; $i <= $toCol; $i++) { $sheet->getColumnDimension($i)->setAutoSize(true); } $sheet->calculateColumnWidths(); } } 调用方法 $data = []; foreach ($items as $key) { $temp[0] = $key->id; $temp[1] = $key->admin_id; $temp[2] = $key->name; $temp[3] = $key->sex; $temp[4] = $key->card; $temp[5] = $key->phone; $temp[6] = $key->created_at; array_push($data, $temp); } $export = new Export( [ '编号', '上传用户', '患者姓名', '性别', '身份证', '联系电话', '创建时间' ], $data ); echo $export->createTable("问卷评估");