PhpSpreadsheet 설정 샘플 코드

<?php

	require 'vendor/autoload.php';

	use PhpOffice\PhpSpreadsheet\IOFactory;
	use PhpOffice\PhpSpreadsheet\Spreadsheet;

	$spreadsheet = new Spreadsheet();
?>

<? php

 // 문서 가로,세로 방향 설정	
 $spreadsheet->getActiveSheet()->getPageSetup()
 ->setOrientation(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::ORIENTATION_PORTRAIT);

 // 문서 사이즈 설정
 $spreadsheet->getActiveSheet()->getPageSetup()
 ->setPaperSize(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::PAPERSIZE_A4);

 // 문서 정보 설정
 $spreadsheet->getProperties()->setCreator("써드아이시스템")
 ->setLastModifiedBy("써드아이시스템")
 ->setTitle("문서 타이틀")
 ->setSubject("문서 제목")
 ->setDescription("문서 설명")
 ->setKeywords("키워드")
 ->setCategory("카테고리");


 // 문서 좌우 여백 설정
 $spreadsheet->getActiveSheet()->getPageMargins()->setTop(0.5);	
 $spreadsheet->getActiveSheet()->getPageMargins()->setBottom(0.25);
 $spreadsheet->getActiveSheet()->getPageMargins()->setRight(0.75);
 $spreadsheet->getActiveSheet()->getPageMargins()->setLeft(0.75);
?>

<? php	
  $spreadsheet->setActiveSheetIndex(0)->setCellValue("C11", $value);
  $spreadsheet->setActiveSheetIndex(0)->setCellValue("B25", "셀 값 설정\r\n다음 줄");
?>

<? php
  $drawing_logo = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();	
  $drawing_logo->setName('Logo');
  $drawing_logo->setDescription('3rdEYESYS Logo');
  $drawing_logo->setPath('/ncp/data/www/img/3rdeyesys_logo.png');
  $drawing_logo->setCoordinates('A1');
  $drawing_logo->setWidth(230);
  $drawing_logo->getShadow()->setVisible(true);
  $drawing_logo->setWorksheet($spreadsheet->getActiveSheet());
?>

<? php	
  // 동일한 행에서 병합
  $spreadsheet->getActiveSheet()->mergeCells("A1:D1");

  // 여러 행에서 병합
  $spreadsheet->getActiveSheet()->mergeCells("G5:I7");

  // 변수 사용
  for ($j = 52; $j <= 57; $j++)
  {
    $spreadsheet->getActiveSheet()->mergeCells("B".$j.":C".$j);
  }
?>

<?php

  for ($j = 1; $j <= 5; $j++)
  {
    $spreadsheet->getActiveSheet()->getRowDimension($j)->setRowHeight(12);
  }

  $spreadsheet->getActiveSheet()->getRowDimension(10)->setRowHeight(35);
?>

<?php

  // 특정 열 너비 설정
  $spreadsheet->getActiveSheet()->getColumnDimension("A")->setWidth(3);

  // 범위 내 여러 열 너비 설정
  foreach(range("B","J") as $columnID) 
  {
    $spreadsheet->getActiveSheet()->getColumnDimension($columnID)->setWidth(15);
  }
?>

<?php

  // 셀 스타일을 배열 형식으로 저장
  // 순서대로 border, font, 배경색 지정하는 스타일
  $styleArray_Cell = [
    'borders' => [
      'allBorders' => [
        'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
        'color' => ['argb' => 'FF20AFA5']
      ]
    ],
    'font' => [
      'bold' => true,
      'size' => 9,
      'color'    => ['argb' => 'FFFFFFFF'],
    ],
    'fill' =>[
      'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
      'color' => ['argb' => 'FF20AFA5']
    ]
  ];

  // 특정 셀에 스타일 적용
  $spreadsheet->getActiveSheet()
  ->getStyle("B40")->applyFromArray($styleArray_Cell);
  
  // 특정 범위의 여러 셀에 스타일 동시 적용
  $spreadsheet->getActiveSheet()
  ->getStyle("G45:G46")->applyFromArray($styleArray_Cell);

  // 특정 셀에 폰트 스타일 직접 적용
  $spreadsheet->getActiveSheet()
  ->getStyle("B80")->getFont()->setSize(13)->setBold(true);
	
?>

<?php
  $spreadsheet->setActiveSheetIndex(0);

  // Mpdf 클래스 별로 설치해야 함
  IOFactory::registerWriter('Pdf', \PhpOffice\PhpSpreadsheet\Writer\Pdf\Mpdf::class);

  $out_put_file_full_name = "sample.pdf";

  // Redirect output to a client’s web browser (PDF)
  header('Content-Type: application/pdf; charset=utf-8' );
  header('Content-Disposition: attachment;filename="'.$out_put_file_full_name.'"');
  header('Cache-Control: max-age=0');

  $writer = IOFactory::createWriter($spreadsheet, 'Pdf');
  $writer->save('php://output');
  exit;
?>

<?php
  $spreadsheet->setActiveSheetIndex(0);

  // Redirect output to a client’s web browser (Excel2007)
  $out_put_file_full_name = "sample.xlsx";

  header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
  header('Content-Disposition: attachment;filename="'.$out_put_file_full_name.'"');
  header('Cache-Control: max-age=0');

  // If you're serving to IE over SSL, then the following may be needed
  header ('Expires: Mon, 26 Jul 2022 05:00:00 GMT'); // Date in the past
  header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
  header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
  header ('Pragma: public'); // HTTP/1.0

  $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');	
  $writer->save('php://output');
  exit;
?>