phpspreadsheet 导出excel简单封装

1.composer 安装

composer require phpoffice/phpspreadsheet 1.8.2

　　因为要兼容php5.6，所以是用的1.8.2版本

2.在公共函数文件中引入头文件

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Style\Alignment;

3.定义封装函数

if (!function_exists('downLoadExcel')){
    /**
     * 导出excel
     * @param $name excel名称
     * @param $titles 标题 [['name'=>'姓名'],['gender'=>'性别']]
     * @param array $data
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    function downLoadExcel($name, $titles, $data=[])
    {
        $count = count($titles);  //计算表头数量
        $spreadsheet = new Spreadsheet();
        $styleArray = [
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER_CONTINUOUS,
                'vertical' => Alignment::VERTICAL_CENTER,
            ],
        ];
        $sheet = $spreadsheet->getActiveSheet();
        for ($i = 65; $i < $count + 65; $i++) {     //数字转字母从65开始，循环设置表头
            $sheet->getStyle(strtoupper(chr($i)))->applyFromArray($styleArray);
            $sheet->getCell(strtoupper(chr($i)).'1')->getStyle()->getFont()->setBold(true);
            $sheet->setCellValue(strtoupper(chr($i)) . '1', key($titles[$i - 65]));
        }
        /*--------------开始从数据库提取信息插入Excel表中------------------*/
        foreach ($data as $key => $item) {             //循环设置单元格：
            //$key+2,因为第一行是表头，所以写到表格时   从第二行开始写
            for ($i = 65; $i < $count + 65; $i++) {     //数字转字母从65开始：
                $sheet->setCellValue(strtoupper(chr($i)) . ($key + 2),$item[key($titles[$i - 65])]);
                $spreadsheet->getActiveSheet()->getColumnDimension(strtoupper(chr($i)))->setAutoSize(true);
            }
        }
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $name . '.xlsx"');
        header('Cache-Control: max-age=0');
        $writer = IOFactory::createWriter($spreadsheet,'Xlsx');
        $writer->save('php://output');
        //删除清空
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
        exit;
    }
}

 4.测试使用

$titles = [['name'=>'姓名'],['gender'=>'性别']];
$data = [
    ['name'=>'小黑','gender'=>'男']
];
downLoadExcel('测试',$titles,$data);

原文地址：https://www.cnblogs.com/trancephp/p/14759982.html