php

<?php
declare(strict_types=1);
use PHPExcel;
use PHPExcel_Cell_DataType;
use PHPExcel_Exception;
use PHPExcel_Writer_Excel2007;
use PHPExcel_Writer_Exception;

class Excel
{
    //列标识数组
    CONST CELL_NAME = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z',
        'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ',
        'BA', 'BB', 'BC', 'BD', 'BE', 'BF', 'BG', 'BH', 'BI', 'BJ', 'BK', 'BL', 'BM', 'BN', 'BO', 'BP', 'BQ', 'BR', 'BS', 'BT', 'BU', 'BV', 'BW', 'BX', 'BY', 'BZ',
        'DA','DB', 'DC', 'DD', 'DE', 'DF', 'DG', 'DH', 'DI', 'DJ', 'DK', 'DL', 'DM', 'DN', 'DO', 'DP', 'DQ', 'DR', 'DS', 'DT', 'DU', 'DV', 'DW', 'DX', 'DY', 'DZ',
        'EA', 'EB', 'EC', 'ED', 'EE', 'EF', 'EG', 'EH', 'EI', 'EJ', 'EK', 'EL', 'EM', 'EN', 'EO', 'EP', 'EQ', 'ER', 'ES', 'ET', 'EU', 'EV', 'EW', 'EX', 'EY', 'EZ',
        'FA', 'FB', 'FC', 'FD', 'FE', 'FF', 'FG', 'FH', 'FI', 'FJ', 'FK', 'FL', 'FM', 'FN', 'FO', 'FP', 'FQ', 'FR', 'FS', 'FT', 'FU', 'FV', 'FW', 'FX', 'FY', 'FZ',
        'GA', 'GB', 'GC', 'GD', 'GE', 'GF', 'GG', 'GH', 'GI', 'GJ', 'GK', 'GL', 'GM', 'GN', 'GO', 'GP', 'GQ', 'GR', 'GS', 'GT', 'GU', 'GV', 'GW', 'GX', 'GY', 'GZ',
        'HA', 'HB', 'HC', 'HD', 'HE', 'HF', 'HG', 'HH', 'HI', 'HJ', 'HK', 'HL', 'HM', 'HN', 'HO', 'HP', 'HQ', 'HR', 'HS', 'HT', 'HU', 'HV', 'HW', 'HX', 'HY', 'HZ'
    ];

    /**
     * 指定数组数据Excel导出[设置多个sheet]
     * @param $data
     * @param $fields
     * @param string $sheetTitleName
     * @param string $sheetDataName
     * @param string $exportName
     * @param string $downloadName
     * @return string
     * @throws PHPExcel_Exception
     * @throws PHPExcel_Writer_Exception
     * @throws Exception
     */
    public static function exportMoreFile(&$data, $fields, $sheetTitleName = 'sheetName',$sheetDataName = 'items',$exportName = "", $downloadName = ""): string
    {
        if (empty($data)) {
            return "";
        }

        $phpexcel = new PHPExcel();
        $write = new PHPExcel_Writer_Excel2007($phpexcel);//2007格式

        /*sheet工作区*/
        $sheetIndex = 0;
        foreach ($data as $keyFirst => $valFirst){ /* 循环sheet */
            /* 设置当前的sheet */
            if($sheetIndex > 0){
                $phpexcel->createSheet();
            }

            $phpexcel -> setActiveSheetIndex($sheetIndex);
            $sheet = $phpexcel -> getActiveSheet();

            /* sheet标题 */
            $sheetTitle = mb_substr($valFirst[$sheetTitleName],0,28);
            $sheet -> setTitle($sheetTitle);

            $key = 0;
            // 设置字段起始写入行
            $column = 1;
            foreach ($fields as $k => $v) {
                $sheet->setCellValue((self::CELL_NAME)[$key] . $column, $v);
                $key++;
            }

            // 设置数据起始写入行
            $column = $column + 1;
            foreach ($valFirst[$sheetDataName] as $keySecond => $valSecond) { /* 循环每页数据 */
                $kk = 0;//设置起始写入列
                foreach ($valSecond as $k => $v) {/* 循环每行数据 */
                    // 获取字段对应的数据
                    $sheet->setCellValue((self::CELL_NAME)[$kk] . $column, $v);
                    $kk++;//列数+1
                }

                $column++;//行数+1
            }

            $sheetIndex ++;
        }

        if (empty($exportName)) {
            $exportName = "data_" . date("YmdHis") . randstr(4, 6) . ".xlsx";
        }

        $fullName = self::exportFolder() . $exportName;
        if (empty($fullName)) {
            throw new Exception("导出文件生成失败", 201);
        }

        $write->save($fullName);
        $phpexcel -> disconnectWorksheets();

        unset($phpexcel);
        unset($sheet);
        unset($write);

        // 删除临时文件
        unlink($fullName);
        return fullName ?? "";
    }

    /**
     * 导出目录
     * @return string
     * @throws Exception
     */
    public static function exportFolder()
    {
        $folder = Env::get("root_path")
            . DIRECTORY_SEPARATOR . "data"
            . DIRECTORY_SEPARATOR . "excel"
            . DIRECTORY_SEPARATOR;
        if (!file_exists($folder)) {
            $mkRes = mkdir($folder);
            if (!$mkRes) {
                throw new Exception("没有权限创建导出文件暂存目录", 201);
            }
        }

        return $folder;
    }

}