thinkphp用phpexcel读取excel,并修改列中的值，再导出excel,带往excel里写入图片

<?php

class GetpriceAction extends AdministratorAction {

//    文件保存路径
    protected $savepath;
//    允许上传的文件类型
    protected $allowFileType;

    public function _initialize(){
        parent::_initialize();
        $this->savepath = './xxx/'.date('Ymd').'/';
        $this->allowFileType = array('xlsx','xls');
    }

    public function index(){
        $this->display();
    }


    public function upload(){
        $beginLine = intval($_POST['beginLine']);
        $bnCol = htmlspecialchars(strtoupper($_POST['bnCol']));
//        $numCol = htmlspecialchars(strtoupper($_POST['numCol']));

        if($beginLine<1){
            $this->dialogmessage('须正确填写开始行数','error');
        }
        if(!preg_match('/^[a-zA-Z]+$/',$bnCol)) {
            $this->dialogmessage('须正确填写货号所在列', 'error');
        }

        $savepath = $this->savepath;
        mk_dir($savepath);
        import("ORG.Net.UploadFile");
        $upload = new UploadFile();
        $upload->maxSize = 114570;
        $upload->allowExts = $this->allowFileType;
        $upload->savePath = $savepath.''; // 讴置附件上传目录
        if(!$upload->upload()){
            $this->dialogmessage($upload->getErrorMsg(),'excel');
            exit();
        }else{
            $fileInfo = $upload->getUploadFileInfo();
            $data = $this->editStore($fileInfo,$beginLine,$bnCol);
        }
        $this->assign($data);
        $this->display();
    }

    /**
     * $info
     *
     * @param $fileInfo
     * @param $beginLine
     * @param $bnCol
     * @return array
     */
    protected function editStore($fileInfo,$beginLine,$bnCol){
        require_once './excel/Classes/PHPExcel.php';
        $filePath = $fileInfo[0]['savepath'].$fileInfo[0]['name'];
        chmod($filePath,0777);
        //建立reader对象
        $PHPReader = new PHPExcel_Reader_Excel2007();
        if(!$PHPReader->canRead($filePath)){
            $PHPReader = new PHPExcel_Reader_Excel5();
            if(!$PHPReader->canRead($filePath)){
                $this->dialogmessage('no excel','error');
            }
        }

//建立excel对象，此时你即可以通过excel对象读取文件，也可以通过它写入文件
        $PHPExcel = $PHPReader->load($filePath);

        /**读取excel文件中的第一个工作表*/
        $currentSheet = $PHPExcel->getSheet(0);
        /**取得最大的列号*/
        $allColumn = $currentSheet->getHighestColumn();
        /**取得一共有多少行*/
        $allRow = $currentSheet->getHighestRow();
//循环读取每个单元格的内容。注意行从1开始，列从A开始
        $codes=array();
        for($rowIndex=$beginLine;$rowIndex<=$allRow;$rowIndex++){
            $bn=$currentSheet->getCell($bnCol.$rowIndex)->getValue();
            $b = $currentSheet->getCell('B'.$rowIndex)->getValue();
            $c = $currentSheet->getCell('C'.$rowIndex)->getValue();
            $d = $currentSheet->getCell('D'.$rowIndex)->getValue();
            $e = $currentSheet->getCell('E'.$rowIndex)->getValue();
            if($bn){
                $f = model('Product')->where('bn=\''.$bn.'\'')->getField('price');
                $codes[] = array('a'=>$bn,'b'=>$b,'c'=>$c,'d'=>$d,'e'=>$e,'f'=>$f);
            }
        }


$data['data'] = $codes;
        $data['title'] = array('货号','1','1','1','1','销售价');
        $data['width'] = array(30,30,30,30,30,30);
        $add = array();
        $add['ctime'] = time();
        $add['data'] = serialize($data);
        $add['title'] = '价格' . date('Ymdhis');
        $name = uniqid();
        $add['file'] = SITE_PATH . '/Public/excel/' . $name . '.xls';
        $add['url'] = SITE_URL . '/Public/excel/' . $name . '.xls';
        $add['uid'] = 1;
        $id = M('excel')->add($add);
        if ($id > 0) {
            pclose(popen('/usr/bin/php -f /xxx/exceltask.php ' . $id . ' &', 'r'));
            echo '导出任务成功,请等待系统完成';
            exit;
        } else {
            echo '导出任务失败';
            exit;
        }

    }

    /**
     * 写入日志
     * @param  Array $arrContent
     * @param  String $filename
     * @return int
     */
    protected function writeLog($arrContent,$filename){
        $intLogStatus = file_put_contents($this->savepath.$filename.'.php','<?php '.PHP_EOL.' return '.
var_export($arrContent,true).';');
        return $intLogStatus;
    }
} 

/xxx/exceltask.php

<?php
    error_reporting(0);
    date_default_timezone_set('RPC');
    define('SITE_PATH',dirname(__FILE__));
    include SITE_PATH.'/db.class.php';
    
    $id=intval($argv[1]);
    if($id<=0){
        exit();
    }

    $exceldata=M('excel')->where('id='.$id)->find();
    $data=unserialize($exceldata['data']);
    include SITE_PATH.'/libs/PHPExcel.php';
    $objExcel=new PHPExcel();
    $columns=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','BA','BB','BC','BD','BE','BF','BG','BH','BI','BJ','BK','BL','BM','BN','BO','BP','BQ','BR','BS','BT','BU','BV','BW','BX','BY','BZ');
    $objExcel->getProperties()->setCreator("xxx");  
    $objExcel->getProperties()->setLastModifiedBy("xxx");  
    $objExcel->getProperties()->setTitle($title);  
    $objExcel->getProperties()->setSubject($title);  
    $objExcel->getProperties()->setDescription($title);  
    $objExcel->getProperties()->setKeywords($title);  
    $objExcel->getProperties()->setCategory($title);  
    $objExcel->setActiveSheetIndex(0);  
    foreach($data['title'] as $k=>$v){
        $objExcel->getActiveSheet()->setCellValue($columns[$k].'1',$v);  
    }
    if($data['width']){
        foreach($data['width'] as $k=>$v){
            $objExcel->getActiveSheet()->getColumnDimension($columns[$k])->setWidth($v);
        }
    }
    $i=2;
    foreach($data['data'] as $v){
        $v=array_values($v);
        foreach($v as $k=>$vv){
            preg_match_all('/http:\/\/img\.xxx\.com\//', $vv, $matches);
            if($matches[0]){
                $objExcel->getActiveSheet()->getRowDimension($i)->setRowHeight(100);
                $objDrawing = new PHPExcel_Worksheet_Drawing();
                $file=SITE_PATH.'/tmp/'.uniqid().'.jpg';
                file_put_contents($file,file_get_contents($vv));
                $objDrawing->setPath($file);
                $objDrawing->setCoordinates($columns[$k].$i);
                $objDrawing->setOffsetX(10);
                $objDrawing->setOffsetY(10);
                $objDrawing->setWidth(100);
                $objDrawing->setHeight(100);
                $objDrawing->setWorksheet($objExcel->getActiveSheet());
                unset($objDrawing);

            }
            else{
                if(is_numeric($vv)){
                    //$vv=number_format($vv,2);
                    $objExcel->getActiveSheet()->setCellValueExplicit($columns[$k].$i,$vv);
                }
                else{
                    $objExcel->getActiveSheet()->setCellValueExplicit($columns[$k].$i,$vv,PHPExcel_Cell_DataType::TYPE_STRING);
                }
            }
            
        }    
        $i++;
    }
    $objWriter = PHPExcel_IOFactory::createWriter($objExcel, 'Excel5');  
    $objWriter->save($exceldata['file']);
    M('excel')->where('id='.$id)->setField('endtime',time());  
    exit;  
?>

 

转载于:https://www.cnblogs.com/walter371/p/4530857.html