thinkphp5 phpexcel导出导入

<?php
namespace app\admin\controller;
use app\common\controller\AdminBase;

use PHPExcel_IOFactory;
use PHPExcel;

/**
 * 操作日志
 * Class Article
 * @package app\admin\controllers
 */
class Baoming extends AdminBase
{

    protected function _initialize()
    {
        parent::_initialize();
    }

    public function daoru(){
        //获取表单上传文件
        $file = request()->file('excel');
        $info = $file->validate(['ext' => 'xlsx,xls'])->move(ROOT_PATH . 'public' . DS . 'uploads' . DS . 'baoming');

        //数据为空返回错误
        if(empty($info)){
            $output['status'] = false;
            $output['info'] = '导入数据失败~';
            $this->ajaxReturn($output);
        }

        //获取文件名
        $exclePath = $info->getSaveName();
        //上传文件的地址
        $filename = ROOT_PATH . 'public' . DS . 'uploads' . DS . 'baoming'. DS . $exclePath;

        //判断截取文件
        $extension = strtolower( pathinfo($filename, PATHINFO_EXTENSION) );

        //区分上传文件格式
        if($extension == 'xlsx') {
            $objReader =\PHPExcel_IOFactory::createReader('Excel2007');
            $objPHPExcel = $objReader->load($filename, $encode = 'utf-8');
        }else if($extension == 'xls'){
            $objReader =\PHPExcel_IOFactory::createReader('Excel5');
            $objPHPExcel = $objReader->load($filename, $encode = 'utf-8');
        }

        $excel_array = $objPHPExcel->getsheet(0)->toArray();   //转换为数组格式
        array_shift($excel_array);
        array_shift($excel_array);

        //dump($excel_array);

        $res = [];
        foreach($excel_array as $k=>$v) {
            //if(empty(db('excel_shop')->where(['goods_id'=>$v[0]])->value('name'))){
                $res[$k]['uid']     = $v[1];
                $res[$k]['fjigou']  = $v[2];
                $res[$k]['jigou']   = $v[3];
                $res[$k]['kemu']    = $v[4];
                $res[$k]['chengji'] = $v[5];
                //$city[$k]['xxx'] = $v[1];  
                //$city[$k]['xxx'] = $v[2]; 
            //}
        }

        //dump($res);

        db('baoming')->insertAll($res); //批量插入数据
        echo '导入数据成功~';
    }

    public function daochu(){

        $res = db('baoming')->select();

        $xlsName  = "User";
        $xlsCell  = array(
        array('id','ID'),
        array('uid','用户名称'),
        //array('zhuti','主题'),
        array('fjigou','父机构'),
        array('jigou','机构'),
        array('kemu','科目'),
        array('chengji','成绩')  
        );
    
        $xlsData  = db('baoming')->field('id,uid,fjigou,jigou,kemu,chengji')->select();
        foreach ($xlsData as $k => $v)
        {
            $res = db('user')->where('id',$v['uid'])->find();
            $name = $res['truename'];
            $xlsData[$k]['uid']=$name;
        }
        $this->exportExcel($xlsName,$xlsCell,$xlsData);
    }

    public function exportExcel($expTitle,$expCellName,$expTableData){
        $xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称
        $fileName = date('_YmdHis');//or $xlsTitle 文件名称可根据自己情况设定
        $cellNum = count($expCellName);
        $dataNum = count($expTableData);
       
        $objPHPExcel = new PHPExcel();
        $cellName = 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');
        
        $objPHPExcel->getActiveSheet(0)->mergeCells('A1:'.$cellName[$cellNum-1].'1');//合并单元格
       // $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $expTitle.'  Export time:'.date('Y-m-d H:i:s'));  
        for($i=0;$i<$cellNum;$i++){
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i].'2', $expCellName[$i][1]); 
        } 
          // Miscellaneous glyphs, UTF-8   
        for($i=0;$i<$dataNum;$i++){
          for($j=0;$j<$cellNum;$j++){
            $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j].($i+3), $expTableData[$i][$expCellName[$j][0]]);
          }             
        }  
        
        header('pragma:public');
        header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xls"');
        header("Content-Disposition:attachment;filename=$fileName.xls");//attachment新窗口打印inline本窗口打印
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');  
        $objWriter->save('php://output'); 
        exit;   
    }


    /**
     * 操作日志
     * @param string $keyword 关键词
     * @param int    $page
     * @return mixed
     */
    public function index( $keyword = '', $page = 1)
    {
        $map   = [];
        
        $field = 'id,uid,zhuti,fjigou,jigou,kemu,chengji';

        if(!empty($keyword)){
            $map['zhuti'] = ['like' , "%{$keyword}%"];
        }
        
        $baoming  = db('baoming')->field($field)->where($map)->order(['id' => 'DESC'])->paginate(15, false, ['page' => $page]);
        return $this->fetch('index', ['baoming' => $baoming , 'keyword' => $keyword ]);
    }

    /**
     * 添加商户
     * @return mixed
     */
    public function add()
    {
        return $this->fetch();
    }

    public function edit_cj(){
        $id = $this->request->post('id');
        $chengji = $this->request->post('chengji');

        db('baoming')->where('id',$id)->setField('chengji',$chengji);
    }

    /**
     * 保存商户
     */
    public function save()
    {
        if ($this->request->isPost()) {
            $data = $this->request->post();
            if (db('baoming')->insert($data)) {
                $this->success('添加成功');
            } else {
                $this->error('添加失败');
            }
        }
    }

    /**
     * 编辑商户
     * @param $ser_id
     * @return mixed
     */
    public function edit($id)
    {
        $baoming = db('baoming')->find($id);
        return $this->fetch('edit', ['baoming' => $baoming]);
    }

    /**
     * 更新商户
     * @param $ser_id
     */
    public function update($id)
    {
        if ($this->request->isPost()) {
            $data = $this->request->post();

            if (db('baoming')->where('id',$id)->update($data) !== false) {
                $this->success('更新成功');
            } else {
                $this->error('更新失败');
            }
        }
    }

    /**
     * 删除商户
     * @param int   $goods_id
     * @param array $goods_ids
     */
    public function delete($id = 0, $ids = [])
    {
        $id = $ids ? $ids : $id;
        if ($id) {
            if (db('baoming')->delete($id)) {               
                $this->success('删除成功');
            } else {
                $this->error('删除失败');
            }
        } else {
            $this->error('请选择需要删除的商户');
        }
    }
    
     
}