首先将PHPExcel文件放到vendor中
PHPExcel下载地址：https://pan.baidu.com/s/1GjbcSXxHwbLeBw8NzzIBYA
提取码：zbqq
数据导出到excel中
控制器中引入类文件
vendor ( ‘PHPExcel‘ );
vendor ( ‘PHPExcel.PHPExcel_IOFactory‘ );
vendor ( ‘PHPExcel.PHPExcel_Cell‘ );
控制器php代码
???public function index()//EXCL表格的导出
???{
???????$data = Db::table(‘score‘)->select();
???????$name = ‘test‘;
???????$excel = new \PHPExcel(); //引用phpexcel
???????iconv(‘UTF-8‘, ‘gb2312‘, $name); //针对中文名转码
???????$header = [‘ID‘, ‘sid‘, ‘语文‘, ‘数学‘, ‘英语‘, ‘政治‘, ‘学期‘]; //表头,名称可自定义
???????$excel->setActiveSheetIndex(0);
???????$excel->getActiveSheet()->setTitle($name); //设置表名
???????$excel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(18);
???????$excel->getActiveSheet()->getColumnDimension(‘B‘)->setWidth(80);
???????$letter = [‘A‘, ‘B‘, ‘C‘, ‘D‘, ‘E‘, ‘F‘, ‘G‘];//列坐标
//生成表头
???????for ($i = 0; $i < count($header); $i++) {
???????????//设置表头值
???????????$excel->getActiveSheet()->setCellValue("$letter[$i]1", $header[$i]);
???????????//设置表头字体样式
???????????$excel->getActiveSheet()->getStyle("$letter[$i]1")->getFont()->setName(‘宋体‘);
???????????//设置表头字体大小
???????????$excel->getActiveSheet()->getStyle("$letter[$i]1")->getFont()->setSize(14);
???????????//设置表头字体是否加粗
???????????$excel->getActiveSheet()->getStyle("$letter[$i]1")->getFont()->setBold(true);
???????????//设置表头文字水平居中
???????????$excel->getActiveSheet()->getStyle("$letter[$i]1")->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
???????????//设置文字上下居中
???????????$excel->getActiveSheet()->getStyle($letter[$i])->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
???????????//设置单元格背景色
???????????$excel->getActiveSheet()->getStyle("$letter[$i]1")->getFill()->getStartColor()->setARGB(‘FFFFFFFF‘);
???????????$excel->getActiveSheet()->getStyle("$letter[$i]1")->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
???????????$excel->getActiveSheet()->getStyle("$letter[$i]1")->getFill()->getStartColor()->setARGB(‘FF6DBA43‘);
???????????//设置字体颜色
???????????$excel->getActiveSheet()->getStyle("$letter[$i]1")->getFont()->getColor()->setARGB(‘FFFFFFFF‘);
???????}
???????//写入数据
???????foreach ($data as $k => $v) {
???????????//从第二行开始写入数据（第一行为表头）
???????????$excel->getActiveSheet()->setCellValue(‘A‘ . ($k + 2), $v[‘id‘]);
???????????$excel->getActiveSheet()->setCellValue(‘B‘ . ($k + 2), $v[‘sid‘]);
???????????$excel->getActiveSheet()->setCellValue(‘C‘ . ($k + 2), $v[‘chinese‘]);
???????????$excel->getActiveSheet()->setCellValue(‘D‘ . ($k + 2), $v[‘math‘]);
???????????$excel->getActiveSheet()->setCellValue(‘E‘ . ($k + 2), $v[‘english‘]);
???????????$excel->getActiveSheet()->setCellValue(‘F‘ . ($k + 2), $v[‘politics‘]);
???????????$excel->getActiveSheet()->setCellValue(‘G‘ . ($k + 2), $v[‘xqid‘]);
???????}
???????//设置单元格边框
???????$excel->getActiveSheet()->getStyle("A1:E" . (count($data) + 1))->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
//清理缓冲区，避免中文乱码
???????ob_end_clean();
???????header(‘Content-Type: application/vnd.ms-excel‘);
???????header(‘Content-Disposition: attachment;filename="‘ . $name . ‘.xls"‘);
???????header(‘Cache-Control: max-age=0‘);
???????//导出数据
???????$res_excel = \PHPExcel_IOFactory::createWriter($excel, ‘Excel2007‘);
???????$res_excel->save(‘php://output‘);
???}
导出是调用index（）就可以了
excel数据导入数据库
引用类
vendor(‘PHPExcel.Writer.IWriter‘);
vendor(‘PHPExcel.Writer.Abstract‘);
vendor(‘PHPExcel.Writer.Excel5‘);
vendor(‘PHPExcel.Writer.Excel2007‘);
vendor(‘PHPExcel.IOFactory‘);
前端excel上传页面
<!DOCTYPE html>
<html lang="en">
<head>
???<meta charset="UTF-8">
???<title>Title</title>
</head>
<body>
<form ?enctype="multipart/form-data" method="post" action="index3">
???<input type="file" name="excel" /> <br>
???<input type="submit" value="上传" />
</form>
</body>
</html>
//请忽略前端页面的样式，重点是功能，嘻嘻^_^
将文件上传到index3控制器中
index3的php代码
public function index3()
{
???// 获取表单上传文件
???$file = request()->file(‘excel‘);
???// 移动到框架应用根目录/public/uploads/ 目录下
???if ($file) {
???????$info = $file->move(ROOT_PATH . ‘public‘ . DS . ‘uploads‘);
???????if ($info) {
???????????$path = ROOT_PATH . ‘public‘ . DS . ‘uploads‘ . DS;
???????????$filename = $info->getSaveName();
???????????$file_path = $path . $filename;
???????????$objPHPExcel = \PHPExcel_IOFactory::load($file_path);//读取上传的文件
???????????$arr = $objPHPExcel->getSheet(0)->toArray();//获取其中的数据
???????????echo "<pre>";
???????????print_r($arr);
???????????die;//excel表中的数据，将数据插入数据库请自行书写^_^..
???????} else {
???????????echo $file->getError();
???????}
???}
}
已经将excel表中数据提取出来，剩余的将数据插入数据库中，请自行书写^_^..
以上代码本人亲测，导入。导出可以顺利实现，没有遇到问题，如有遇到报错或者问题请联系本人。
一起努力，共同进步。
thinkphp5中的excel表的导入导出
原文地址：https://www.cnblogs.com/cds98/p/10113310.html