YII2 中使用PHPExcel

1. 下载PHPExcel

2. 找到vender核心文件   创建目录   vendor/phpoffice/phpexecel/

3. 引入类

方法一

<?php
require dirname(dirname(__FILE__)).'/vendor/phpoffice/phpexcel/Classes/PHPExcel.php';
>

方法二

( 1 )    修改文件vendor/composer/autoload_namespace.php，在return array();中增加一行：

'PHPExcel' => array($vendorDir . '/phpoffice/phpexcel/Classes'),

( 2 )     修改文件vendor/composer/autoload_static.php，找到 public static $prefixesPsr0 = array () 部分，在键名P的下面新增PHPExcel的内容：

public static $prefixesPsr0 = array (
    'P' => 
    array (
        'Prophecy\\' => 
        array (
            0 => __DIR__ . '/..' . '/phpspec/prophecy/src',
        ),
        
        #   PHPExcel ->  SRART
        'PHPExcel' =>
        array (
            0 => __DIR__ . '/..' . '/phpoffice/phpexcel/Classes',
        ),
        #   PHPExcel ->  END
    ),
    
    
);

4. 使用

<?php
namespace common\components\phpexcel;

use Yii;

class Phpexcel 
{
	// 通过文件后缀，调用类
	const TYPE_EXCEL2007 = 'Excel2007';
	const TYPE_EXCEL5 = 'Excel5';
	const TYPE_EXCEL2003XML = 'Excel2003XML';
	const TYPE_CSV = 'CSV';
    /**
     * excel第一排字母名字
     *
     * @var array
     */
    public $menuKey = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z');

    public $questionTypeStr = '';
	/**
	 * 获取 - 从文件获取数据转换成数组
	 * @return array
	 */ 
	public static function toData($filePath = null, $type = Phpexcel::TYPE_CSV, $arrAtrr = [], $arrSpecial = [])
	{
		if (!file_exists($filePath)) {
			return [];
		}

		set_time_limit(0);
	    ini_set('memory_limit','256M');

	    $phpexcel     = new \PHPExcel;
        $excelReader  = \PHPExcel_IOFactory::createReader($type);
        $phpexcel     = $excelReader->load($filePath)->getSheet(0);//载入
        $total_line   = $phpexcel->getHighestRow(); 
        $total_column = $phpexcel->getHighestColumn(); 
        ++$total_column;

        $arrData = [];
        for ($row = 2; $row <= $total_line; $row++) {
            $data = [];
            for ($column = 'A'; $column !=$total_column; $column++) {
            	// 给相应的数据赋值字段名称
            	if (!empty($arrAtrr) && !isset($arrAtrr["{$column}"])) {
            		continue;
            	}
            	$name = isset($arrAtrr["{$column}"]) ? $arrAtrr["{$column}"] : strtolower($column);
                $data[$name] = iconv('gb2312', 'utf-8', trim($phpexcel->getCell($column.$row) -> getValue()));
            }

            if (empty(array_filter($data))) {
                continue;
            }

            $arrData[] = $data;
        }

        return $arrData;
	}

	/**
     * 导出excel
     *
     * @param array $excelData => array('menu', 'data'), menu为第一排菜单，data为excel下载数据
     * menu为一列名 例如 $menu = array('username'=>'用户名')   'username'对应data数据项的username
     * data为多维数据项 例如 $data = array( 0=>array('username'=>'admin'), 1=>array('username' =>'test')
     * @param string $fileName
     */
    public function save($excelData, $fileName)
    {

        set_time_limit(0);
        ini_set('memory_limit', '5120M');
        ob_end_clean();//使用PHPExcel导出Excel时，需要清空缓冲区，否则会导致乱码！

        if ($excelData['menu'] && $excelData['data']) {
            //创建一个excel
            $objPHPExcel = new \PHPExcel();

            //第一排为菜单
            $i = 0;
            foreach ($excelData['menu'] as $key => $menuName) {
                $menuKey = $this->menuKey[$i] . '1';
                $objPHPExcel->getActiveSheet()->setCellValue($menuKey, $menuName);
                $i++;
            }


            //从第二排开始处理
            $i = 2;
            foreach ($excelData['data'] as $key=>$val) {
                $j = 0;
                foreach ($excelData['menu'] as $key => $menuName) {
                    $menuKey = $this->menuKey[$j] . $i;
                    $menuVal = isset($val[$key]) ? $val[$key] : '';
                    $objPHPExcel->getActiveSheet()->setCellValue($menuKey, $menuVal);
                    $this->selectOption($objPHPExcel, $key);
                    $j++;
                }
                $i++;
            }

            //保存excel—2007格式
            $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);

            //直接输出到浏览器
            if (strpos($_SERVER["HTTP_USER_AGENT"], "Firefox")) {
                //$fileName = iconv('utf-8', 'gb2312', $fileName);
            } else {
                if(ob_get_contents()) ob_end_clean();//ob_end_clean();
                //$fileName = urlencode($fileName);
            }
            header("Content- Type: application/vnd.ms-excel; charset=gb2312");
            header("Pragma: public");
            header("Expires: 0");
            header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
            header("Content-Type:application/force-download");
            header("Content-Type:application/vnd.ms-execl");
            header("Content-Type:application/octet-stream");
            header("Content-Type:application/download");;
            header("Content-Disposition:attachment;filename=" . $fileName);
            header("Content-Transfer-Encoding:binary");
            $resout = $objWriter->save('php://output');

            exit;
        } else {
            throw new Exception('excelData must array =>array(menu, data)');
        }
    }

    /**
     * [selectOption 下拉菜单]
     * @Author   mgang
     * @DateTime 2019-10-19T17:35:45+0800
     * @param    [type]                   $model [description]
     * @param    [type]                   $k     [description]
     * @return   [type]                          [description]
     */
    public function selectOption( $model, $k )
    {
        $info = '1-类型1,2-类型2,3-类型3,4-类型4';
        $model->setActiveSheetIndex(0);
        $objActSheet = $model->getActiveSheet();
        $objValidation = $objActSheet->getCell("B".($k+2))->getDataValidation(); //这一句为要设置数据有效性的单元格
        $objValidation -> setType(\PHPExcel_Cell_DataValidation::TYPE_LIST)
                   -> setErrorStyle(\PHPExcel_Cell_DataValidation::STYLE_INFORMATION)
                   -> setAllowBlank(false)
                   -> setShowInputMessage(true)
                   -> setShowErrorMessage(true)
                   -> setShowDropDown(true)
                   -> setErrorTitle('输入的值有误')
                   -> setError('您输入的值不在下拉框列表内.')
                   -> setPromptTitle('问题类型')
                   -> setFormula1('"' . $info . '"');
    }


    /**
     * [excelData 获取导入文件的数据]
     * @Author   mgang
     * @DateTime 2019-10-21T14:26:02+0800
     * @param    string                   $file  [description]
     * @param    integer                  $sheet [description]
     * @return   [type]                          [description]
     */
    public function excelData($file='', $sheet=0){ 
        $file = iconv("utf-8", "gb2312", $file);   //转码 
        if(empty($file) OR !file_exists($file)) exit(json_encode(array('code'=>0,'msg'=>'导入文件不存在')));
        
        $objRead = new \PHPExcel_Reader_Excel2007();   //建立reader对象 
        
        if(!$objRead->canRead($file)){ 
            $objRead = new \PHPExcel_Reader_Excel5(); 
            if(!$objRead->canRead($file)){ 
                die('No Excel!'); 
            } 
        } 
       
        $cellName = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ'); 
       
        $obj = $objRead->load($file);  //建立excel对象 
        $currSheet = $obj->getSheet($sheet);   //获取指定的sheet表 
        $columnH = $currSheet->getHighestColumn();   //取得最大的列号 
        $columnCnt = array_search($columnH, $cellName); 
        $rowCnt = $currSheet->getHighestRow();   //获取总行数 
       
        $data = array(); 
        for($_row=1; $_row<=$rowCnt; $_row++){  //读取内容 
            for($_column=0; $_column<=$columnCnt; $_column++){ 
                $cellId = $cellName[$_column].$_row; 
                $cellValue = $currSheet->getCell($cellId)->getValue(); 
                 //$cellValue = $currSheet->getCell($cellId)->getCalculatedValue();  #获取公式计算的值 
                if($cellValue instanceof \PHPExcel_RichText){   //富文本转换字符串 
                    $cellValue = $cellValue->__toString(); 
                } 
       
                $data[$_row][$cellName[$_column]] = $cellValue; 
            } 
        }
        return $data; 
    } 
    

}