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

Myth丶恋晨 2022-02-02 22:39 807阅读 0赞

> 本文已在本人博客[https://www.nsxsg.com/archives/192][https_www.nsxsg.com_archives_192]首发

### 文章目录 ###

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

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

## 安装PHPExcel ##

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

composer require phpoffice/phpexcel

![image][]

## 导入数据 ##

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

![image][image 1]

1.  在ThinkPHP中引入PHPExcel类库

![image][image 2]

1.  编写导入的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, '导入失败！');
            }
        }

![image][image 3]

## 导出数据 ##

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);
        }

![image][image 4]

1.  导出测试

![image][image 5]

![image][image 6]

[https_www.nsxsg.com_archives_192]: https://www.nsxsg.com/archives/192
[image]: http://image.nsxsg.com/uploads/20190118/20190118100710_d9d43bbb414724c653040db2605bc09b.png
[image 1]: http://image.nsxsg.com/uploads/20190118/20190118100710_d7f9b3b74d968344200ea6bc3b355f28.png
[image 2]: http://image.nsxsg.com/uploads/20190118/20190118100710_be1e6384222341fa5f32b29b168e952d.png
[image 3]: http://image.nsxsg.com/uploads/20190118/20190118100710_62de8bc7c6c6a83821340422d65c0d15.png
[image 4]: http://image.nsxsg.com/uploads/20190118/20190118100710_4aa9dd9e9b75394c641eadcd089b4b74.png
[image 5]: http://image.nsxsg.com/uploads/20190118/20190118100710_0bceff2d0e2bb387fa859f972568262d.png
[image 6]: http://image.nsxsg.com/uploads/20190118/20190118100711_30a8ae834d6e4519176ac0c927899f78.png