How to import Excel using PHPExcel in thinkphp-ThinkPHP-php.cn

How to import Excel using PHPExcel in thinkphp

尚

Release： 2020-05-13 09:06:23

forward

4405 people have browsed it

How to import Excel using PHPExcel in thinkphp

Introduction to how thinkphp imports Excel:

Development ideas

1. First upload the Excel file to the server

2. Get the content of the server Excel file

3. Write to the database

1. Upload the Excel file, use the upload method "\Think\Upload();" that comes with PHP, you can Very convenient to implement. For this reason, I have sorted out the simplest way to use this method

/**
 * TODO 上传文件方法
 * @param $fileid form表单file的name值
 * @param $dir 上传到uploads目录的$dir文件夹里
 * @param int $maxsize 最大上传限制，默认1024000 byte
 * @param array $exts 允许上传文件类型 默认array(&#39;gif&#39;,&#39;jpg&#39;,&#39;jpeg&#39;,&#39;bmp&#39;,&#39;png&#39;)
 * @return array 返回array,失败status=0 成功status=1,filepath=newspost/2014-9-9/a.jpg
 */
function uploadfile($fileid,$dir,$maxsize=5242880,$exts=array(&#39;gif&#39;,&#39;jpg&#39;,&#39;jpeg&#39;,&#39;bmp&#39;,&#39;png&#39;),$maxwidth=430){
    $upload = new \Think\Upload();// 实例化上传类
    $upload->maxSize   =     $maxsize;// 设置附件上传大小，单位字节(微信图片限制1M
    $upload->exts      =     $exts;// 设置附件上传类型
    $upload->rootPath  =     &#39;./uploads/&#39;; // 设置附件上传根目录
    $upload->savePath  =     $dir.&#39;/&#39;; // 设置附件上传（子）目录
    // 上传文件
    $info   =   $upload->upload();

    if(!$info) {// 上传错误提示错误信息
        return array(status=>0,msg=>$upload->getError());
    }else{// 上传成功
        return array(status=>1,msg=>&#39;上传成功&#39;,filepath=>$info[$fileid][&#39;savepath&#39;].$info[$fileid][&#39;savename&#39;]);
    }
}

Copy after login

By default, it is uploaded to the folder uploads where the ThinkPHP entry file index.php is located. This method returns a data, and the status is success when status=1. It is recommended that when writing functional modules or encapsulating, the entire system should have an agreement at the early stage of the architecture. If necessary, the return value should be in the form of an array. If successful,

return array(status=>1,data=>....,info=>.....)

Copy after login

can be returned. If failed,

array(status->0,info=>&#39;可以说明出错的原因&#39;,....)

Copy after login

can be returned.

This unified approach is conducive to standardized development. It can improve efficiency and reduce thinking when looking at the code during team collaboration. To put it bluntly, the method of calling the upload method is as follows:

//excel 文件
        if(!empty($_FILES[&#39;xls&#39;][&#39;name&#39;])){
            $upload=uploadfile(&#39;xls&#39;,&#39;tempxls&#39;,5242880,array(&#39;xls&#39;,&#39;xlsx&#39;));
            if($upload[&#39;status&#39;]){
                $path=$upload[&#39;filepath&#39;];
            }else{
                $this->error($upload[&#39;msg&#39;]);
            }
        }

Copy after login

2. Obtain Excel data

1. First, you need to introduce the PHPExcel class library

require_once &#39;module/PHPExcel/Classes/PHPExcel/IOFactory.php&#39;;

Copy after login

2. Get the 0th sheet of Excel (Sheet1)

//获取excel文件
$objPHPExcel = \PHPExcel_IOFactory::load("uploads/$path");
$objPHPExcel->setActiveSheetIndex(0);
$sheet0=$objPHPExcel->getSheet(0);

Copy after login

3. Get the number of rows and put the data Read out the $data array

$rowCount=$sheet0->getHighestRow();//excel行数
        $data=array();
        for ($i = 2; $i <= $rowCount; $i++){
            $item[&#39;name&#39;]=$this->getExcelValue($sheet0,&#39;A&#39;.$i);
            $item[&#39;sex&#39;]=$this->getExcelValue($sheet0,&#39;B&#39;.$i);
            $item[&#39;contact&#39;]=$this->getExcelValue($sheet0,&#39;C&#39;.$i);
            $item[&#39;remark&#39;]=$this->getExcelValue($sheet0,&#39;D&#39;.$i);
            $item[&#39;addtime&#39;]=$this->getExcelValue($sheet0,&#39;E&#39;.$i);

            $data[]=$item;
        }

Copy after login

3. Finally save to the data

$success=0;
        $error=0;
        $sum=count($data);
        foreach($data as $k=>$v){
            if(M(&#39;temp_area3&#39;)->data($v)->add()){
                $success++;
            }else {
                $error++;
            }
        }

        echo "总{$sum}条，成功{$success}条，失败{$error}条。";

Copy after login