php PhpSpreadsheet 读写操作表格

<?php
require '../vendor/autoload.php';

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

error_reporting(0);
header('Access-Control-Allow-Origin: *');

const FILE_PATH = '../excel/user-data.xlsx';

$params = $_REQUEST;

try {
    if (isset($params['type']) && $params['type'] == 'download') {
        download();
    } else{
        redAndWrite();
    }

} catch (Exception $e) {
    msg(['code' => 400, 'msg' => '接口错误，请关闭excel文件']);
}



function download()
{
    $file_path = FILE_PATH;
    echo '<a href="' . $file_path . '" style="font-size: 22px;">下载表格</a>';
}

// excel读写操作
function redAndWrite()
{
    $params = $_REQUEST;

    $username = isset($params['username']) ? $params['username'] : '';
    if (empty($username)) {
        msg(['code' => 400, 'msg' => '请填写姓名']);
    }
    $phone = isset($params['phone']) ? $params['phone'] : '';
    if (empty($phone)) {
        msg(['code' => 400, 'msg' => '请填写手机号']);
    }
    $weixin = isset($params['weixin']) ? $params['weixin'] : '';
    if (empty($weixin)) {
        msg(['code' => 400, 'msg' => '请填写微信号']);
    }
    $age = isset($params['age']) ? $params['age'] : '';
    if (empty($age)) {
        msg(['code' => 400, 'msg' => '请填写年龄']);
    }
    $file_path = FILE_PATH;
    try {
        $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($file_path);
    } catch (Exception $e) {
        //不存在则创建
        $spreadsheet = new Spreadsheet();
    }

    $sheet = $spreadsheet->getSheet(0); // 读取第一個工作表

    $highest_row = $sheet->getHighestRow(); // 取得总行数
//$highest_columm = $sheet->getHighestColumn(); // 取得总列数
//$highestColumm = $sheet->getHighestColumn(); // 取得总列数

    if ($highest_row == 1) {
        //没有任何记录，从第三行开始
        $addRow = 3;
    } else {
        $addRow = $highest_row + 1;
    }

// 以下代码行将活动工作表索引设置为第一个工作表：
    $spreadsheet->setActiveSheetIndex(0);

// 您还可以按名称/标题设置活动工作表
//$spreadsheet->setActiveSheetIndexByName('DataSheet');
    $worksheet = $spreadsheet->getActiveSheet();
    for ($i = 3; $i <= $highest_row; $i++) {
        $tmp_phone   = $worksheet->getCell("B" . $i)->getValue();
        if(trim($tmp_phone) == $phone){
            msg(['code' => 400, 'msg' => '手机号已提交']);
            die;
        }
    }
//设置工作表标题名称
    $worksheet->setTitle('客户表单数据');
    $worksheet->setCellValueByColumnAndRow(1, 1, '客户表单数据');
    $worksheet->setCellValueByColumnAndRow(1, 2, '姓名');
    $worksheet->setCellValueByColumnAndRow(2, 2, '电话');
    $worksheet->setCellValueByColumnAndRow(3, 2, '微信');
    $worksheet->setCellValueByColumnAndRow(4, 2, '年龄');
//合并单元格
    $worksheet->mergeCells('A1:D1');
    $styleArray = [
        'font' => [
            'bold' => true
        ],
        'alignment' => [
            'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
        ],
    ];
//设置单元格样式
    $worksheet->getStyle('A1')->applyFromArray($styleArray)->getFont()->setSize(28);
//写入数据
    $worksheet->getCell('A' . $addRow)->setValue($username);
    $worksheet->getCell('B' . $addRow)->setValue($phone);
    $worksheet->getCell('C' . $addRow)->setValue($weixin);
    $worksheet->getCell('D' . $addRow)->setValue($age);
// 也可以
    /*$worksheet->setCellValue('A'.$i,123."\t");*/
    /*$worksheet->setCellValueByColumnAndRow(1, 10, 'PhpSpreadsheet');*/
// 结束 进行导出
    ob_end_clean(); // 清空（擦除）缓冲区并关闭输出缓冲
    ob_start(); // 打开输出控制缓冲
    /*
    $listname = 'test';
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="' . $listname . '.xlsx"');
    header('Cache-Control: max-age=0');*/

    $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
    $writer->save($file_path);//保存文件
//$writer->save('php://output');
    msg(['code' => 200]);
}


function msg($data)
{
    echo json_encode($data, JSON_UNESCAPED_UNICODE);
    die;
}

function pr($data)
{
    print_r($data);
    die;
}