project-manager/api/app/common/util/PhpOffice.php

<?php
/**
 * Created by PhpStorm.
 * User: yckj-yf7
 * Date: 2021/9/26
 * Time: 11:14
 */

namespace app\common\util;

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class PhpOffice
{
    public static function export()
    {
        #获取对象
        $spreadsheet = new Spreadsheet();
        #获取工作簿
        $sheet = $spreadsheet->getActiveSheet();

        $QuestionAnswerRecordModel = new \app\common\model\Order();
        $pageList = $QuestionAnswerRecordModel->analysis();
        $list = $pageList['data'];
        $arr = [];
        #问题
        $title = '问卷提交统计Excel';
        $sheet->setCellValue(2,1,$title);
        $sheet->getStyle('B1')
            ->getFont()->setBold(true)
            ->setName('宋体')
            ->setSize(20);

        $styleArray = [
            'borders' => [
                'left' => [
                    'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN //细边框
                ],
                'right' => [
                    'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN //细边框
                ],
                'top' => [
                    'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN //细边框
                ],
                'bottom' => [
                    'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN //细边框
                ],
            ],
            'alignment' => [
                'horizontal' =>  \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            ],
        ];

        $styleArrayTitle = [
            'alignment' => [
                'horizontal' =>  \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            ],
        ];

        $sheet->getColumnDimension('B')->setWidth(70);
        $sheet->getStyle('B1')->applyFromArray($styleArrayTitle);

        $i = 2;
        foreach ($list['PaperQuestion'] as $key => $value) {
//            $sheet->setCellValueByColumnAndRow(2,$i+1,'选项');
//            $sheet->setCellValueByColumnAndRow(8,$i+1,'小计');
//            $sheet->setCellValueByColumnAndRow(9,$i+1,'比例');
//                 $sheet->mergeCells('')
            $sheet->setCellValue(2,$i+1,$value['content']);
            $sheet->getStyle('B'.($i+1).':'.'F'.($i+1))->applyFromArray($styleArray);
            $sheet->getStyle('B'.($i+1))->getFont()->setBold(true)->setName('宋体')->setSize(15);
            foreach ($value['paperQuestionAnswer'] as $key2=>$value2){
                $sheet->setCellValue(2,$i+2,$value2['content']);
                $sheet->setCellValue(5,$i+2,$value2['num']);
//                $sheet->setCellValueByColumnAndRow(6,$i+2,$value2['avg'].'%');
                $sheet->getStyle('B'.($i+2).':'.'F'.($i+2))->applyFromArray($styleArray);
                $i++;
            }
            $i+=3;
        }

        self::exportBinary($spreadsheet);
//        $this->exportFile($spreadsheet);
    }

    /**
     *   ##导出一 直接生成xlsx文件
     */
    public static function exportFile($spreadsheet)
    {
        $writer = new Xlsx($spreadsheet);
        $root = $_SERVER['DOCUMENT_ROOT'];
        $filePath = $root . '/excel/';
        if (!is_dir($filePath)) {
            mkdir($filePath, 0777, true);
        }
        ##文件地址
        $file =  time() . '.xlsx';
        $fileName = $filePath . $file;
        $writer->save($fileName);
        $data = [
            'fileName'=>$fileName,
            'url'=>request()->host().'/excel/'.$file,
        ];
        return $data;
    }

    /**
     * 导出二 直接返回二进制数据
     */
    public static function exportBinary($spreadsheet)
    {
        header('Content-Type:application/vnd.ms-excel');
        header('Content-Disposition:attachment;filename=' . time() . '.xls');
        header('Cache-Control:max-age=0');
        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xls');
        $writer->save('php://output');
        exit;
    }


}