PHPExcel 导出数据到excel表格

tp5测试可用：

通过链接下载phpexcel插件

https://download.csdn.net/upload/11304406

 

解压放到vendor下面

 

 

php代码

public function excel_down()
{
   
    $list = Db::name('test')->select();

    $product_state=Config::get('product_state');
    $designbm=Config::get('designbm');
    $pclass_array=Config::get('pclass');

    vendor("PHPExcel.PHPExcel");
    $objPHPExcel = new \PHPExcel();
    // 设置sheet
    $objPHPExcel->setActiveSheetIndex(0);
    // 设置列的宽度
    $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(8);
    $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(60);
    $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
    $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(35);
    $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(15);
    $objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(15);

    // 设置表样式
    // 字体大小
    $objPHPExcel->getDefaultStyle()->getFont()->setSize(12);
    $objPHPExcel->getActiveSheet()->getStyle("A1:Z1")->getFont()->setSize(14);
    //加粗
    $objPHPExcel->getActiveSheet()->getStyle("A1:Z1")->applyFromArray(array('font'=>array('bold'=>true)));
    //行高
    $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(24);
    //居中
    $objPHPExcel->getActiveSheet()->getStyle('A1:Z1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

    // 设置表头
    $objPHPExcel->getActiveSheet()->SetCellValue('A1', 'ID');
    $objPHPExcel->getActiveSheet()->SetCellValue('B1', '项目名称');

    //存取数据
    $num = 2;
    foreach ($list as $k => $v) {
        $objPHPExcel->getActiveSheet()->SetCellValue('A'.$num, $v['id']);
        $objPHPExcel->getActiveSheet()->SetCellValue('B'.$num, $v['name']);

        $num++;
    }

    // 文件名称
    $fileName = date('Y-m-d',time())."项目信息";
    $xlsName = iconv('utf-8', 'gb2312', $fileName);
    // 设置工作表名
    $objPHPExcel->getActiveSheet()->setTitle('sheet');
    //下载 excel5与excel2007
    $objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);
    ob_end_clean();     // 清除缓冲区,避免乱码
    header("Pragma: public");
    header("Expires: 0");
    header("Cache-Control:must-revalidate,post-check=0,pre-check=0");
    header("Content-Type:application/force-download");
    header("Content-Type:application/vnd.ms-execl;charset=UTF-8");
    header("Content-Type:application/octet-stream");
    header("Content-Type:application/download");
    header("Content-Disposition:attachment;filename=" . $xlsName . ".xls");
    header("Content-Transfer-Encoding:binary");
    $objWriter->save("php://output");
}

 

 

html代码

<a href="{:url('admin/index/excel_down')}" title="导出EXCEL表格"></a>

 

导出成功