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

class ExportService
{
   /**
     * 多sheet的导出
     * @author chaihao <158533752@qq.com>
     * @param [array] $data_array
     */
    public  function xtExport(array $dataArray): string
    {
        $spreadsheet = new Spreadsheet();
        foreach ($dataArray as $key => $data) {
            $this->opSheet($spreadsheet, $key, $data);
        }
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $name . '.xlsx"');
        header('Cache-Control: max-age=0');
        // $writer = new Xlsx($spreadsheet);
        // $writer->save('php://output');
        // //删除清空：
        // $spreadsheet->disconnectWorksheets();
        // unset($spreadsheet);
        // exit;

       if (!file_exists(BASE_PATH . '/exportFile/')) {
            mkdir(BASE_PATH . '/exportFile/', 0777, true);
        }
        $path = BASE_PATH . '/exportFile/' . time() . uniqid() . '.xlsx';
        // 导出Excel文件
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save($path);
        return $path;
    }


    /**
     *  设定EXCEL表头
     * @param integer $num
     * @return array
     * @author: chaihao <158533752@qq.com>
     */
    public function getExcelHeader($num = 100)
    {
        $col = range('A', 'Z');
        $newCol = [];

        for ($j = 0; $j < ceil($num / 26); $j++) {
            for ($i = 0; $i < 26; $i++) {
                if (count($newCol) >= $num) {
                    break 2;
                }
                $newCol[] = ($j == 0 ? '' : $col[$j - 1]) . $col[$i];
            }
        }
        return $newCol;
    }


    /**
     * 处理多sheet
     * @param [type] $spreadsheet
     * @param [type] $n
     * @param [type] $data
     */
    public function opSheet($spreadsheet, $n, $data)
    {
        $spreadsheet->createSheet(); //创建sheet
        $objActSheet = $spreadsheet->setActiveSheetIndex($n); //设置当前的活动sheet
        // $keys = $data['rows'][0]; //这是你的数据键名
        // $count = count($keys); //计算你所占的列数
        $count = count($data['rows'][0]); //计算你所占的列数
        $infoNum = count($data['info'] ?? []); //求k-v值的所占行数, 多表头占用行
        $infoStart = $infoNum + 1; //下面的详细信息的开始行数
        $cellName = $this->getExcelHeader($count);

        $sheet = $spreadsheet->getActiveSheet($n)->setTitle($data['title']); //设置sheet的名称

        // $spreadsheet->getActiveSheet($n)->mergeCells('A1:' . $cellName[$count - 1] . '1'); //合并单元格
        // $spreadsheet->getActiveSheet($n)->getStyle('A1')->getFont()->setSize(20); //设置title的字体大小
        $spreadsheet->getActiveSheet($n)->getStyle($infoStart)->getFont()->setBold(true); //标题栏加粗
        // $objActSheet->setCellValue('A1', $data['title']); //设置每个sheet中的名称title


        foreach ($data['rows'] as $key => $item) {
            //循环设置单元格：
            for ($i = 0; $i < $count; $i++) {
                $sheet->setCellValue($cellName[$i] . ($key + $infoStart), $item[$i]);
                $spreadsheet->getActiveSheet($n)->getColumnDimension($cellName[$i])->setWidth(20); //固定列宽
            }
        }
    }
}

    /**
     * 导出数据
     * @return \Psr\Http\Message\ResponseInterface
     */
    public function exportLink($data = [])
    {
        if ($data) {
            //  设置 sheet 名
            $export['title'] = '链接';
            $export['rows'] = [];
            foreach ($data as $item) {
                $result = [
                    '标题' => $item['title'] ?? '',
                    '链接' => $item['url'],
                    '开始时间' => $item['start_date'],
                    '结束时间' => $item['end_date'],
                    'IP (总)' => $item['ip'],
                ];
                if (empty($export['rows'])) {
                    // 设置表头
                    $export['rows'][] = array_keys($result);
                }
                // 内容
                $export['rows'][] = array_values($result);
            }
            // 每个$export_sheet 都是 一个 sheet 表格
           //  [
           //   $export_sheet_1,
           //   $export_sheet_2,
           //   $export_sheet_3
           //   ...
           //  ]
            $path = (new ExportService())->xtExport([$export]);
            return $this->response->download($path);
        } else {
            return $this->failed('数据为空');
        }
    }

多表头处理