ThinkPHP5.1 框架下 PhpSpreadsheet 操作 Excel 表的导入导出

`■☞ 背景`

最近在后台业务需求中又涉及到了 Excel 表的导入导出正要参考以前整理的文章 —— 【ThinkPHP5.1 excel 表的导入导出操作 (PHPExcel)】 但是发现，好多道友早就提示了 "PHPExcel" 几年前就不再维护了所以，觉得有必要整理一份 "PhpSpreadsheet " 的使用指导希望能帮到有需求的道友，欢迎指摘 …
参考文档
1. 官方文档 ——【欢迎使用 PhpSpreadsheet 的文档】(英文不好的直接谷歌右键翻译就好呗)
2. 如果觉得有帮助，也可参考参考鄙人获取的一份入门手册

代码语言：javascript复制

链接：https://pan.baidu.com/s/1NEIH-yO-c9okPxhD2bkShA  提取码：8ssr

操作环境

代码语言：javascript复制

> 框架：ThinkPHP5.1.40
> 类库：PhpSpreadsheet 
> 技能：Composer, MySql
> PHP: php7.2.9 (建议 7.2 版本以上哦！)

`■☞ 前期准备`

提示：本人整理的文章，目前只是针对于拓展名为 ".xls"和".xlsx" 的 excel 规范文件

`▶ PhpSpreadsheet 安装`

建议使用 composer ，执行命令如下：

代码语言：javascript复制

composer require phpoffice/phpspreadsheet

`▶ 操作参考表 "tp5_xop_excel" 的创建`

为了方便测试，在此可以创建一个数据表，如果想节约时间，可以跳过

代码语言：javascript复制

CREATE TABLE `tp5_xop_excel` (
  `goods_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '商品ID',
  `goods_name` varchar(50) NOT NULL DEFAULT '' COMMENT '商品名称',
  `thumbnail` varchar(200) NOT NULL DEFAULT '' COMMENT '缩略图，一般用于订单页的商品展示',
  `place` varchar(50) CHARACTER SET utf8 NOT NULL DEFAULT '1' COMMENT '产地，国家：美国、澳大利亚等',
  `list_order` int(11) NOT NULL DEFAULT '0' COMMENT '排序，越大越靠前',
  `reference_price` decimal(11,2) NOT NULL DEFAULT '0.00' COMMENT '商品参考价',
  `updated_at` timestamp NOT NULL DEFAULT '1970-01-01 10:00:00' COMMENT '商品更新时间',
  `status` tinyint(2) NOT NULL DEFAULT '0' COMMENT '状态 -1：删除 0：待上架 1：已上架 2：预售 ',
  PRIMARY KEY (`goods_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COMMENT='商品表. 注意：status 的规定，app 上只显示上架的产品哦';

`▶ 集成操作类 "SpreadsheetService.php" 的下载`

可下载【附录】中提供的源码文件 "SpreadsheetService.php"

`■☞ 读取 excel 中的数据`

一般读取到 excel 表中的数据，就可以做后续的操作（发送短信，备忘数据等）；此处以我导入数据库（mySQL）为例讲解

`▷ 前端操作`

一般是需要一个文件上传按钮的，以鄙人使用 LayUI 框架为例，核心代码如下：

代码语言：javascript复制

    layui.use('upload', function () {
        const upload = layui.upload;
        //普通图片上传
        upload.render({
            elem: '.btn_upload_excel'
            , url: "{:url('cms/expand/opExcel')}"
            , accept:'file' //上传文件类型
            , done: function (res) {
                //如果上传成功
                layer.msg(res.message);
            }
            , error: function () {
                //演示失败状态，并实现重传
                return layer.msg('上传失败,请重新上传');
            }
        });
    });

`▷ 服务端接口操作`

此处即为服务端的处理接口,在此介绍一下鄙人的思路：首先，将对象文件拷贝到项目的 "public/upload" 目录下然后，需要获取其【绝对路径】（网上总说 Linux/windows 下的相对路径操作有区别）接着，就是读取 excel 文件中的内容，并将其插入到数据库 mySQL 的表中最后，考虑到上传的文件没必要留着，进行删除操作【注意删除前要释放空间，即文中的代码 "unset($info); "】

如下，便是鄙人的服务端接口核心源码：

代码语言：javascript复制

public function opExcel(Request $request){
	$file = $request->file('file');
	$info = $file->move('upload');
	if ($info){
	    //绝对路径，把反斜杠()替换成斜杠(/) 因为在 windows下上传路是反斜杠径
	    $file_real_path = str_replace("\", "/", $info->getRealPath());
	    unset($info); //释放空间,也可使用 $info = null;(写在这里最好，后面总是不执行！！)
	    $opRes = (new Xmozxx())->importExcelData($file_real_path);
	    //TODO 操作完成后，删除文件
	    deleteServerFile($file_real_path);
	}else{
	    $opRes['status'] = 0;
	    $opRes['message'] = "文件上传失败 ".$file->getError();
	}
	return showMsg($opRes['status'],$opRes['message']);
}

上面代码中提到的 "(new Xmozxx())->importExcelData($file_real_path);",一般是要提取到 Model 操作类 中的，核心代码如下：

代码语言：javascript复制

    /**
     * 导入 excel 表中的数据
     * @param $file_real_path
     * @return array
     */
    public function importExcelData($file_real_path){
        $opRes = (new SpreadsheetService())->readExcelFileToArray($file_real_path,"A2");
        //TODO 根据返回来到数据数组，进行数据向数据库的插入或其他操作 ...
        if (isset($opRes['data'])){
            $resultArr = [];
            foreach ($opRes['data'] as $key => $value) {
                $resultArr[$key]['goods_name'] = isset($value[0])?$value[0]:'';
                $resultArr[$key]['thumbnail'] = isset($value[1])?$value[1]:'';
                $resultArr[$key]['place'] = isset($value[2])?$value[2]:'';
                $resultArr[$key]['reference_price'] = isset($value[3])?$value[3]:'';
                $resultArr[$key]['updated_at'] = date("Y-m-d H:i:s",time());
            }
            /**
             * TODO 此时进行数据表记录的遍历插入操作即可
             * 因为数据量较大，建议使用批量插入的方式,以我的业务需求，代码举例如下：
             */
            Db::name('xop_excel')->data($resultArr)->limit(10)->insertAll();
        }
        return ['status' => $opRes['status'],'message'=>$opRes['message']];
    }
}

`▶ 【重要提示】`

其中最重要的就是集成类 "SpreadsheetService" 的调用，建议详细阅读鄙人的注释信息

代码语言：javascript复制

(new SpreadsheetService())->readExcelFileToArray()

附录文件夹中的 "small.xlsx" ,可作为导入操作的目标文件

【注意】：

第一点：上传的文件要标准比如：直接将原文文件的扩展名 ".xlsx" 改为了 ".xls"，这是不允许的（可以打开后点击 “另存为”）
第二点：操作的文件数据不要太大鄙人测试，一万条的 excel 表还是没出问题的如果出现溢出内存的问题，第一可考虑在 php.ini 中增加 memory_limit 的限制如果是百万数据的 excel 文件，目测还是需要优化代码的目前，鄙人没有这个需求，毕竟小网站业务量有限 …

`■☞ 将数据导出到 excel 文件`

业务需求中经常遇到这个，当前整合的方法适合 excel 表样式不复杂的导出操作；实际业务如果要求严格的话，那是需要根据开发文档，设置表格内容样式（文字颜色，大小等…）的哦 !

`▷ 前端操作`

鄙人发现，多数的业务适合将 excle 文件下载到客户端【为例】，极少数要求保存到服务器指定位置的！

前端核心参考代码如下：

代码语言：javascript复制

<form class="form-download" action="{url('cms/expand/opExcel')}" method="post">
        <input type="hidden" name="op_tag" value="down">
</form>
<button type="button" onclick="downloadExcel()" class="layui-btn layui-btn-danger">下载Excel表格</button>

<script>
function downloadExcel(){
        $(".form-download").submit();
        layer.msg('数据下载中...',{timer:3500})
    }
</script>

【提示】：

以鄙人的开发经验，有的项目中使用 form 表单提交方式下载会在浏览器中出现乱码分析原因，很可能是项目框架使用的 jquery 库不兼造成的我的解决方案就是转化为 GET 页面提交，最简单的方式即为：window.location = toUrl query;
多样的实际测试，需要多做经验积累，建议多做尝试即可 …

`▷ 服务端接口操作`

相对来说，这个需求是简单的，在此讲解一下鄙人的操作思路：首先，设置一个一维的

"目标数据"ThinkPHP5

代码语言：javascript复制

Db::name('xop_excel')
        ->field('goods_name,thumbnail,place,reference_price,status')
        ->limit(6)
        ->select();

" (new SpreadsheetService())->outputDataToExcelFile() "

`▶ 【提示】`

此处展示一下对操作方法 "outputDataToExcelFile()" 的注释信息，保证阅读后才能正确使用

【提示】

如果业务上需要保存文件到指定位置，那就请参考方法 "public static function saveExcelFileToLocal()"
鄙人也是建议不要下载太大的数据量当前测试，满足个一两万条数据 （700KB左右） 的下载如果要求大数据量，还是需要进行代码优化的哦！【可见附录…】

`■□☞ 附录`

☞☛ 【`源码下载 >>>`】

☛ 代码中涉及到的公共方法

代码语言：javascript复制

/**
 * 公用的方法  返回json数据，进行信息的提示
 * @param int $status 状态
 * @param string $message 提示信息
 * @param array $data 返回数据
 */
function showMsg($status = 1,$message = '',$data = array()){
    $result = array(
        'status' => $status,
        'message' =>$message,
        'data' =>$data
    );
    exit(json_encode($result,JSON_UNESCAPED_UNICODE));
}
/**
 * 删除本地文件，Linux 上比较适用
 * 有时不能生效，需要注意释放文件变量的内存，例：unset($info)
 * @param string $file_real_path 一般来说为 绝对路径
 * @return bool
 */
function deleteServerFile($file_real_path = "")
{
    //检查文件是否存在
    if (file_exists($file_real_path)) {
        @unlink($file_real_path);
        return true;
    } else {
        return false;
    }
}

☹ ☞ 开发过程中，遇到的奇葩问题

都说是 "PHPExcel" 的升级维护版本，怎么感觉比当初开发旧版都麻烦恁？

①. `"The filename xxxxx is not recognised as an OLE file"`

这个保存信息提示，基本的解释就是：上传的文件 【不标准】

代码语言：javascript复制

比如：	直接将原文文件的扩展名 ".xlsx" 改为了 ".xls"
		或者，文件是自己代码操作所下载的，生成过程不标准
		该问题可以考虑打开文件，另存为 excel 其他类型的文件

②. `"ZipArchive::getFromName(): Invalid or uninitialized Zip object"`

这出现在文件上传操作过程，值得一提的是，鄙人觉得这个问题最奇葩

代码语言：javascript复制

	网上对于这个问题：
	多数的言论都是说 —— 操作文件的格式没有区分好 （"Xls/Xlsx"）;
	另有部分言论认为 —— 没有对该文件的操作权限 （我整了没用，建议可以多试试！）
	
	然后，鄙人发现了神奇的一幕：
		只要将文件打开后，再保存一下（啥也没动也可以）就能正常读取操作了；
		好在鄙人认为，我们多数正常的操作都是会对 excel 文件进行编辑保存的，出现错的情况也不多；
		暂时没找到好的方案，
		可以提醒操作人员，如果报错就再保存一下好了，只能当做曲线救国吧 ...

③. `"文件删除时会有报错：unlink xxx Resource temporarily unavailable`

一般来说，我们都是用 "unlink()" 进行文件的删除操作

解决方案：代码 "unset($info);" 如果在获得绝对路径后就调用才可，如果放在后面，将会无效、报错！
参考文章：【TP5使用 unlink 删除文件报错：Resource temporarily unavailable】

④. `"Fatal error: Allowed memory size of 268435456 bytes exhausted (tried to allocate 268435464 bytes) in ..."`

报错原因，基本就是由"访问过大文件使得内存不足" 而造成的，亦或引用变量过多未及时销毁

解决方案，一是多数建议的更改 php.ini 文件的配置信息，另外就是注意代码中，变量的及时用完销毁
推荐文章：【PHP 错误 Allowed memory size of 67108864 bytes exhausted 的3种解决办法】【PHP unset() 详解】

☛ `【如果想下载大量数据，比如20万条】--- （2021-3-25 补充）`

通过对网上经验的参考，建议使用 fputcsv（） 进行输出流的分割下载参考代码如下（我没有整合到源代码中，可做后期扩展）

代码语言：javascript复制

 /**
     * 下载大量的数据到 excel中，此处以 .csv 文件存储
     * @param string $fileName 文件名称
     * @param array $opData 需要下载的数据
     * @param array $header 表格标题栏数据
     */
    public static function downloadBigExcelFileToClient($fileName = "muTou.csv",$opData = [],$header = []){
        set_time_limit(0);
        header('Content-Description: File Transfer');
        header('Expires: 0');
        header('Cache-Control: must-revalidate');
        header('Pragma: public');
        header('Content-Type: application/vnd.ms-excel/csv');
        header("Content-Disposition: attachment;filename=$fileName"); // MIME 协议的扩展
        header('Cache-Control: max-age=0'); // 缓存控制

        $fp = fopen('php://output', 'a');//打开output流
        fputcsv($fp, $header);//将数据格式化为 csv 格式并写入到output流中

        $dataNum = count( $opData );
        $perSize = 500;//每次导出的条数
        $pages = ceil($dataNum / $perSize);

        for ($i = 0; $i < $pages; $i  ) {
            $opList = array_slice($opData,$perSize*$i,$perSize);
            foreach ($opList as $item) {
                fputcsv($fp, $item);
            }
            //刷新输出缓冲到浏览器
            ob_flush();
            flush();//必须同时使用 ob_flush() 和flush() 函数来刷新输出缓冲。
        }
        fclose($fp);
        unset($writer);
        exit;
    }

☛ `【如果想一次生成带有多个 sheet 的文件】` （2021-5-19 补充）

这种需求其实就是: spreadsheet->createSheet(

优化思路：

① . 在 "outputDataToExcelFile（）"方法中，添加一个参数 $sheetTag , 此处提供一个整理过的方法如下：

代码语言：javascript复制

//注意我的参数的不同！注意！！注意！！！
public static function outputDataToExcelFileMulSheet(Spreadsheet $spreadsheet,
                                                          $header = [], $opData = [], $sheetTag = 1,
                                                          $excelTitle = "-", $colStart = "A",$rowStart = 1){
        if ($sheetTag == 1){
            $work_sheet = $spreadsheet->getActiveSheet();
        }else{
            $work_sheet = $spreadsheet->createSheet($sheetTag);
        }
        //设置工作簿标题
        $work_sheet->setTitle($excelTitle);
        //确定表头标题栏的开始位置
        $keyC = ord($colStart);
        foreach ($header as $headName) {
            $colKey = chr($keyC);
            //设置表格头（即 excel表格的第一行）
            $work_sheet->setCellValue($colKey.$rowStart,$headName);
            $keyC  ;
        }
        $work_sheet->fromArray($opData, null, $colStart . intval($rowStart   1));
    }

②. 当需要写入数据时，循环调用上面的这个方法，最后再调用后面的导出文件代码即可：

代码语言：javascript复制

if ($bigTag){
  self::downloadBigExcelFileToClient($saveFileName,$opData,$header);
}else{
	self::downloadExcelFileToClient($saveFileName);
}

③. 上述代码是需要使用时优化的，尤其注意新提供参考方法中参数的不同，多多实际动手调优，胜过一切示例代码！
④. 参考最终效果如下：

php thinkphp

0 人点赞

ThinkPHP5.1 框架下 PhpSpreadsheet 操作 Excel 表的导入导出

■☞ 背景

■☞ 前期准备

▶ PhpSpreadsheet 安装

▶ 操作参考表 "tp5_xop_excel" 的创建

▶ 集成操作类 "SpreadsheetService.php" 的下载

■☞ 读取 excel 中的数据

▷ 前端操作

▷ 服务端接口操作

▶ 【重要提示】

■☞ 将数据导出到 excel 文件

▷ 前端操作

▷ 服务端接口操作

▶ 【提示】

■□☞ 附录

☞☛ 【源码下载 >>>】

☛ 代码中涉及到的公共方法

☹ ☞ 开发过程中，遇到的奇葩问题

①. "The filename xxxxx is not recognised as an OLE file"

②. "ZipArchive::getFromName(): Invalid or uninitialized Zip object"

③. "文件删除时会有报错：unlink xxx Resource temporarily unavailable

④. "Fatal error: Allowed memory size of 268435456 bytes exhausted (tried to allocate 268435464 bytes) in ..."

☛ 【如果想下载大量数据，比如20万条】--- （2021-3-25 补充）

☛ 【如果想一次生成带有多个 sheet 的文件】 （2021-5-19 补充）

优化思路：

`■☞ 背景`

`■☞ 前期准备`

`▶ PhpSpreadsheet 安装`

`▶ 操作参考表 "tp5_xop_excel" 的创建`

`▶ 集成操作类 "SpreadsheetService.php" 的下载`

`■☞ 读取 excel 中的数据`

`▷ 前端操作`

`▷ 服务端接口操作`

`▶ 【重要提示】`

`■☞ 将数据导出到 excel 文件`

`▷ 前端操作`

`▷ 服务端接口操作`

`▶ 【提示】`

`■□☞ 附录`

☞☛ 【`源码下载 >>>`】

①. `"The filename xxxxx is not recognised as an OLE file"`

②. `"ZipArchive::getFromName(): Invalid or uninitialized Zip object"`

③. `"文件删除时会有报错：unlink xxx Resource temporarily unavailable`

④. `"Fatal error: Allowed memory size of 268435456 bytes exhausted (tried to allocate 268435464 bytes) in ..."`

☛ `【如果想下载大量数据，比如20万条】--- （2021-3-25 补充）`

☛ `【如果想一次生成带有多个 sheet 的文件】` （2021-5-19 补充）