PHPExcelでエクセル出力

メモ PHP

PHPExcelのインストール

composer require phpoffice/phpexcel


スポンサーリンク





値入力

$book = new PHPExcel();
$sheet = $book->getActiveSheet();
$book->getActiveSheet()->setTitle("シート1です");
$book->createSheet()->setTitle("二枚目!");
$book->createSheet()->setTitle("さんまいめだよ");


// セル番地で書いてみる
$sheet->setCellValue('A1', 10000);
$sheet->setCellValue('A2', true);
$sheet->setCellValue('A3', 'テスト');

// 行列番号で書いてみる
$column = 1;
$sheet->setCellValueByColumnAndRow($column, 1, 'B1');
$sheet->setCellValueByColumnAndRow($column, 2, 'B2');
$sheet->setCellValueByColumnAndRow($column, 3, 'B3');
$sheet->setCellValue('C1', '=A1 * B1');

// 罫線
$sheet
    ->getStyle('A1:C3')
    ->getBorders()
    ->getAllBorders()
    ->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

// 色を指定する場合
$sheet
    ->getStyle('A5:C8')
    ->applyFromArray([
        'borders' => [
            'allborders' => [
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => ['rgb' => 'FF0000'],
            ],
        ],
    ]);

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="output.xlsx"');
header('Cache-Control: max-age=0');

$writer = PHPExcel_IOFactory::createWriter($book, 'Excel2007');
$writer->save('php://output');

テンプレートから出力する

$products = [
    ['Microsoft Excel 2016',      14000, 2],
    ['Microsoft Word 2016',       14800, 1],
    ['Microsoft PowerPoint 2016', 15000, 1],
];

$book = PHPExcel_IOFactory::load('templates.xltx');
$sheet = $book->getActiveSheet();

$rowOffset = 3;
foreach ($products as $row => $product) {
    foreach ($product as $col => $value) {
        $sheet->setCellValueByColumnAndRow($col, $row + $rowOffset, $value);
    }
}

$writer = PHPExcel_IOFactory::createWriter($book, 'Excel2007');
$writer->save('output.xlsx');

参照

qiita.com