PhpSpreadsheet系列二： laravel框架下 PhpSpreadsheet 使用攻略 excle的常用样式

laravel框架下 PhpSpreadsheet 使用攻略 excle的各种样式

写在前面

按照上次安装好

laravel框架下 新手如何使用安装PhpSpreadsheet

在你的控制器新增 PhpSpreadsheet调用

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet(); //新建实例
$spreadsheet->createSheet();      //创建sheet
$objActSheet= $spreadsheet->setActiveSheetIndex(0);//设置当前的活动sheet

一、样式设置

1、对其方式设置

//默认水平居中
$spreadsheet->getDefaultStyle()->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
//默认垂直居中
$spreadsheet->getDefaultStyle()->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);

2、字体设置

//加粗、字体样式、字体大小
$spreadsheet->getActiveSheet(0)->getStyle('A:L')->getFont()->setBold(true)->setName('宋体')->setSize(12);
//颜色
$spreadsheet->getActiveSheet($n)->getTabColor()->setARGB('00F4B084')

3、固定第一行

$spreadsheet->getActiveSheet(0)->freezePane('A1');

4、边框设置

$border = [
    'borders' => [
        'outline' => [//外边框
            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
        ],
        'inside' =>[ //内边框
            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
        ]
    ],
 ];
$spreadsheet->getActiveSheet($n)->getStyle('A:J')->applyFromArray($border);

5、行高 行宽

//行高
$spreadsheet->getActiveSheet()->getDefaultRowDimension()->setRowHeight(30);
//行宽
$spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(8);

6、合并单元格

$spreadsheet->getActiveSheet()->mergeCells('A1:L1'); 

二、内容

1、表头赋值

$objActSheet->setCellValue('A1', '名称')
            ->setCellValue('B2', '时间');

2、内容赋值

//数据循环
$sql="";
$data=DB::select($sql);
foreach ($data as $i => $d) {
    $spreadsheet->getActiveSheet(0)->setCellValue('A' . ($i + 1), $d->name);
    $spreadsheet->getActiveSheet(0)->setCellValue('B' . ($i + 1), $d->time);
}

三、输出文档

header('Content-Type: application/vnd.ms-excel; charset=UTF-8');
header('Content-Disposition: attachment;filename="' . $name . '.xlsx"');
header('Cache-Control: max-age=0');
$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
//删除清空：
$spreadsheet->disconnectWorksheets();
unset($spreadsheet);
exit;

本作品采用《CC 协议》，转载必须注明作者和本文链接