使用PhpSpreadsheet导出和导入Excel

<?php
/**
 * 导出Excel
 * 备注：需要PHP版本大于或等于7.2.0。
 *
 * @param array $head 表头
 * @param array $data 数据
 * @param string $filename 文件名（不含文件扩展名）
 * @return void
 */
function export_excel($head, $data, $filename)
{
    require_once __DIR__ . '/phpspreadsheet/autoload.php';

    $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();

    $key1 = ord('A'); // 设置表头
    $key2 = ord('@'); //   超过26列会报错的解决方案

    $worksheetIndex = 0;

    // 设置单元格水平居中和垂直居中
    try {
        $spreadsheet->getDefaultStyle()->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER); // 水平居中
        $spreadsheet->getDefaultStyle()->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER); // 垂直居中
    } catch (\PhpOffice\PhpSpreadsheet\Exception $exception) {
        exit($exception->getMessage());
    }


    // 输出表头
    foreach ($head as $value) {
        // 超过26列会报错的解决方案
        if ($key1 > ord('Z')) {
            $key2++;
            $key1 = ord('A');
            $colum = chr($key2) . chr($key1); //超过26个字母时才会启用
        } else if ($key2 >= ord('A')) {
            $colum = chr($key2) . chr($key1);
        } else {
            $colum = chr($key1);
        }

        $key1++;

        // 写入表头
        try {
            $spreadsheet->setActiveSheetIndex($worksheetIndex)->setCellValue("{$colum}1", $value);
        } catch (\PhpOffice\PhpSpreadsheet\Exception $exception) {
            exit($exception->getMessage());
        }

        // 自适应宽度
        $spreadsheet->getActiveSheet()->getColumnDimension($colum)->setWidth(strlen($value) + 5);
    }

    // 输出数据
    $column = 2;
    $objActSheet = $spreadsheet->getActiveSheet();
    foreach ($data as $row) {
        $span1 = ord('A');
        $span2 = ord('@');
        foreach ($row as $value) {
            // 超过26列会报错的解决方案
            if ($span1 > ord('Z')) {
                $span2++;
                $span1 = ord('A');
                $span = chr($span2) . chr($span1); //超过26个字母时才会启用
            } else if ($span2 >= ord('A')) {
                $span = chr($span2) . chr($span1);
            } else {
                $span = chr($span1);
            }

            // 写入数据
            $objActSheet->setCellValueExplicit("{$span}{$column}", $value, \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING2);
            $span1++;
        }
        $column++;
    }

    try {
        $spreadsheet->setActiveSheetIndex($worksheetIndex);
    } catch (\PhpOffice\PhpSpreadsheet\Exception $exception) {
        exit($exception->getMessage());
    }

    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header("Content-Disposition: attachment;filename={$filename}.xlsx");
    header('Cache-Control: max-age=0');

    try {
        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
    } catch (\PhpOffice\PhpSpreadsheet\Writer\Exception $exception) {
        exit($exception->getMessage());
    }

    $writer->save('php://output');

    exit();
}

$head = [
    'name' => '姓名',
    'gender' => '性别',
    'age' => '年龄',
    'mobile' => '手机号码',
    'id' => '身份证号码',
];

$data = [
    [
        'name' => '张三',
        'gender' => '男',
        'age' => 18,
        'mobile' => '18833333333',
        'id' => '430802198211276545',
    ],

    [
        'name' => '李四',
        'gender' => '女',
        'age' => 17,
        'mobile' => '18844444444',
        'id' => '44022419830411629X',
    ],

    [
        'name' => '王五',
        'gender' => '男',
        'age' => 19,
        'mobile' => '18855555555',
        'id' => '610626198501166495',
    ],
];

$filename = '导出Excel演示 - ' . date('YmdHis');

export_excel($head, $data, $filename);

<?php
/**
 * 导入Excel
 * 备注：需要PHP版本大于或等于7.2.0。
 *
 * @param string $file Excel文件硬盘路径
 * @return array Excel数据
 */
function import_excel($file)
{
    require_once __DIR__ . '/phpspreadsheet/autoload.php';

    $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($file);

    $activeSheet = $spreadsheet->getActiveSheet();

    $data = [];

    foreach ($activeSheet->getRowIterator() as $key => $value) {
        // 第一行是表头，直接跳过本轮循环
        if ($key === 1) {
            continue;
        }

        $cellIterator = $value->getCellIterator();

        $cellIterator->setIterateOnlyExistingCells(false);

        $row = [];
        foreach ($cellIterator as $cell) {
            $row[] = $cell->getValue();
        }
        $data[] = $row;
    }

    return $data;
}

$file = __DIR__ . '/demo.xlsx';
$data = import_excel($file);
foreach ($data as list($name, $gender, $age, $mobile, $id)) {
    echo "{$name}  {$gender}  {$age}  {$mobile}  {$id}" . PHP_EOL;
}
//================= 输出结果 =================//
// 张三  男  18  18833333333  430802198211276545
// 李四  女  17  18844444444  44022419830411629X
// 王五  男  19  18855555555  610626198501166495