jianzhi/application/store/controller/Hospital.php

<?php

namespace app\store\controller;
use library\Controller;
use think\Db;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Shared\Date;

class Hospital extends  Controller
{

    /**
     * 绑定数据表
     * @var string
     */
    protected $table = 'store_hospital';


    /**
     * 系统消息
     * @auth true
     * @menu true
     * @throws \think\Exception
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     * @throws \think\exception\PDOException
     */
    public function index()
    {
        $this->title = '超级医疗平台列表';
        $query = $this->_query($this->table)->like('name');
        $query->order('id desc')->page();
    }
    /**
     * 数据列表处理
     * @param array $data
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    protected function _index_page_filter(&$data)
    {
        foreach ($data as &$vo) {
            $vo['sheng'] =Db::name('q_area')->where('id',$vo['sheng'])->value('shortname');
            $vo['shi'] =Db::name('q_area')->where('id',$vo['shi'])->value('shortname');
            $vo['qu'] =Db::name('q_area')->where('id',$vo['qu'])->value('shortname');

        }
    }



    /**
     * 添加消息
     * @auth true
     * @throws \think\Exception
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     * @throws \think\exception\PDOException
     */
    public function add()
    {
        $this->_form($this->table, 'form');
    }


    /**
     * 编辑消息
     * @auth true
     * @throws \think\Exception
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     * @throws \think\exception\PDOException
     */
    public function edit()
    {


        $this->_form($this->table, 'form');
    }
    protected function _form_filter(&$data)
    {

        $data['sheng']=1;
        $data['shi']=2;
        $data['qu']=3;



    }


    /**
     * 删除消息
     * @auth true
     * @throws \think\Exception
     * @throws \think\exception\PDOException
     */
    public function del()
    {
        $this->_save($this->table, ['is_deleted' => '1']);
    }
    function get_address($id){
        $add = db('q_area')->where('pid', $id)->select();
        return $add;
    }

    public function import(){
        set_time_limit(60);//限制页面执行时间60s
        $file = $_FILES['file']['tmp_name'];//临时文件路径或者文件url
        try{
            $file = iconv("utf-8", "gb2312", $file);//转码
            if (empty($file) OR !file_exists($file)) {
                throw new \Exception('文件不存在!');
            }
            $objReader = IOFactory::createReader('Xlsx');
            if (!$objReader->canRead($file)) {
                $objReader = IOFactory::createReader('Xls');
                if (!$objReader->canRead($file)) {
                    throw new \Exception('只支持导入Excel文件！');
                }
            }
            $objReader->setReadDataOnly(true);//忽略任何格式的信息
            $objPHPExcel = $objReader->load($file);
            $worksheet = $objPHPExcel->getActiveSheet();
            $sheet = $objPHPExcel->getSheet(0);   //excel中的第一张sheet
            $highestRow = $sheet->getHighestRow();       // 取得总行数
            // $highestColumn = $sheet->getHighestColumn();   // 取得总列数
            /**
             * 必填：    姓名 手机号 性别 报读院校 报读专业 报读层次 收件信息
             * 非必填：  民族 政治面貌 户口所在地（市区） 考试县区 身份证号码 考前学历 毕业院校 毕业时间（年月） 毕业证书编号 毕业专业
             */
            $genders = [ 0 => '保密', 1 => '男', 2 => '女'];
            $data = [];
            $msg = '';
            //$keys = ['name', 'phone', 'gender','end_date'];

            for ($j = 2; $j <= $highestRow; $j++) {
                //foreach ($keys as $k => $v) {
                //    $$v = $worksheet->getCellByColumnAndRow($k + 1, $j)->getValue();
                //}
                $name = $worksheet->getCellByColumnAndRow(1, $j)->getValue();
                $phone = $worksheet->getCellByColumnAndRow(2, $j)->getValue();
                $gender= $worksheet->getCellByColumnAndRow(3, $j)->getValue();
                $gender = array_search($gender, $genders);
                $end_date = $worksheet->getCellByColumnAndRow(4, $j)->getValue();
                //$end_date = date('Y-m-d', ($value - 25569) * 24 * 3600)
                $end_date = date('Y-m-d', Date::excelToTimestamp($end_date));//转化为时间类型
                //$name = $worksheet->getCell("A" . $j)->getValue();
                //$phone = $worksheet->getCell("B" . $j)->getValue();
                //$gender =  $worksheet->getCell("C" . $j)->getValue();
                if (empty($name)) {
                    $msg .=  '<p style="color:red">第'.($j-1).'条数据,姓名没有填写，插入失败</p>';
                } else {
                    $data[] = [
                        'name' => $name,
                        'phone' => $phone,
                        'gender' => $gender,
                        'end_date ' => $end_date ,
                    ];
                }
            }
            $res = Db::name('User')->data($data)->limit(100)->insertAll();// 分批插入数据
            $msg .= '<p style="color:blue">导入完成</p><p style="color:blue">总共'.($highestRow-1).'条数据</p><p style="color:blue">成功'.count($data).'条数据</p><p style="color:red">失败'.($highestRow-count($data)-1).'条数据</p>';
            return json(['code'=>1, 'msg'=>$msg]);
        } catch (\Exception $e) {
            return json(['code'=>0, 'msg'=>$e->getMessage()]);
        }
    }


}