lrz0829/Thinkphp5整合excel导入导出/application/index/controller/Index.php

<?php
namespace app\index\controller;
use think\Db;
use think\Controller;
use think\Loader;

class Index extends Controller
{

     public function __construct()
    {
        parent::__construct();
    }


    public function index()
    {
        
        return $this->fetch();
            
    }

    public function impExcel(){
        vendor("PHPExcel.PHPExcel");
        Loader::import('PHPExcel.PHPExcel.IOFactory.PHPExcel_IOFactory');
        Loader::import('PHPExcel.PHPExcel.Reader.Excel5');
        $objPHPExcel = new \PHPExcel();
        //获取表单上传文件  
        $file = request()->file('excel');  
        $info = $file->validate(['size'=>2097152,'ext'=>'xlsx,xls,csv'])->move(ROOT_PATH . 'public' . DS . 'excel');
        if($info){  
            $exclePath = $info->getSaveName();  //获取文件名
            $name=strrchr($exclePath,'.');
            $file_name = ROOT_PATH . 'public' . DS . 'excel' . DS . $exclePath;   //上传文件的地址
            if($name=='.xlsx'){
                $objReader =\PHPExcel_IOFactory::createReader('Excel2007');
            }elseif($name=='.xls'){
                $objReader =\PHPExcel_IOFactory::createReader('Excel5');

            }elseif($name=='.csv'){
                $objReader =\PHPExcel_IOFactory::createReader('csv');
            }
            $obj_PHPExcel =$objReader->load($file_name, $encode = 'utf-8');  //加载文件内容,编码utf-8
            $excel_array=$obj_PHPExcel->getsheet(0)->toArray();   //转换为数组格式  
            array_shift($excel_array);  //删除第一个数组(标题);
            $data = [];  
            $i=0;  
            foreach($excel_array as $k=>$v) {  
                $data[$k]['id'] = $v[0];  
                $data[$k]['name'] = $v[1];  
                $data[$k]['image'] = $v[2];  
                $i++;  
            }  
           $success=Db::name('member')->insertAll($data); //批量插入数据
           $error=$i-$success;  
            echo "总{$i}条，成功{$success}条，失败{$error}条。";
        }else{  
            // 上传失败获取错误信息  
            echo $file->getError();  
        }
    }  

    public function expExcel()  
    {  
        vendor("PHPExcel.PHPExcel");  
        $list=db('member')->field('*')->select();  
        $objPHPExcel = new \PHPExcel();
        $objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);
        //设置sheet名称
        $sheets=$objPHPExcel->getActiveSheet()->setTitle('demo');
        //设置sheet列头信息
        $objPHPExcel->setActiveSheetIndex()->setCellValue('A1', 'id')->setCellValue('B1', 'name')->setCellValue('C1', 'image');
        $i=2;
        foreach($list as $v){
            //设置单元格的值
            $sheets=$objPHPExcel->getActiveSheet()->setCellValue('A'.$i,$v['id']);
            $sheets=$objPHPExcel->getActiveSheet()->setCellValue('B'.$i,$v['name']);
            $sheets=$objPHPExcel->getActiveSheet()->setCellValue('C'.$i,$v['image']);
            $i++;
        }
        //整体设置字体和字体大小
        $objPHPExcel->getDefaultStyle()->getFont()->setName( 'Arial');
        $objPHPExcel->getDefaultStyle()->getFont()->setSize(10);
        // $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true); //单元格宽度自适应
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20); //设置列宽度
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20); //设置列宽度
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20); //设置列宽度
        $objPHPExcel->getActiveSheet()->getStyle('B3')->getFont()->setBold(true); //设置单元格字体加粗
        // 输出Excel表格到浏览器下载
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="demo.xls"'); //excel表格名称
        $objWriter->save('php://output'); 
    }  

}