phpexcel导入导出

 

  先下载类文件，将类文件放到lib下，然后到入口文件定义一个新的的路径，以便引入文件。

   

<?php
namespace app\index\controller;
use think\Controller;
require EXTEND_PATH."/PHPExcel/Classes/PHPExcel.php";
use think\Db;
use PHPExcel;
use PHPExcel_Writer_Excel2007;
use PHPExcel_Reader_Excel2007;
use PHPExcel_Cell;
class Excel extends Controller
{
    public function index()
    {
        $list=Db::table("excel")->field('id,name,password')->select();
        $this->assign("list",$list);
        return view("index");
    }
    //导出
    public function export(){
        $list=Db::table("excel")->field('id,name,password')->select();
        //创建一个phpexcel对象
        $objPHPExcel = new PHPExcel();
        //构造一个对象
        $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
        //设置一个sheet
        $objPHPExcel->setActiveSheetIndex(0);
        //设置sheet名称
        $objPHPExcel->getActiveSheet()->setTitle("users");
        //设置title
        $zm = array('A','B','C');
        $title=array('编号','名称','密码');
        $num=1;
        foreach($title as $key=>$val){
            $objPHPExcel->getActiveSheet()->setCellValue($zm[$key].$num, $val);
        }
        //设置数据
        foreach($list as $k=>$v){
            $num++;
            $k=0;
            foreach($v as $kk=>$vv){
                $objPHPExcel->getActiveSheet()->setCellValue($zm[$k].$num,$vv);
                $k+=1;
            }
        }
        //输出到浏览器
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename="export.xls"');
        //保存文件
        $objWriter->save('php://output');
    }
    //导入
    public function import(){
        header('content-type:text/html;charset=utf-8');
        $file=$_FILES['user']['tmp_name'];
        //获取读取excel对象
        $PHPReader = new PHPExcel_Reader_Excel2007();
        $boole7=$PHPReader->canRead($file);
        if(!$boole7){
            $boole5=$PHPReader = new PHPExcel_Reader_Excel5($file);
            if(!$boole5){
                $this->error('文件不可读');
            }
        }
        //加载文件
        $PHPExcel = $PHPReader->load($file);
        //获取第一个工作表
        $currentSheet = $PHPExcel->getSheet(0);
        //获取当前工作表有多少列
        $allColumn = PHPExcel_Cell::columnIndexFromString($currentSheet->getHighestColumn());
        //获取当前工作表有多少行
        $allRow = $currentSheet->getHighestRow();
        $title=array('id','name','password');
        $zm=array('A','B','C','D');
        $insertData=array();
        for($i=2;$i<=$allRow;$i++){
            for($j=1;$j<=$allColumn;$j++){
                $value = $currentSheet->getCell($zm[$j-1].$i)->getValue();
                $insertData[$i][$title[$j-1]]=htmlspecialchars($value);
            }
        }
        $insertData=array_values($insertData);
        $res=Db::name('user')->insertAll($insertData);
        if($res<=0){
            $this->error('数据导入失败');
        }else{
            $this->success('数据导入成功','show');
        }
    }
    //展示导入数据
    public function show(){
        $list=Db::table("user")->select();
        return view("show",['list'=>$list]);
    }
}