PhpSpreadsheet 保存excel文件

读取excel
blog
安装

composer require phpoffice/phpspreadsheet

例子

<?php
namespace app\index\controller;

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use think\Db;

class Excel
{
    public function outexcel()
    {
        ini_set("memory_limit", "-1");
        $spreadsheet = new Spreadsheet();
        $sheet       = $spreadsheet->getActiveSheet();
        $sheet->setCellValue('A1', 'ID');
        $sheet->setCellValue('B1', '姓名');
        $sheet->setCellValue('C1', '地址');
        $sheet->setCellValue('D1', '手机');
        $sheet->setCellValue('E1', 'P');
        $i     = 1;
        $datas = Db::table('info2')->select();
        foreach ($datas as $data) {
            $i++;
            $sheet->setCellValue('A' . $i, $data['id']);
            $sheet->setCellValue('B' . $i, $data['name']);
            $sheet->setCellValue('C' . $i, $data['address']);
            $sheet->setCellValue('D' . $i, $data['mobile']);
            $sheet->setCellValue('E' . $i, $data['p']);
        }
        // 保存文件
        // $writer = new Xlsx($spreadsheet);
        // $writer->save(ROOT_PATH . "vcf/hello world.xlsx");
        // 下载文件
        $filename = '成绩表.xlsx';
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="' . $filename . '"');
        header('Cache-Control: max-age=0');
        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
    }
}

大容量使用chunk分段,传入&$i关键,chunk不支持mysql视图,因为没有索引

public function outexcel()
    {

        $spreadsheet = new Spreadsheet();
        $sheet       = $spreadsheet->getActiveSheet();
        $sheet->setCellValue('A1', 'ID');
        $sheet->setCellValue('B1', '姓名');
        $sheet->setCellValue('C1', '地址');
        $sheet->setCellValue('D1', '手机');
        $sheet->setCellValue('E1', 'P');
        $i = 1;
        Db::table('info2')->chunk(100, function ($datas) use ($sheet, &$i) {
            foreach ($datas as $data) {
                $i++;
                $sheet->setCellValue('A' . $i, $data['id']);
                $sheet->setCellValue('B' . $i, $data['name']);
                $sheet->setCellValue('C' . $i, $data['address']);
                $sheet->setCellValue('D' . $i, $data['mobile']);
                $sheet->setCellValue('E' . $i, $data['p']);
            }
        });
        $filename = '成绩表.xlsx';
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="' . $filename . '"');
        header('Cache-Control: max-age=0');
        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
    }