简介一份用于将xls文件导入MySQL数据库的PHP程序包适合有PHP基础、需要批量把Excel表格数据写入数据库的开发者。程序支持自定义数据库名、表名与字段对应关系能够读取xls格式文件并按表头匹配写入对中文内容做了完整处理保存时统一采用UTF-8编码可避免乱码与手工录入的低效。压缩包内共四个文件包含三个PHP脚本和一个inc辅助文件分别承担文件上传、Excel解析、数据插入及底层读取功能整体仅十三KB结构精简便于直接部署到PHP环境或改造复用。目前已有二百七十六人学习下载。资源虽小却覆盖了从上传、解析到入库的完整流程并配有注意事项能帮助读者快速实现表格数据迁移也可作为学习PHP处理Excel格式的入门样例。1. 把 xls 文件导进 MySQL别再让运营同事拿着 Excel 一个个粘贴把 xls 文件导进 MySQL在 PHP 项目里是个反复出现的需求。运营或者业务那边经常丢来一个几十兆的 Excel 表格说“帮我导进库今晚就要”。如果你还停留在手工打开 Excel、复制几列、再到 navicat 里粘贴的流程数据量超过一千行就会开始怀疑人生。这个项目做的其实就一件事用 PHP 程序读 .xls 文件把每一行数据整理成数组再批量写进 MySQL顺带解决日期变成数字、中文乱码、重复导入这类老问题。适合正在给公司写内部工具的后端开发也适合刚接触 PHPExcel/PhpSpreadsheet 的学生拿来当完整范例。读完全文你不仅能跑通一条导入链路还能避开我在生产环境里踩过的几个真坑。2. xls 导入 MySQL 的底层逻辑它不是一个 csv 文件也不是一个没格式的表格2.1 真正的 .xls 在磁盘上长什么样一个 OLE2 容器很多新手拿到 xls 文件的第一反应是“能不能用 fgetcsv 直接读”。答案是读不了。CSV 本质是纯文本用逗号分隔字段而 .xls 是一种二进制格式早期 Excel 97-2003 用的 xls 文件内部是 OLE2 复合文档结构像一个压缩包一样把多个流打包在一起。这些流里存的不是“了解”而是 BIFF8 记录单元格的坐标、字体、公式、样式全都以二进制块存储。你直接按文本方式读取出来的几乎全是乱码。所以处理 xls 的正确姿势是让专门解析这种二进制结构的库去干活。PHP 生态里最成熟的就是 PhpSpreadsheet它是 PHPExcel 的继任者目前新项目我一般直接用它。它负责把 BIFF 记录翻译成 PHP 数组或对象你只需要关心“拿到第几行第几列”。还有一点值得注意不少下载下来的“xls 文件”实际上是 HTML 表格或者 CSV只是改了后缀名。这类文件在 Excel 里双击能打开但用二进制解析库去读就会报“文件格式不正确”。后面避坑章节我会专门讲怎么提前识别这种文件。2.2 选哪个库PHPExcel 与 PhpSpreadsheet 之间的取舍如果你的 PHP 版本还在 5.6 或更老那只能选 PHPExcel这个库在 2017 年左右就停止了维护代码仓库也已经归档。问题是 PHP 5.6 本身也早就不受支持用老版本跑内部工具安全风险很大。PHP 7.2 以上推荐直接上 PhpSpreadsheet。它保留了 PHPExcel 大部分 API 习惯只是命名空间从PHPExcel换成了PhpOffice\PhpSpreadsheet。比如原来读取一个单元格是$objPHPExcel-getActiveSheet()-getCell(A1)-getValue()现在是$spreadsheet-getActiveSheet()-getCell(A1)-getValue()改动量很小。网上搜到的老教程里大量的 PHPExcel 写法放到 PhpSpreadsheet 里往往只需要改类名和命名空间就能跑通。如果是那种只有列名、没有复杂样式、数据量很大的报表我也见过有人先把 xls 转成 CSV 再用LOAD DATA LOCAL INFILE导入 MySQL。这种做法速度最快但前提是 Excel 文件里没有合并单元格、没有多级表头、数据类型不混装。真实运营给的表格往往没这么规矩所以我更倾向于读 xls 后逐行验证再写入链路长一点但稳。选择库的标准其实就两条PHP 版本决定能不能用新库文件内容决定该不该走 CSV 快速通道。这两点想清楚了再往下写代码就不会中途推翻重来。2.3 读取前先查扩展php -m 里少了哪几个就别开始PhpSpreadsheet 依赖一堆 PHP 扩展缺任何一个都会在运行时抛异常。最常遇到的三个是ext-zip、ext-xml、ext-gd。其中ext-zip不是拿来做文件压缩的而是 PhpSpreadsheet 读取 xlsx 压缩包用的xls 格式也会走内部的解包逻辑ext-xml和ext-simplexml负责解析 XML 类型的表格ext-gd是处理 Excel 里嵌入图片时才会用到如果你只需要纯数据导入可以不用管。部署到服务器之前先跑一下命令确认当前环境支持情况php -m | grep -E zip|xml|gd|simplexml如果输出里缺项在 Ubuntu/Debian 系列系统上常见做法是安装对应扩展比如apt-get install php-zip php-gd php-xml然后重启 PHP-FPM。Windows 环境下则需要在 php.ini 里去掉extensionphp_zip.dll、extensionphp_gd2.dll等行前面的分号。这里的逻辑很简单grep 出的扩展名对应的是 PHP 编译进内核还是动态加载的模块少一个PhpSpreadsheet 的 IOFactory 就会在文件类型检测阶段直接失败。顺带提一句不要为了省事直接禁用 PhpSpreadsheet 的某些依赖比如ext-simplexml在很多精简安装里没有但 Excel 文件读取出错后又很难联想到是它。提前查一遍能省掉后面大部分排错时间。3. xls 读取的 PHP 实现从打开文件到拿到每一行数组3.1 安装依赖并写第一个能打开 xls 的程序PhpSpreadsheet 用 Composer 安装项目目录下执行composer require phpoffice/phpspreadsheet如果你的 PHP 版本较低Composer 会自动选一个兼容的版本号。装完之后写一个最基础的读取脚本目标只有一个把 xls 第一张工作表的内容完整打印出来。?php require vendor/autoload.php; use PhpOffice\PhpSpreadsheet\IOFactory; $inputFile ./demo.xls; // 用 IOFactory 自动识别文件类型xls/xlsx/csv 都能走这里 $spreadsheet IOFactory::load($inputFile); // 默认读取第一张工作表也可以按名称取 $sheet $spreadsheet-getSheet(0); // 把整张表转成二维数组行和列都从 0 开始 $rows $sheet-toArray(); foreach ($rows as $lineNum $row) { // $row 的顺序对应 A、B、C、D……列 echo implode(\t, $row), PHP_EOL; }这段代码里IOFactory::load()会根据文件头部信息自动判断是 xls 还是 xlsx你不用在代码里写死类型。getSheet(0)取的是第一张工作表如果一个 xls 里有多个 sheet这个参数就是你要的工作表序号从 0 数起。toArray()返回的二维数组中外层是行内层是列顺序和 Excel 里的 A、B、C 完全对应。要注意的是toArray()默认会把格式化的值也带出来比如日期列可能已经被转成字符串数字列可能带千分位符号这在后一步入库时需要格外小心我更推荐后面会讲到的getValue()原始值读取方式。3.2 读回 Sheet 的数据并说明 getSheet、toArray 等参数toArray()是最快的上手方式但它有个隐藏问题默认会把整张表的所有单元格都读进来包括完全没有内容的空白行。而且它返回的每个单元格值是“格式化之后的显示值”Excel 里设置过大数字格式的单元格读出来可能已经变成了科学计数法字符串。真实导入场景下我更习惯直接遍历有数据的行范围用getCell()-getValue()拿原始值。这样做的好处是能拿到 Excel 里真正存储的数值方便在 PHP 侧统一处理。?php require vendor/autoload.php; use PhpOffice\PhpSpreadsheet\IOFactory; $inputFile ./demo.xls; // 读取时只加载数据不加载样式内存占用会明显降低 $reader IOFactory::createReaderForFile($inputFile); $reader-setReadDataOnly(true); $spreadsheet $reader-load($inputFile); $sheet $spreadsheet-getSheet(0); // 获取表中实际用到的最大行号和列号 $highestRow $sheet-getHighestDataRow(); $highestColumn $sheet-getHighestDataColumn(); echo 总行数{$highestRow}最右侧列{$highestColumn}, PHP_EOL;这里createReaderForFile()是 IOFactory 的底层方法返回一个针对当前文件类型的 Reader 对象setReadDataOnly(true)告诉 Reader 不要加载单元格样式、合并单元格信息这类无关数据。getHighestDataRow()和getHighestDataColumn()返回的才是真正有数据的边界比起getHighestRow()能避开那些“只设置了格式但没填内容”的垃圾行。拿到这两个边界值你就能精确控制遍历范围不会把 Excel 底部几十万个空白行也读进内存。3.3 空行处理与读取范围限制大文件也敢整张读当 xls 文件超过 2 万行直接toArray()会把 PHP 内存吃到一两百兆这在生产环境很容易触发memory_limit报错。更稳的做法是逐行读取遇到空行跳过并且只读取需要的列范围。?php require vendor/autoload.php; use PhpOffice\PhpSpreadsheet\IOFactory; $inputFile ./demo.xls; $reader IOFactory::createReaderForFile($inputFile); $reader-setReadDataOnly(true); $spreadsheet $reader-load($inputFile); $sheet $spreadsheet-getSheet(0); // 假设第一行是表头从第 2 行开始读列只取 A 到 F即索引 0 到 5 $startRow 2; $endRow $sheet-getHighestDataRow(); $columnStart 0; $columnEnd 5; $dataRows []; for ($row $startRow; $row $endRow; $row) { $rowData []; for ($col $columnStart; $col $columnEnd; $col) { $cell $sheet-getCellByColumnAndRow($col 1, $row); $rowData[] $cell-getValue(); } // 整行都是 null 视为空行直接丢弃 if (count(array_filter($rowData, function ($v) { return $v ! null $v ! ; })) 0) { continue; } $dataRows[] $rowData; // 每攒够 5000 行可以先写一次避免一次性堆太多真实项目里这里会调用写入函数 } echo 实际有效数据行数 . count($dataRows), PHP_EOL;这段代码的核心是双层循环外层控制行号内层控制列号。getCellByColumnAndRow()接收的列参数是自然数列号col 1是因为 Excel 的 A 列是 1而不是 0。array_filter配合闭包检查整行是否为空空行直接跳过这样最终得到的$dataRows里全是有效数据。这里我把行堆在内存里只是为了演示真实场景中应该在循环里达到比如 5000 行就批量入库一次然后清空数组这样 PHP 的内存占用会被压得非常低。4. PHP 将数据写入 MySQL字段映射、批量提交和字符集的最后一道关4.1 设计导入表哪一列设置唯一索引直接影响导入结果读出来的数据最终要落到表里表结构设计的好不好直接决定重复导入时你是在补数据还是在洗数据。最常见的业务场景是导入客户名单或者订单明细这类数据通常有一个天然的“唯一键”比如手机号、订单号、身份证号。设计表时把唯一键加上唯一索引后续导入就能用INSERT ... ON DUPLICATE KEY UPDATE或INSERT IGNORE来处理冲突。CREATE TABLE customer_import ( id int(11) NOT NULL AUTO_INCREMENT, mobile varchar(20) NOT NULL DEFAULT COMMENT 手机号, name varchar(100) NOT NULL DEFAULT COMMENT 姓名, amount decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT 消费金额, remark varchar(255) NOT NULL DEFAULT COMMENT 备注, created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_mobile (mobile) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT客户导入表;这个建表语句里UNIQUE KEY uk_mobile就是用来兜底的。如果 xls 里同一个手机号出现多次这条唯一索引会拦截掉第二次插入。很多人忽略的一个细节是mobile字段在 Excel 里可能是数字格式长度超过 15 位时后面的数字会变成 0所以表里手机号和订单号这类字段一律用varchar而不是int。amount用decimal(10,2)是为了避免浮点数误差Excel 里的金额虽然看着是两位小数但底层是浮点存储的直接转成 float 再插入可能出现 0.1 0.2 不等于 0.3 的问题。created_at设为默认当前时间导入时就不用单独赋值方便以后排查“这批数据是什么时候导入的”。如果你导入的数据没有唯一键那表里至少要有一个导入批次号字段每次导入生成一个批次号逻辑删除时直接按批次号删除不然重复执行脚本会复制出一堆脏数据。4.2 用 PDO 预处理做批量插入一次 execute 和多次 execute 的差别老式 PHP 代码里常见的写法是循环拼 SQL每条 INSERT 执行一次。数据量小还好超过 5000 条这种写法会让数据库疲于奔命。正确姿势是 PDO 预处理 批量执行同一个预处理语句可以反复绑定不同的值。?php $pdo new PDO( mysql:host127.0.0.1;dbnametest;charsetutf8mb4, root, your_password, [ PDO::ATTR_ERRMODE PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE PDO::FETCH_ASSOC, ] ); // $rows 是从 xls 读出来的有效数据结构[[name 张三, mobile 13800138000], ...] $sql INSERT INTO customer_import (mobile, name, amount, remark) VALUES (:mobile, :name, :amount, :remark); $stmt $pdo-prepare($sql); $batchSize 2000; $count 0; $pdo-beginTransaction(); foreach ($rows as $row) { $stmt-execute([ :mobile $row[mobile], :name $row[name], :amount $row[amount], :remark $row[remark] ?? , ]); $count; if ($count % $batchSize 0) { // 每攒够 2000 条提交一次避免事务日志过大 $pdo-commit(); $pdo-beginTransaction(); } } $pdo-commit();PDO 的prepare()会把 SQL 发送到 MySQL 服务端进行预编译后续execute()只需要传参数值省去了每次 SQL 解析的开销。这里最关键的是事务处理beginTransaction()开启事务后所有 INSERT 都先缓存在事务里最后commit()才真正落盘。如果没有批量提交每条执行完立即隐式提交遇到第 1000 条数据出错前面 999 条已经写进库了且不好回滚。我把batchSize设为 2000这是从经验来看比较稳的数值事务太大时 InnoDB 的 undo log 会膨胀回滚也变慢事务太小又体现不出批量提交的好处。如果你的服务器内存只有 512M建议调成 500。4.3 入库前的字段修正把 Excel 日期数字改回日期把手机号补零Excel 里的日期本质是一个数字代表从 1900 年 1 月 1 日算起的天数。所以用getValue()读到的日期列可能是43466这种整数而不是你眼睛看到的“2026-01-01”。这个问题很典型必须在写入 MySQL 前做一个转换函数。?php /** * 将 Excel 序列日期转成 MySQL 日期字符串 * * param float|int $excelSerial Excel 中的日期序列值 * return string Y-m-d 格式日期 */ function excelSerialToDate($excelSerial) { // 25569 是 1970-01-01 在 Excel 序列日期中的对应值 $unixTimestamp ($excelSerial - 25569) * 86400; // 避免时区对日期偏移的影响用 gmdate 而不是 date return gmdate(Y-m-d, $unixTimestamp); } // 转换示例 echo excelSerialToDate(43466); // 输出 2018-12-28具体值随 Excel 版本有细微差异这个函数的换算逻辑基于一个固定事实Excel 的日期起点是 1900 年 1 月 1 日Unix 时间戳的起点是 1970 年 1 月 1 日两者之间相差 25569 天。用序列值减 25569 再乘一天的秒数 86400就得到 Unix 时间戳。这里我特意用gmdate而不是date是为了避免 PHP 默认时区带来的 8 小时偏差确保日期不会偏移一天。手机号这类字段也有隐藏坑。如果 Excel 单元格格式是“数字”输入13800138000没问题但如果号码是1380013800这种 10 位开头、又恰好设置了“科学计数”格式读取时可能变成1.38001E10。处理方式是用字符串截取或正则强行还原比如判断读取值包含字母E就用number_format($val, 0, , )去掉科学计数法的小数点。这一层修正看起来不起眼但导入 10 万行时有几百个手机号被 Excel 自动截断是常态你能在入库前发现问题就不至于让业务方拿到数据后还要拉着一群人对表。5. xls 导入 MySQL 的避坑清单五个我踩过的现场5.1 后缀是 .xls但文件根本不是 Excel现象IOFactory::load()直接抛异常提示“File format is not recognized”或者读取出来全是乱码。原因拿到的是一个从网页导出的 HTML 表格或者从某些财务系统导出的 CSV只是被人改成了.xls后缀。Excel 能打开是因为它做了兼容但 PhpSpreadsheet 按二进制 OLE 格式解析就翻车了。解决读取之前先检查文件头四个字节。真正的 xls 文件以D0 CF 11 E0开头这是 OLE2 复合文档的魔数。$handle fopen($inputFile, rb); $header fread($handle, 4); fclose($handle); if ($header \xD0\xCF\x11\xE0) { echo 确认是标准 xls 文件; } else { // 可能是 CSV 伪装或者 HTML 表格 echo 不是标准 xls请检查源文件; }从那以后我写的所有导入脚本都会先做这步文件头校验因为用户传假 xls 的几率比你想的高得多。真正 xls 的魔数判断虽然不能覆盖所有格式变体但至少能拦住八成假文件。5.2 日期列读出 43466眼睁睁看着不对现象Excel 里明明写着“2026-01-05”用getValue()读出来却是一串数字比如 46026。原因这是 Excel 内部存储日期的固有机制所有日期在底层都以自 1900-01-01 起算的序列数字保存显示成日期只是单元格格式在起作用。getValue()默认返回原始值所以返回的是数字。解决不要把日期列当字符串用读取时调用前面写的excelSerialToDate()转换函数统一处理。另外更好的做法是在 PhpSpreadsheet 端就判断单元格格式如果数据格式是日期类型直接调用$cell-getFormattedValue()拿显示值。这段的逻辑是先拿到单元格的格式码再决定走原始值还是格式化值。5.3 5 万行的 xls 直接把 PHP 内存吃完了现象脚本跑几秒后报Allowed memory size of 134217728 bytes exhausted文件越大挂得越快。原因toArray()一次性把整个工作表加载进了内存再加上后续的数据处理PHP 默认 128M 内存根本不够。解决限制读取范围只读数据不读样式。核心是setReadDataOnly(true)加循环分批处理不要让$dataRows一直膨胀。按 2000 行一个批次插入后立刻unset清空数组内存就能稳定在几十兆。这是对这个场景最有效的招数比调memory_limit治本得多。5.4 中文导入 MySQL 全变问号写入前字符集没对齐现象数据插入成功后用客户端查询看到中文全部是???或者乱码英文和数字正常。原因四处字符集不一致。第一处是 PHP 侧连接 MySQL 时没指定charsetutf8mb4第二处是数据库表本身用了latin1第三处是 xls 文件里的字符串用了非 UTF-8 编码。解决MySQL 连接串里强制加charsetutf8mb4建表时明确指定DEFAULT CHARSETutf8mb4。对于从老版本 Excel 生成的 xls 文件字符串可能存储为 GBK 编码这种情况需要在写入前对读出来的值做一次转换// 如果确定来源是 GBK 编码的 xls转成 UTF-8 再入库 $value mb_convert_encoding($value, UTF-8, GBK);注意mb_convert_encoding只有在 PHP 安装了mbstring扩展时才能用。我在做银行导出的旧格式 xls 时遇到过这类问题文件里的中文用 UTF-8 解码出来是乱码转成 GBK 才正常。你在接入新系统导出的文件时最好先导出一两条数据打印原始字节确认编码再决定转换方向别盲目转换。5.5 第二次导入撞上唯一索引报错堆了一屏幕现象脚本第一次跑完正常第二次再跑报SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry脚本中断。原因表里的唯一索引在职守。同一批数据如果已经导入过一遍第二次插入自然撞上唯一键冲突而代码里没有把这些冲突当作预期内的情况处理。解决导入前先做一次清理或者用幂等写入。我常用的做法是给导入表加一个batch_no批次字段每次导入生成一个唯一批次号脚本开始前先执行DELETE FROM customer_import WHERE batch_no 当前批次号把上次残留的数据清掉再导入。这个方案比INSERT IGNORE更可控至少你很清楚地知道自己清掉了什么。如果没有批次字段就用INSERT ... ON DUPLICATE KEY UPDATE做覆盖更新把可变的字段更新一遍保留新增字段。6. 导入后的校验手法与批量提速一个让重复数据现形的习惯脚本跑完不等于事情干完真正决定这个工具靠不靠得住的是导入完成后的校验。我的固定动作是三步先数行数再查重复最后抽样比对。行数校验最简单。脚本开始前记录 Excel 有效数据行数$excelCount导入完成后执行SELECT COUNT(*) FROM customer_import WHERE batch_no xxxx得到$mysqlCount两者相等说明没有丢行。这里要注意如果中途有数据被唯一索引拦截两个数对不上是正常的你得在代码里统计“成功条数”和“跳过条数”分别打出来这样对不上也能解释清楚。查重复这个动作我每次都会做即使有唯一索引在兜底也挡不住 Excel 内部本身就存在重复数据。校验 SQL 长这样SELECT mobile, COUNT(*) AS cnt FROM customer_import GROUP BY mobile HAVING cnt 1;如果返回结果不为空说明源头文件里就有重复项这不是 MySQL 能解决的问题得去问业务方哪个版本才是他们想要的。抽样比对则是对着一张打印出来的 Excel 原表随机挑五条记录在数据库里查一遍核对关键字段。这一步看似笨但能发现日期偏移一天、手机号变成科学计数法这一类库员看不见的底层问题。最后再讲讲提速习惯。我在真实项目里遇到过单次导入 20 万行的情况逐条 INSERT 要跑十几分钟后来把批量 size 调到 5000并确保所有写入放在同一个事务里速度提升到两分钟出头。关键点有两个一是每次execute()前不要重新prepare()一个预处理语句反复用二是把PDO::ATTR_TIMEOUT和 MySQL 的max_allowed_packet适当调大避免大数据包写入时连接中断。从那以后我每次做导入脚本都强制走一遍“文件头校验 → 分批读取 → 唯一键检查 → 入库 → 行数比对 → 重复检测”的完整链路。这套流程写进自己的工具包里换哪个项目都是同一套逻辑在跑。希望帮到你。本文还有配套的精品资源点击获取