php excel数据导入数据库

猫九森

于 2024-06-13 18:49:59 发布

阅读量100

点赞数 1

文章标签： php excel 数据库

本文链接：https://blog.csdn.net/qq_66313178/article/details/139661846

版权

 function import()
    {
        $data = $this->app->request->param();
        //接收到的是一个访问就下载的excel链接 https://cdn.xinjiangxiaoxiangkeji.com/4a/e2e6a825c0bc7183829e25f371e582.xlsx
        //将excel读取然后存储在php框架的public下的文件夹中public/upload/excel/file_excel22224848.xLsx
        $file = file_get_contents($data['urlname']);
        $num = rand(11111111, 99999999);
        $file_name = "upload/excel/file_excel" . "$num" . '.xLsx';
        //存储在public下面
        file_put_contents($file_name, $file);
        $ext = pathinfo($file_name, PATHINFO_EXTENSION);
        if (!in_array($ext, ['xLs', 'xLsx'])) {
            $this->success('未知文件格式', [], 200);
        }
        //具体实例化哪一种excel类
        if ($ext === 'xLs') {
            $reader = new Xls();
        } else {
            $reader = new Xlsx();
        }

        if (!$PHPExcel = $reader->load($file_name)) {
            $this->success('未知数据格式', [], 300);
        }
        $sheetCount = $PHPExcel->getSheetCount(); //默认选中sheet0表
        $sheetSelected = 0;
        $PHPExcel->setActiveSheetIndex($sheetSelected);
        //获取表格行数
        $rowCount = $PHPExcel->getActiveSheet()->getHighestRow();
//        //获取表格列数
//        $columnCount = $PHPExcel->getActiveSheet()->getHighestColumn();
//        $dataArr = array();
        $res = [];
        $data = [];
        /* 循环读取每个单元格的数据 excel有ABCDEFG...列 A1就是第A列第一行 */
        for ($i = 2; $i <= $rowCount; $i++) {
            //读取excel中的内容存储在变量中
            $data['username'] = $PHPExcel->getActiveSheet()->getCell("A" . $i)->getValue();
            $data['remark'] = $PHPExcel->getActiveSheet()->getCell("B" . $i)->getValue();
            $data['idcard'] = $PHPExcel->getActiveSheet()->getCell("C" . $i)->getValue();
            $data['phone'] = $PHPExcel->getActiveSheet()->getCell("D" . $i)->getValue();
            $data['type'] = 2;
            $res[] = $data;  //数据赋值到数组
        }
        //拿到数据循环添加入库
        foreach ($res as $v) {
            $result = DataUserBlack::mk()->insert($v);
        }
        unlink($file_name);
        if ($result) {
            $this->success('导入成功', [], 200);
        }
        $this->error('导入失败', [], 300);


    }