excel导出类

<?php

namespace common\tools;

use Yii;

//excel处理
class Excel {
    public static $width=15;
    //execl out
    /**
      * templatepath 模版路径
      * m_data页面数据[['row_offset'=>行偏移,'col_offset'=>列偏移,data=>],[],...]  
      * args['outputFileName'=>'输出exce.xlsx文件名','cell_writedate'=>'日期写入那个单元格']
      * $callback 回调函数
      *
    **/
    public static function out($templatepath,$m_data,$args=[],$callback="") {
        set_time_limit(0);
        ini_set('memory_limit', '1024M');
        $cell_writedate=isset($args['cell_writedate'])?$args['cell_writedate']:'C2';
        $outputFileName=isset($args['outputFileName'])?$args['outputFileName']:'temp.xlsx';
        //$filePath = Yii::getAlias('@template').'/team_staff.xlsx';
        $filePath =$templatepath;
        if(!file_exists($filePath)){
            exit('模板文件不存在!');
        }
        $readObj =  \PHPExcel_IOFactory::createReaderForFile($filePath);
        $excelObj = $readObj->load($filePath);
         if($m_data){
            //var_dump($m_data);exit;
            foreach($m_data as $page=>$data_v){//页
                 $downdate=date('Y-m-d H:i:s');
                 $excelObj->setActiveSheetIndex($page-1);
                 $sheet = $excelObj->getActiveSheet();
                 if($cell_writedate) $sheet->setCellValue($cell_writedate,$downdate);
                  $row_offset=isset($data_v['row_offset'])?$data_v['row_offset']:'0';
                  $col_offset=isset($data_v['col_offset'])?$data_v['col_offset']:'0';
                  $startrownum=1+$row_offset;
                  //$startrownum=0;
                  $maxcol=0;
                 foreach($data_v['data'] as $v_key=>$v_v){
                    $row=$v_key+1+$row_offset;
                    //if($v_key==0) $startrownum=$row;
                    foreach($v_v as $v_v_k=>$celldate){
                        //列，行
                        $col=$v_v_k + $col_offset;
                        $maxcol=$maxcol<$col?$col:$maxcol;
                        $sheet->setCellValueByColumnAndRow($col,$row,  $celldate);
                    }
                    
                 }
                 //设置列宽
                 for($nCol = $col_offset; $nCol <= $maxcol; ++ $nCol) {
                   $sheet->getColumnDimensionByColumn ( $nCol )->setAutoSize ( false );
                   $sheet->getColumnDimensionByColumn ( $nCol )->setWidth(self::$width);
                 }

                  //回调函数
                if($callback){
                  $callback($sheet,$row,$startrownum,$args);
                }

            }
          }
         //默认激活0 sheet导出的时候
          $excelObj->setActiveSheetIndex(0);
       // $outputFileName =  'XXXXX_'.date('Y-m-d').'.xlsx';
        $agent = Yii::$app->request->getUserAgent();
        if (stripos($agent, 'windows nt')) {
            $outputFileName = iconv("utf-8", 'gbk', $outputFileName);
        }
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="'. $outputFileName .'"');
        header('Cache-Control: max-age=0');
		
        $objWriter = \PHPExcel_IOFactory:: createWriter($excelObj, 'Excel2007');
        $objWriter->save( 'php://output');
        exit;
    }


  // 无模版导出excel
  public static function excel($filename, $datainfo, $name, $format = 0) {
        $phpexcel = new \PHPExcel ();

        $phpexcel->getProperties ()->setCreator ( "www" );
        $phpexcel->getProperties ()->setLastModifiedBy ( "rorot" );
        $phpexcel->getProperties ()->setTitle ( $filename );
        $phpexcel->getProperties ()->setSubject ( $filename );
        $phpexcel->getProperties ()->setDescription ( $filename );
        $phpexcel->getProperties ()->setKeywords ( "office 2007 lenovo openxml php" );
        $phpexcel->getProperties ()->setCategory ( "statistics file" );
      
        foreach($datainfo as $k => $data){
          if($k == 0){
            $phpexcel->setActiveSheetIndex ( $k );
            $phpexcel->getActiveSheet ()->setTitle ( $name[$k] );
          }else{
            //插入第二张工作表
            $msgWorkSheet = new \PHPExcel_Worksheet($phpexcel, $name[$k]); //创建一个工作表
            $phpexcel->addSheet($msgWorkSheet); //插入工作表
            $phpexcel->setActiveSheetIndex($k); //切换到新创建的工作表
          }
          $nRow = 0;
          $nCol = 0;
          $sheet = $phpexcel->getActiveSheet ();

          for($nRow = 0; $nRow < count ( $data ); ++ $nRow) {
            $row = $data [$nRow];
            for($nCol = 0; $nCol < count ( $row ); ++ $nCol) {
              $sheet->setCellValueByColumnAndRow ( $nCol, $nRow + 1, $row [$nCol]);
            }
          }
          for($nCol = 0; $nCol < count ( $data [0] ); ++ $nCol) {
            $sheet->getColumnDimensionByColumn ( $nCol )->setAutoSize ( false );
            $sheet->getColumnDimensionByColumn ( $nCol )->setWidth(self::$width);
          }
        }
        
	  $phpexcel->setActiveSheetIndex(0);
	
	  $outputFileName = $filename . '.xlsx';
        $agent = Yii::$app->request->headers->get('user-agent');
        if (stripos($agent, 'windows nt')) {
          $outputFileName = iconv("utf-8", 'gbk', $outputFileName);
        }
        header ( "Content-Type: application/force-download" );
        header ( "Content-Type: application/octet-stream" );
        header ( "Content-Type: application/download" );
        header ( 'Content-Disposition:inline;filename="' . $outputFileName . '"' );
        header ( "Content-Transfer-Encoding: binary" );
        header ( "Expires: Mon, 26 Jul 1997 05:00:00 GMT" );
        header ( "Last-Modified: " . gmdate ( "D, d M Y H:i:s" ) . " GMT" );
        header ( "Cache-Control: must-revalidate, post-check=0, pre-check=0" );
        header ( "Pragma: no-cache" );
        \PHPExcel_IOFactory::createWriter ( $phpexcel, 'Excel2007' )->save ( 'php://output' );
        exit;
  }

  //导出csv
  public static function csv($filename, $data){
      //设置header头
      header('Content-Description: File Transfer');
      header('Content-Type: application/vnd.ms-excel');
      header('Content-Disposition: attachment; filename="' . $filename . '"');
      header('Expires: 0');
      header('Cache-Control: must-revalidate');
      header('Pragma: public');
      //打开php数据输入缓冲区
      $fp = fopen('php://output', 'a');
      //如果在csv中输出一个空行，向句柄中写入一个空数组即可实现
      foreach ($data as $row) {
          //将数据编码转换成GBK格式
          mb_convert_variables('GBK', 'UTF-8', $row);
          fputcsv($fp, $row);
          //将已经存储到csv中的变量数据销毁，释放内存
          unset($row);
      }
      //关闭句柄
      fclose($fp);
      exit;
  }
}