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

最新推荐文章于 2023-11-17 15:21:57 发布
最新推荐文章于 2023-11-17 15:21:57 发布
阅读量3k 收藏 14
点赞数 7
分类专栏： ThinkPHP 数据库 文章标签： PhpSpreadsheet php 数据库 thinkphp
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u011415782/article/details/111208015
版权

■☞ 背景

  • 最近在后台业务需求中又涉及到了 Excel 表的导入导出
    正要参考以前整理的文章 —— 【ThinkPHP5.1 excel 表的导入导出操作 (PHPExcel)】

    但是发现，好多道友早就提示了 "PHPExcel" 几年前就不再维护了
    所以，觉得有必要整理一份 "PhpSpreadsheet " 的使用指导

    希望能帮到有需求的道友，欢迎指摘 …

  • 参考文档

    1. 官方文档 ——【欢迎使用 PhpSpreadsheet 的文档】(英文不好的直接谷歌右键翻译就好呗)
    2. 如果觉得有帮助，也可参考参考鄙人获取的一份入门手册
链接：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() "
    $header = ['商品名称','缩略图','产地','售价','状态'];
$opData = (new Xmozxx())->getExcelTestLoginData();
//此时去下载 Excel文件
(new SpreadsheetService())->outputDataToExcelFile($header,$opData,"哎呦喂-数据表");

▶ 【提示】

此处展示一下对操作方法 "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()" 进行文件的删除操作

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

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

☛ 【如果想下载大量数据，比如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($sheetTag) 的使用

优化思路：

  • ① . 在 "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);
}

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

  • ④. 参考最终效果如下：

moTzxx
关注
  • 7
    点赞
  • 踩
  • 14
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
thinkphp5.1 框架导入/导出excel文件操作示例
10-15
主要介绍了thinkphp5.1 框架导入/导出excel文件操作,结合实例形式分析了thinkphp5.1 框架导入/导出excel文件具体操作实现技巧与相关注意事项,需要的朋友可以参考下
ThinkPHP5.1 框架下 PhpSpreadsheet 操作 Excel 表的导入导出.rar
12-16
本资源对应文章：ThinkPHP5.1 框架下 PhpSpreadsheet 操作 Excel 表的导入导出 希望可以帮助到有需要的道友们，入门后可做优化哦
ThinkPHP5.1 excel表的导入导出操作 (PHPExcel)
04-21
本资源为了实现 excel表的导入导出操作 同时也包含了 phpexcel 的源码，以保证后期的备份使用 具体使用方法，请参考文章： https://blog.csdn.net/u011415782/article/details/89430330
thinkphp导入excel遇到Fatal error: Allowed memory size of 1073741824 bytes exhausted
qq_35220984的博客
09-20 2470
记录一下使用thinkphp开发excel导入功能时碰到的一个小BUG。
thinkphp 操作导入excel表 且保存错误数据到新的excel表
李小文的博客
06-03 376
准备工作：下载PHPExcel文件存放在自己的文件下（我放在了extend目录下） 1.前端：先实现可以上传文件（和平时上传文件一样） 因为我的页面还有其他功能的实现，所以自己写了个方法来收回传的上传成功文件的路径（本人用的是前端layui框架，自带上传） 后台：处理数据的方法。 $upload = input('upload'); $acti...
ZipArchive::getFromName(): Invalid or uninitialized Zip object
wgchen
07-19 5660
PHPword操作word报错处理解决办法：调用php自带的zip服务原因解释 composer.json 扩展包 "phpoffice/phpword": "^0.18.2", 开发框架：laravel5.8 报错提示：ZipArchive::getFromName(): Invalid or uninitialized Zip object 问题描述：在用 new \PhpOffice\PhpWord\TemplateProcessor(‘1.docx’)进行模板替换时出现错误 问题原因：可能有多种
导入、导出大量数据时请求404(刚开始时请求状态是pending，过一段时间变为404)
sun_luming的博客
12-23 2422
背景： springboot+vue前后端分离开发，导入Excel数据时请求 从pending状态变为404： 在nginx中加入以下配置： proxy_read_timeout 6000s; proxy_send_timeout 6000s; 注意配置添加的位置，以及nginx多配制文件情况下配置的优先级，这种个别接口的配置尽量不要放到主配置文件中。 ...
tp5 PHPExcel导入大数据导致服务端程序不响应 超时
bosris的博客
10-19 1358
tp5下PHPExcel导入，前端采用ajax方式处理，起初导入一切正常。当数据量变大后只能导入前部分数据，后面直接报服务器500错误，无法找到服务端处理方法，后续数据导入失败。2、php.ini里的 max_execution_time = 300 改 max_execution_time = 0。归根结底还是php超时问题，因为我用的是自己的服务器，可以修改服务器配置文件，下面是解决办法。这里我用的是Apache，如果是Nginxy也是同样的思路。最大执行时间单位为妙，0为不做限制。
Warning: ZipArchive::getFromName(): Invalid or unitialized Zip object 解决方法
weixin_33858336的博客
02-28 1415
Warning: ZipArchive::getFromName(): Invalid or unitialized Zip object 解决方法 Excel版本问题。解决方法：判断当前版本。 $objReader = PHPExcel_IOFactory::createReader('Excel2007');if(!$objReader->canRead($file)){ $o...
PHP连接SQL Server的方法分析【基于thinkPHP5.1框架】
10-17
主要介绍了PHP连接SQL Server的方法,结合实例形式分析了基于thinkPHP5.1框架Db类以及使用PDO进行SQL Server数据库连接的相关操作实现技巧,需要的朋友可以参考下
宝塔ThinkPhp6通过excel插入五十万条数据实例
最新发布
weixin_47961874的博客
11-17 165
在TP6下上传excel表格并把excel中的数据导入到数据库的一种方式，数据量为50W级
php phpspreadsheet批量导入多工作表excel数据
向宇
12-27 1539
phpspreadsheet批量导入使用的进阶版 关联文章： thinkphp thinkadmin使用phpspreadsheet批量导入excel数据 https://blog.csdn.net/qq_36303853/article/details/113662876 需求 我想要批量导入获取后两个表格的内容 工作表的具体内容如下 代码实现 后端代码 use PhpOffice\PhpSpreadsheet\IOFactory; public function import(){ s.
php _get invalid,PHP 错误: ZipArchive::getFromName(): Invalid or unitialized Zip object in 解决方法...
weixin_36212135的博客
04-01 938
PHP 错误: ZipArchive::getFromName(): Invalid or unitialized Zip object in 解决方法发布于 2014-11-07 15:00:00 | 4304 次阅读 | 评论: 3 | 来源: 网友投递PHP开源脚本语言PHP(外文名: Hypertext Preprocessor，中文名：“超文本预处理器”)是一种通用开源脚本语言。语法吸...
php 表格 筛选,excel表格中筛选归类_如何将数据从mysql数据库导出成excel格式?是php的...
weixin_31688465的博客
03-09 230
php中如何将execl的数据导入到数据库中1.使用PHPExcelParserPro软件，但是这个软件收件；2.可将EXCEL表保存为CSV格式，然后通过phpmyadmin或者SQLyog导入，SQLyog导入的方法为：·将EXCEL表另存为CSV形式；·打开SQLyog，对要导入的表格右击，点击“导入”-“导入使用加载本地CSV数据”；·在弹出的对话框中，点击“改变..”，把选择“填写exc...
Thinkphp5中使用PhpSpreadsheet实现excel的导入导出
morishima的博客
10-16 1814
一、PhpSpreadsheet 介绍 1.为什么要使用 PhpSpreadsheet 由于 PHPExcel 已经不再维护，所以推荐使用 PhpSpreadsheet 来实现在 Thinkphp5 中实现 excel 的导入或导出功能，PhpSpreadsheet 是一个用 php 编写的库，并引入了命名空间，PSR 规范等。这里简单介绍下 PhpSpreadsheet 在Thinkphp5 框架中的使用方法。 2.使用环境 PHP5.6 或更高版本，推荐 PHP7 支持 php_zip 扩展 支持 ph
利用PHPExcel上传Excel表格出现的“ZipArchive::getFromName(): Invalid or uninitialized Zip object”错误解决方法之一
热门推荐
spaceX_91的博客
05-24 1万+
原文详细地址：http://www.newphper.com/post/384.html 我在使用thinkPHP5.X整合PHPExcel进行Excel表格上传时出现了错误： ZipArchive::getFromName(): Invalid or uninitialized Zip object。 Excel表格分为.xls和.xlsx两种格式，后来经过查资料和实际测试，发现PHPEx...
tp6 thinkphp6 excel导入导出 PHPExcel导入导出excel
08-02 4947
tp6 thinkphp6用PHPExcel导入导出excel
ThinkPHP5框架：Layui 下 image、video、excel 文件的上传实现
u011415782的专栏
05-22 1409
背景 前段时间因为业务需求，涉及到了多种文件的上传操作； 其中，包含 图片、Excel文件、视频文件 比如：我需要进行 excel 表格文件的上传，然后读取其中的数据写入数据库 那么，至少要限制一下上传文件的类型 在此，进行整理一番，欢迎指摘 … 环境 - 前端框架：Layui 2.5.4 - 使用框架：ThinkPHP 5.1.2 - 文件类型：image、excel、video
thinkphp5.1 使用Spreadsheet 导出10万条数据到excel的代码
03-14
以下是一个使用Spreadsheet导出10万条数据到Excel的示例代码： ``` use PhpOffice\PhpSpreadsheet\Spreadsheet; use PhpOffice\PhpSpreadsheet\Writer\Xlsx; // 创建一个新的Excel文档 $spreadsheet = new ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值