一个网站建设爱好者,技术交流,相互学习
当前位置:首页>>程序猿
ThinkPHP6(tp6) 导出数据到Excel
发布时间:2021-11-09 09:47:20 栏目:程序猿 阅读量:452 作者: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');

以上两段代码在同一个方法内,经过测试,可以上线使用。

评论:

IT视频教程
Copyright © web小工匠 保留所有权利. 渝ICP备15008469号 网站地图