phpspreadsheet案例

<?php
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx as WriterXlsx;

/**
 * php office
 */
class pub_office
{

    private static Spreadsheet $spreadsheet;
    private static string $title = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
    private static array  $center = []; // 居中对齐
    private static array $left = []; // 左对齐
    private static array $right = []; //  右对齐

    public static function _init()
    {
        self::$spreadsheet = new Spreadsheet();
        self::$center  = [
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
            ],
        ];

        self::$left  = [
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_LEFT,
            ],
        ];

        self::$right  = [
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_RIGHT,
            ],
        ];
    }

    /**
     * 列不要太多，A-Z之内
     * @param array $data
     * $data['filename'] = '用户列表.xls'; 如果是保存文件，这里要写保存位置的绝对路径/Applications/MxSrvs/www/infinix/web/excel.xsl
     * $data['title'] = ['name' => '姓名', 'age' => '年龄', 'addtime' => '注册时间']; 文档标题，自动加粗
     * $data['list']  = [['name' => '张三', 'age' => 21,'addtime' => time()], ['name' => '李四', 'age' => 22, 'addtime' => time()]]; 数据
     * $data['download'] = 1; // 是否下载文件，不为空就是下载文件，不然就是保存文件,保存文件需要filename是绝对路径
     * pub_office::writer($data);
     * @return void
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public static function writer(array $data): void
    {
        $sheet = self::$spreadsheet->getActiveSheet();
        $line  = 1;
        $k     = [];

        if(!empty($data['title']) && is_array($data['title']))
        {
            $k = array_keys($data['title']);
            foreach ($k as $_k => $_v)
            {
                $coordinate = self::$title[$_k] . $line;
                // 设置单元内容
                $sheet->setCellValue($coordinate, $data['title'][$_v]);
                // 设置加粗
                $sheet->getStyle($coordinate)->getFont()->setBold(true);
                // 设置居中
                $sheet->getStyle($coordinate)->applyFromArray(self::$center);
            }
        }

        if(!empty($data['list']) && is_array($data['list']))
        {
            foreach ($data['list'] as $row)
            {
                $line++;
                for ($i=0; $i < count($data['title']); $i ++)
                {
                    // 单元格
                    $coordinate = self::$title[$i] . $line;
                    $key        = $k[$i];
                    $value      = $row[$key];

                    // 如果传的时间没有格式化，格式化一下
                    if(in_array($key, ['addtime', 'create_time', 'uptime', 'update_time']) && is_int($value))
                    {
                        $value = date('Y-m-d H:i:s', $value);
                    }

                    // 设置内容
                    $sheet->setCellValue($coordinate, $value);
                    // 设置左对齐
                    $sheet->getStyle($coordinate)->applyFromArray(self::$left);
                }
            }
        }

        $filename = $data['filename'] ?? 'excel.xlsx';
        $writer   = new WriterXlsx(self::$spreadsheet);
        if(!empty($data['download']))
        {
            header('Content-Type:application/vnd.ms-excel');
            header('Content-Disposition:attachment;filename=' . $filename);
            header('Cache-Control:max-age=0');
            $writer = IOFactory::createWriter(self::$spreadsheet, 'Xls');
            $writer->save('php://output');
        }
        else
        {
            $writer->save($filename);
        }
    }

    /**
     * @param $file
     * $file = '/Users/akio/Downloads/用户列表.xls';
     * $data = pub_office::reader($file);
     * array(
     *      [0] => [
     *          [0] => 张三,
     *          [1] => 21,
     *      ],
     *      xxx
     * )
     * @return int|array -1文件不存在
     */
    public static function reader($file): int|array
    {
        if(!is_file($file))
        {
            return -1;
        }

        $spreadsheet = IOFactory::load($file);
        $sheet = $spreadsheet->getActiveSheet();
        return $sheet->toArray();
    }
}

• 使用

//$data['filename'] = '用户列表.xls';
//$data['title'] = ['name' => '姓名', 'age' => '年龄', 'addtime' => '注册时间'];
//$data['list']  = [['name' => '张三', 'age' => 21,'addtime' => time()], ['name' => '李四', 'age' => 22, 'addtime' => time()]];
//$data['download'] = 1;
//pub_office::writer($data);

//$file = '/Users/user/Downloads/用户列表.xls';
//$data = pub_office::reader($file);
//print_r($data);exit;