관리자 페이지를 만들다 보면 "이 목록 엑셀로 받을 수 있게 해 주세요"라는 요청이 거의 반드시 나옵니다. CSV로 내려 주면 간단하긴 한데, 한글이 깨진다는 얘기를 듣거나 숫자가 지수로 바뀐다는 불만이 따라오죠. 진짜 xlsx 파일로 만들어 주면 그런 문의가 확 줄어듭니다. 예전에 PHP에서 이 일을 할 때 가장 많이 쓰던 게 PHPExcel입니다.

라이브러리 준비

PHPExcel은 GitHub 저장소에서 받을 수 있습니다.

광고

압축을 풀면 여러 폴더가 나오는데, 실제로 필요한 건 Classes 폴더 하나입니다. 이 폴더를 통째로 프로젝트 안 원하는 위치에 복사해 두면 됩니다. Composer 없이 require_once 한 줄로 불러 쓰는 구조라서, 오래된 호스팅처럼 Composer를 못 쓰는 환경에서도 바로 적용할 수 있다는 게 장점이었습니다.

GitHub PHPExcel 저장소에서 최신 버전을 내려받는 화면

압축을 푼 PHPExcel 폴더에서 Classes 폴더를 복사하는 화면

객체 생성과 문서 속성

엑셀을 만들 페이지(예: index.php)에서 가장 먼저 라이브러리를 불러오고 PHPExcel 객체를 만듭니다. 문서 속성은 파일을 연 뒤 "파일 > 정보"에서 보이는 작성자, 제목 같은 값입니다.

/** Include PHPExcel */
require_once 'Classes/PHPExcel.php';

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

// Set document properties
$objPHPExcel->getProperties()->setCreator("../../../")
                             ->setLastModifiedBy("../../../")
                             ->setTitle("../../../")
                             ->setSubject("../../../")
                             ->setDescription("../../../")
                             ->setKeywords("../../../")
                             ->setCategory("../../../");

각 메서드에 들어간 "../../../"는 자리만 잡아 둔 값입니다. 실제로 쓸 때는 회사명이나 시스템 이름, 파일 설명처럼 원하는 문자열로 바꿔 넣으면 됩니다. 속성을 아예 안 넣어도 파일 생성에는 문제가 없습니다.

테이블 전체를 엑셀로 내보내는 코드

$DB_TBLName = isset($tb_nm) ? $tb_nm : "pt_product"; //MySQL Table Name
$filename = $DB_TBLName.".xlsx";         //File Name

$col = "*";
$sql = "Select $col from $DB_TBLName";
$result = @mysql_query($sql,$con) or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno());

$excelnm = iconv('UTF-8','EUC-KR',$filename);
$row_array = array();
$data_array = array();

for ($i = 0; $i < mysql_num_fields($result); $i++) {
    $row_array[$i] = mysql_field_name($result,$i);
}
array_push($data_array, $row_array);

while($row = mysql_fetch_row($result))
{
    $row_array = array();
    for($j=0; $j<mysql_num_fields($result);$j++)
    {
        $col = mysql_field_name($result,$j);
        if(!isset($row[$j]))
            $row_array[$j] = "NULL";
        elseif ($row[$j] != "")
            if($col!='pd_img')
                $row_array[$j] = $row[$j];
            else
                $row_array[$j] = "Bitmap Images";
        else
            $row_array[$j] = "";
    }
    array_push($data_array, $row_array);
}

$objPHPExcel->getActiveSheet()->fromArray($data_array,NULL,'A1');

header('Content-Type: application/octet-stream');
header('Content-Disposition: attachment; filename="'.$excelnm.'"');
header('Cache-Control: max-age=0');

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

흐름은 단순합니다. 먼저 필드 이름을 뽑아 첫 줄(헤더)로 넣고, 그다음 레코드를 한 줄씩 배열로 만들어 2차원 배열 $data_array에 쌓습니다. 값이 NULL이면 "NULL"이라는 글자로, 빈 문자열이면 빈칸으로 넣고, pd_img처럼 이미지 바이너리가 들어 있는 칼럼은 엑셀 셀에 넣어 봐야 깨진 글자만 나오니 "Bitmap Images"라는 표시로 대신합니다. 이 부분은 테이블 구조에 맞게 칼럼 이름을 바꿔 쓰면 됩니다.

다 모은 배열은 fromArray로 A1 셀부터 한 번에 채웁니다. 셀을 하나씩 setCellValue로 넣는 것보다 코드가 짧고 빠릅니다. 마지막으로 다운로드 헤더를 보내고 php://output에 저장하면, 서버에 파일을 남기지 않고 브라우저로 바로 내려갑니다.

파일 이름을 iconv로 EUC-KR로 바꾸는 건 예전 IE에서 한글 파일명이 깨지던 문제 때문입니다. 테이블명이 영문이면 영향이 없고, 요즘 브라우저에서는 오히려 filename*=UTF-8''... 형식으로 보내는 쪽이 맞습니다.

문서 속성이 설정된 엑셀 파일의 정보 화면

내려받은 xlsx 파일의 속성 결과 화면

지금 서버에서 돌리려면

위 코드는 mysql_query, mysql_fetch_row 같은 mysql_* 함수를 씁니다. 이 함수들은 PHP 7.0에서 완전히 제거돼서, PHP 7 이상에서는 "Call to undefined function" 오류로 바로 멈춥니다. 아래는 같은 동작을 mysqli로 바꿔 본 수정본입니다($con이 mysqli 연결이라고 가정).

$result = mysqli_query($con, $sql) or die("Couldn't execute query:<br>" . mysqli_error($con) . "<br>" . mysqli_errno($con));

$fields = mysqli_fetch_fields($result);
$row_array = array();
$data_array = array();

foreach ($fields as $i => $f) {
    $row_array[$i] = $f->name;
}
array_push($data_array, $row_array);

while ($row = mysqli_fetch_row($result)) {
    $row_array = array();
    foreach ($fields as $j => $f) {
        if (!isset($row[$j]))
            $row_array[$j] = "NULL";
        elseif ($row[$j] != "")
            $row_array[$j] = ($f->name != 'pd_img') ? $row[$j] : "Bitmap Images";
        else
            $row_array[$j] = "";
    }
    array_push($data_array, $row_array);
}

한 가지 더, $tb_nm을 요청 파라미터에서 그대로 받아 SQL에 붙이면 아무 테이블이나 내려받을 수 있게 됩니다. 허용할 테이블 이름을 배열로 정해 두고 그 안에 있을 때만 실행하도록 막아 두세요.

PHPExcel 자체도 개발이 끝난 프로젝트이고 PHP 8에서는 경고가 쏟아집니다. 새로 만든다면 후속 라이브러리인 PhpSpreadsheet를 쓰는 게 맞습니다. 사용 방식이 거의 비슷해서, PHPExcel 클래스를 Spreadsheet로, PHPExcel_IOFactory의 Excel2007 writer를 Xlsx writer로 바꾸는 정도로 대부분 옮겨집니다. 다만 레코드가 수만 건을 넘으면 어느 쪽이든 메모리를 많이 먹으니, 그때는 기간 조건을 걸어 나눠 받게 하는 편이 낫습니다.