PHP 환경에서 엑셀 파일을 다뤄야 할 때, 보통 PHPExcel을 많이 사용하는데 이는 이미 서비스가 2019년에 종료되었으며 공식 문서도 PhpSpreadsheet를 권고한다고 명시되어있다. (현재 PhpSpreadsheet는 PHP 8 이상부터 지원되므로 참고)
PhpSpreadsheet를 사용한 간단한 목록의 엑셀 파일 다운로드 및 업로드(후 데이터 수정) 기능을 구현하는 코드에 대하여 설명하겠다.
1. PhpSpreadsheet 설치
웹 서버에 PhpSpreadsheet를 사용하는 데 필요한 PHP 확장 모듈들이 설치되어 있는지 php-m으로 확인하고 설치한다. 확장 모듈은 xml, gd, mbstring, zip이 필요하다. 설치가 완료되었다면 php-m으로 확장 모듈들의 설치 여부를 한 번 더 확인한다.
php-m sudo dnf install php-xml php-gd php-mbstring sudo dnf install php8.2-devel php-pear libzip libzip-devel sudo pecl install zip echo "extension=zip.so" | sudo tee /etc/php.d/20-zip.ini
PhpSpreadsheet를 설치하는 데 필요한 PHP 의존성 관리 도구 Composer를 설치한다. 설치가 완료되었다면 composer-V로 Composer의 설치 여부를 확인한다.
php -r "copy('https://getcomposer.org/installer', 'composer-setup.php');"
php -r "if (hash_file('sha384', 'composer-setup.php') === 'e21205b207c3ff031906575712edab6f13eb0b361f2085f1f1237b7126d785e826a450292b6cfd1d64d92e6563bbde02') { echo 'Installer verified'; } else { echo 'Installer corrupt'; unlink('composer-setup.php'); } echo PHP_EOL;"
php composer-setup.php
php -r "unlink('composer-setup.php');"
sudo mv composer.phar /usr/local/bin/composer
composer-V
PHP 확장 모듈 및 Composer 설치가 완료되었다면 PhpSpreadsheet를 사용할 디렉터리에 PhpSpreadsheet를 설치한다.
composer require phpoffice/phpspreadsheet
2. jQuery 스크립트 태그 추가
파일 최상단 또는 Head 파일에 다음과 같이 작성하여 jQuery를 사용하기 위해 추가한다.
<script src="https://code.jquery.com/jquery-3.6.1.js" integrity="sha256-3zlB5s2uwoUzrXK3BT7AX3FyvojsraNFxCc2vC/7pNI=" crossorigin="anonymous"></script>
3. 목록 및 엑셀 다운로드, 업로드 폼 작성
설명을 위해 목록은 페이징 없이 간단하게 작성했다. 목록들을 체크 박스로 선택한 후 엑셀 다운로드 버튼을 클릭하면 해당 목록들이 포함된 엑셀 파일 다운로드가 가능하도록 구현한다.
엑셀 업로드 버튼을 클릭하면 업로드 폼을 띄우고 다운로드 받았던 엑셀 파일의 기타 정보를 입력한 후 엑셀 파일을 업로드하여 폼의 등록 버튼을 클릭하면 입력한 데이터로 기타 정보가 수정되도록 구현한다.
<table>
<tr>
<td>
<button type="button" id="excel_download">엑셀 다운로드</button>
<button type="button" id="excel_upload">엑셀 업로드</button>
</td>
</tr>
</table>
<form>
<table>
<thead>
<tr>
<th><input type= "checkbox" name="chkall" id="chkall" onclick="check_all(this.form)"></th>
<th>번호</th>
<th>가입 일자</th>
<th>아이디</th>
<th>이름</th>
<th>생년월일</th>
<th>전화번호</th>
<th>기타 정보</th>
</tr>
</thead>
<?php
global $mysqli;
$sql = "select * from table order by key desc";
$result = $mysqli->query($sql);
while($row = $result->fetch_array()){
if(empty($row["기타 정보"])){
$row["기타 정보"] = "없음";
}else{
if(mb_strlen($row["기타 정보"], "utf-8") > 15){
$row["기타 정보"] = mb_substr($row["기타 정보"], 0, 15, "utf-8")."...";
}
}
?>
<tr>
<td><input type= "checkbox" name="chk[]" value="<?=$row["key"]?>"></td>
<td><?=$row["key"]?></td>
<td><?=$row["가입 일자"]?></td>
<td><?=$row["아이디"]?></td>
<td><?=$row["이름"]?></td>
<td><?=$row["생년월일"]?></td>
<td><?=$row["전화번호"]?></td>
<td><?=$row["기타 정보"]?></td>
</tr>
<?php
}
?>
</tbale>
</form>
<div id="excel_upload_frm">
<h3>엑셀 업로드</h3>
<span style="color:red;">※ 엑셀 다운로드 후 비어있는 기타 정보를 채우고 업로드하시면 자동으로 등록됩니다.</span>
<form action="엑셀 업로드 처리 파일 경로" id="excel_uploading" enctype="multipart/form-data" method="POST" style="display:none; z-index: 999; text-align:center;">
<input name="target_excel" type="file" id="target_excel">
</form>
<button type="button" id="btn_upload_excel">등록</button>
<button type="button" id="btn_upload_excel_back">닫기</button>
</div>
<script type="text/javascript" src="동작 구현 파일"></script>
4. 동작 구현 파일 작성
체크 박스로 목록들을 선택한 후 엑셀 다운로드를 클릭하면 선택한 목록들의 키값들을 임의의 폼을 생성해 전송하고 선택한 목록이 없으면 예외 처리한다.
엑셀 업로드 버튼을 클릭하면 업로드 폼을 띄우고 엑셀 파일을 업로드 한 후 폼의 등록 버튼을 클릭하여 엑셀 데이터를 전송한다.
function check_all(f){
var chk = document.getElementsByName("chk[]");
for(i=0; i<chk.length; i++) chk[i].checked = f.chkall.checked;
}
$('#excel_download').click(function(){
var chk = document.getElementsByName("chk[]");
var z = 0;
for(i=0; i<chk.length; i++){
if(chk[i].checked){
z++;
}
}
if(z == 0){
alert("다운로드 하실 내역을 선택해주세요.");
return;
}
var chk = document.getElementsByName("chk[]");
var no_arr = [];
for(i=0; i<chk.length; i++){
if(chk[i].checked){
no_arr[no_arr.length] = chk[i].value;
}
}
var excelForm = $("<form></form>");
excelForm.attr("method", "Post");
excelForm.attr("action", "엑셀 다운로드 처리 파일 경로");
excelForm.append($("<input/>", {type: "hidden", name: "noArr", value: no_arr}));
excelForm.appendTo("body");
excelForm.submit();
});
$("#excel_upload").click(function(){
$("#excel_upload_frm").show();
});
$("#btn_upload_excel_back").click(function(){
$("#excel_upload_frm").hide();
});
$("#btn_upload_excel").click(function(){
if($("#target_excel")[0].files.length == 0){
alert("엑셀 파일을 첨부해주세요.");
return false;
}
$("#excel_uploading").submit();
});
체크 박스, 엑셀 업로드 버튼과 폼이 동작하는지 확인한다.
5. 엑셀 다운로드 처리 파일 작성
설치한 PhpSpreadsheet를 사용하기 위해 PhpSpreadsheet를 불러오는 코드를 최상단에 추가한다. 첫 행에는 컬럼명이 출력되도록 설정하고 나머지 열의 크기, 색상, 정렬을 보기 편하게 설정한다. 마지막으로 엑셀 파일 이름에 날짜를 붙여 저장되도록 구현한다.
<?php
require_once($_SERVER["DOCUMENT_ROOT"]."PhpOffice/Psr/autoloader.php 경로");
require_once($_SERVER["DOCUMENT_ROOT"]."PhpOffice/PhpSpreadsheet/autoloader.php 경로");
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
$spreadsheet = new Spreadsheet();
// 첫 행
$spreadsheet->setActiveSheetIndex(0)
->setCellValue("A1", "번호")
->setCellValue("B1", "가입 일자")
->setCellValue("C1", "아이디")
->setCellValue("D1", "이름")
->setCellValue("E1", "생년월일")
->setCellValue("F1", "전화번호")
->setCellValue("G1", "기타 정보");
// 각 열 크기
$spreadsheet->getActiveSheet()->getColumnDimension("A")->setWidth(5);
$spreadsheet->getActiveSheet()->getColumnDimension("B")->setWidth(20);
$spreadsheet->getActiveSheet()->getColumnDimension("C")->setWidth(20);
$spreadsheet->getActiveSheet()->getColumnDimension("D")->setWidth(20);
$spreadsheet->getActiveSheet()->getColumnDimension("E")->setWidth(20);
$spreadsheet->getActiveSheet()->getColumnDimension("F")->setWidth(20);
$spreadsheet->getActiveSheet()->getColumnDimension("G")->setWidth(20);
// 각 열 백그라운드 색상
$spreadsheet->getActiveSheet()->getStyle("A1:G1")->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setRGB("DDDDDD");
// 각 열 정렬
$spreadsheet->getActiveSheet()->getStyle("A1:G1")->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER); // 수평 중앙 정렬
$spreadsheet->getActiveSheet()->getStyle("A:G")->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER); // 수직 중앙 정렬
$spreadsheet->getActiveSheet()->getStyle("A:G")->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle("A:G")->getAlignment()->setWrapText(true); // 셀에 여러 줄 표시
// 각 열 값
$noArr = $_POST["noArr"];
global $mysqli;
$sql = "select * from table where key in (".$noArr.") order by key desc";
$result = $mysqli->query($sql);
$cnt = 2;
while($row = $result->fetch_array()){
$spreadsheet->getActiveSheet()->setCellValue("A".$cnt, $row["key"]);
$spreadsheet->getActiveSheet()->setCellValue("B".$cnt, $row["가입 일자"]);
$spreadsheet->getActiveSheet()->setCellValue("C".$cnt, $row["아이디"]);
$spreadsheet->getActiveSheet()->setCellValue("D".$cnt, $row["이름"]);
$spreadsheet->getActiveSheet()->setCellValue("E".$cnt, $row["생년월일"]);
$spreadsheet->getActiveSheet()->setCellValue("F".$cnt, $row["전화번호"]);
$spreadsheet->getActiveSheet()->setCellValue("G".$cnt, $row["기타 정보"]);
++$cnt;
}
$fileName = "목록".date("Ymd").".xlsx";
$write = new Xlsx($spreadsheet);
ob_end_clean();
header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
header("Content-Disposition: attachment; filename=".$fileName);
header("Cache-Control: max-age=0");
$write->save("php://output");
exit;
?>
엑셀이 잘 다운로드 되는지 확인한다.
6. 엑셀 업로드 처리 파일 작성
다운로드 처리 파일과 같이 PhpSpreadsheet를 불러오는 코드를 최상단에 추가한다. DB의 기타 정보 데이터를 수정하기 위한 함수를 작성하고 발생할 수 있는 에러를 예외 처리한다.
파일을 임시 저장 폴더에 저장하고 PhpSpreadsheet Reader로 저장된 엑셀 파일을 읽는다. 첫 번째 행에는 컬럼명이 출력되어 있으므로 A열 2행부터 데이터를 불러와서 A열(키 값), G열(기타 정보) 데이터를 데이터 수정 함수로 전송한다. 수정 함수가 정상적으로 작동하면 알림 메시지를 띄우고 목록 페이지로 돌아간다.
<?php
require_once($_SERVER["DOCUMENT_ROOT"]."PhpOffice/Psr/autoloader.php 경로");
require_once($_SERVER["DOCUMENT_ROOT"]."PhpOffice/PhpSpreadsheet/autoloader.php 경로");
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
function update_column($data){
global $mysqli;
$sql = "update table set column = '".$data["기본 정보"]."' where key = '".$data["key"]."';";
$result = $mysqli->query($sql);
if($result){
return $sql;
}
}
// 업로드 가능한 확장자 설정
$allowed_file = array('xls', 'xlsx');
$error = $_FILES['target_excel']['error'];
$file_fullname = $_FILES['target_excel']['name'];
$file_name = array_pop(explode('.', $file_fullname));
try {
if($_FILES['target_excel']['error'] != 4){
if($error != UPLOAD_ERR_OK){
switch($error){
case UPLOAD_ERR_INI_SIZE:
case UPLOAD_ERR_FORM_SIZE:
throw new Exception("파일의 용량이 너무 큽니다. (".$error.")");
break;
case UPLOAD_ERR_NO_FILE:
throw new Exception("파일이 첨부 되지 않았습니다. (".$error.")");
break;
default:
throw new Exception("파일이 제대로 업로드 되지 않았습니다. (".$error.")");
}
exit;
}
if(!in_array($file_name, $allowed_file)){ // 확장자 확인
throw new Exception("업로드가 불가한 파일 형식입니다.");
}
if(!move_uploaded_file($_FILES['target_excel']['tmp_name'], "파일 임시 저장 폴더 경로".$file_fullname)){
throw new Exception("파일 저장에 실패하였습니다.");
}
$file_path = "파일 임시 저장 폴더 경로".$file_fullname;
$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
$reader->setReadDataOnly(true);
$spreadsheet = $reader->load($file_path);
$maxRow = $spreadsheet->getActiveSheet()->getHighestRow(); // 마지막 행
$maxColumn = $spreadsheet->getActiveSheet()->getHighestColumn(); // 마지막 열
$target = "A"."2".":"."$maxColumn"."$maxRow";
$lists = $spreadsheet->getActiveSheet()->rangeToArray($target, NULL, TRUE, FALSE);
foreach($lists as $key => $list){
$col = 0;
$list = array(
"A"=>$list[$col++],
"B"=>$list[$col++],
"C"=>$list[$col++],
"D"=>$list[$col++],
"E"=>$list[$col++],
"F"=>$list[$col++],
"G"=>$list[$col++]
);
$data = array();
$data["no"] = $list["A"];
$data["member_info"] = $list["G"];
if(!update_column($data)){throw new Exception("엑셀 업로드 정보 수정에 실패하였습니다.");}
}
unlink($file_path);
}
echo "<script>alert('엑셀 업로드 정보 수정에 성공하였습니다.');history.back();</script>";
exit;
}catch(Exception $e){
echo "<script>alert('".$e->getMessage()."');history.back();</script>";
exit;
}
?>
엑셀의 기타 정보를 입력하고 업로드했을 때 데이터 수정이 잘 되는지 확인한다.
