PHP 导出 Excel

由于业务需要,需要在后台添加导出Excel的功能。所以很自然的想到了PHPExcel,于是就开始动手了。
由于之前没有做过,所以基本都是借鉴网上大佬的。

  1. 检查是否有PHPExcel包,如果没有,请在网上下好。
    2.开始写代码了。需要注意得是,一般导出数据,数据量都会较大。所以需要设置内存使用量和,超时时间。并且需要注意导出的数据会被Excel改写,如身份证号,手机号,时间戳等长数字会被显示为科学计数法,时间格式也显示为Excel的默认显示格式。脾气是大得很呐....惹不起惹不起。所以经过查资料寻得PHPExcel中有个方法(setCellValueExplicit)是可以设置导出单元格格式为文本。还有种方法是获取数据时做处理,后面会讲到。

    /**\
    * 导出Excel文件 速度慢
    * @param $fileName 导出的文件名
    * @param $headArr 数据头
    * @param $data 导出数据
    */
    function getExcel($fileName,$headArr,$data){
      //设置PHP最大单线程的独立内存使用量
      ini_set('memory_limit','1024M');
      //程序超时设置设为不限时
      ini_set('max_execution_time ','0');
    
      //导入PHPExcel类库,因为PHPExcel没有用命名空间,所以使用vendor导入
      vendor("PHPExcel.PHPExcel.IOFactory");
      vendor("Excel.PHPExcel");
      vendor("Excel.PHPExcel.Writer.Excel5");
      vendor("Excel.PHPExcel.IOFactory.php");
    
      //对数据进行检验
      if(empty($data) || !is_array($data)){
          die("data must be a array");
      }
      //检查文件名
      if(empty($fileName)){
          exit;
      }
      $date = date("Y_m_d",time());
      $fileName .= "_{$date}.xls";
      //创建PHPExcel对象
      $objPHPExcel = new \PHPExcel();
    
      //设置表头
      $key = ord("A");
      foreach($headArr as $hkey => $v){
          $colum = chr($key);
          $objPHPExcel->setActiveSheetIndex(0) ->setCellValue($colum.'1', $v);
          $key += 1;
          unset($headArr[$hkey]);
      }
      $column = 2;
      $objActSheet = $objPHPExcel->getActiveSheet();
      foreach($data as $key => $rows){ //行写入
          $span = ord("A");
          foreach($rows as $keyName=>$value){// 列写入
              $j = chr($span);
              //设置导出单元格格式为文本,避免身份证号的数据被Excel改写
              $objActSheet->setCellValueExplicit($j.$column, $value);
              $span++;
              unset($rows[$keyName]);
          }
          $column++;
          unset($data[$key]);
      }
      $fileName = iconv("utf-8", "gb2312", $fileName);
      //重命名表
      // $objPHPExcel->getActiveSheet()->setTitle('test');
      //设置活动单指数到第一个表,所以Excel打开这是第一个表
      $objPHPExcel->setActiveSheetIndex(0);
      ob_end_clean();
      ob_start();
      header('Content-Type: application/vnd.ms-excel');//定义输出的文件类型为excel文件
      header("Content-Disposition: attachment;filename=\"$fileName\"");//定义输出的文件名
      header('Cache-Control: max-age=0');//强制每次请求直接发送给源服务器,而不经过本地缓存版本的校验。
      $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
      $objWriter->save('php://output'); //文件通过浏览器下载
      exit;
    
    }

    3.调用实现,此处用的是thinkPHP。如框架不同,请自行修改语法。

    public function exportexcel(){
        $sqlWhere = session('H5OPERATIONLOG_TIME_WHERE');
        $data = M(self::T_TABLE)->where('create_time'.$sqlWhere)->select();//导出数据
        $filename="XXXXX";//导出文件名
        $headArr=array("XXX","XXX1","XXX2");//数据头
        getExcel($filename,$headArr,$data);
    }
    
    <td><a href="你的exportexcel访问路径"><input type="button" value="导出Excel"></input></a></td>

    以上借鉴于:https://www.cnblogs.com/HoverM/p/6210178.h...

4.原本以为这样完事了。(事实上这样也算完事了)但是后来觉得,貌似导出速度有点慢。就找了时间再深入研究了一下。于是找到了另一种比较好的方法。-------导出CSV--------

5.CSV概念

CSV是一种通用的、相对简单的文件格式,被用户、商业和科学广泛应用。最广泛的应用是在程序之间转移表格数据,而这些程序本身是在不兼容的格式上进行操作的(往往是私有的和/或无规范的格式)。因为大量程序都支持某种CSV变体,至少是作为一种可选择的输入/输出格式。

6.因为CSV通常也是使用Excel查看。所以不可避免的,还是需要注意一些Excel的限制。

表数据限制:

Excel 2003及以下的版本。一张表最大支持65536行数据,256列。

Excel 2007-2010版本。一张表最大支持1048576行,16384列。

7.PHPExcel于CSV需要注意的地方

PHPexcel内存溢出:
既然数据限制在104W,那么数据分割就数据分割呗,于是你尝试50W一次导入表,然而PHPexcel内部有函数报内存溢出错误,然后你就不断的调小数据量,直到5W一次导入你都会发现有内存溢出错误。这是为什么呢,虽然你分割数据来导入多个数据表,但是最后PHPexcel内部还是一次性把所有表数据放进一个变量中来创建文件……额,这几百万数据一个变量存储,你想内存不溢出,还真有点困难。
(后来看了一些文章发现PHPExcel也有解决方案,PHPExcel_Settings::setCacheStorageMethod方法更改缓冲方式来减小内存的使用)

CSV输出buffer过多:
当你用PHP原生函数putcsv()其实就使用到了输出缓存buffer,如果你把几百万的数据一直用这个函数输出,会导致输出缓存太大而报错的,因此我们每隔一定量的时候,必须进行将输出缓存中的内容取出来,设置为等待输出状态。具体操作是:
ob_flush();
flush();

然后需要解决的就是,当数据量达到百万以上是。不可避免的就需要面对Excel的显示数据问题以及大量数据导出的问题。这个问题也是我在目前为止并没有解决的。参考文章中有说道一个方案就是使用yield生成器以及分段生成CSV文件然后再统一打包下载。如果有大神有这方面经验,还请不吝赐教。

文章出处:https://blog.csdn.net/qq_16142851/article/...

8.具体实现代码如下,需要注意的是。导出CSV的方法并不像PHPExcel一样提供了转文本格式的方法。所以这里需要在传入数据是做数据处理。具体方法为:在需要处理的数据后面拼接制表符:"/t"。一定要双引号。

     /**
   * @param array $head 数据头
   * @param string $sql  对象
   * @param string $mark csv文件名
   * @param string $fileName 压缩文件名
   * @param array $dateCountArr
   */
  function putCsv($head, $sql, $mark, $fileName, $dateCountArr)
  {
      //处理导出Csv需要转换为文本格式的数据
      $textArr = array(
          //mem_loan_excel键名 = $fileName
          "mem_loan_excel" => array(
              //对应字段
              'phone',
              'id',
              'time',
          ),
      );
      $Model = new \Think\Model();
      //设置PHP最大单线程的独立内存使用量
      ini_set('memory_limit','128M');
      set_time_limit(0);

//    输出Excel文件头
      header('Content-Type: application/vnd.ms-excel;charset=utf-8');
      header('Content-Disposition: attachment;filename="' . $fileName . '.zip"');
      header('Cache-Control: max-age=0');

      // 每隔$limit行,刷新一下输出buffer
      $limit = 2000;
      // buffer计数器
      $sqlLimitCount = 0;
      $cnt = 0;
      $fileNameArr = array();
      // 逐行取出数据,不浪费内存
      foreach ($dateCountArr as $key => $value){
          $fp = fopen($mark . '_' . $value['date'] . '.csv', 'w'); //生成临时文件
          fwrite($fp, chr(0xEF).chr(0xBB).chr(0xBF));//转码 防止乱码(比如微信昵称(乱七八糟的))
          $fileNameArr[] = $mark . '_' .  $value['date'] . '.csv';
//        将数据通过fputcsv写到文件句柄
          fputcsv($fp, $head);

          $sqlLimitCount += $value['count'];

          $sqlLimit = $sqlLimitCount - $value['count'];//每次计数开始的位置,所有数据相加 - 当前数据

          $dataArr = $Model->table($sql . ' a')->limit($sqlLimit,$value['count'])->select();
          foreach ($dataArr as $a) {
              //加"\t"形成文本格式,原样输出
              if($textArr[$fileName]){
                  foreach ($a as $key => $val){
                      if(in_array($key,$textArr[$fileName])){
                          $a[$key] = $val."\t";
                      }
                  }
              }

              $cnt++;
              if ($limit == $cnt) {
                  //刷新一下输出buffer,防止由于数据过多造成问题
                  ob_flush();
                  flush();
                  $cnt = 0;
              }

              fputcsv($fp, $a);
          }
          fclose($fp);  //每生成一个文件关闭
      }
      //进行多个文件压缩
      $zip = new ZipArchive();
      $ZipFilename = $fileName . ".zip";
      $openFile = $zip->open($ZipFilename, ZipArchive::CREATE);   //打开压缩包
      if($openFile!=true || empty($fileNameArr)){
          exit("file Error");
      }
      foreach ($fileNameArr as $file) {
          $zip->addFile($file, basename($file));   //向压缩包中添加文件
      }
      $zip->close();  //关闭压缩包
      foreach ($fileNameArr as $file) {
          unlink($file); //删除csv临时文件
      }
      //输出压缩文件提供下载
      header("Cache-Control: public");
      header("Content-Description: File Transfer");
      header('Content-disposition: attachment; filename='.basename($ZipFilename)); //文件名
      header("Content-Type: application/zip"); //zip格式的
      header("Content-Transfer-Encoding: binary"); //告诉浏览器,这是二进制文件
      header('Content-Length: '. filesize($ZipFilename)); //告诉浏览器,文件大小

      ob_clean();
      flush();

      @readfile($ZipFilename);
      unlink($ZipFilename);
      exit;
  }

    /**
     ** 导出信息
      **/
    public function exportexcel(){
      $sqlWhere = session('H5OPERATIONLOG_TIME_WHERE');//这里是我这里的条件,需要修改
      $sql = M(self::T_TABLE)->where('create_time'.$sqlWhere)->buildSql();//获取到查询数据的sql
      $Model = new \Think\Model();
      //得到每一天的数据总数
      $dateCountArr = $Model->table($sql . ' a')
     ->field('count(*) as count, DATE_FORMAT(a.create_time,"%Y-%m-%d") as date')
     ->group('DATE_FORMAT(a.create_time,"%Y-%m-%d")')
     ->select();
     $filename="h5_operation_log_excel".time();
      $mark="h5_operation_log_info";
      $headArr=array("id","用户编号","数据唯一编号","数据名称","操作","操作者IP","操作批次","操作时间");
     putCsv($headArr,$sql,$mark,$filename,$dateCountArr);
    }

    <td><a href="你的exportexcel访问路径"><input type="button" value="导出Excel"></input></a></td>

    //此地方的出处找不到了,希望原作看到不要揍我。狗头保命ing......

9.CSV在Excel中展示会有点小问题

PHP 导出 Excel

解决方案:SYLK 文件时一个文本文件,开头的为“ID”或“ID_XXXX”(其中XXXX是文本字符串)。
SYLK 文件的第一个的记录是在 ID_Number 记录的。
Excel 将识别该文本在文本文件开头时, 它会将该文件解释为 SYLK 文件。
Excel 将尝试从该 SYLK 格式转换该文件,但不能这样做,因为"ID"字符后不有任何有效的 SYLK 代码。
因为 Excel 不能转换该文件,您收到错误消息。

当您打开一个文本文件、 CSV 文件和文件的前两个字符是大写字母"I","D"时,会发生此问题。
例如文本文件可能包含以下文本:
ID, STATUS 123, open 456, closed
如果前两个字母小写"i"和"d"不会发生此问题的 。

参考:https://www.cnblogs.com/A2008A/archive/201...

至此也就完成了。原本导出1W+条数据,PHPExcel需要8秒。现在换成CSV方法只需要两秒,也算能满足日常的运营需求了。当然,可以优化的地方还有很多。欢迎指正修改。

讨论数量: 2

是时候使用spreadsheet了

4天前 评论

@linzening 可以,有空试一试。感谢

4天前 评论

请勿发布不友善或者负能量的内容。与人为善,比聪明更重要!