安裝PHPEXCEL

1.安裝命令：composer require phpoffice/phpexcel

引入

• 在使用時(shí)，需要引入classes目錄下的PHPExcel.php的PHPExcel類(lèi)以及classes\PHPExcel\IOFactory.php的PHPExcel_IOFactory類(lèi)。
• 如果使用thinkphp5.1框架，則直接use即可
  use PHPExcel_IOFactory;
use PHPExcel;
  我這里使用的TP3.2
  vendor('PHPExcel.PHPExcel');
vendor('PHPExcel.PHPExcel_IOFactory');

代碼

/**
     * 導(dǎo)入excel數(shù)據(jù)
     */
    public function excelAction(){
        $file = $_FILES['excel_file'];  //獲取文件
        header("Content-type:text/html;charset=utf-8");
        set_time_limit(0);
        vendor('PHPExcel.PHPExcel');
        vendor("PHPExcel.PHPExcel.Shared.Date");
        $upload = new \Think\Upload();// 實(shí)例化上傳類(lèi)

        $upload->maxSize = 3145728;// 設(shè)置附件上傳大小
        $upload->autoSub = false;
        $upload->exts = array('xlsx', 'xls', 'csv');// 設(shè)置附件上傳類(lèi)型
        $upload->rootPath = __ROOT__ . 'Public/Upload/excel/'; // 設(shè)置附件上傳根目錄
        // 上傳單個(gè)文件
        $info = $upload->uploadOne($file);

        if (!$info) {// 上傳錯(cuò)誤提示錯(cuò)誤信息
            throw new Exception($upload->getError());
        } else {// 上傳成功 獲取上傳文件信息
            $file_name = __ROOT__ . 'Public/Upload/excel/' . $info['savepath'] . $info['savename'];
        }

        $type = $info['ext'];
        if ($type == 'xls') {
            $objReader = \PHPExcel_IOFactory::createReader('Excel5');
            $objPHPExcel = $objReader->load($file_name, $encode = 'utf-8');
        } elseif ($type == 'xlsx') {
            $objReader = \PHPExcel_IOFactory::createReader('Excel2007');
            $objPHPExcel = $objReader->load($file_name, $encode = 'utf-8');
        } elseif ($type == 'csv') {
            $objReader = \PHPExcel_IOFactory::createReader('CSV')
                ->setDelimiter(',')
                ->setInputEncoding('GBK')
                ->setEnclosure('"')
//                    ->setLineEnding("\r\n") //報(bào)錯(cuò)
                ->setSheetIndex(0);
            $objPHPExcel = $objReader->load($file_name);
        }

        $sheet = $objPHPExcel->getSheet(0);
        $highestRow = $sheet->getHighestRow(); // 取得總行數(shù)
        $highestColumn = $sheet->getHighestColumn(); // 取得總列數(shù)

        //開(kāi)啟事務(wù)

        $row_str = array('A','B','C','D','E','F','G');

        $row_count = count($row_str);

        //組數(shù)據(jù) 數(shù)據(jù)庫(kù)的字段
        $data_str = array('department','process','content','source','storage','custodian','position');
        $error = '';
        $error_save = '';
        for ($i = 4; $i <= ($highestRow-1); $i++)//這個(gè)地方根據(jù)需要,一般第一行是名稱(chēng),所以從第二行開(kāi)始循環(huán),也可以從第一行開(kāi)始
        {
            //數(shù)據(jù)庫(kù)字段和excel列相對(duì)應(yīng)
            $data_start = 0;
            $sava_data = array();
            for ($r = 0;$r<$row_count;$r++){
                $sava_data[$data_str[$data_start]] = (string)$objPHPExcel->getActiveSheet()->getCell("$row_str[$r]". $i)->getValue();
                $data_start++;
            }

            if (!in_array("",$sava_data)){
                //判斷是否存在，存在則修改
                $replace = M('Knowledge')->where(['content'=>$sava_data['content']])->find();
                if ($replace){
                    $result_rep =  M('Knowledge')->where(['id'=>$replace['id'],'f_id'=>0])->save($sava_data);
                    if ($result_rep){
                        $error_save .= $i.',';
                    }
                    continue 1;
                }

                //添加
                $result =  M('Knowledge')->add($sava_data);
                if (!$result){
                    $error .= $i.',';
                }
            }else{
                $error .= $i.',';
            }

        }

        if ($error_save != ''){
            $error_save .= ' 行修改成功';
        }
        if ($error != ''){
            $error .= ' 行添加失敗';
        }

        //刪除文件
        unlink($file_name);
        if ($error != '' || $error_save != ''){
            $this->errorReturn($error_save.$error);
        }

        $this->successReturn('添加成功');
    }