■☞ 背景
- 最近在后台业务需求中又涉及到了
Excel表的导入导出 正要参考以前整理的文章 ——【ThinkPHP5.1 excel 表的导入导出操作 (PHPExcel)】但是发现，好多道友早就提示了"PHPExcel"几年前就不再维护了 所以，觉得有必要整理一份"PhpSpreadsheet "的使用指导 希望能帮到有需求的道友，欢迎指摘 … - 参考文档
- 官方文档 ——【欢迎使用 PhpSpreadsheet 的文档】(英文不好的直接谷歌右键翻译就好呗)
- 如果觉得有帮助，也可参考参考鄙人获取的一份入门手册
链接：https://pan.baidu.com/s/1NEIH-yO-c9okPxhD2bkShA 提取码：8ssr - 操作环境
> 框架：ThinkPHP5.1.40
> 类库：PhpSpreadsheet
> 技能：Composer, MySql
> PHP: php7.2.9 (建议 7.2 版本以上哦！)■☞ 前期准备
提示： 本人整理的文章，目前只是针对于 拓展名为
".xls"和".xlsx"的 excel 规范文件
▶ PhpSpreadsheet 安装
- 建议使用
composer，执行命令如下：
composer require phpoffice/phpspreadsheet▶ 操作参考表 "tp5_xop_excel" 的创建
为了方便测试，在此可以创建一个数据表，如果想节约时间，可以跳过
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 框架为例，核心代码如下：
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); "】
如下，便是鄙人的服务端接口核心源码：
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 操作类中的，核心代码如下：
/**
* 导入 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"的调用 ，建议详细阅读鄙人的注释信息
(new SpreadsheetService())->readExcelFileToArray()
附录文件夹中的
"small.xlsx",可作为导入操作的目标文件
【注意】：
- 第一点：上传的文件要标准
比如：直接将原文文件的扩展名
".xlsx"改为了".xls"，这是不允许的（可以打开后点击 “另存为”） - 第二点：操作的文件数据不要太大
鄙人测试，一万条的 excel 表还是没出问题的
如果出现溢出内存的问题，第一可考虑 在
php.ini中 增加memory_limit的限制 如果是百万数据的 excel 文件，目测还是需要优化代码的 目前，鄙人没有这个需求，毕竟小网站业务量有限 …
■☞ 将数据导出到 excel 文件
业务需求中经常遇到这个，当前整合的方法适合 excel 表样式不复杂的导出操作； 实际业务如果要求严格的话，那是需要根据开发文档，设置表格内容样式（文字颜色，大小等…）的哦 !
▷ 前端操作
鄙人发现，多数的业务适合将
excle文件下载到客户端【为例】，极少数要求保存到服务器指定位置的！
- 前端核心参考代码如下：
<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
Db::name('xop_excel')
->field('goods_name,thumbnail,place,reference_price,status')
->limit(6)
->select();" (new SpreadsheetService())->outputDataToExcelFile() "
▶ 【提示】
此处展示一下对操作方法
"outputDataToExcelFile()"的注释信息，保证阅读后才能正确使用
【提示】
- 如果业务上需要保存文件到指定位置，那就请参考方法
"public static function saveExcelFileToLocal()" - 鄙人也是建议不要下载太大的数据量
当前测试，满足个一两万条数据
（700KB左右）的下载 如果要求大数据量，还是需要进行代码优化的哦 ！【可见附录…】
■□☞ 附录
☞☛ 【源码下载 >>>】
☛ 代码中涉及到的公共方法
/**
* 公用的方法 返回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"
这个保存信息提示，基本的解释就是：上传的文件 【不标准】
比如： 直接将原文文件的扩展名 ".xlsx" 改为了 ".xls"
或者，文件是自己代码操作所下载的，生成过程不标准
该问题可以考虑打开文件，另存为 excel 其他类型的文件②. "ZipArchive::getFromName(): Invalid or uninitialized Zip object"
这出现在文件上传操作过程，值得一提的是，鄙人觉得这个问题最奇葩
网上对于这个问题：
多数的言论都是说 —— 操作文件的格式没有区分好 （"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（）进行输出流的分割下载 参考代码如下（我没有整合到源代码中，可做后期扩展）
/**
* 下载大量的数据到 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, 此处提供一个整理过的方法如下：
//注意我的参数的不同！注意！！注意！！！
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));
}- ②. 当需要写入数据时，循环调用上面的这个方法，最后再调用后面的导出文件代码即可：
if ($bigTag){
self::downloadBigExcelFileToClient($saveFileName,$opData,$header);
}else{
self::downloadExcelFileToClient($saveFileName);
}- ③. 上述代码是需要使用时优化的，尤其注意新提供参考方法中参数的不同，多多实际动手调优，胜过一切示例代码！
- ④. 参考最终效果如下：
