PHP导入导出EXCEL之Spreadsheet （phpexc

一  要求：

      1 php5.6版本以上

      2 需要安装composer进行安装

二   然后下载PhpOffice\PhpSpreadsheet

三   然后控制器添加如下代码可以测试：

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;

use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$rows=array(

    array('name'=>'xiaoming','age'=>'12','zhiye'=>'教师'),

    array('name'=>'芳芳','age'=>'20','zhiye'=>'军人'),

    array('name'=>'jony','age'=>'52','zhiye'=>'医生'),

    array('name'=>'tom','age'=>'23','zhiye'=>'程序猿'),

    array('name'=>'小红','age'=>'26','zhiye'=>'工程师')

    );

        $spreadsheet = new Spreadsheet();

        $worksheet = $spreadsheet->getActiveSheet();

//设置工作表标题名称

$worksheet->setTitle('个人信息表');

//表头

//设置单元格内容

$worksheet->setCellValueByColumnAndRow(1, 1, '个人信息表');

$worksheet->setCellValueByColumnAndRow(1, 2, '姓名');

$worksheet->setCellValueByColumnAndRow(2, 2, '年龄');

$worksheet->setCellValueByColumnAndRow(3, 2, '职业');

//合并单元格

$worksheet->mergeCells('A1:C1');

$styleArray = [

    'font' => [

        'bold' => true

    ],

    'alignment' => [

        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,

    ],

];

//设置单元格样式

$worksheet->getStyle('A1')->applyFromArray($styleArray)->getFont()->setSize(28);

$worksheet->getStyle('A2:C2')->applyFromArray($styleArray)->getFont()->setSize(14);

$len=count($rows);

$j = 0;

for ($i=0; $i < $len; $i++) {

    $j = $i + 3; //从表格第3行开始

    $worksheet->setCellValueByColumnAndRow(1, $j, $rows[$i]['name']);

    $worksheet->setCellValueByColumnAndRow(2, $j, $rows[$i]['age']);

    $worksheet->setCellValueByColumnAndRow(3, $j, $rows[$i]['zhiye']);

}

$styleArrayBody = [

    'borders' => [

        'allBorders' => [

            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,

            'color' => ['argb' => '666666'],

        ],

    ],

    'alignment' => [

        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,

    ],

];

$total_rows = $len + 2;

//添加所有边框/居中

$worksheet->getStyle('A1:C'.$total_rows)->applyFromArray($styleArrayBody);

$filename = '信息.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');

欢迎关注交流