ThinkPHP3.2.3 PHPExcel读取excel插入数据库

版本 ThinkPHP3.2.3 

下载PHPExcel

将这两个文件放到并更改名字

excel文件:

数据库表:

CREATE TABLE `sh_name` (
`name` varchar(255) DEFAULT NULL,
`age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

代码:

代码主要在index方法中,有数据提交则写入数据库,否则展示表单

<?php
namespace Home\Controller;
use Think\Controller;
use Think\Upload;

class IndexController extends Controller {
    public function indexAction(){
        if(IS_POST){
            $file = $_FILES;
            //上传
            $path = $this->upload($file);
            //读取excel
            $arr = $this->excel($path, 0);
            //实例化模型
            $model = D('name');
            //添加的数据
            $data = [];
            for($i=2; $i<=count($arr); $i++){
                $data[] = ['name'=>$arr[$i]['A'], 'age'=>$arr[$i]['B']];
            }
            //添加
            $model->addAll($data);
        }else{
            $this->display();
        }
        
    }
    public function testAction(){
        echo "<h1>hello world</h1>";
    }
    //上传
    public function upload(){
        $upload = new Upload();
        $upload->maxSize = 3145728 ;// 设置附件上传大小
        $upload->exts = array('jpg', 'gif', 'png', 'jpeg', 'xls');// 设置附件上传类型
        $upload->rootPath = APP_PATH . 'Uploads/'; // 设置附件上传根目录
        $upload->savePath = 'xls/'; // 设置附件上传（子）目录
        // 上传文件
        $info = $upload->upload();
        $info = $info['inputfile'];
        if(!$info) {// 上传错误提示错误信息
            $this->error($upload->getError());
        }

        return APP_PATH . 'Uploads/' . $info['savepath'] . $info['savename'];
    }

    //excel
    public function excel($filePath='', $sheet=0){
        
        import("Org.Util.PHPExcel");
        import("Org.Util.PHPExcel.Reader.Excel5");
        import("Org.Util.PHPExcel.Reader.Excel2007");

        if(empty($filePath) or !file_exists($filePath)){die('file not exists');}
        $PHPReader = new \PHPExcel_Reader_Excel2007();        //建立reader对象
        if(!$PHPReader->canRead($filePath)){
            $PHPReader = new \PHPExcel_Reader_Excel5();
            if(!$PHPReader->canRead($filePath)){
                 echo 'no Excel';
                return ;
            }
        }
        $PHPExcel = $PHPReader->load($filePath);        //建立excel对象
        $currentSheet = $PHPExcel->getSheet($sheet);        //**读取excel文件中的指定工作表*/
        $allColumn = $currentSheet->getHighestColumn();        //**取得最大的列号*/
        $allRow = $currentSheet->getHighestRow();        //**取得一共有多少行*/
        $data = array();
        for($rowIndex=1;$rowIndex<=$allRow;$rowIndex++){        //循环读取每个单元格的内容。注意行从1开始，列从A开始
            for($colIndex='A';$colIndex<=$allColumn;$colIndex++){
                $addr = $colIndex.$rowIndex;
                $cell = $currentSheet->getCell($addr)->getValue();
                if($cell instanceof PHPExcel_RichText){ //富文本转换字符串
                    $cell = $cell->__toString();
                }
                $data[$rowIndex][$colIndex] = $cell;
            }
        }
        return $data;
    }  
}