ThinkPHP使用Excel导出数据

1.下载PHPExcel

composer下载：  composer require phpoffice/phpexcel

没有安装composer的话可以去https://github.com/PHPOffice/PHPExcel下载，

2.引入

use PHPExcel;
use PHPExcel_IOFactory;

3.使用

根据自己需求设置标题，宽高

public function getListToExcel($where = null)
    {
        $res = $this
            ->order('id', 'desc')
            ->where($where)
            ->select()
            ->toArray();
        if ($res) {

            //导出表格
            $objExcel = new PHPExcel();
            $objWriter = PHPExcel_IOFactory::createWriter($objExcel, 'Excel5');
            // 设置水平垂直居中
            $objExcel->getActiveSheet()->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objExcel->getActiveSheet()->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            // 字体和样式
            $objExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(10);
            $objExcel->getActiveSheet()->getStyle('A2:AB2')->getFont()->setBold(true);
            $objExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
            // 第一行、第二行的默认高度
            $objExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(30);
            $objExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
            //设置某一列的宽度
            $objExcel->getActiveSheet()->getColumnDimension('A')->setWidth(10);
            $objExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15);
            $objExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
            $objExcel->getActiveSheet()->getColumnDimension('D')->setWidth(10);
            $objExcel->getActiveSheet()->getColumnDimension('E')->setWidth(10);
            $objExcel->getActiveSheet()->getColumnDimension('F')->setWidth(30);
            $objExcel->getActiveSheet()->getColumnDimension('G')->setWidth(30);
            $objExcel->getActiveSheet()->getColumnDimension('H')->setWidth(15);
//设置表头
//  合并
            $objExcel->getActiveSheet()->mergeCells('A1:I1');
            $objActSheet = $objExcel->getActiveSheet(0);
            $objActSheet->setTitle('结算统计');//设置excel的标题
            $objActSheet->setCellValue('A1', '结算统计');
            $objActSheet->setCellValue('A2', '日期');
            $objActSheet->setCellValue('B2', '充值笔数');
            $objActSheet->setCellValue('C2', '当日充值');
            $objActSheet->setCellValue('D2', '佣金比例');
            $objActSheet->setCellValue('E2', '邀请奖励');
            $objActSheet->setCellValue('F2', '订单退款');
            $objActSheet->setCellValue('G2', '结算金额');
            $objActSheet->setCellValue('H2', '状态');

            $baseRow = 3; //数据从N-1行开始往下输出 这里是避免头信息被覆盖
            foreach ($res as $r => $d) {
                $i = $baseRow + $r;
                $objExcel->getActiveSheet()->setCellValue('A' . $i, $d['date']);
                $objExcel->getActiveSheet()->setCellValue('B' . $i, $d['num']);
                $objExcel->getActiveSheet()->setCellValue('C' . $i, $d['recharge']);
                $objExcel->getActiveSheet()->setCellValue('D' . $i, $d['rate']);
                $objExcel->getActiveSheet()->setCellValue('E' . $i, $d['invest_money']);
                $objExcel->getActiveSheet()->setCellValue('F' . $i, $d['refund_money']);
                $objExcel->getActiveSheet()->setCellValue('G' . $i, $d['money']);
                $objExcel->getActiveSheet()->setCellValue('H' . $i, $d['status']);
            }
            $objExcel->setActiveSheetIndex(0);
            //输出
            $objExcel->setActiveSheetIndex();
            header('Content-Type: applicationnd.ms-excel');
            $time = date('YmdHis');
            header("Content-Disposition: attachment;filename=结算统计$time.xls");
            header('Cache-Control: max-age=0');
            $objWriter->save('php://output');
            exit();
        }
    }