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 協議》，轉載必須註明作者和本文連結