php数据导出excel

weixin_50024460

已于 2022-08-23 15:31:58 修改

阅读量71

点赞数

分类专栏： PHP 文章标签： php

于 2022-08-11 10:09:28 首次发布

本文链接：https://blog.csdn.net/weixin_50024460/article/details/126279625

版权

PHP 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

<?php
/**
 * Created by PhpStorm.
 * User: REN
 * Date: 2020/6/23
 * Time: 14:39
 */



namespace app\backstage\controller;


use think\Controller;

class Excel extends Controller
{
public function index($list){
	
    include_once '../vendor/PHPExcel/PHPExcel.php';//这里是excel导出第三方库
    $objPHPExcel = new \PHPExcel();
    $objPHPExcel->getProperties()->setCreator("ctos")
        ->setLastModifiedBy("ctos")
        ->setTitle("Office 2007 XLSX Test Document")
        ->setSubject("Office 2007 XLSX Test Document")
        ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
        ->setKeywords("office 2007 openxml php")
        ->setCategory("Test result file");
//设置表格
    $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(50);

    //设置行高度
    $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(22);
    $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);

    //set font size bold
    $objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(12);//设置字体大小
    $objPHPExcel->getActiveSheet()->getStyle('A2:E2')->getFont()->setBold(true);

    $objPHPExcel->getActiveSheet()->getStyle('A2:E2')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
    $objPHPExcel->getActiveSheet()->getStyle('A2:E2')->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);

    //设置水平居中
    $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
    $objPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $objPHPExcel->getActiveSheet()->getStyle('B')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $objPHPExcel->getActiveSheet()->getStyle('D')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $objPHPExcel->getActiveSheet()->getStyle('E')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $objPHPExcel->getActiveSheet()->getStyle('F')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

    //合并cell
    //$objPHPExcel->getActiveSheet()->mergeCells('A1:J1');

    // set table header content
    //设置表格头部主题
    $objPHPExcel->setActiveSheetIndex(0)
        ->setCellValue('A1', '数据库ID')
        ->setCellValue('B1', '大学名称')
        ->setCellValue('C1', '会员数')
        ->setCellValue('D1', '会员金额')
        ->setCellValue('E1', '分红给邀请人金额(元)')
        ->setCellValue('F1', '给商家提成金额(元)');

    //dump($list[0]["create_time"]);die;
    // Miscellaneous glyphs, UTF-8
// $time= strtotime(date("Y-m-d H:i"));
// $date=date("Y-m-d H:i",$list[0]["create_time"]);
//设置表格内容
    for($i=0;$i<=count($list)-1;$i++){
        $objPHPExcel->getActiveSheet(0)->setCellValue('A'.($i+3), $list[$i]['id']);
        $objPHPExcel->getActiveSheet(0)->setCellValue('B'.($i+3), $list[$i]['school_name']);
        $objPHPExcel->getActiveSheet(0)->setCellValue('C'.($i+3), $list[$i]['all_money']);
        $objPHPExcel->getActiveSheet(0)->setCellValue('D'.($i+3), $list[$i]['member_count']);
        $objPHPExcel->getActiveSheet(0)->setCellValue('E'.($i+3), $list[$i]['business']);
        $objPHPExcel->getActiveSheet(0)->setCellValue('F'.($i+3), $list[$i]['invite_part']);

        //$objPHPExcel->getActiveSheet()->getStyle('A'.($i+3).':J'.($i+3))->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //$objPHPExcel->getActiveSheet()->getStyle('A'.($i+3).':J'.($i+3))->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getRowDimension($i+3)->setRowHeight(16);
    }
    //dump($objPHPExcel);die;

    //  sheet命名
    $objPHPExcel->getActiveSheet()->setTitle('大学统计表');


    // Set active sheet index to the first sheet, so Excel opens this as the first sheet
    $objPHPExcel->setActiveSheetIndex(0);


    // excel头参数
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment;filename="大学统计表('.date('Ymd-His').').xls"');  //日期为文件名后缀
    header('Cache-Control: max-age=0');

    $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');  //excel5为xls格式，excel2007为xlsx格式

    $objWriter->save('php://output');
}
}