TP5导出EXCEL表格（附带样式修改）

下载好TP框架

http://packagist.p2hp.com/ 

搜索phpexcel

框架根目录

composer require phpoffice/phpexcel

下载excle包

 

 

$objExcel = new \PHPExcel;

$objWriter = \PHPExcel_IOFactory::createWriter($objExcel, 'Excel5');
$objActSheet = $objExcel->getActiveSheet(0);
$objActSheet->setTitle('测试');

//合并单元格

$objActSheet->mergeCells("A1:H2"); 纵横 合并，或者斜着合并。都可以

//加粗

$objActSheet->getStyle('A1')->getFont()->setBold(true);//加粗

垂直居中

$objActSheet->getStyle('A1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);

$objActSheet->getStyle('A2:F2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

水平居中

$objActSheet->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$objActSheet->getStyle('A1:F3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

设置默认行高：

$objActSheet->getDefaultRowDimension()->setRowHeight(27);//设置默认行高

$objActSheet->->getRowDimension('1')->setRowHeight(20);//设置第一行行高

设置A列宽度

$objActSheet->getColumnDimension('A')->setWidth(10);//设置A列宽度

 

字体与样式

$objActSheet->getDefaultStyle()->getFont()->setSize(12);//字体大小

$objActSheet->getStyle('A1:F1')->getFont()->setBold(false);//是否加粗

$objActSheet->getStyle('A1')->getFont()->setBold(true);//是是否加粗

$objActSheet->getStyle('A1')->getFont()->setSize(12);//第一行字体大小

 

 

 

贴代码

<?php
namespace app\index\controller;
use think\Db;
use PHPExcel_IOFactory;
use PHPExcel;


class Index
{



    public function index()
    {

        return view();
        // return '<style type="text/css">*{ padding: 0; margin: 0; } div{ padding: 4px 48px;} a{color:#2E5CD5;cursor: pointer;text-decoration: none} a:hover{text-decoration:underline; } body{ background: #fff; font-family: "Century Gothic","Microsoft yahei"; color: #333;font-size:18px;} h1{ font-size: 100px; font-weight: normal; margin-bottom: 12px; } p{ line-height: 1.6em; font-size: 42px }</style><div style="padding: 24px 48px;"> <h1>:) </h1><p> ThinkPHP V5.1<br/><span style="font-size:30px">12载初心不改（2006-2018） - 你值得信赖的PHP框架</span></p></div><script type="text/javascript" src="https://tajs.qq.com/stats?sId=64890268" charset="UTF-8"></script><script type="text/javascript" src="https://e.topthink.com/Public/static/client.js"></script><think id="eab4b9f840753f8e7"></think>';
    }

    public function hello()
    {
        $tim = date("Y-m-d H:i:s ",time());

         $objExcel = new \PHPExcel;

        $objWriter = \PHPExcel_IOFactory::createWriter($objExcel, 'Excel5');
        $objActSheet = $objExcel->getActiveSheet(0);
        $objActSheet->setTitle('测试');
            

        $objActSheet->mergeCells("A1:H2");//合并

        $objActSheet->setCellValue('A1','易为易科技有限公司');//插入数据

        $objActSheet->mergeCells("A3:H3");//合并
        $objActSheet->setCellValue('A3','产品报价单');//插入数据

        $objActSheet->getStyle('A1')->getFont()->setBold(true);//加粗
        $objActSheet->getStyle('A2')->getFont()->setBold(true);//加粗
        $objActSheet->getStyle('A1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objActSheet->getStyle('A3')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        // $objActSheet->getStyle('A2:H2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $objActSheet->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objActSheet->getStyle('A3')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        // $objActSheet->getStyle('A1:H3')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $objActSheet->getDefaultRowDimension()->setRowHeight(27);//设置默认行高
        $objActSheet->getColumnDimension('A')->setWidth(10);//设置A列宽度
        $objActSheet->getColumnDimension('B')->setWidth(10);//设置b列宽度
        $objActSheet->getColumnDimension('C')->setWidth(10);//设置c列宽度
        $objActSheet->getColumnDimension('D')->setWidth(10);//设置d列宽度
        $objActSheet->getColumnDimension('E')->setWidth(10);//设置e列宽度
        $objActSheet->getColumnDimension('F')->setWidth(10);//设置f列宽度
        $objActSheet->getColumnDimension('G')->setWidth(10);//设置g列宽度
        $objActSheet->getColumnDimension('H')->setWidth(15);//设置h列宽度
        $objActSheet->getStyle('A1')->getFont()->setSize(22); //设置 A1字体大小
        $objActSheet->getStyle('A3')->getFont()->setSize(15); //设置 A1字体大小



        $objActSheet->mergeCells("A4:C4");//合并
        $objActSheet->mergeCells("D4:G4");//合并
        $objActSheet->setCellValue('A4','购货单位:');//插入数据
        $objActSheet->setCellValue('D4','联系电话:');//插入数据

        $objActSheet->getStyle('A4')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objActSheet->getStyle('D4')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $objActSheet->setCellValue('H4', $tim );


        $objActSheet->setCellValue('A5', '');
        $objActSheet->setCellValue('B5', '产品名称');
        $objActSheet->setCellValue('C5', '规格');
        $objActSheet->setCellValue('D5', '单位');
        $objActSheet->setCellValue('E5', '单价');    
        $objActSheet->setCellValue('F5', '数量');
        $objActSheet->setCellValue('G5', '总金额');
        $objActSheet->setCellValue('H5', '备注' );

        $objActSheet->mergeCells("A11:G11");//合并
        $objActSheet->setCellValue('A11', '合计金额：贰万叁仟肆佰玖拾陆元二角' );
        $objActSheet->setCellValue('H11', '¥：23496.2元' );



        $objActSheet->mergeCells("A12:G13");//合并
        $objActSheet->setCellValue('A12', '备  注：' );


        $objActSheet->mergeCells("A14:D14");//合并
        $objActSheet->setCellValue('A14', '报价人：' );
        $objActSheet->mergeCells("E14:H14");//合并
        $objActSheet->setCellValue('A14', '电   话：' );
        $objActSheet->mergeCells("A14:H14");//合并
        $objActSheet->setCellValue('A14', '地   址：' );
                    



        $baseRow = 6; //数据从N-1行开始往下输出 这里是避免头信息被覆盖
        $explame_data = Db::name('spec')->select();
        // var_dump($explame_data);die();
        foreach ($explame_data as & $lottery) {

        }

        foreach ($explame_data as $key => $value){
        $i = $baseRow + $key;

        $objExcel->getActiveSheet()->setCellValue('A' . $i, $value['id']);
        $objExcel->getActiveSheet()->setCellValue('B' . $i, $value['name']);
        $objExcel->getActiveSheet()->setCellValue('C' . $i, $value['spec_name']);
        $objExcel->getActiveSheet()->setCellValue('D' . $i, $value['company']);
        $objExcel->getActiveSheet()->setCellValue('E' . $i, $value['price']);
        $objExcel->getActiveSheet()->setCellValue('F' . $i, $value['number']);
        $objExcel->getActiveSheet()->setCellValue('G' . $i, $value['total_amount']);
        $objExcel->getActiveSheet()->setCellValue('H' . $i, $value['remarks']);




            }
            $objExcel->setActiveSheetIndex(0);
            //4、输出
            $objExcel->setActiveSheetIndex();
            header('Content-Type: applicationnd.ms-excel');
            // $filename = date('YmdHis');$filename
            header("Content-Disposition: attachment;filename=".'1'.".xlsx");
            header('Cache-Control: max-age=0');
            $objWriter->save('php://output');





    }
}