PHP 导出 Excel

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

检查是否有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方法只需要两秒，也算能满足日常的运营需求了。当然，可以优化的地方还有很多。欢迎指正修改。

php excel csv

本作品采用《CC 协议》，转载必须注明作者和本文链接

_zzh

课程读者 125 声望

暂无个人描述~

2 人点赞

讨论数量: 3

linzening

71 声望

是时候使用spreadsheet了

5年前评论

_zzh

课程读者 125 声望

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

5年前评论

xuri

版主 617 声望 / 作者 @ 《Excelize权威指南》

推荐一个功能丰富、兼容性好、高性能的 Excel 文档基础库：github.com/xuri/excelize

4年前评论

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容，与人为善，比聪明更重要！

帮助

PHP 导出 Excel

社区赞助商

关于 LearnKu

资源推荐

服务提供商

其他信息

PHP 导出 Excel

社区赞助商

关于 LearnKu

资源推荐

服务提供商

其他信息

请登录