前言

最近网站准备对商城商户订单模块添加一个电子面单功能。选用的是快递 100 的 API，之前做商城类 APP 有接过快递鸟的物流追踪接口，电子面单没接触过。电子面单是快递公司网点或者电商商家或者一件代发厂家，在包裹好快递之后，在包装盒或者包装袋上粘贴的包含必要快递信息的单据。以下是电子面单示例：

电子面单示例

每家快递公司的电子面单都长的不一样。因为面单是面向快递公司的，所以电子面单客户账户或月结账号、电子面单密码、收件网点名称等信息也是必要的。当然，也需要快递 100 所有支持的快递公司的快递编码。这个编码接口给了一个页面展示，最新版本有 1054条记录，下载表格得到了一个 Excel。虽然这么多快递公司，用到的不过寥寥几个，但就像省市区表一样，作为完整的国家划分，一条记录都不能少。一开始手动复制粘贴到数据库中，一条一条记录地添加，弄了半小时，发现才弄了一百多条。感觉这样是不行的，肯定得自动化导入到数据库（虽然探究导入方法的过程花了很长的时间，但等下次再遇到这种情况就能直接用脚本导入了，效率会高很多）。

过程

想到之前 ci、tp 都做过导入 Excel 获取数据的功能，就去找相关的代码及应用库。但很多时候就是这样，框架的东西好在集成方便，但相关的代码想要直接拿来用，又不太方便。于是便想着重新整理一下，写一个不依赖框架的 demo 出来。看了一下使用到的 PHPExcel 库，发现这个库到了今年已经不再维护了，最新的版本 1.8.1 是 2015 年开发的，在了 2017 年就废弃了。最新的开发迁移到了 PHPSpreadsheet。但我熟悉的还是 PHPExcel，而且 PHPSpreadsheet 应用了命名空间之类的特性，不确定能否直接调用。在 github 上搜索的时候，还发现了一个 基于 PHPExcel 项目的 demo，于是便借鉴了他的 demo 里的一些内容。以下为整理的使用步骤：

引入 PHPExcel 库文件，并实例化

require_once dirname(__FILE__) . '/../vendor/PHPExcel-1.8/Classes/PHPExcel/IOFactory.php';
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objReader->load($fileName);

创建读控制器支持两种类型，‘Excel2007’ 和 ‘Excel5’，其中 ‘Excel5’ 针对的较低版本的 Excel。后面直接加载 Excel 文件，这个文件可以是自定义的静态文件，也可以是前端上传的文件。

获取 xlsx 文件数据

我发现，获取 xlsx 的方式的有几种，大致分成两类 ，一种是通过循环迭代获取到数据，还有一种是直接调用工作表单 worksheet 类 的变量方法 toArray()。

关于第一种，我找到了两个方法，一个是使用 worksheet 的迭代器：

    foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
        echo 'Worksheet - ' , $worksheet->getTitle() , EOL;

        foreach ($worksheet->getRowIterator() as $row) {
            echo '    Row number - ' , $row->getRowIndex() , EOL;

            $cellIterator = $row->getCellIterator();
            $cellIterator->setIterateOnlyExistingCells(false); // Loop all cells, even if it is not set
            foreach ($cellIterator as $cell) {
                if (!is_null($cell)) {
                    echo '        Cell - ' , $cell->getCoordinate() , ' - ' , $cell->getCalculatedValue() , EOL;
                }
            }
        }
    }

这是在 PHPExcel 的样例 Examples 中 28iterator.php 找到的。因为 worksheet 也有可能有多张的，所以最外层是对工作表的迭代。

另一种是比较常见的：

$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow(); // 取得总行数
$highestColumn = $sheet->getHighestColumn(); // 取得总列数

获取到总行数和总列数，然后通过 PHP for 循环，执行 $sheet->getCell($k.$j)->getValue(); 来获取值，这里的 $k 代表 ‘A’~‘Z’ 的列号，$j 代表行数。比如，A1 的值为 $sheet->getCell('A1')->getValue();。我在框架里使用的就是这种写法。

然后在我查看另一个 erp 项目时，发现其内获取 xlsx 文件数据使用了 toArray() 方法。

    $result = $objPHPExcel->getSheet(0)->toArray (null,false,false,True);//获取首个工作簿信息并转为数组
    // 过滤空白行
    foreach ($result as $key=>$vo) {
        if(count(array_unique($vo))==1){
            unset($result[$key]);
        }
    }
    array_walk_recursive($result, function(&$nod){$nod===null?($nod=''):$nod=htmlentities($nod);});//NULL转空白字符|拦截XSS

建议查看一下 toArray() 的注解，最后一个参数为 true 时，返回的数组使用的是实际的行和列 ID 下标索引，当它为 false 是，返回简单数组，数据下标索引从 0 开始。

后面还添加了过滤空白行和拦截 XSS （转义 html 实体字符）的功能，确是惊喜。

写入到数据库中

写入数据库大概也是三个步骤：连接数据库，构造查询语句，执行（我本地的环境是 php 7.3.2，支持 mysqli 或者 pdo 连接方式）。

1.连接数据库

    // 数据库连接
    $link = mysqli_connect('localhost','root','123456','lejiao1688');
    if(!$link){
        exit("数据库连接失败" . EOL);
    }
    mysqli_query($link,'set names utf8');

最后一句设置数据库字符编码尤为重要，我就碰到了写入数据库乱码的情况。

2.构造查询语句
3.执行

         $sql = "INSERT INTO `destoon_kuaidi`(`name`, `eng`, `code`, `type`) VALUES ('".$res['A']."','".$res['B']."','".$res['B']."','".$res['C']."')";
        if(mysqli_query($link, $sql)){
            echo $res['A'].' , ';
        } else {
            echo "导入数据失败";
            echo mysqli_errno($link);
        }

我这里的构造查询语句和执行都是在循环内部，执行成功后，输出快递公司名称。

补充说明

在读取 Excel 数据的时候，总是会有一些不需要的行或者列，这些数据可以通过设置‘读取过滤器’来实现（当然也可以在循环插入到数据库的时候添加判断条件，过滤掉不需要的信息）。

    class MyReadFilter implements PHPExcel_Reader_IReadFilter
    {
        public function readCell($column, $row, $worksheetName = '') {
            // 读取 1001 ~ 1500 行的数据
            if ($row >= 1001 && $row <= 1500) {
                return true;
            }

            return false;
        }
    }

    $objReader = PHPExcel_IOFactory::createReader('Excel2007');
    $objReader->setReadFilter( new MyReadFilter() );
    $objPHPExcel = $objReader->load($fileName);

设置方法在加载 Excel 文件操作之前，并且需要单独定义一个实现了 PHPExcel_Reader_IReadFilter 的 readCell 方法的自定义筛选类。

行号从 1 开始，返回 true，就通过，返回 false 则忽略该单元格 cell 的数据。还可以对列和工作表进行筛选。

在 PHPExcel 的 Examples 中还找到了应用很普遍的一些用法，如设置错误输出，统计执行时间和使用内存情况。

    error_reporting(E_ALL);
    ini_set('display_errors', TRUE);
    ini_set('display_startup_errors', TRUE);

    define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');

    date_default_timezone_set('Asia/Shanghai');

    $callStartTime = microtime(true);
    ...
    $callEndTime = microtime(true);
    $callTime = $callEndTime - $callStartTime;
    echo 'Call time to read Workbook was ' , sprintf('%.4f',$callTime) , " seconds" , EOL;


    // Echo memory usage
    echo date('H:i:s') , ' Current memory usage: ' , (memory_get_usage(true) / 1024 / 1024) , " MB" , EOL;
    ...
    echo date('H:i:s') , ' Current memory usage: ' , (memory_get_usage(true) / 1024 / 1024) , " MB" , EOL;

    // Echo memory peak usage
    echo date('H:i:s') , " Peak memory usage: " , (memory_get_peak_usage(true) / 1024 / 1024) , " MB" , EOL;