ThinkPHP 数据导出EXCEL表格

composer require phpoffice/phpspreadsheet

• PHP版本: ^7.2 || ^8.0

https://packagist.org/packages/phpoffice/phpspreadsheet

文档：https://phpspreadsheet.readthedocs.io/en/latest/#getting-started.

Excel导出

tip: 修改数组，将$array[0]修改为表头标题

$array = [
    [
        'id' => 'ID',
        'name' => '姓名',
        'age' => '年龄'
    ],
    [
        'id' => 1,
        'name' => 'r1',
        'age' => 10
    ],
    [
        'id' => 2,
        'name' => 'r2',
        'age' => 10
    ],
    [
        'id' => 3,
        'name' => 'r3',
        'age' => 12
    ],
    [
        'id' => 4,
        'name' => 'r4',
        'age' => 9
    ],
    [
        'id' => 5,
        'name' => 'r5',
        'age' => 8
    ],
];

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

$word = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';

$num = 1;
foreach ($array as $k => $v) {
    $start = 0;
    foreach ($v as $k1 => $v1) {
        if ($start < 26) {
            $sheet->setCellValue($word[$start] . $num, $v1);
        } else {
            $rw = (int)(($start + 1) / 26);
            $sheet->setCellValue($word[$rw - 1] . $word[$start - ($rw * 26)] . $num, $v1);
        }
        $start += 1;
    }
    $num++;
}

$writer = new Xlsx($spreadsheet);
$writer->save('./xlsx/hello world.xlsx');

提示：以上代码仅支持导出每行数据不超过（26 * 26 + 26 = 702   A - ZZ）列的数据

由于时间关系，我仅用了29列数据测试，如果你的数据超过26列，请在导出后检查数据

导出文件在根目录xlsx目录下

补充（18：25）：

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

if (!function_exists('export_data')) {
    /**
     * 数据导出
     * @param array $list 数据列表
     * @param string $file_name 文件名
     * @param string $suffix 文件后缀: xlsx/xls
     * @param string $save_path 保存路径
     * @return void
     */
    function export_data (array $list, $file_name = '', $suffix = 'xlsx', $save_path = '') {
        try {
            $spreadsheet = new Spreadsheet();
            $sheet = $spreadsheet->getActiveSheet();

            $word = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';

            $num = 1;
            foreach ($list as $k => $v) {
                $start = 0;
                foreach ($v as $k1 => $v1) {
                    if ($start < 26) {
                        $sheet->setCellValue($word[$start] . $num, $v1);
                    } else {
                        $rw = (int)(($start + 1) / 26);
                        $sheet->setCellValue($word[$rw - 1] . $word[$start - ($rw * 26)] . $num, $v1);
                    }
                    $start += 1;
                }
                $num++;
            }

            // 处理后缀
            if ($suffix[0] === '.') {
                $suffix = substr($suffix, 1);
            }
            $suffix = strtolower($suffix);

            if ($save_path) {
                // 处理路径
                if (substr($save_path, -1, 1) !== '/') {
                    $save_path .= '/';
                }
                if (!file_exists('.' . $save_path)) {
                    mkdir('.' . $save_path, 0777, true);
                }
                switch ($suffix) {
                    case 'xlsx':
                        (new Xlsx($spreadsheet))->save('.' . $save_path . $file_name . '.xlsx');
                        break;
                    case 'xls':
                        (new Xls($spreadsheet))->save('.' . $save_path . $file_name . '.xls');
                        break;
                }
            } else {
                switch ($suffix) {
                    case 'xlsx':
                        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
                        break;
                    case 'xls':
                        header('Content-Type: application/vnd.ms-excel');
                        break;
                    default:
                        exit('导出失败, 未定义的类型:' . $suffix);
                }
                header("Content-Disposition: attachment;filename=" . $file_name . '.' . $suffix);
                header('Cache-Control: max-age=0'); //禁止缓存

                $objWriter = IOFactory::createWriter($spreadsheet, ucfirst($suffix));
                $objWriter->save('php://output');
            }
        } catch (\Exception $e) {
            exit('导出失败: ' . $e->getMessage());
        }
    }
}

使用：

// $array[0]是表头（标题）
$array = [
    [
        'id' => 'ID',
        'name' => '姓名',
        'age' => '年龄',
        'grade' => '年级',
    ],
    [
        'id' => 1,
        'name' => '小王',
        'age' => 4,
        'grade' => 1,
    ],
    [
        'id' => 2,
        'name' => '小李',
        'age' => 3,
        'grade' => 1,
    ],
    [
        'id' => 3,
        'name' => '小赵',
        'age' => 5,
        'grade' => 1,
    ],
    [
        'id' => 4,
        'name' => '小二',
        'age' => 3,
        'grade' => 1,
    ],
    [
        'id' => 5,
        'name' => '小丽',
        'age' => 5,
        'grade' => 1
    ],
];

export_data($array, 'test', '.xlsl', '/xlsx/1/'); // 保存文件：根目录/xlsx/1/

export_data($array, 'test', '.xlsl'); // 下载文件