ThinkPHP5.0之数据导出excel表格
- 使用composer安装phpexcel扩展类库
composer require phpoffice/phpexcel
- 控制器类中导入扩展类
use PHPExcel_IOFactory;
use PHPExcel;
- 控制器中方法
/**
* 显示列表
*
* @return \think\Response
*/
public function getIndex()
{
$list = Db::table('student')->select();
// 加载模板
return $this->fetch('index', ['list' => $list]);
}
/**
* excel表格导出处理
*/
public function getExport()
{
//1.从数据库中取出数据
$list = Db::name('student')->select();
//2.实例化PHPExcel类
$objPHPExcel = new \PHPExcel();
//3.激活当前的sheet表
$objPHPExcel->setActiveSheetIndex(0);
//4.设置表格头(即excel表格的第一行)
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A1', 'ID')
->setCellValue('B1', '姓名')
->setCellValue('C1', '年龄')
->setCellValue('D1', '班级')
->setCellValue('E1', '电话')
->setCellValue('F1', '邮箱');
//设置F列水平居中
$objPHPExcel->setActiveSheetIndex(0)->getStyle('F')->getAlignment()
->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//设置单元格宽度
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('E')->setWidth(15);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('F')->setWidth(30);
//5.循环刚取出来的数组,将数据逐一添加到excel表格。
for($i=0;$i<count($list);$i++){
$objPHPExcel->getActiveSheet()->setCellValue('A'.($i+2),$list[$i]['id']);//添加ID
$objPHPExcel->getActiveSheet()->setCellValue('B'.($i+2),$list[$i]['username']);//添加姓名
$objPHPExcel->getActiveSheet()->setCellValue('C'.($i+2),$list[$i]['age']);//添加年龄
$objPHPExcel->getActiveSheet()->setCellValue('D'.($i+2),$list[$i]['class']);//添加班级
$objPHPExcel->getActiveSheet()->setCellValue('E'.($i+2),$list[$i]['phone']);//添加电话
$objPHPExcel->getActiveSheet()->setCellValue('F'.($i+2),$list[$i]['email']);//添加邮箱
}
//6.设置保存的Excel表格名称
$filename = 'student'.date('ymd',time()).'.xls';
//7.设置当前激活的sheet表格名称;
$objPHPExcel->getActiveSheet()->setTitle('学生信息');
//8.设置浏览器窗口下载表格
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
header('Content-Disposition:inline;filename="'.$filename.'"');
//生成excel文件
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
//下载文件在浏览器窗口
$objWriter->save('php://output');
exit;
}
- 前台页面
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>列表</title>
<link href="https://cdn.bootcss.com/twitter-bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet">
</head>
<body>
<div class="container" style="margin-top: 2%;">
<table class="table table-bordered" width="60%">
<thead>
<tr>
<th width="30">ID</th>
<th width="50">姓名</th>
<th width="30">年龄</th>
<th width="30">班级</th>
<th width="30">电话</th>
<th width="30">邮箱</th>
</tr>
</thead>
<tbody>
{volist name="list" id="v"}
<tr>
<td>{$v['id']}</td>
<td>{$v['username']}</td>
<td>{$v['age']}</td>
<td>{$v['class']}</td>
<td>{$v['phone']}</td>
<td>{$v['email']}</td>
</tr>
{/volist}
</tbody>
</table>
<button type="button" onclick="window.open('/student/export')">导出Excel</button>
</div>
</body>
</html>