ThinkPHP6(tp6) 导出数据到Excel
发布时间:2021-11-09 09:47:20
栏目:程序猿
阅读量:4799
作者:webcms
我们在做网站后台或者某系统后台的时候往往会用到一个导出数据的功能,在这里我就简单的记录一下我用TP6导出数据到Excel的功能:
安装PHPOFFICE第三方库
首先在命令行工具到站点根目录, 利用composer 安装phpoffice/phpspreadsheet 第三方库
composer require phpoffice/phpspreadsheet
获取需要导出的数据
$start_time= strtotime(input('start_time'));
$end_time= strtotime(input('end_time'))+86399;
if (empty($start_time)){
$start_time=strtotime(date('Y-m-01'));
}
if (empty($end_time) || $end_time == 86399 ){
$end_time = strtotime(date('Y-m-d H:i:s',time()));
}
$userUp= new UserUp();
$where=[
[ 'status' ,'=', '1'],
['create_time','between',[$start_time,$end_time]]
];
$up_all= $userUp->with('user')->where($where)->select();
if (empty($up_all) || $up_all == ""){
return show(config('status.error'),'暂时没有信息!');
}
以上代码是获取指定日期内的数据 获取到相关数据以后,下面就是导入到excel了。
首先在控制器导入 PhpSpreadsheet\Spreadsheet 类库
use PhpOffice\PhpSpreadsheet\Spreadsheet;
然后下面就是 开始设置excel格式,然后写入到excel。
$spreadsheet = new Spreadsheet();
$worksheet = $spreadsheet->getActiveSheet();
$worksheet->setCellValueByColumnAndRow(1, 1, '姓名');
$worksheet->setCellValueByColumnAndRow(2, 1, '性别');
$worksheet->setCellValueByColumnAndRow(3, 1, '电话');
$worksheet->setCellValueByColumnAndRow(4, 1, '充值金额');
$worksheet->setCellValueByColumnAndRow(5, 1, '备注');
$worksheet->setCellValueByColumnAndRow(6, 1, '充值时间');
$worksheet->setCellValueByColumnAndRow(7, 1, '操作人');
$styleArray = [
'font' => [
'bold' => false
],
'alignment' => [
'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
],
];
$worksheet->getStyle('A1:G1')->applyFromArray($styleArray)->getFont()->setSize(14);
$worksheet->getColumnDimension('C')->setWidth('15');
$worksheet->getColumnDimension('D')->setWidth('15');
$worksheet->getColumnDimension('E')->setWidth('15');
$worksheet->getColumnDimension('F')->setWidth('20');
$j = 0;
for ($i=0; $i < count($up_all); $i++) {
$j = $i + 2; //从表格第3行开始
$worksheet->setCellValueByColumnAndRow(1, $j, $up_all[$i]['user']['name']);
$gender =$up_all[$i]['user']['gender']== 1 ? '男':'女';
$worksheet->setCellValueByColumnAndRow(2, $j, $gender );
$worksheet->setCellValueByColumnAndRow(3, $j, $up_all[$i]['user']['phone']);
$worksheet->setCellValueByColumnAndRow(4, $j, $up_all[$i]['amount']);
$worksheet->setCellValueByColumnAndRow(5, $j, $up_all[$i]['notes']);
$worksheet->setCellValueByColumnAndRow(6, $j, $up_all[$i]['create_time']);
$worksheet->setCellValueByColumnAndRow(7, $j, $up_all[$i]['editer']);
}
$filename = '会员充值记录'. date('Ymd',$start_time).'-'.date('Ymd',$end_time).'.xlsx';
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="'.$filename.'"');
header('Cache-Control: max-age=0');
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save('php://output');
以上两段代码在同一个方法内,经过测试,可以上线使用。
下一篇:TP6 上传图片
评论: