牛骨文教育服务平台(让学习变的简单)
博文笔记

PHPExcel 大数据的导出

创建时间：2017-12-05 投稿人： 浏览次数：642


技术QQ交流群:294088839


PHPExcel 是一个php语言读取导出数据、导入生成Excel的类库，使用起来非常方便，但有时会遇到以些问题，比如导出的数据超时，内存溢出等。

下面我们来说说这些问题和解决办法。

PHPExcel 版本：@version    1.8.0, 2014-03-02

能遇到这样的问题一般都是因为数据量大导致

1.PHPExcel 报错

报错提示：

"break" not in the "loop" or "switch" context

严格的讲这个不是PHPExcel的错误，是PHP版本的问题，大于PHP5.6以后，“break”必须要在循环体内执行（for ,foreach, while, switch）

此处无循环，解决办法：注释掉break;

2.超时

提示：

Maximum execution time of 30 seconds exceeded

数据量过大，php执行超过30秒后就会报这样的信息

解决办法：

可修改php.ini  或直接在执行页面中添加

set_time_limit(0);

这样就设置了php的执行超时

 

3.内存溢出

超时解决好之后，等待了好几十秒后又来了个错误：

Allowed memory size of 134217728 bytes exhausted (tried to allocate 20480 bytes)

内存不足呀！

解决内存溢出我们分两步走，

第一步：设置memory_limit

默认情况memory_limit 大小为100MB，当所需内存大于100MB就会溢出，所以设置足够大的值

ini_set("memory_limit", "1024M");  // 根据电脑配置不够继续增加

第二步：设置PHPExcel单元格缓存

单元格缓存是将所需PHPExcel内存单元格对象缓存到磁盘、memcache、MemoryGZip等，这样读取上会更耗时，但可以降低内存的消耗。

PHPExcel_CachedObjectStorageFactory 这个类中提供了这几个单元格缓存

复制代码 
    const cache_in_memory               = "Memory";
    const cache_in_memory_gzip          = "MemoryGZip";  #将单元格序列化后再进行Gzip压缩，然后保存在内存中
    const cache_in_memory_serialized    = "MemorySerialized";  # 将单元格数据序列化后保存在内存中
    const cache_igbinary                = "Igbinary";    #存储为紧密的二进制形式
    const cache_to_discISAM             = "DiscISAM";    #缓存在临时的磁盘文件中，速度可能会慢一些
    const cache_to_apc                  = "APC";     #Alternative PHP Cache可选PHP缓存
    const cache_to_memcache             = "Memcache";   #保存在memcache中
    const cache_to_phpTemp              = "PHPTemp";    #保存在php://temp
    const cache_to_wincache             = "Wincache";
    const cache_to_sqlite               = "SQLite";
    const cache_to_sqlite3              = "SQLite3";
复制代码

每一个worksheet都会有一个独立的缓存，当一个worksheet实例化时，就会根据设置或配置的缓存方式来自动创建。一旦你开始读取一个文件或者你已经创建了第一个worksheet，就不能在改变缓存的方式了。

  • MemorySerialized： 使用这种缓存方式，单元格会以序列化的方式保存在内存中，这是降低内存使用率性能比较高的一种方案。 
  • MemoryGZip： 与序列化的方式类似，这种方法在序列化之后，又进行gzip压缩之后再放入内存中，这回跟进一步降低内存的使用，但是读取和写入时会有一些慢。
  • DiscISAM：当使用cache_to_discISAM这种方式时，所有的单元格将会保存在一个临时的磁盘文件中，只把他们的在文件中的位置保存在PHP的内存中，这会比任何一种缓存在内存中的方式都慢，但是能显著的降低内存的使用。临时磁盘文件在脚本运行结束是会自动删除。
  • PHPTemp： 类 似cache_to_discISAM这种方式，使用cache_to_phpTemp时，所有的单元格会还存在php://temp I/O流中，只把 他们的位置保存在PHP的内存中。PHP的php://memory包裹器将数据保存在内存中，php://temp的行为类似，但是当存储的数据大小超 过内存限制时，会将数据保存在临时文件中，默认的大小是1MB，但是你可以在初始化时修改它。php://temp文件在脚本结束是会自动删除。 

 

4.大数据导出

微软的Excel设置单元格行数默认是6万行rows，相对的讲其实当我们超过1万行的时候已经是大数据的导出。

好比：有客户10000人，平均每人每天产生10条活动记录，要导出上周所有的客户活动记录： 10000*10*7=700000

估计看70万行的Excel这个人会疯掉的，我们的建议是分批次导出，按时间导出到不同的excel

下面是一个PHPExcel官方的Demo（已修改过）

复制代码 
        define("EOL", "<br />");
        $objPHPExcel = new appextensionsPHPExcelPHPExcel();
        ini_set("memory_limit", "1024M"); // 设置php可使用内存

        $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip;
        if (!PHPExcel_Settings::setCacheStorageMethod($cacheMethod)) {
            die($cacheMethod . " 缓存方法不可用" . EOL);
        }
        echo date("H:i:s"), " 当前使用的缓存方法是： ", $cacheMethod, " 方式", EOL;
        echo date("H:i:s"), " 开始设置文档属性", EOL;
        $objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
                ->setLastModifiedBy("Maarten Balliauw")
                ->setTitle("Office 2007 XLSX Test Document")
                ->setSubject("Office 2007 XLSX Test Document")
                ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
                ->setKeywords("office 2007 openxml php")
                ->setCategory("Test result file");


        echo date("H:i:s"), " 开始添加单元格标题", EOL;
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setCellValue("A1", "Firstname");
        $objPHPExcel->getActiveSheet()->setCellValue("B1", "Lastname");
        $objPHPExcel->getActiveSheet()->setCellValue("C1", "Phone");
        $objPHPExcel->getActiveSheet()->setCellValue("D1", "Fax");
        $objPHPExcel->getActiveSheet()->setCellValue("E1", "Is Client ?");
        
        // 设置单元格宽度
        $objPHPExcel->getActiveSheet()->getColumnDimension("A")->setAutoSize(true);
        $objPHPExcel->getActiveSheet()->getColumnDimension("B")->setWidth(50);
        
        /**
         * 左对齐与 右对齐
         * 可设置整列->getStyle("N")   可针对行rows设置getStyle("N3")
         */
        $objPHPExcel->getActiveSheet()->getStyle("B")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
        $objPHPExcel->getActiveSheet()->getStyle("B3")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);

        echo date("H:i:s"), " 设置隐藏C D列", EOL;
        $objPHPExcel->getActiveSheet()->getColumnDimension("C")->setVisible(false);
        $objPHPExcel->getActiveSheet()->getColumnDimension("D")->setVisible(false);

        echo date("H:i:s"), " 设置大纲级别", EOL;
        $objPHPExcel->getActiveSheet()->getColumnDimension("E")->setOutlineLevel(1)
                ->setVisible(false)
                ->setCollapsed(true);

        /**
         * 开始添加数据
         */
        for ($i = 2; $i <= 50000; $i++) {
            $objPHPExcel->getActiveSheet()->setCellValue("A" . $i, "FName $i")
                    ->setCellValue("B" . $i, "LName $i")
                    ->setCellValue("C" . $i, "PhoneNo $i")
                    ->setCellValue("D" . $i, "FaxNo $i")
                    ->setCellValue("E" . $i, true);
        }

        $objPHPExcel->getActiveSheet()->setTitle("供应商信息");
        echo date("H:i:s"), " 设置格式为Excel2007版格式", EOL;
        $callStartTime = microtime(true);

        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, "Excel2007");
        $objWriter->save(str_replace(".php", ".xlsx", __FILE__));
        $callEndTime = microtime(true);
        $callTime = $callEndTime - $callStartTime;

        echo date("H:i:s"), " 设置生成的文件为： ", str_replace(".php", ".xlsx", pathinfo(__FILE__, PATHINFO_BASENAME)), EOL;
        echo date("H:i:s"), " 写入Workbook中耗时 ", sprintf("%.4f", $callTime), " 秒", EOL;
        echo date("H:i:s"), " 当前内存使用情况: ", (memory_get_usage(true) / 1024 / 1024), " MB", EOL;
        echo date("H:i:s"), " 内存使用峰值: ", (memory_get_peak_usage(true) / 1024 / 1024), " MB", EOL;
        echo date("H:i:s"), " 完成写入文件", EOL;
        echo date("H:i:s"), " 文件被创建在： ", getcwd(), "目录", EOL;
复制代码

 

 

下面模拟一个大数据的导出：

msyql中tcustomer表有数据4万多条

我们设置

set_time_limit(0);
ini_set("memory_limit", "1024M"); 
PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip;  # 单元格缓存为MemoryGZip

然后导出所有4万多条数据

代码如下（Yii2）

复制代码 
/**
     * PHPExcel 数据导出
     */
    public function actionPhpexcel() {

        define("EOL", "<br />");
        $objPHPExcel = new appextensionsPHPExcelPHPExcel();
        ini_set("memory_limit", "1024M"); // 设置php可使用内存
        set_time_limit(0);  # 设置执行时间最大值

        $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip;
        if (!PHPExcel_Settings::setCacheStorageMethod($cacheMethod)) {
            die($cacheMethod . " 缓存方法不可用" . EOL);
        }
        echo date("H:i:s"), " 当前使用的缓存方法是： ", $cacheMethod, " 方式", EOL;
        echo date("H:i:s"), " 开始设置文档属性", EOL;
        $objPHPExcel->getProperties()->setCreator("dcb3688")
                ->setLastModifiedBy("dcb3688")
                ->setTitle("客户信息记录")
                ->setSubject("客户信息Document")
                ->setDescription("描述……")
                ->setKeywords("office 2007  php")
                ->setCategory("产品信息AAA");


        echo date("H:i:s"), " 开始添加单元格标题", EOL;
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setCellValue("A1", "客户姓名");
        $objPHPExcel->getActiveSheet()->setCellValue("B1", "性别");
        $objPHPExcel->getActiveSheet()->setCellValue("C1", "Province");
        $objPHPExcel->getActiveSheet()->setCellValue("D1", "City");
        $objPHPExcel->getActiveSheet()->setCellValue("E1", "Town");
        $objPHPExcel->getActiveSheet()->setCellValue("F1", "Telephone");
        $objPHPExcel->getActiveSheet()->setCellValue("G1", "属相");
        $objPHPExcel->getActiveSheet()->setCellValue("H1", "星座");
        $objPHPExcel->getActiveSheet()->setCellValue("I1", "血型");
        $objPHPExcel->getActiveSheet()->setCellValue("J1", "Nid");
        $objPHPExcel->getActiveSheet()->setCellValue("K1", "Uid");
        $objPHPExcel->getActiveSheet()->setCellValue("L1", "Etime");
        $objPHPExcel->getActiveSheet()->setCellValue("M1", "Regtime");
        $objPHPExcel->getActiveSheet()->setCellValue("N1", "Signup");
        $objPHPExcel->getActiveSheet()->setCellValue("O1", "经度");
        $objPHPExcel->getActiveSheet()->setCellValue("P1", "纬度");
        $objPHPExcel->getActiveSheet()->setCellValue("Q1", "类型");
        $objPHPExcel->getActiveSheet()->setCellValue("R1", "状态");

        /**
         * 单元格宽度
         */
        $objPHPExcel->getActiveSheet()->getColumnDimension("F")->setAutoSize(true);
        $objPHPExcel->getActiveSheet()->getColumnDimension("J")->setWidth(45);
        $objPHPExcel->getActiveSheet()->getColumnDimension("L")->setAutoSize(true);
        $objPHPExcel->getActiveSheet()->getColumnDimension("M")->setAutoSize(true);
        $objPHPExcel->getActiveSheet()->getColumnDimension("N")->setAutoSize(true);
        
        /**
         * 左对齐与 右对齐
         * 可设置整列->getStyle("N")   可针对行rows设置getStyle("N3")
         */
        $objPHPExcel->getActiveSheet()->getStyle("N")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
        $objPHPExcel->getActiveSheet()->getStyle("N3")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);





        #####################################开始添加数据###############################################################
        
        /**
         * 分页分时间： 微软Execl最大值6万行， total/60000=文件个数，  limit 60000, 60000
         * $model->find()->offset($pages->offset)->limit(60000)->all()
         */
        $model = appmodelsTcustomer::find()->orderBy("regtime desc")->all();
        if (!empty($model)) {
            foreach ($model as $key => $value) {
                $Cellkey = $key + 2;
                $blood = [1 => "A型", 2 => "B型", 3 => "AB型", 4 => "O型"];
                $objPHPExcel->getActiveSheet()->setCellValue("A" . $Cellkey, mb_substr($value->realname, 0, -1) . "*");
                $objPHPExcel->getActiveSheet()->setCellValue("B" . $Cellkey, rand(1, 2) == 1 ? "先生" : "女士");
                $objPHPExcel->getActiveSheet()->setCellValue("C" . $Cellkey, $value->province);
                $objPHPExcel->getActiveSheet()->setCellValue("D" . $Cellkey, $value->city);
                $objPHPExcel->getActiveSheet()->setCellValue("E" . $Cellkey, $value->town);
                $objPHPExcel->getActiveSheet()->setCellValue("F" . $Cellkey, $value->telephone ? substr($value->telephone, 0, 3) . "*********" : "");
                $objPHPExcel->getActiveSheet()->setCellValue("G" . $Cellkey, $value->sx);
                $objPHPExcel->getActiveSheet()->setCellValue("H" . $Cellkey, $value->constel);
                $objPHPExcel->getActiveSheet()->setCellValue("I" . $Cellkey, $blood[array_rand($blood)]);
                $objPHPExcel->getActiveSheet()->setCellValue("J" . $Cellkey, mb_substr($value->nid, 0, -3));
                $objPHPExcel->getActiveSheet()->setCellValue("K" . $Cellkey, $value->uid);
                $objPHPExcel->getActiveSheet()->setCellValue("L" . $Cellkey, $value->exp);
                $objPHPExcel->getActiveSheet()->setCellValue("M" . $Cellkey, $value->regtime);
                $objPHPExcel->getActiveSheet()->setCellValue("N" . $Cellkey, $value->signup ? $value->signup : "无数据");
                $objPHPExcel->getActiveSheet()->setCellValue("O" . $Cellkey, substr($value->lng, 0, 4) . rand(10000, 999999));
                $objPHPExcel->getActiveSheet()->setCellValue("P" . $Cellkey, substr($value->lat, 0, 3) . rand(10000, 999999));
                $objPHPExcel->getActiveSheet()->setCellValue("Q" . $Cellkey, $value->type == 1 ? "意向客户" : "观望中客户");
                $objPHPExcel->getActiveSheet()->setCellValue("R" . $Cellkey, $value->status == 1 ? "已下单" : "已跟进");
            }
        } else {
            die(" 暂无数据" . EOL);
        }

        #######################################################################################################

        $objPHPExcel->getActiveSheet()->setTitle("客户信息");
        echo date("H:i:s"), " 设置格式为Excel2007版格式", EOL;
        $callStartTime = microtime(true);

        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, "Excel2007");
        $objWriter->save(str_replace(".php", ".xlsx", __FILE__));
        $callEndTime = microtime(true);
        $callTime = $callEndTime - $callStartTime;

        echo date("H:i:s"), " 设置生成的文件为： ", str_replace(".php", ".xlsx", pathinfo(__FILE__, PATHINFO_BASENAME)), EOL;
        echo date("H:i:s"), " 写入Workbook中耗时 ", sprintf("%.4f", $callTime), " 秒", EOL;
        echo date("H:i:s"), " 当前内存使用情况: ", (memory_get_usage(true) / 1024 / 1024), " MB", EOL;
        echo date("H:i:s"), " 内存使用峰值: ", (memory_get_peak_usage(true) / 1024 / 1024), " MB", EOL;
        echo date("H:i:s"), " 完成写入文件", EOL;
        echo date("H:i:s"), " 文件被创建在： ", getcwd(), "目录", EOL;
    }
复制代码

执行后信息：

打开文件：

 

声明：该文观点仅代表作者本人，牛骨文系教育信息发布平台，牛骨文仅提供信息存储空间服务。
未上传头像

热门文章
CTF writeup 2_南邮网络攻防训...
SSM框架——详细整合教程（...
Linux Shell脚本编程－－curl命...
HttpClient使用详解
Java面试题全集（上）
JAVA设计模式之单例模式
java.lang.OutOfMemoryError: PermGen ...
TCP协议中的三次握手和四次...
form表单的两种提交方式，su...
String,StringBuffer与StringBuilder...
最新文章
Java之品优购课程讲义_day20（7）
剑指 Offer - 8：跳台阶
Netty权威指南_札记02_NIO编程
mysql时间属性之时间戳和datetime之...
虚拟现实或许可以拯救古埃及的“...
spring cloud服务注册中心eureka---集群...
Java SE 第六章
HTTP请求+数据库
HIDL学习笔记之HIDL C++（第二天）
ubuntu系统下指定tomcat运行时为JDK1.8...
copyright © 2008-2019 亿联网络 版权所有 备案号：粤ICP备14031511号-2