ThinkPHP5.1使用PHPExcel进行数据的导入导出

本文已在本人博客https://www.nsxsg.com/archives/192首发

ThinkPHP5.1使用PHPExcel进行数据的导入导出

安装PHPExcel

1. 进入项目目录，使用composer安装PHPExcel

composer require phpoffice/phpexcel

导入数据

1. 准备一个含有数据的Excel表格，表头和数据表中的表字段相对应

2. 在ThinkPHP中引入PHPExcel类库

3. 编写导入的PHP代码

    /**
     * 批量导入管理员model
     * @param array 导入的数据
     * @return 返回成功或者失败信息
     */
    public function inputAdmins()
    {
        // 实例化类库
        $obj_phpexcel = new \PHPExcel();
        $objReader = \PHPExcel_IOFactory::createReader('Excel5');
        // 接收文件
        $inputdata = $_FILES;
        // 获取并处理数据
        $objData = $objReader->load($inputdata['file']['tmp_name'], $encode = 'utf-8');
        $excel_array=$objData->getsheet(0)->toArray();
        unset($excel_array[0]);
        $num = 0;
        $insertData = '';
        foreach ($excel_array as $key => $value) {
            $insertData[$num]['adminname'] = $value[1];
            $insertData[$num]['status'] = $value[2];
            $insertData[$num]['icon'] = $value[3];
            $insertData[$num]['password'] = $value[4];
            $insertData[$num]['uptime'] = $value[5];
            $insertData[$num]['addtime'] = $value[6];
            $insertData[$num]['delete'] = $value[7];
            $num++;
        }
        // 添加数据
        $user = new Apiadmins;
        $res = $user->saveAll($insertData);
        if ($res) {
            return $this -> cbMsg(true, '导入成功！');
        } else {
            return $this -> cbMsg(false, '导入失败！');
        }
    }

导出数据

1. 编写导出的PHP代码

    /**
     * 批量导出管理员model
     * @param string $fileName 导出文件名
     * @param array $headArr 表头
     * @return 导出Excel表格
     */
    public function outputAdmins($fileName = '文件名', $headArr = ['id','adminname','status','icon','password','uptime','addtim','delete'])
    {
        $fileName .= "_" . date("Ymd_His", time()) . ".xls";
        $objPHPExcel = new \PHPExcel();
        // Set document properties （设置文档属性）
        $objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
                                    ->setLastModifiedBy("Maarten Balliauw")
                                    ->setTitle("Office 2007 XLSX Test Document")
                                    ->setSubject("Office 2007 XLSX Test Document")
                                    ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
                                    ->setKeywords("office 2007 openxml php")
                                    ->setCategory("Test result file");
        // 设置头信息
        $objPHPExcel->setActiveSheetIndex(0);
        $key = ord('A');
        foreach ($headArr as $v) {
            $colum = chr($key);
            $objPHPExcel->getActiveSheet()->setCellValue($colum . '1', $v);
            $key += 1;
        }
        // 获取管理员全部数据
        $data = Apiadmins::all();
        $data = $data->toArray();
        $column = 2;
        foreach ($data as $key => $rows) { // 行写入
            $span = ord("A");
            foreach ($data[$key] as $keyName => $value) { // 列写入
                $objPHPExcel->getActiveSheet()->setCellValue(chr($span) . $column, $value);
                $span++;
            }
            $column++;
        }
        $objPHPExcel->setActiveSheetIndex(0); // 设置活动单指数到第一个表,所以Excel打开这是第一个表
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save(str_replace('.php', '.xls', Env::get('runtime_path').$fileName));
        return $this -> cbMsg(true, '导出成功，请去目录：'.Env::get('runtime_path').' 查看！文件：'.$fileName);
    }

2. 导出测试