[PHP] PHPExcel 라이브러리

PHPExcel 라이브러리를 이용한 엑셀다운로드 및 엑셀파일 DB 처리

PHPExcel은 Excel 2007, xlsx 및 Excel .xls와 같은 다양한 스프레드시트 파일 형식에서 읽고 쓸수 있는

PHP 라이브러리로 Microsoft Excel뿐만 아니라 다른 형식과도 상호 작용합니다.

 

PHPExcel 라이브러리 다운로드 : https://github.com/PHPOffice/PHPExcel

PHPExcel 다운로드

 

1. Excel 다운로드

require_once(dirname(__FILE__)."/../PHPExcel/Classes/PHPExcel.php");

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
$sheet = $objPHPExcel->getActiveSheet();
// sheet 활성화  
$objPHPExcel->setActiveSheetIndex(0);
// Excel 파일의 각셀의 타이틀 셋팅 
$objPHPExcel->getActiveSheet()
	->setCellValue('A1', '코드')
    ->setCellValue('B1', '상태')
    ->setCellValue('C1', '성명')
    ->setCellValue('D1', '연락처')
    ->setCellValue('E1', '휴대폰')
    ->setCellValue('F1', '이메일주소')
    ->setCellValue('G1', '우편번호')
    ->setCellValue('H1', '주소')
    ->setCellValue('I1', '메모');

// DB에서 데이터를 가져와 각해당 셀에 순차적 데이터 입력 
$sql = "select * from members order by idx asc";
$query = mysqli_query($sql);
// DB 데이터를 저장할 배열 초기화 
$tmp_excel = [];
while($res=mysqli_fetch_assoc($query))
{
	$result['code']	= $res['code'];
	$result['status'] = $res['status'];
	$result['name'] = $res['name'];
	$result['phone'] = $res['phone'];
	$result['mobile'] = $res['mobile'];
	$result['email'] = $res['email'];
	$result['zipcode'] = $res['zipcode'];
	$result['address'] = $res['address'];
	$result['memo'] = $res['memo'];

	$tmp_excel[] = $result;
}
// sheet 이름 설정 
$sheet->setTitle(date('Y-m-d')."회원정보");
// DB데이터 배열을 A2셀부터 입력 
$objPHPExcel->getActiveSheet()->fromArray($tmp_excel, NULL, 'A2');
// A1 ~ I1 까지 폰트 bold 설정
$objPHPExcel->getActiveSheet()->getStyle('A1:I1')->getFont()->setBold(true);
// A1 ~ I1 까지 셀의 배경색을 단색으로 채움.
$objPHPExcel->getActiveSheet()->getStyle('A1:I1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
// A1 ~ I1 까지 셀의 배경색 지정 
$objPHPExcel->getActiveSheet()->getStyle('A1:I1')->getFill()->getStartColor()->serRGB('FFF1F1F1');
// A1 ~ I1 까지 셀의 세로정렬을 가운데 정렬로 설정
$objPHPExcel->getActiveSheet()->getStyle('A1:I1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
// A1 ~ I1 까지 셀의 가로정렬을 가운데 정렬로 설정
$objPHPExcel->getActiveSheet()->getStyle('A1:I1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
// A1 ~ I1 까지 셀의 테두리(border) 설정 - 얅은 두께로 설정
$objPHPExcel->getActiveSheet()->getStyle('A1:I1')->getBorders()->getOutline()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
/*
배경색, 정렬, 보더 한번에 설정시 
$objPHPExcel->getActiveSheet()->getStyle('A1:L1')->applyFromArray(
array(
	'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('argb' => 'FFF1F1F1')), 
    'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, 'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER), 
    'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN))
   ));
*/

// Excel 파일 다운로드 
$filename = "회원목록(".date('Ymd').").xls";
header("Content-Type: application/vnd.ms-excel;charset=utf-8");
header("Content-Disposition: attachment;filename='".$filename."'");
header("Cache-Control: max-age=0");

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

 

2. 엑셀파일 업로드 DB Insert 처리

require_once(dirname(__FILE__)."/../PHPExcel/Classes/PHPExcel.php");

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

// 엑셀 업로드파일 확인후 읽기
if(!empty($_FILES['excel']['name']))
{
	$tmpexcel = $_FILES['excel']['tmp_name'];
    //엑셀 열에 따라 나눔
    $excelReader = PHPExcel_IOFactory::createReaderForFile($tmpexcel);
    //읽기전용으로 set
    $excelReader->setReadDataOnly(true);
    //엑셀파일 읽기
    $objExcel = $excelReader->load($tmpexcel);
    // 첫번째 시트를 선택
    $objExcel->setActiveSheetIndex(0);
    
    $objWorksheet = $objExcel->getActiveSheet();
    $rowIterator = $objWorksheet->getRowIterator();
    foreach ($rowIterator as $row) 
    {
    	$cellIterator = $row->getCellIterator();
        $cellIterator->setIterateOnlyExistingCells(false);
    }
    
    $maxRow = $objWorksheet->getHighestRow();
    $maxCell = $objWorksheet->getHighestColumn();
    
    for($i=1; $i <= $maxRow; $i++) 
    {
    	// 첫번째 행이 타이틀인경우 continue 
        if($i == 1) continue;
        
        $A = $objWorksheet->getCell('A'.$i)->getValue();	// A행값 가져오기
        $B = $objWorksheet->getCell('B'.$i)->getValue();	// B행값 가져오기
        $C = $objWorksheet->getCell('C'.$i)->getValue();	// C행값 가져오기
        $D = $objWorksheet->getCell('D'.$i)->getValue();	// D행값 가져오기
        
        // DB insert //
        $sql = sprintf("insert into member set a='%s', b='%s', c='%s', d='%s'", $A, $B, $C, $D);
        $qry = mysqli_query($conn, $sql);
   }
}
else
  echo("등록할 엑셀파일이 존재하지 않습니다.");