ThinkPHP 3导出数据防止内存泄露

一、php代码

/**
    * 分页导出数据(总是顺序导出).
    * 返回的是 json, 由前端 js 生成 csv/xls 文件.
    * @param int $page 当前页数
    * @param int $limit 当前导出的记录数目 (这个值不能太大, 否则会造成内存超出限制)
    */
   public function export_json($page = 1, $limit = 2000)
   {
      $this->checkIndexPermission();
      if (!$this->admin->canExportGoods()) {
         exit;
      }
      
      $exportType = null;
      $goods = D('Goods');
      $where = $this->getSearchOptions();
      $count = $goods->alias('a')->where($where)->count();
      $totalPage = ceil($count / $limit);
      $list = $goods->alias('a')
         ->join('LEFT JOIN goods_trace b ON a.id = b.gid')
         ->field('a.*,b.record_time')
         ->where($where)
         ->page($page, $limit)->order('a.id desc')->select();
      $titles = $this->getExportFieldNames($exportType);
      $rows = $this->exportToArray($list);
      $data = array(
         'titles' => array_values($titles), // 标题
         'rows' => $rows, // 数据列表
         'percentage' => (int)(($page / $totalPage) * 100), // 当前总进度
      );

      // 如果不是最后一页, 就加一个 next_url 的字段, 前端根据这个字段继续请求下一页数据
      if ($page < $totalPage) {
         $search = I('get.');
         $search['page'] = $page + 1;
         $data['next_url'] = U('export_json', $search);
      }
      $this->ajaxReturn($data);
   }
复制代码

 /***
    *  导出字段名称
    */
   public function getExportFieldNames()
   {
      $allFields = array(
         // 字段名 => 标题名称
         'id' => '订单id',
         'dian' => '店铺',
         'utime' => '进店时间',
         'wang' => '网销',
         'name' => '客户姓名',
         'phone' => '客户电话',
         'name1' => '客户姓名1',
         'phone1' => '客户电话1',
         'wx' => '微信',
         'is_contacted' => '是否联系',
         'men' => '门市',
         'record_time' => '走单时间',
         'jiage' => '标准套系',
         'sfje' => '实付金额',
         'kefu' => '客服',
         'beizhu' => '备注',
         'ctime' => '录入时间',
      );

      return $allFields;
   }
复制代码

/**
    * 获取 字段映射 数组.
    * [数据库中的字段名 => 导出的字段名]
    * @return array
    */
   public function exportToArray($list)
   {
      $res = array();
      foreach ($list as $key=>$data) {
         $res[$key] = array_values(array(
            'id' => $data['id'],
            'dian' => $data['dian'],
            'utime' => $data['utime'],
            'wang' => $data['wang'],
            'name' => $data['name'],
            'phone' => $data['phone'],
            'name1' => $data['name1'],
            'phone1' => $data['phone1'],
            'wx' => $data['wx'],
            'is_contacted' => $data['is_contacted'] == 1 ? '是' : '否',
            'men' => $data['men'],
            'record_time' => $data['record_time'],
            'jiage' => $data['jiage'],
            'sfje' => $data['sfje'],
            'kefu' => $data['kefu'],
            'beizhu' => $data['beizhu'],
            'ctime' => $data['ctime'],
         ));
      }
      return $res;
   }

复制代码

二、html代码

<a id="export-data-btn" class="btn btn-warning" data-href="{:U('keyun/list/export_json', array_merge($search_options))}">数据导出</a>

<div class="modal fade" id="export-modal" tabindex="-1" role="dialog" data-backdrop="static">
   <div class="modal-dialog" role="document">
      <div class="modal-content">
         <div class="modal-header">
            <button type="button" class="close" data-dismiss="modal" aria-label="Close">
            <span aria-hidden="true">&times;</span></button>
            <h4 class="modal-title" id="myModalLabel">数据导出</h4>
         </div>
         <div class="modal-body">
            <p>数据导出中, 请勿刷新或关闭浏览器</p>
            <div class="progress">
               <div id="export-progress" class="progress-bar" role="progressbar" aria-valuenow="0" aria-valuemin="0"
                    aria-valuemax="100" style="width: 0%;">
               </div>
            </div>
         </div>
      </div>
   </div>
</div>

<script>
   $(function () {
      $('#export-data-btn').click(function () {
         var url = $(this).data('href');
         var fetched_data = {
            rows: []
         };

         var hint = $('#export-modal');
         hint.modal('show');
         fetchDataAndExport(url, fetched_data);

         // 分页获取数据, 并导出
         function fetchDataAndExport(url, data, failed) {
            if (!failed) {
               failed = 0;
            }
            $.get(url).done(function (res) {
               if (res.titles) {
                  data.titles = res.titles
               }
               if (res.rows) {
                  data.rows = $.merge(data.rows, res.rows);
               }
               if (res.next_url) { // 还没有完成, 继续获取数据
                  fetchDataAndExport(res.next_url, data, failed)
               } else { // 已经完成
                  var date = new Date();
                  var datetime = date.getFullYear() + '-' + date.getMonth() + '-' + date.getDate();
                  download(generateCSV(data), datetime + '.xls', 'application/vnd.ms-excel')
                  setTimeout(function () {
                     hint.modal('hide')
                  }, 1000)
               }

               $('#export-progress').css('width', res.percentage + '%');
            }).fail(function () {
               failed++;

               // 整个过程允许失败 5次, 如果超过 5次, 终止
               if (failed > 5) {
                  hint.modal('hide');
                  alert('导出失败, 获取数据失败!')
               } else {
                  // 重试
                  fetchDataAndExport(url, data, failed)
               }
            })
         }

         // 直接用 js 生成(保存)文件
         function download(data, filename, type) {
            var file = new Blob([data], {type: type});
            // IE10+
            if (window.navigator.msSaveOrOpenBlob) {
               window.navigator.msSaveOrOpenBlob(file, filename);
            } else { // Others
               var a = document.createElement("a"),
                  url = URL.createObjectURL(file);
               a.href = url;
               a.download = filename;
               document.body.appendChild(a);
               a.click();
               setTimeout(function () {
                  document.body.removeChild(a);
                  window.URL.revokeObjectURL(url);
               }, 0);
            }
         }

         // 返回字符串
         function generateCSV(data) {
            var content = "\ufeff"; // BOM头, 防止中文乱码
            if (data.titles) {
               content += generateRow(data.titles);
            }
            for (var i in data.rows) {
               content += generateRow(data.rows[i])
            }

            return content
         }

         function generateRow(row) {
            var str = '';
            for (var i = 0; i < row.length; i++) {
               var val = row[i] === null ? '' : row[i];
               if (i === 0) {
                  str = val
               } else {
                  str = str + ',' + val
               }
            }

            return str + '\r\n';
         }
      });
   })
</script>

复制代码