Method of ThinkPHP Importing Excel File Based on PHPExcel

This article illustrates the method of ThinkPHP importing Excel files based on PHPExcel. Share it for your reference. The specific methods are as follows:

Main knowledge points, using PHPExcel to import Excel data can be tested for several days, and xls and xlsx can obtain Excel data.
Download address: http://phpexcel.codeplex.com/

Development ideas:

1. Upload the Excel file to the server first

2. Get the contents of the server Excel file

3. Writing to the database

1. Upload the Excel file, using the upload method "\ Think\ Upload ();" , can be very convenient to achieve. For this reason, I have sorted out the easiest way to use this method

/**

 * TODO  Upload file method 

 * @param $fileid form Form file Adj. name Value 

 * @param $dir  Upload to uploads Directory $dir In the folder 

 * @param int $maxsize  Maximum upload limit, default 1024000 byte

 * @param array $exts  Allow uploading file types   Default array('gif','jpg','jpeg','bmp','png')

 * @return array  Return array, Failure status=0  Success status=1,filepath=newspost/2014-9-9/a.jpg

 */

function uploadfile($fileid,$dir,$maxsize=5242880,$exts=array('gif','jpg','jpeg','bmp','png'),$maxwidth=430){

    $upload = new \Think\Upload();//  Instantiate the upload class 

    $upload->maxSize   =     $maxsize;//  Set attachment upload size, unit byte ( WeChat picture restrictions 1M

    $upload->exts      =     $exts;//  Set attachment upload type 

    $upload->rootPath  =     './uploads/'; //  Set the attachment upload root directory 

    $upload->savePath  =     $dir.'/'; //  Set the attachment upload (sub) directory 

    //  Upload a file 

    $info   =   $upload->upload();
    if(!$info) {//  Upload Error Prompt Error Message 

        return array(status=>0,msg=>$upload->getError());

    }else{//  Upload succeeded 

        return array(status=>1,msg=>' Upload succeeded ',filepath=>$info[$fileid]['savepath'].$info[$fileid]['savename']);

    }

}

Upload to ThinkPHP entry file index. php folder uploads by default, this method returns 1 data, status status=1 is successful, also recommend that you write functional modules or do encapsulation, the whole system in the early stage of the architecture should have agreement, if necessary, return values in the form of arrays, successful return

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

You can return on failure

array(status->0,info=>' The cause of the error can be explained ',....)

In this way, it is beneficial to standardize development in a unified way. When looking at the code in team cooperation, it can improve efficiency and reduce thinking operation. To go far, the method call method for uploading is as follows:

//excel  Documents 

if(!empty($_FILES['xls']['name'])){

    $upload=uploadfile('xls','tempxls',5242880,array('xls','xlsx'));

    if($upload['status']){

 $path=$upload['filepath'];

    }else{

 $this->error($upload['msg']);

    }

}

2. Get Excel data

1. First, we need to introduce the class library of PHPExcel

require_once 'module/PHPExcel/Classes/PHPExcel/IOFactory.php';

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

// Get excel Documents 

$objPHPExcel = \PHPExcel_IOFactory::load("uploads/$path");

$objPHPExcel->setActiveSheetIndex(0);

$sheet0=$objPHPExcel->getSheet(0);

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

$rowCount=$sheet0->getHighestRow();//excel Number of rows 

$data=array();

for ($i = 2; $i <= $rowCount; $i++){

    $item['name']=$this->getExcelValue($sheet0,'A'.$i);

    $item['sex']=$this->getExcelValue($sheet0,'B'.$i);

    $item['contact']=$this->getExcelValue($sheet0,'C'.$i);

    $item['remark']=$this->getExcelValue($sheet0,'D'.$i);

    $item['addtime']=$this->getExcelValue($sheet0,'E'.$i);
    $data[]=$item;

}

3. Finally save to database

$success=0;

$error=0;

$sum=count($data);

foreach($data as $k=>$v){

    if(M('temp_area3')->data($v)->add()){

 $success++;

    }else {

 $error++;

    }

}
echo " Total {$sum} Article, success {$success} Article, failure {$error} Article. ";

At this point, you are done! I hope this article is helpful to everyone's ThinkPHP framework programming.