PHP 엑셀 다운로드 export data to Excel

PHPExcel 이라는 모듈을 통해 엑셀파일을 다운로드 받을 수 있다.

require_once("./include/PHPExcel/Classes/PHPExcel.php");
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator("NoErrorNoGain")
                             ->setLastModifiedBy("NoErrorNoGain")
                             ->setTitle("데이터")
                             ->setSubject("데이터")
                             ->setDescription("데이터")
                             ->setKeywords("데이터")
                             ->setCategory("데이터");
                             
                             
$objPHPExcel->getActiveSheet()->getRowDimension(2)->setRowHeight(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(9);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(27); 
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(27);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(27);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(100);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(16);
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(16); //폭 조절
$objPHPExcel->getDefaultStyle()->getFont()->setSize(9); // 글자 사이즈
$objPHPExcel->getActiveSheet()->mergeCells('A1:J1'); //머지 하기
$objPHPExcel->getActiveSheet()->setCellValue('A1','NoError');
$objPHPExcel->getActiveSheet()->setCellValue('B1','NoGain');
//색 지정하고 채우기
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('FFFFBF');
//글자 Weight
$objPHPExcel->getActiveSheet()->getStyle('A2:J2')->getFont()->setSize(10)->setBold(true);
//가로 세로 정렬
$objPHPExcel->getActiveSheet()->getStyle('A2:J2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A2:J2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

//WartText하여 줄바꿈된 글 한방에 표기
$objPHPExcel->getActiveSheet()->getStyle("A1:J$n")->getAlignment()->setWrapText(true);
//Border 작업
$objPHPExcel->getActiveSheet()->getStyle("A1:J$n")->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
//서식 적용 '$'표시
$objPHPExcel->getActiveSheet()->getStyle("J3:J$n")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD_SIMPLE);

 

셀들의 스타일이나 value들을 지정하는 것들과 웬만한 스타일도 다 지정이 가능하다. 위의 기능으로 for문돌려서 엑셀을 채운다.

 

그리고 다운로드될 수 있게 설정.

 

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

//header 지정
header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=$filename");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0,pre-check=0");
header("Pragma: public");
$objWriter->save('php://output');