Thinkphp6Excel导入导出

1.composer下载phpspreadsheet

composer require phpoffice/phpspreadsheet

2.二次封装，类 

<?php
/**
 * Created by PhpStorm.
 * Author: Shadow
 * Date: 2021/12/14
 * Time: 8:00 上午
 * description: Excel.php
 */


namespace app\admin\lib;

use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use think\exception\ValidateException;
use think\facade\Filesystem;

class Excel
{
    /**
     * @param string $filename
     * @return array|string
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     */
    public static function importExcel($filename = "")
    {
        $file[] = $filename;

        try {
            // 验证文件大小，名称等是否正确
            validate(['file' => 'fileSize:51200|fileExt:xls,xlsx'])
                ->check($file);
            // 将文件保存到本地
            $savename = Filesystem::disk('public')->putFile('file', $file[0]);
            // 截取后缀
            $fileExtendName = substr(strrchr($savename, '.'), 1);
            // 有Xls和Xlsx格式两种
            if ($fileExtendName == 'xlsx') {
                $objReader = IOFactory::createReader('Xlsx');
            } else {
                $objReader = IOFactory::createReader('Xls');
            }
            // 设置文件为只读
            $objReader->setReadDataOnly(TRUE);
            // 读取文件，tp6默认上传的文件，在runtime的相应目录下，可根据实际情况自己更改
            $objPHPExcel = $objReader->load(public_path() . '/storage/' . $savename);
            //excel中的第一张sheet
            $sheet = $objPHPExcel->getSheet(0);
            // 取得总行数
            $highestRow = $sheet->getHighestRow();
            // 取得总列数
            $highestColumn = $sheet->getHighestColumn();
            Coordinate::columnIndexFromString($highestColumn);
            $lines = $highestRow - 1;
            if ($lines <= 0) {
                return "数据为空数组";
            }
            // 直接取出excle中的数据
            $data = $objPHPExcel->getActiveSheet()->toArray();
            // 删除第一个元素（表头）
            array_shift($data);
            //删除文件
            unlink(public_path() . '/storage/' . $savename);
            // 返回结果
            return $data;
        } catch (ValidateException $e) {
            return $e->getMessage();
        }
    }

    // 导出
    public static function export($header = [], $type = true, $data = [], $fileName = "1910")
    {
        // 实例化类
        $preadsheet = new Spreadsheet();
        // 创建sheet
        $sheet = $preadsheet->getActiveSheet();
        // 循环设置表头数据
        foreach ($header as $k => $v) {
            $sheet->setCellValue($k, $v);
        }
        // 生成数据
        $sheet->fromArray($data, null, "A2");
        // 样式设置
        $sheet->getDefaultColumnDimension()->setWidth(12);
        // 设置下载与后缀
        if ($type) {
            header("Content-Type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            $type = "Xlsx";
            $suffix = "xlsx";
        } else {
            header("Content-Type:application/vnd.ms-excel");
            $type = "Xls";
            $suffix = "xls";
        }
        ob_end_clean();//清楚缓存区
        // 激活浏览器窗口
        header("Content-Disposition:attachment;filename=$fileName.$suffix");
        //缓存控制
        header("Cache-Control:max-age=0");
        // 调用方法执行下载
        $writer = IOFactory::createWriter($preadsheet, $type);
        // 数据流
        $writer->save("php://output");
    }
}

 3.form表单页面 

<!--    //导入-->
    <form action="index" method="post" enctype="multipart/form-data">
        <input type="file" name="myfile">
        <input type="submit" value="提交">
    </form>
<!--    导出-->
    <a href="{:url('./get')}"><button type="button" class="btn btn-primary">导出</button></a>

 4.路由

//导入
Route::any('index', 'UserControllers/index');
//导出
Route::get('get', 'UserControllers/get');

控制器层调用

excel的导入、导出

 //导入
    public function index(Request $request)
    {
        // 接收文件上传信息
        $files = $request->file("myfile");
        // 调用类库，读取excel中的内容
        $data = Excel::importExcel($files);
        $arr = [];
        foreach($data as $val){
            $arr[] = [
                'number'=>$val[1],
                'password'=>$val[2],
                'pwd'=>$val[3],
                'classify'=>$val[4],
                'photo'=>$val[5],
                'name'=>$val[6],
                'content'=>$val[7],
            ];
        }
        $obj = new User();
        $obj->saveAll($arr);
        if ($obj){
            return redirect('show');
        }
    }

    //导出
    public function get()
    {
        //设置表格的表头数据
        $header = ['A1' => "编号","B1" => "账号","C1" => "密码","D1" => "确认","E1" => "部门","F1" => "人脸对比检测照片","G1" => "姓名","H1" => "简介"];
        // 假设下面这个数组从数据库查询出的二维数组
        $data = User::select()->toArray();
        // 保存文件的类型
        $type= true;
        // 设置下载文件保存的名称
        $fileName = '信息导出'.time();
        // 调用方法导出excel
        Excel::export($header,$type,$data,$fileName);
    }

模型层

static function sel(){
    //查询所有数据
    return self::select();
}