使用PHPSpreadsheet读取Excel文件导入MariaDB的语法问题

核心问题修复点 #

• 前端表单必须添加enctype="multipart/form-data"属性，否则文件无法正常上传到后端
• 读取上传文件时错误使用了客户端文件名$_FILES['file']['name']，PHP上传文件的临时存储路径为$_FILES['file']['tmp_name']，替换后才能正确读取文件内容
• getcalculatedValue()方法名拼写错误，正确为驼峰格式getCalculatedValue()
• 你允许xls、xlsx两种格式上传，但代码写死了创建xlsx阅读器，需根据扩展名动态匹配阅读器
• 现有SQL写法存在注入风险，拼接变量的方式浪费了PDO预处理的安全能力，改为占位符写法更安全
• 跳转前输出print_r、成功/报错提示会导致header跳转失效，需注释或删除前置输出内容

修正后代码 #

<?php
session_start();

require_once "db_connect.php";
require 'PhpSpreadsheet/vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\IOFactory;

// 先校验上传是否出错
if (isset($_FILES["file"]) && $_FILES["file"]["error"] == UPLOAD_ERR_OK) {
    $allowed_extension = array('xls', 'xlsx');
    $file_array = explode(".", $_FILES['file']['name']);
    $file_extension = strtolower(end($file_array));
    if (in_array($file_extension, $allowed_extension)) {

        // 根据扩展名创建对应阅读器
        $reader = IOFactory::createReader($file_extension);
        // 加载临时文件而不是客户端文件名
        $spreadsheet = $reader->load($_FILES['file']['tmp_name']);
        $activeSheet = $spreadsheet->getActiveSheet();
        
        $data = [];
        $data[1] = $activeSheet->getCell('K10')->getCalculatedValue();
        $data[2] = $activeSheet->getCell('K4')->getCalculatedValue();
        $data[3] = $activeSheet->getCell('K5')->getCalculatedValue();
        $data[4] = $activeSheet->getCell('K6')->getCalculatedValue();
        $data[5] = $activeSheet->getCell('K7')->getCalculatedValue();
        $data[6] = $activeSheet->getCell('K8')->getCalculatedValue();
        $data[7] = $activeSheet->getCell('K9')->getCalculatedValue();
        $data[8] = $activeSheet->getCell('N9')->getCalculatedValue();
        $data[9] = $activeSheet->getCell('Y4')->getCalculatedValue();
        $data[10] = $activeSheet->getCell('U5')->getCalculatedValue();
        $data[11] = $activeSheet->getCell('AA9')->getCalculatedValue();
        $data[12] = $activeSheet->getCell('AA10')->getCalculatedValue();
        $data[13] = $activeSheet->getCell('AA11')->getCalculatedValue();
        $data[14] = "1";
        $data[15] = $activeSheet->getCell('S7')->getCalculatedValue();
        $data[16] = $activeSheet->getCell('S8')->getCalculatedValue();
        $data[17] = $activeSheet->getCell('K15')->getCalculatedValue();
        $data[18] = $activeSheet->getCell('N15')->getCalculatedValue();
        $data[19] = $activeSheet->getCell('P15')->getCalculatedValue();
        $data[20] = $activeSheet->getCell('S15')->getCalculatedValue();
        $data[21] = $activeSheet->getCell('U15')->getCalculatedValue();
        $data[22] = $activeSheet->getCell('X15')->getCalculatedValue();
        $data[23] = $activeSheet->getCell('Z15')->getCalculatedValue();
        $data[24] = $activeSheet->getCell('AC15')->getCalculatedValue();
        $data[25] = $activeSheet->getCell('K14')->getCalculatedValue();
        $data[26] = $activeSheet->getCell('P14')->getCalculatedValue();
        $data[27] = $activeSheet->getCell('U14')->getCalculatedValue();
        $data[28] = $activeSheet->getCell('Z14')->getCalculatedValue();

        // 用占位符替代拼接变量，避免注入风险
        $placeholders = rtrim(str_repeat('?,', count($data)), ',');
        $sql_insert = "INSERT INTO auftrag (...) VALUES ($placeholders)";
   
        // 调试时再打开下面的输出，正常运行时注释避免跳转失败
        // print_r($data);
        try {
            $stmt = $pdo->prepare($sql_insert);
            $stmt->execute($data);
            // echo "OK<br>";
        } catch (Exception $ex) {
            // 调试时打开，正常运行可以把错误存入session后在home页展示
            // echo $ex->getMessage() . "<br>";
            exit;
        }
    }
}

header("location:home.php");
exit;
?>

内容的提问来源于stack exchange，提问作者alb