[php] PHPExcel 엑셀로 데이터 다운받기

 

 

1. PHPExcel 다운받기

 

PHPExcel은 데이터를 엑셀로 다운받을 수 있게 해주는 라이브러리이다.
https://github.com/PHPOffice/PHPExcel 홈페이지에서 다운받거나 아래 파일을 다운 받는다.

PHPExcel.zip

0.92MB

 

 

2. 다운로드 파일 코드 

별도의 php파일을 만든 후, 파일 상단에 include_once('/PHPExcel.php');를 넣어 PHPExcel.php을 불러온다.

include_once('/PHPExcel.php');

 

그 다음으로 SQL문 등 데이터를 가져오는 구문들을 넣어주고

$rows[] = array(1열데이터,2열데이터, 3열데이터, 4열데이터, 5열데이터); 이런식으로 array()안에 데이터를 콤마로 구분하여 넣어준다. 나는 세개의 시트로 나누어서 다운받게 할 예정이라 각 시트별로 $rows, $rows2, $rows3 이렇게 3개로 구분되어있다.

$sql  = " selec * from ~~"; //SQL문 입력
$result = mysqli_query($conn, $sql);

for($i=1; $row=mysqli_fetch_array($result); $i++) { // 헤더가 두줄이므로 $i=1부터 시작   
        $rows[] = array($key,$row['user_id'],$user_add['age'],$user_add['gender'],$row['name'],$row['phone'],$rg['region_name'],$user_add['job'],$user_add['diagnosis'],$user_add['income'],$user_add['family'],$sv1['survey_scale'],$sv2['survey_scale'],0,$value1,$end_date);//첫번째 시트 데이터
        $rows2[] = array($key,$row['user_id'],$user_add['age'],$user_add['gender'],$row['name'],$row['phone'],$rg['region_name'],$user_add['job'],$user_add['diagnosis'],$user_add['income'],$user_add['family'],$sv1['survey_scale'],$sv2['survey_scale'],0,$value2,$end_date);//두번째 시트 데이터
        $rows3[] = array($key,$row['user_id'],$user_add['age'],$user_add['gender'],$row['name'],$row['phone'],$rg['region_name'],$user_add['job'],$user_add['diagnosis'],$user_add['income'],$user_add['family'],$sv1['survey_scale'],$sv2['survey_scale'],0,$value3,$end_date);//세번째 시트 데이터
}

 

 

엑셀로 출력하는 구문과 시트별로 원하는 옵션이 있으면 입력하면 된다.

$excel = new PHPExcel();

/*sheet1*/
$excel->setActiveSheetIndex(0)->getStyle( "A1:${last_char}1" )->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB($header_bgcolor);
$excel->setActiveSheetIndex(0)->getStyle( "A:$last_char" )->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setWrapText(true);
foreach($widths as $i => $w) $excel->setActiveSheetIndex(0)->getColumnDimension( column_char($i) )->setWidth($w);
$excel->getActiveSheet()->fromArray($data1,NULL,'A1');
$excel->getActiveSheet()->setTitle("1번시트제목"); 

/* sheet2 */
$Sheet2 = $excel->createSheet(1);
$Sheet2->getStyle( "A1:${last_char}1" )->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB($header_bgcolor);
$Sheet2->getStyle( "A:$last_char" )->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setWrapText(true);
foreach($widths as $i => $w) $Sheet2->getColumnDimension( column_char($i) )->setWidth($w);
$Sheet2->fromArray($data2,NULL,'A1');
$Sheet2->setTitle("2번시트제목"); 

/* sheet3*/
$Sheet3 = $excel->createSheet(2);
$Sheet3->getStyle( "A1:${last_char}1" )->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB($header_bgcolor);
$Sheet3->getStyle( "A:$last_char" )->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setWrapText(true);
foreach($widths as $i => $w) $Sheet3->getColumnDimension( column_char($i) )->setWidth($w);
$Sheet3->fromArray($data3,NULL,'A1');
$Sheet3->setTitle("3번시트제목");

 

PHPExcel옵션에는 이런 것들이 있다.

엑셀 파일 오픈시 활성화될 시트 지정	$excel -> setActiveSheetIndex(0); //첫번째 시트 활성화
시트 생성	$excel->createSheet(1); $excel->createSheet(2); $excel->createSheet(3);
시트 이름 지정	$sheet->setTitle("시트이름");
셀 값 입력	$sheet->setCellValue("A1", "셀값");
셀 합치기	$sheet->mergeCells('A1:C1');
셀 포맷 지정	$sheet->getNumberFormat()->setFormatCode('#,##0');  //숫자에 콤마 찍기
타이틀 색상지정	$sheet -> getStyle( "A1:F1" )->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB("F2F2F2");
(가로) 가운데 정렬	$sheet -> getStyle( "A1:F1" ) -> getAlignment()
(세로) 가운데 정렬	$sheet -> getStyle( "A1:F1" ) -> getAlignment() -> setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
셀 가로 크기 지정	$sheet -> getColumnDimension("A") -> setWidth(10); $sheet -> getColumnDimension("B") -> setWidth(20); $sheet -> getColumnDimension("C") -> setWidth(20); $sheet -> getColumnDimension("D") -> setWidth(30);
셀 세로 높이 지정	$sheet->getRowDimension(1)->setRowHeight(25);
셀 텍스트 굵게	$sheet->getStyle('A1')->getFont()->setBold(true);
셀 텍스트 사이즈 지정	$sheet->getStyle('A1')->getFont()->setSize(13);
바깥 윤곽선 테두리 지정	$sheet->getStyle('A1:C6')->getBorders()->getOutline()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

 

 

 

 

 

첫번째 시트만 $excel->getActiveSheet() 이런식으로 지정해주면 되고,

나머지 시트는 $Sheet2 = $excel->createSheet(1); 시트를 하나 생성해서 $Sheet2 변수로 담은 뒤, $Sheet2->setTitle("2번시트제목"); 이런식으로 옵션을 지정해주면 된다.

 

 

 

마지막으로 가장 하단에 엑셀로 저장할 수 있도록 코드를 넣어준다.

$excel->setActiveSheetIndex(0);

header("Content-Type: application/octet-stream");
header("Content-Disposition: attachment; filename=\"파일명.xls\"");
header("Cache-Control: max-age=0");

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

 

 

 

 

전체코드

<?php
include_once('/PHPExcel.php');

function column_char($i) { return chr( 65 + $i ); }

/*첫번째시트 헤더*/
$top1 = array('NO', 'ID', 'AGE', 'SEX', 'NAME', 'HP', 'REGION', 'JOB', 'DIA', 'INCOME', 'FAMILY','PHQ-9','ANX', 'SCALE', 'GPQ', 'DATE');
$headers1 = array('번호','ID', '나이', '성별', '이름', '전화번호', '지역', '직업', '진단명', '수입', '가구원', '검사점수','검사점수','검사점수', 'GPS측정값', '날짜');

/*두번째시트 헤더*/
$top2 = array('NO', 'ID', 'AGE', 'SEX', 'NAME', 'HP', 'REGION', 'JOB', 'DIA', 'INCOME', 'FAMILY','PHQ-9','ANX', 'SCALE', 'TOUCH', 'DATE');
$headers2 = array('번호','ID', '나이', '성별', '이름', '전화번호', '지역', '직업', '진단명', '수입', '가구원', '검사점수','검사점수','검사점수', '터치 횟수', '날짜');

/*세번째시트 헤더*/
$top3 = array('NO', 'ID', 'AGE', 'SEX', 'NAME', 'HP', 'REGION', 'JOB', 'DIA', 'INCOME', 'FAMILY','PHQ-9','ANX', 'SCALE', 'SLEEP', 'DATE');
$headers3 = array('번호','ID', '나이', '성별', '이름', '전화번호', '지역', '직업', '진단명', '수입', '가구원', '검사점수','검사점수','검사점수', '수면시간', '날짜');

$widths  = array(8, 15, 8, 8, 15, 15, 15, 15, 15, 15, 15, 15, 15, 15, 15, 15); //칸 넓이
$header_bgcolor = 'FFDDEBF7'; //헤더 배경색

$last_char = column_char(count($headers1) - 1);

$sql  = " selec * from ~~ "; //SQL문 입력
$result = mysqli_query($conn, $sql);

for($i=1; $row=mysqli_fetch_array($result); $i++) { // 헤더가 두줄이므로 $i=1부터 시작   
        $rows[] = array($key,$row['user_id'],$user_add['age'],$user_add['gender'],$row['name'],$row['phone'],$rg['region_name'],$user_add['job'],$user_add['diagnosis'],$user_add['income'],$user_add['family'],$sv1['survey_scale'],$sv2['survey_scale'],0,$value1,$end_date);//첫번째 시트 데이터
        $rows2[] = array($key,$row['user_id'],$user_add['age'],$user_add['gender'],$row['name'],$row['phone'],$rg['region_name'],$user_add['job'],$user_add['diagnosis'],$user_add['income'],$user_add['family'],$sv1['survey_scale'],$sv2['survey_scale'],0,$value2,$end_date);//두번째 시트 데이터
        $rows3[] = array($key,$row['user_id'],$user_add['age'],$user_add['gender'],$row['name'],$row['phone'],$rg['region_name'],$user_add['job'],$user_add['diagnosis'],$user_add['income'],$user_add['family'],$sv1['survey_scale'],$sv2['survey_scale'],0,$value3,$end_date);//세번째 시트 데이터
}


$data1 = array_merge(array($top1), array_merge(array($headers1), $rows));
$data2 = array_merge(array($top2), array_merge(array($headers2), $rows2));
$data3 = array_merge(array($top3), array_merge(array($headers3), $rows3));

$excel = new PHPExcel();

/*sheet1*/
$excel->setActiveSheetIndex(0)->getStyle( "A1:${last_char}1" )->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB($header_bgcolor);
$excel->setActiveSheetIndex(0)->getStyle( "A:$last_char" )->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setWrapText(true);
foreach($widths as $i => $w) $excel->setActiveSheetIndex(0)->getColumnDimension( column_char($i) )->setWidth($w);
$excel->getActiveSheet()->fromArray($data1,NULL,'A1');
$excel->getActiveSheet()->setTitle("1번시트제목"); 

/* sheet2 */
$Sheet2 = $excel->createSheet(1);
$Sheet2->getStyle( "A1:${last_char}1" )->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB($header_bgcolor);
$Sheet2->getStyle( "A:$last_char" )->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setWrapText(true);
foreach($widths as $i => $w) $Sheet2->getColumnDimension( column_char($i) )->setWidth($w);
$Sheet2->fromArray($data2,NULL,'A1');
$Sheet2->setTitle("2번시트제목"); 

/* sheet3*/
$Sheet3 = $excel->createSheet(2);
$Sheet3->getStyle( "A1:${last_char}1" )->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB($header_bgcolor);
$Sheet3->getStyle( "A:$last_char" )->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setWrapText(true);
foreach($widths as $i => $w) $Sheet3->getColumnDimension( column_char($i) )->setWidth($w);
$Sheet3->fromArray($data3,NULL,'A1');
$Sheet3->setTitle("3번시트제목"); 


$excel->setActiveSheetIndex(0);

header("Content-Type: application/octet-stream");
header("Content-Disposition: attachment; filename=\"파일명.xls\"");
header("Cache-Control: max-age=0");

$writer = PHPExcel_IOFactory::createWriter($excel, 'Excel5');
$writer->save('php://output');
?>