laravel框架:phpoffice/phpspreadsheet/phpexcel 点击按钮,导出/下载 EXCEL.dem...

namespace App\Http\Controllers; use PhpOffice\PhpSpreadsheet\Reader\Xlsx; use PhpOffice\PhpSpreadsheet\Reader\Xls; use PhpOffice\PhpSpreadsheet\IOFactory; use PhpOffice\PhpSpreadsheet\Cell\Coordinate; use PhpOffice\PhpSpreadsheet\Spreadsheet; use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup; use PhpOffice\PhpSpreadsheet\Cell\DataType; use PhpOffice\PhpSpreadsheet\Style\Fill; use PhpOffice\PhpSpreadsheet\Style\Color; use PhpOffice\PhpSpreadsheet\Style\Alignment; use PhpOffice\PhpSpreadsheet\Style\Border; use PhpOffice\PhpSpreadsheet\Style\NumberFormat; class ExcelController extends Controller { const tableHeader0 = [ 'title1', 'title2', 'title3' ]; const tableHeader1 = [ 'title4', 'title5', 'title6' ]; const tableHeader2 = [ 'title7', 'title8', 'title9' ]; public function export(Request $request) { $data = Product::query()->selectRaw('id, url, title')->get(); $tableHeader[0] = ModelEnglandClothing::tableHeader0; $tableHeader[1] = ModelEnglandClothing::tableHeader1; $tableHeader[2] = ModelEnglandClothing::tableHeader2; $this->outdata($tableHeader, $data); } /** * Excel导出，TODO 可继续优化 * * @param array tableHeader 表头 * @param array $data 导出数据 * @return boolean true: 成功; false: 失败; */ public function outdata($tableHeader = [], $data = []) { // 表头需要写多少列 $row_count_table_header = count($tableHeader[0]); // 表头需要写多少行 $column_count_table_header = count($tableHeader); // 数据 $column_count_data = count($data); try { set_time_limit(0); /** @var Spreadsheet $objSpreadsheet */ $objSpreadsheet = app(Spreadsheet::class); /* 设置Excel Sheet */ $activeSheet = $objSpreadsheet->setActiveSheetIndex(0); /* 写表头 三行 *//* 列数超过26,亲测可用 */ for ($i = 0; $i < $row_count_table_header; $i++) { for ($j = 0; $j < $column_count_table_header; $j++) { $pCoordinate = Coordinate::stringFromColumnIndex($i + 1) . '' . ($j + 1); $pValue = $tableHeader[$j][$i]; $activeSheet->setCellValueExplicit($pCoordinate, $pValue, DataType::TYPE_STRING); } } /** 写数据 */ $j = 4; // 第 4 行,开始写起 for ($i = 0; $i < $column_count_data; $i++) { // 第 4 行, 第 1 列 写 id. Excel对应格式: [A4] $pCoordinate = Coordinate::stringFromColumnIndex(1) . '' . $j; $pValue = $data[$i]->id; $activeSheet->setCellValueExplicit($pCoordinate, $pValue, DataType::TYPE_STRING); // 第 4 行, 第 10 列 写 url. Excel对应格式: [J4] $pCoordinate = Coordinate::stringFromColumnIndex(10) . '' . $j; $pValue = $data[$i]->url; $activeSheet->setCellValueExplicit($pCoordinate, $pValue, DataType::TYPE_STRING); // 第 4 行, 第 15 列 写 title. Excel对应格式: [O4] $pCoordinate = Coordinate::stringFromColumnIndex(15) . '' . $j; $pValue = $data[$i]->title; $activeSheet->setCellValueExplicit($pCoordinate, $pValue, DataType::TYPE_STRING); $j++; } /* 直接导出Excel，无需保存到本地，输出07Excel文件 */ header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header( "Content-Disposition:attachment;filename=" . iconv( "utf-8", "GB2312//TRANSLIT", (date('YmdHis') . '.xlsx') // 文件名:当前时间 )); //禁止缓存 header('Cache-Control: max-age=0'); $savePath = 'php://output'; ob_clean(); ob_start(); $objWriter = IOFactory::createWriter($objSpreadsheet, 'Xlsx'); $objWriter->save($savePath); /* 释放内存 */ $objSpreadsheet->disconnectWorksheets(); unset($objSpreadsheet); ob_end_flush(); return true; } catch (\Exception $e) { dd($e->getMessage()); return false; } } }