PHPEXCEL 20万数据导入导出（一）

本片博客记录了一次实际开发中的需要使用PHPExcel导入导出大量数据（20万）的解决过程。

复盘优化自己的项目，好处在于，一旦已找到好的方法，好的代码，你就不可能再使用差的代码

场景描述：开发环境：普通电脑+win+php+mysql+apache。开发一个人力资源管理系统，涉及单位，部门，人员和之间的对应关系。在本项目开发中，需要导入20万条人员的信息，导出100万条用户信息。

Ques1:如何使用phpexcel导入20万条用户数据

1，优化逻辑代码

对于20万条数据显然无法进行测试，我们先使用1万条数据进行测试

先看一下缩略版的逻辑代码

foreach(...){
    $arr = db('department')->where('departCode',$strs[1])->where('unitCode',$strs[2])->find();
    if(empty($arr)){      
        db('department')->insert([        
        'departCode' => $strs[0],       
        'name' => $strs[1],       
        'unitCode' => $strs[2],   
        ]);
    }else{    
        return $this->error("上传失败,已存在该部门",'department/lst');
    }
 }
           

2，减少sql的执行

第一次上传了 7000 条数据，时间24秒左右。

分析在导入数据时，并不是直接全部导入，还需要进行一些必要的逻辑判断，所以每一条数据都需要判断和插入，插入肯定没有办法避免，可以看出判断语句是在插入前判断表内是否有重复的数据，避免插入重复的数据，那如果将判断的sql去掉，能不能减少运行时间呢？

测试后结果在17秒左右。

[外链图片转存失败(img-1U76vGxi-1564537982061)(en-resource://database/488:1)]

证明省去了7000条sql查询语句还是有用的，所以我们可以去掉这个查重的判断，在最后数据出入完毕时，再对重复数据进行处理和删除。

按照这个思路，我们可以把 foreach中的插入的sql语句，放在外面，在循环中把数据拼接起来，在循环外去执行插入多条数据的一条sql。

代码为

$data = array();
foreach(...){
    $temp = [       
        'departCode' => $strs[0],       
        'name' => $strs[1],        
        'unitCode' => $strs[2],    
    ];    
    array_push($data,$temp);
}
db('department')->insertAll($data);
           

来看一下运行时间竟然不到一秒。

[外链图片转存失败(img-DzUS3NnK-1564537982063)(en-resource://database/486:1)]

要执行的sql次数等于 = 循环内sql数 x 数据量，对于大量数据，mysql处理会很吃力。所以要避免过多sql，优化插入的sql。尽量把数据交给程序去处理，减少sql语句才是关键。

3，保护内存

那我们就用20万条数据来测试一下结果。

（需要注意几个点：以下几个默认配置都会让你在导入大量数据时出错

• php默认上传文件大小限制2M，（php.ini： upload_max_filesize）
• 默认脚本使用最大内存限制128M，（php.ini： memory_limit）
• mysql数据库支持最大字节为4194304byte，（数据库配置：max_allowed_packet）

• excel03版（后缀.xls）以前最大行数六万五，之后版本(.xlsx)是104万。

  所以20万条数据，我的配置是，使用新版excel，php上传文件大小调高到20M，最大内存500M，mysql最大字节100M）

[外链图片转存失败(img-t5huTAwG-1564537982064)(en-resource://database/492:1)]

虽然用了40s，但是至少程序可以运行了，如果数据量继续增大，有可能进行运算的内存很有可能不够用，所以，另一方面，应该从保护内存的方面思考。在程序中设置断点查看运行时间，phpexcel加载这20万条数据花了15s，一行行的读花了接近40s，等于说全部时间还是花费在phpexcel本身执行上面，不知道有什么好的优化方法。

Ques2:如何使用phpexcel导出100万条用户数据

导出六万条数据

[外链图片转存失败(img-Ano6yvjn-1564537982064)(en-resource://database/494:1)]

20万条数据

发现时间超过100s无法运行

1，使用csv文件格式导出

csv 与 excel相比，好处比较多，比如更安全，文本格式不会产生错误，编程语言处理速度更快等

[外链图片转存失败(img-l3rDZ3c3-1564537982066)(en-resource://database/496:1)]

20万条数据17s完成了

2，分批生成文件，最后下载压缩包

我们将100万条数据分为五份，每次只从数据库中取出20万条数据，然后写入csv文件中，最后将这五个文件压缩打包下载

[外链图片转存失败(img-L5mmH0bR-1564537982066)(en-resource://database/498:1)]

100万条数据能在15s内完成

生成单个文件的话11s就可以完成