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;
?>