PHPExcel 读取 Excel 数据并导入数据库

前言

最近网站准备对商城商户订单模块添加一个电子面单功能。选用的是快递 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;

添加新评论

评论列表