PhpSpreadsheet – Tips

$spreadsheet = new Spreadsheet();
$spreadsheet->getProperties()->setCreator('jongwan')
     ->setTitle('')
     ->setSubject('')
     ->setDescription('');

PhpSpreadsheet 초기화하기

$styleArray = [
     'borders' => [
         'allBorders' => [
             'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN
         ],
         'outline' => [
             'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK
         ]
     ]
 ];
 $spreadsheet->getActiveSheet()->getStyle('A1:D10')->applyFromArray($styleArray);

외곽선과 내부선을 스타일 지정하는 방법

// 숫자형 셀스타일
$spreadsheet->getActiveSheet()->setCellValueExplicit('A1', '내용', \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_NUMERIC);

// 문자형 셀스타일
$spreadsheet->getActiveSheet()->setCellValueExplicit('B1', '내용', \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);

// 지정없이 입력
$spreadsheet->getActiveSheet()->setCellValue('C1', '내용');

셀에 데이터타입을 지정하기

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="filename.xls"');
header('Cache-Control: no-cache');
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT+9'); // always modified
header('Pragma: public'); // HTTP/1.0
$writer = IOFactory::createWriter($spreadsheet, 'Xls');
$writer->save('php://output');

파일로 다운로드하기

// 첫번째 시트 선택 (0번부터 시작)
$spreadsheet->setActiveSheetIndex(0);
$spreadsheet->getActiveSheet()->setTitle('sheet name');

// 시트 생성하기
$spreadsheet->createSheet();
$spreadsheet->setActiveSheetIndex(1);
$spreadsheet->getActiveSheet()->setTitle("상세내역");

시트 생성, 선택, 이름지정하기

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

셀합치기(merge)

$spreadsheet->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_TOP);
$spreadsheet->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_BOTTOM);


$spreadsheet->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT);
$spreadsheet->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);

셀내용 정렬하기

$spreadsheet->getActiveSheet()->getStyle('A1:D5')->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setRGB('dddddd');

배경색 지정하기